Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating temp table in Batch file Expand / Collapse
Author
Message
Posted Friday, June 5, 2009 5:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #729582
Posted Friday, June 5, 2009 5:38 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 9, 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
Posted Friday, June 5, 2009 8:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, 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
Posted Monday, June 8, 2009 1:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #730517
Posted Monday, June 8, 2009 1:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Monday, June 8, 2009 9:29 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #731089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse