October 16, 2008 at 3:29 pm
Hi all - I have an ISQL Batch File here:
isql.exe -U SA -P XXXXXX -d DBNAME -S SERVERNAME -n -i vsm_his2he.sql -o log.txt
bcp DBNAME.dbo.hotel_expert_room_registry out out1.txt -f param.txt -S SERVERNAME -U SA -P XXXXXX
net use X: /DELETE
net use X: \\vsmsvr-hexpert\fofile
xcopy out1.txt X:\ /Q /Y
net use X: /DELETE
exit
Basically what this all does is log into my DB and call up a script located in the same directory the batch file is run from called "vsm_his2he.sql". This script creates a temp table and stored procedure and runs the procedure...loads up the table and then the BCP command (above in batch file) rips it out of the table into a tab delimited text file. Then you see the xcopy to put it over on another server in the network...a good ole fashioned "interface".
This obviously does not work on SQL 2005. Can anyone help me translate this so that I can create a SQLCMD batch file? Does OSQL work in SQL2005? I am also curious about my param.txt and whether SQLCMD can use that variable...
let me know!
October 17, 2008 at 5:35 am
jonathanmreynolds (10/16/2008)
Hi all - I have an ISQL Batch File here:
isql.exe -U SA -P XXXXXX -d DBNAME -S SERVERNAME -n -i vsm_his2he.sql -o log.txtbcp DBNAME.dbo.hotel_expert_room_registry out out1.txt -f param.txt -S SERVERNAME -U SA -P XXXXXX
net use X: /DELETE
net use X: \\vsmsvr-hexpert\fofile
xcopy out1.txt X:\ /Q /Y
net use X: /DELETE
exit
Sqlcmd works in very similar ways to osql & isql. You can still call osql. Here's the sqlcmd equivalent of your call. It's not very different at all.
If you really want to have fun, get PowerShell on your system and call the sqlcmd object, bcp, etc., from there. I've been having a blast with it.
sqlcmd.exe -U SA -P XXXXXX -d DBNAME -S SERVERNAME -i vsm_his2he.sql -o log.txt
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 17, 2008 at 7:25 am
thanks Grant...didn't think it would be that easy...so the BCP commands will work just the same then hey?
October 17, 2008 at 8:07 am
The areas I've been looking at are:
http://msdn.microsoft.com/en-us/library/ms144250(SQL.90).aspx
and
http://msdn.microsoft.com/en-us/library/ms180175(SQL.90).aspx
I'm just not sure how this translates the data on the screen.
I tried this, but it didn't work:
select N'????????' COLLATE Chinese_PRC_BIN as china
select cast('????????' as nvarchar(100)) COLLATE Chinese_PRC_BIN as china
Other than that... I'm stuck. Sorry.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply