Creating temp table in Batch file

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Creating temp table in Batch file

Creating temp table in Batch file

Rate Topic

Display Mode

Topic Options

Author

Message

Bhuvnesh

Posted Friday, June 05, 2009 5:21 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #729582

Kruti Kansara

Posted Friday, June 05, 2009 5:38 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 09, 2011 2:49 AM
Points: 536, Visits: 412

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"

Post #729596

Florian Reischl

Posted Friday, June 05, 2009 8:29 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

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

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Post #729768

Bhuvnesh

Posted Monday, June 08, 2009 1:35 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #730517

winash

Posted Monday, June 08, 2009 1:28 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883

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

Post #730937

Bhuvnesh

Posted Monday, June 08, 2009 9:29 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #731089

« Prev Topic | Next Topic »

Permissions