Does permission creating a file dynamically differs with xp_cmdshell and bcp in SQL Server 2000?
-
Thursday, January 26, 2012 3:40 PM
Hi All,
I am trying to create a text file using "bcp" in a local drive for which I has admin rights. But, I couldn't know why it doesn't work for me. However, when I use a xp_cmdshell it does work. Can somebody help me with this?
This does work for me creating a file but couldn't do an empty file with this:
EXEC master..xp_cmdshell 'echo This is a Test Message.>> G:\Customers\Cust_info.txt'
However, running this query gave me the following output without creating any file:
DECLARE @bcpCommand VARCHAR(255) DECLARE @Result INT SET @bcpCommand = 'bcp "" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S ' EXEC @Result = master..xp_cmdshell @bcpCommand
Thanks
Bangaaram
Known is a DROP, Unknown is an OCEAN.- Edited by Bangaaram Thursday, January 26, 2012 3:41 PM
All Replies
-
Thursday, January 26, 2012 3:42 PMModeratorIn your bcp command I don't see any query at all. You need to query some table.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Thursday, January 26, 2012 3:44 PM
In your bcp command I don't see any query at all. You need to query some table.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
Is it not possible to create any empty file using "bcp" ?
Known is a DROP, Unknown is an OCEAN. -
Thursday, January 26, 2012 3:50 PMModeratorSee this http://stackoverflow.com/questions/210201/how-to-create-empty-text-file-from-a-batch-file
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Thursday, January 26, 2012 4:09 PM
See this http://stackoverflow.com/questions/210201/how-to-create-empty-text-file-from-a-batch-file
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
Didn't get that.However, when I use the same bcp query on my SQL Server 2008 R2 it created an empty file. However, its not showing up the same in SQL Server 2000.
Known is a DROP, Unknown is an OCEAN. -
Thursday, January 26, 2012 4:14 PM
You must supply the instance name If it named instance -
DECLARE @bcpCommand VARCHAR(255)
DECLARE @Result INT
SET @bcpCommand = 'bcp "" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S <InstanceNamehere>'
EXEC @Result = master..xp_cmdshell @bcpCommand
http://uk.linkedin.com/in/ramjaddu- Edited by RamJaddu Thursday, January 26, 2012 4:15 PM
-
Thursday, January 26, 2012 4:28 PM
You must supply the instance name If it named instance -
DECLARE @bcpCommand VARCHAR(255)
DECLARE @Result INT
SET @bcpCommand = 'bcp "" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S <InstanceNamehere>'
EXEC @Result = master..xp_cmdshell @bcpCommand
http://uk.linkedin.com/in/ramjaddu
Sorry but How to check my instance name?
Known is a DROP, Unknown is an OCEAN. -
Thursday, January 26, 2012 4:50 PMModerator
Try
SELECT @@SERVERNAME
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Thursday, January 26, 2012 5:00 PM
Try
SELECT @@SERVERNAME
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
I have an idea about this but, thought Server Name & Instance Name would be different. Between, I still get the same error even if I mention the Instance Name.
Known is a DROP, Unknown is an OCEAN. -
Thursday, January 26, 2012 5:29 PM Then pls try this -
DECLARE @bcpCommand VARCHAR(255)
DECLARE @Result INT
SET @bcpCommand = 'bcp "Select '' Name" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S <InstanceNamehere>'
EXEC @Result = master..xp_cmdshell @bcpCommand
http://uk.linkedin.com/in/ramjaddu- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, February 07, 2012 9:51 PM
-
Thursday, January 26, 2012 5:37 PM
Then pls try this -
DECLARE @bcpCommand VARCHAR(255)
DECLARE @Result INT
SET @bcpCommand = 'bcp "Select '' Name" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S <InstanceNamehere>'
EXEC @Result = master..xp_cmdshell @bcpCommand
http://uk.linkedin.com/in/ramjaddu
I got this error when I ran the above query:
Known is a DROP, Unknown is an OCEAN. -
Thursday, January 26, 2012 5:46 PMModerator
change the query to be
select space(1) as Name
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Thursday, January 26, 2012 7:12 PM
change the query to be
select space(1) as Name
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
It created the file giving following output too. However, it has writtern 1 in the file. I could do the same using xp_cmdshell echo command. But, all I wanted to do is create an empty file.
Known is a DROP, Unknown is an OCEAN. -
Thursday, January 26, 2012 7:15 PMModerator
It should not put 1 in the file. It should put a space there. Did you check the file? Also, you can add where 1=0 to the query.
Finally, what was wrong with the link I gave you before as how to create empty file? Why do you need to use bcp if all you need is to create an empty file?
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, February 07, 2012 9:51 PM
-
Thursday, January 26, 2012 7:29 PM
It should not put 1 in the file. It should put a space there. Did you check the file? Also, you can add where 1=0 to the query.
Finally, what was wrong with the link I gave you before as how to create empty file? Why do you need to use bcp if all you need is to create an empty file?
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
Sorry I ran the wrong script (Select 1 instead of Select(1)) earlier. Now it created an empty file with a space which is fine.Coming to the link you've mentioned, I couldy barely understand it. Thats the reason I've asked again.
Thanks
Bangaaram
Known is a DROP, Unknown is an OCEAN. -
Thursday, January 26, 2012 7:32 PMModerator
echo ' '
should work. That link was showing some other methods, such as copy null to textfile.txt I haven't looked too close myself or try them, but I think you can use something simple, no need for bcp for your case.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Thursday, January 26, 2012 8:15 PM
echo ' '
should work. That link was showing some other methods, such as copy null to textfile.txt I haven't looked too close myself or try them, but I think you can use something simple, no need for bcp for your case.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
EXEC maste..xp_cmdshell 'echo ' ' >>G:\Customers\Cust_info.txt' gave me an error sayingLine 1: Incorrect syntax near ' >>G:\Customer\Cust_info.txt'.
However, running EXEC maste..xp_cmdshell 'echo ' ' >>G:\Customers\Cust_info.txt' created a text file with a ' in it. Its fine if it do so, but, later I copy some windows commands to this file and rename it as a batch file in order to execute it. Thats the reason I wanted it to create as an empty file as the data which is already been written cannot be overwritten when I use this command.
Known is a DROP, Unknown is an OCEAN. -
Thursday, February 02, 2012 6:36 PMModerator
Any progress?
Consider using the very excellent SSIS Import/Export Wizard:
http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/
Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, February 07, 2012 9:51 PM
-
Wednesday, February 29, 2012 7:19 PM
Any progress?
Consider using the very excellent SSIS Import/Export Wizard:
http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/
Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
No. I should do it only using T-SQL as I am using SQL Server 2000. Please help me with T-SQL for this.Known is a DROP, Unknown is an OCEAN.
.png)
