Database

ais0711-Dec-08 23:48

ais07

11-Dec-08 23:48

Yes.....

Vimalsoft(Pty) Ltd12-Dec-08 1:10

Vimalsoft(Pty) Ltd

12-Dec-08 1:10

Show us the Whole Code

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Ashfield12-Dec-08 1:21

Ashfield

12-Dec-08 1:21

You cannot do a select from a variable unless the varaible is a table data type. The same applies to @ColumnName.

Bob
Ashfield Consultants Ltd

Can i insert bulk data to remote sqlserver

Mycroft Holmes12-Dec-08 22:28

Mycroft Holmes

12-Dec-08 22:28

This query is totally screwed.

You cannot do the following

ArifShaikh wrote:
FROM [@TableName]

There is not such table as @TableName and SQL cannot evaluate the variable in the from clause.

You also cannot do the following

ArifShaikh wrote:
WHERE @PrimaryColumn=@PrimaryValue

SQL does not evaluate the variable.

You are going to have to think up a completely different method for logging.

Never underestimate the power of human stupidity
RAH

snehasish11-Dec-08 6:04

11-Dec-08 6:04

hi,
My problem is that when i want to insert bulk data to my local sqlserver database it execute successfully.the code is:

insert into xmlproperty select * from openrowset(bulk 'C:\celtic\App_Data\PW_XMLfeed_15224.xml',single_clob) as XmlColumn

But when i insert same in remote database it generate error:

Msg 4834, Level 16, State 1, Line 1
You do not have permission to use the bulk load statement.

My question is can i insert bulk data to sqlserver like this way.if not, how can i insert?
wating for reply.
Thanks
snehasish

Wendelius11-Dec-08 6:16

11-Dec-08 6:16

I think you need ADMINISTER BULK OPERATIONS server privilege.

The need to optimize rises from a bad design.My articles[^]

snehasish11-Dec-08 6:42

11-Dec-08 6:42

i am using share hosting so sqlserver and xml file is in different places.can i insert this xml data to remote sql server using bulk copy?Does both database and file need to remain in same server?

Wendelius11-Dec-08 7:14

11-Dec-08 7:14

snehasish wrote:
can i insert this xml data to remote sql server using bulk copy

Yes you can but the SQL Server must have access to the file. Most likely you need to use domain credentials for the account under which the SQL Server is executed and make sure that this account has access to the file via network. Also use UNC-paths to specify the location.

snehasish wrote:
Does both database and file need to remain in same server

As I described they don't have to, but it makes things a whole lot easier.

The need to optimize rises from a bad design.My articles[^]

snehasish11-Dec-08 8:10

11-Dec-08 8:10

thanks.i will talk with hosting company according to your advice.

Wendelius11-Dec-08 8:41

11-Dec-08 8:41

snehasish wrote:
thanks

No problem Smile | :)

If for some reason it becomes a problem to get access from host to the client, you always have other options. For example:
- you could create a stored procedure which takes the xml-data as a parameter. After that you load the data into the xmlproperty-table inside the procedure (not necessarily using file in this case).
- you could use ftp or something else to upload the file to the database server (or near it) and after that you use openrowset from the ftp target location
- if you're using .Net framework at client side, you could use SqlBulkCopy class at the client to load the data to the table etc.

The need to optimize rises from a bad design.My articles[^]

snehasish11-Dec-08 20:37

11-Dec-08 20:37

hi,
Many thanks for giving possible soln. i have tried this one:

........
ds.readxml("http://www.myweb.com/xyz.xml")
 Dim xmldoc As New XmlDataDocument(ds)
        Dim xd As String = xmldoc.OuterXml()          
        Dim con As New SqlConnection(constr)
        Dim sqlstr As String = "insert into xmlproperty values( convert (xml," & xd & " ))"
        Dim com As New SqlCommand(sqlstr, con)
        con.Open()
        Dim i As Integer = com.ExecuteNonQuery()
        con.Close()
.........

first i fill dataset by readxml() method.Then i take xml in variable xd.then i am trying to insert this xml to xmlproperty table.But i get an error:

Incorrect syntax near '<'. The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure. asp.net

how to fix this error.
Thanks.

snehasish12-Dec-08 1:50

12-Dec-08 1:50

hi,
i still getting sql error for this code:

........
ds.readxml("http://www.myweb.com/xyz.xml")
Dim xmldoc As New XmlDataDocument(ds)
Dim xd As String = xmldoc.OuterXml()
Dim con As New SqlConnection(constr)
Dim sqlstr As String = "insert into xmlproperty values( convert (xml," & xd & " ))"
Dim com As New SqlCommand(sqlstr, con)
con.Open()
Dim i As Integer = com.ExecuteNonQuery()
con.Close()
.........

Error:Incorrect syntax near '<'. The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure.

Any help me..

Thanks
Snehasish Frown | :(

Wendelius12-Dec-08 3:15

12-Dec-08 3:15

snehasish wrote:
Dim sqlstr As String = "insert into xmlproperty values( convert (xml," & xd & " ))"

snehasish wrote:
Incorrect syntax near '<'. The label 'http' has already been declared

Don't concatenate the xml to the SQL statement. Instead use parameters (see: SqlParameter[^]).

The need to optimize rises from a bad design.My articles[^]

snehasish15-Dec-08 10:42

15-Dec-08 10:42

Hi;
For some reason i had to go to out of station.According to your suggestion i have solved this.I think you are great.All of your given possible soln is very good.Thank you again.Thanks a lot. Smile | :)

this is my code in vb.net:

Dim ds As New DataSet
        ds.ReadXml("http://www.myweb.com/abc.xml")
        Dim doc As New XmlDataDocument(ds)       
        Dim con As New SqlConnection("Server=x.x.x.x;Database=celtic;User ID=xxxx;Password=xxxx;Trusted_Connection=False")
        Dim com As New SqlCommand("sp_InsertXmlData", con)
        com.CommandType = CommandType.StoredProcedure
        com.Parameters.Add("@XMLDOC", SqlDbType.Text).Value = doc.OuterXml
        con.Open()
        Dim i As Integer = com.ExecuteNonQuery()
        con.Close()

This is stored procedure:

Create PROCEDURE [dbo].[sp_InsertXmlData]
	@XMLDOC text
AS
BEGIN	
	insert into xml_properties(xmldoc)
	values(@XMLDOC)
END

snehasish

Wendelius16-Dec-08 6:11

Struggling with using xp_cmdshell in stored procedure

16-Dec-08 6:11

Thanks for your kind words Blush | :O

The need to optimize rises from a bad design.My articles[^]

dan!sh 11-Dec-08 4:45

Re: Struggling with using xp_cmdshell in stored procedure

11-Dec-08 4:45

I am using following command:

exec master..xp_cmdshell 'bcp "select * from cab.useraudit" queryout "c:\test.xls" -c -T'

On execution I am getting following error:

Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to
 the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the
 default settings SQL Server does not allow remote connections

I have configured the server to allow remote connections. What might be wrong?

C isn't that hard: void (*(*f[])())() defines f as an array of unspecified size, of pointers to functions that return pointers to functions that return void

"Always program as if the person who will be maintaining your program is a violent psychopath that knows where you live." - Martin Golding

Parwej Ahamad11-Dec-08 5:14

Re: Struggling with using xp_cmdshell in stored procedure

11-Dec-08 5:14

Are you sure to done settings ?

Please refer below URL:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277[^]

Parwej Ahamad
♫ Strength lies in differences, not in similarities.♫

dan!sh 11-Dec-08 5:16

Re: Struggling with using xp_cmdshell in stored procedure

11-Dec-08 5:16

Yes I am. I doubted myself and reconfigured everything. Still no help. I am running this on Vista. Is that the problem?

Parwej Ahamad11-Dec-08 5:24

Re: Struggling with using xp_cmdshell in stored procedure

11-Dec-08 5:24

I have not tried on Vista. Well did you tried with -U (Userid) and -P (password) switches for bcp command ?

Parwej Ahamad

Parwej Ahamad11-Dec-08 5:38

Re: Struggling with using xp_cmdshell in stored procedure

11-Dec-08 5:38

It may be firewall issue.

Parwej Ahamad

Wendelius11-Dec-08 5:25

Re: Struggling with using xp_cmdshell in stored procedure

11-Dec-08 5:25

If your instance isn't the default instance, you must specify it. Try adding to the end:

-S.\instancename

The need to optimize rises from a bad design.My articles[^]

dan!sh 11-Dec-08 5:27

Re: Struggling with using xp_cmdshell in stored procedure

11-Dec-08 5:27

I have just the one instance running. Now my belief is getting stronger. Its vista thats playing evil.

Wendelius11-Dec-08 5:29

Re: Struggling with using xp_cmdshell in stored procedure

11-Dec-08 5:29

d@nish wrote:
Its vista thats playing evil

I'm not so sure about it. I've got Vista and just tested your problem. Nothing wrong...

Even though you only have one instance it's not necessarily configured as the default. It still may be a named instance. Give it a try. At least if not working it eliminates one possibility.

The need to optimize rises from a bad design.My articles[^]

dan!sh 11-Dec-08 5:41

Re: Struggling with using xp_cmdshell in stored procedure

11-Dec-08 5:41

Thanks Mika. I have sort of progressed. Now this is what I have done:

exec master..xp_cmdshell 'bcp "select * from cab.useraudit" queryout "c:\test.xls" -S "MACHINE\SQLEXPRESS" -U "uid" -P "pwd"'

Now I am getting this error:

Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'Machine\Danish'. 
The user is not associated with a trusted SQL Server connection.

How to deal with this?

Please bear. This is the first time I am writing a decent stored procedure.

Parwej Ahamad11-Dec-08 5:43