Creating temp table in Batch file

Question

Creating temp table in Batch file

Bhuvnesh

SSC Guru

Points: 59351
More actions
June 5, 2009 at 5:21 am

#210669

hi,
Below is the code which i m using in SQL script.
This is basically a batch file
With the help of this file , i need to add all directories name into one temp table
SQL CODE : insert into tmp exec xp_cmdshell 'DIR c:\'
Can i anybody told me that how can i create temp table and use above query.
please add your code in below ("---------''INSERT CODE HERE"------)
Thanks
--------------------------------------------------------------------------------
ECHO OFF
REM Author: Bhuvnesh Kumar
REM Date: June 5, 2009
REM Usage: FolderCheck [share_map_drive] [FolderName] [share_unc] [share_user] [share_pwd]
ECHO ON
net use %1
IF errorlevel 2 IF NOT errorlevel 1 GOTO FolderCheck
IF errorlevel 0 GOTO UnmapDrive
:UnmapDrive
net use %1 /delete
:FolderCheck
net use %1 %3 %5 /USER:%4
-----------''INSERT CODE HERE"---------------------------
net use %1 /delete
:End
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 1

Sorry, but i couldnt get your problme clearly.

Though, as per i understood, you have one batch file, say mybat.bat on c:\, which will give list of dirs. Now, you are going to execute this batch file from sql server and try to insert result into a temp table, as

insert into tmp exec xp_cmdshell 'DIR c:\'

If so, then you need to be clear about the output structure of your .bat file, and accordingly create temp table before executing above sql statement.

OR

1) change you .bat file and create output with comma saprated values.

2) store this result into .csv file

3) Try to import data from this .csv file into new(temp) table.

"Don't limit your challenges, challenge your limits"

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

I'm not sure if a temp table is the correct way to go in this case. It will be removed immediately after your batch file disconnects from database.

I would use a state-full table. Here a little sample how to call SQLCMD to create a table and insert "dir C:\"

sqlcmd -S .\Sql2k8 -E -Q "CREATE TABLE MyCmd (Line VARCHAR(512)) INSERT INTO MyCmd EXECUTE xp_cmdshell 'dir C:\'" -d Sandbox

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

hi

below is rthe script , i m using for BATCH script

---------------------------------------------------------------

ECHO OFF

REM Author: Bhuvnesh Kumar

REM Date: June 5, 2009

REM Usage: FolderCheck [share_map_drive] [FolderName] [share_unc] [share_user] [share_pwd]

ECHO ON

net use %1

IF errorlevel 2 IF NOT errorlevel 1 GOTO FolderCheck

IF errorlevel 0 GOTO UnmapDrive

:UnmapDrive

net use %1 /delete

5:33 PM 6/5/2009

:FolderCheck

net use %1 %3 %5 /USER:%4

Sqlcmd –St2DBA03\report –E –Q "insert into tmp exec xp_cmdshell 'dir R:'"

net use %1 /delete

:End

------------------------------------------------------------------------------------

the line in BOLD , contains sql code which is giving error

Error:

---------------------------------------------------------------------

C:\WINDOWS\system32>Sqlcmd ûSt2DBA03\report ûE ûQ "insert into tmp exec xp_cmdshell 'dir R:'"

Sqlcmd: 'ûSt2DBA03\report': Unknown Option. Enter '-?' for help.

---------------------------------------------------------------------

can somebody resolve it

or give me alternative code for it

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

winash SSChampion Points: 11446 More actions · Answer 4

Looks like some sort of connectivity issue...

a) Is SQL Browser enabled?

b) Are remote connections enabled (in SQL 2005 this is done via Surface Area Configuration Manager)

c) What happens when you excute the following at command prompt

Sqlcmd –S t2DBA03\report –E

Do you get a "1>" on the command prompt?

And I'd recommend you use the -d switch (as given in the example) to connect to the appropriate database that has the "tmp" table created in it

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Thanks to all for your efforts

i went with the slightly different approach .

i made a sql file and execute it

Thanks to ALL

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)