Re: Xml BulkLoad - Database Discussion Boards

Re: what is wrong with the code

Blue_Boy3-Sep-07 4:19

Blue_Boy

3-Sep-07 4:19

prasadbuddhika wrote:
sc.CommandText = "create login " + textBox6.Text +
" with password ='" + textBox7.Text + "' ";

try to replace with

<br />
sc.CommandText = "create login " + textBox6.Text +<br />
" with [password] ='" + textBox7.Text + "' ";

I Love SQL

Re: what is wrong with the code

Paddy Boyd3-Sep-07 4:26

Paddy Boyd

3-Sep-07 4:26

What is wrong with this code is the huge potential for someone to inject SQL into your database. You should look at paramterising this.

Re: what is wrong with the code

Pete O'Hanlon3-Sep-07 5:06

Pete O'Hanlon

3-Sep-07 5:06

Apart from leaving you wide open to SQL Injection attacks, the variable names are really bad. What would you expect if I swapped textBox46 and textBox2 around here? How could you tell when looking at the code?

Deja View - the feeling that you've seen this post before.

Re: what is wrong with the code

Colin Angus Mackay4-Sep-07 0:52

Colin Angus Mackay

4-Sep-07 0:52

The code is practially unreadable! You have left yourself open to SQL Injection Attacks so you might want to find out how to prevent SQL Injection Attacks[^].

Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ...

My website

Xml BulkLoad

Panchal Hardik3-Sep-07 0:54

Panchal Hardik

3-Sep-07 0:54

Hi
I am getting following error on connection string in xmlbulkload:

"QueryInterface for interface SQLXMLBULKLOADLib.ISQLXMLBulkLoad failed."

Thanks

Hardik Panchal

Re: Xml BulkLoad

Panchal Hardik3-Sep-07 2:39

Panchal Hardik

3-Sep-07 2:39

I got the solution.
thanks

Hardik Panchal

Re: Xml BulkLoad

Pete O'Hanlon3-Sep-07 2:51

Pete O'Hanlon

3-Sep-07 2:51

Now, if you post your solution to your problem, I'll give it a 5 vote.

Deja View - the feeling that you've seen this post before.

Re: Xml BulkLoad

Panchal Hardik3-Sep-07 2:58

Panchal Hardik

3-Sep-07 2:58

I create the one thread and call my bulk upload method from that thread.
which is work for me. before i not use the thread which is not working in my new application but working in other application.

Here is the code.
Public objBulkthread As System.Threading.Thread
If Not IsNothing(objBulkthread) Then
If Not objBulkthread.IsAlive Then
objBulkthread = New System.Threading.Thread(AddressOf objGeneral.UploadXmlBulk)
objBulkthread.ApartmentState = Threading.ApartmentState.STA
objBulkthread.Start()
End If
Else
objBulkthread = New System.Threading.Thread(AddressOf objGeneral.UploadXmlBulk)
objBulkthread.ApartmentState = Threading.ApartmentState.STA
objBulkthread.Start()
End If

Thanks

Hardik Panchal

Combine result

RichardBlare3-Sep-07 0:39

RichardBlare

3-Sep-07 0:39

Hi, would you please help how to solve it ?
The following is my sql query.

1.
Select RegionCode, count(CountryCode)
From Country
Where RegionCode ='NorthAmerica'
Group by RegionCode

2.
Select RegionCode, count(CountryCode)
From Country
Where RegionCode ='SouthAmerica'
Group by RegionCode

First query return like this

NorthAmerica 5

Second query return like this

SouthAmerica 2

If I use UNION, it combine as rows

NorthAmerica 5
SouthAmerica 2

What I want is

NorthAmerica SouthAmerica
5 2

How can I combine the results ?

Thanks in advance,

RB

Re: Combine result

Pete O'Hanlon3-Sep-07 0:56

Pete O'Hanlon

3-Sep-07 0:56

If you are using SQL Server 2005, take a look at the Pivot command.

Deja View - the feeling that you've seen this post before.

Re: Combine result

RichardBlare3-Sep-07 23:19

RichardBlare

3-Sep-07 23:19

Thanks for your help Pete.
But, I don't know how to use PIVOT to get my result.
Would you please show me some good samples ?

Thanks,

Help me!

chanzeb2-Sep-07 21:01

chanzeb

2-Sep-07 21:01

Hi everybody,
I want to retrieve the databse schema.database is in sql server2005.The language is c#.please send me some reading related meterial.

Regards,
chanzeb chaudhary.
chanzeb_chaudhary@hotmail.com

Re: Help me!

Pete O'Hanlon2-Sep-07 21:44

Pete O'Hanlon

2-Sep-07 21:44

Here[^] you go. This is a database agnostic way of retrieving information about database schemas.

Deja View - the feeling that you've seen this post before.

Re: Help me!

chanzeb3-Sep-07 0:15

chanzeb

3-Sep-07 0:15

thanks alot. Smile | :)

.....

Get databases

Syed Mujtaba Hassan2-Sep-07 20:15

Syed Mujtaba Hassan

2-Sep-07 20:15

Hi there,

I am new to sql server 2005 can somebody tell me is there any sql command that returns all databases hosted on SQL server 2005.

Mujtaba

"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."

Re: Get databases

John-ph2-Sep-07 20:43

John-ph

2-Sep-07 20:43

sp_databases - This [system stroed procedure] return's all databases that reside in an instance of Microsoft SQL Server.

Regards
J O H N

"Even eagles need a push." David McNally

Re: Get databases

shivvish2-Sep-07 21:12

shivvish

2-Sep-07 21:12

hi
this is available in sql 2000 also

sp_helpdb

get back for more info.

Re: Get databases

Syed Mujtaba Hassan2-Sep-07 22:42

Syed Mujtaba Hassan

2-Sep-07 22:42

OK thanx buddies. So nice of u.

prasadbuddhika2-Sep-07 17:50

prasadbuddhika

2-Sep-07 17:50

through my application i craete a login int the sql server(express) and then i created a user using login create in the above .. in the sql server it shows the login and user i created. but when login into sql server using the created (above) user name and password it says

" login failed for user 'user01'.The user is not associated with a trusted sql server connection. Microsoft sql server ,Error 10452.

could anyone explain me the reason and the solution.. Smile | :)

Re: login problem in sql server express

M LN Rao2-Sep-07 21:32

M LN Rao

2-Sep-07 21:32

Please check the credentials given in the connectionString of web.config

Intelligence is measured by common sense not by how many scholarly books you read.

Re: login problem in sql server express

John Gathogo3-Sep-07 20:28

John Gathogo

3-Sep-07 20:28

The problem is related to the fact that when you installed SQLEXPRESS, it only enabled Windows Authentication. What you need to do is to enable both Windows and SQL Authentication modes. If you have a tool like Management Studio, just login to the server with Windows Authentication, then right-click on the SQLEXPRESS instance. From the menu that appears click Properties. You will find a number of Server Properties, select the Security page. From this page change Server Authentication to 'SQL Server and Windows Authentication mode'. Click OK. That should solve the problem you are experiencing.

SQL Order By in paging algorithim

Polymorpher2-Sep-07 11:50

Polymorpher

2-Sep-07 11:50

ok, here is the code:

Private Sub CreateProcedure(ByVal con As SqlClient.SqlConnection)
Dim curRowIndex As Integer = ((GetCurPageIndex() * RowsPerPage) + 1)
If curRowIndex > GetTotalRowCount() Then
mCurPageIndex = NumPagesAvailable()
curRowIndex = GetTotalRowCount()
End If

Dim createProcedureSQL As String = "CREATE PROCEDURE [dbo].[PageResultsProcedure]"
createProcedureSQL &= "AS" & vbCr
createProcedureSQL &= "DECLARE @FirstID INT, @StartRow INT" & vbCr
createProcedureSQL &= "SET ROWCOUNT " & curRowIndex & vbCr
createProcedureSQL &= "SELECT @FirstID = " & TablePKName & " FROM " & TableName & " " & WhereSegment & vbCr
createProcedureSQL &= "SET ROWCOUNT " & RowsPerPage & vbCr
createProcedureSQL &= "SELECT " & ColumnsToReturn & " FROM " & TableName & CStr(IIf(WhereSegment = "", " WHERE ", " " & WhereSegment & " AND ")) & TablePKName & " >= @FirstID" & vbCr
createProcedureSQL &= "ORDER BY " & TablePKName & vbCr
createProcedureSQL &= "SET ROWCOUNT 0"

Dim cmd As SqlClient.SqlCommand

cmd = con.CreateCommand
cmd.CommandText = createProcedureSQL
con.Open()
cmd.ExecuteNonQuery()
con.Close()
cmd.Dispose()
cmd = Nothing
End Sub

This code is part of a procedure to allow paging a data table...the question is; I need a way of doing this, but being able to sort the results by a given column that is not guaranteed to have unique values in the table...Any Ideas? Thanks in advance for any help.

--
"Keyboard not found. Press < F1 > to RESUME. "
Source unknown (appears in many common BIOSes as a real error message)

Re: SQL Order By in paging algorithim

Colin Angus Mackay2-Sep-07 12:04

Colin Angus Mackay

2-Sep-07 12:04

Umm... Wow!

There are a number of things that are wrong with this before we even get to solving the problem you posted about.

1. Why are you creating a stored procedure like this?

2. Your code is vulnerable to a SQL Injection attack.

3. The use of SET ROWCOUNT should be discouraged because it will affect other queries that happen to be running at the same time. Use SELECT TOP n instead (where n is the number of rows you want)

If you are running SQL Server 2005 the answer is quite simple as there is some new functionality built in to address this problem. If you are running SQL Server 2000 then it is workable, but the query will be more complex. Which version of SQL Server are you using?

Re: SQL Order By in paging algorithim

Polymorpher2-Sep-07 15:50

Polymorpher

2-Sep-07 15:50

I'm using SQL Server 2000, and the code wont actually be building the query in the long run, this is just basically a proof of concept and is like this for the time being so that I can play with it in a designer I coded...SET ROWCOUNT is set back to 0 at the end to restore it for latter query's. An explanation of why I'm using SET ROWCOUNT can be found here:

http://www.4guysfromrolla.com/webtech/042606-1.shtml[^]

It is a fairly efficient way of doing this...

I'm only trying to find a way to incorporate an order by into this...

--
"Keyboard not found. Press < F1 > to RESUME. "
Source unknown (appears in many common BIOSes as a real error message)

Re: SQL Order By in paging algorithim

Colin Angus Mackay2-Sep-07 22:54

Colin Angus Mackay

2-Sep-07 22:54

Polymorpher wrote:
SET ROWCOUNT is set back to 0 at the end to restore it for latter query's. An explanation of why I'm using SET ROWCOUNT can be found here:

http://www.4guysfromrolla.com/webtech/042606-1.shtml[^]

It is a fairly efficient way of doing this...

Maybe, but it is also a fairly dangerous thing to do. SET ROWCOUNT will affect other queries running at the same time. If you are never going to have other users then this is fine. But in that case why are you using something like SQL Server.

As for incorportating an order. Use subquerys - If you expect to be paging through lots of data then you may wish to use a temp table for the innermost SELECT. Also, remember to only use the columns you actually need, this will help the performance of the query rather than the demonstration SELECT * I've used here.

First lets, say you want rows 76 to 100 (25 rows in total). Get the first 100 rows

SELECT TOP 100 * 
FROM MyTable 
ORDER BY OrderColumn

Then Reverse the order of the result set getting only 25 rows

SELECT TOP 25 * 
FROM (SELECT TOP 100 * 
      FROM MyTable 
      ORDER BY OrderColumn)
ORDER BY OrderColumn DESC

Finally, flip the order back again:

SELECT *
FROM (SELECT TOP 25 *
      FROM (SELECT TOP 100 * 
            FROM MyTable 
            ORDER BY OrderColumn)
      ORDER BY OrderColumn DESC)
ORDER BY OrderColumn

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.