SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating temp table in Batch file


Creating temp table in Batch file

Author
Message
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13166 Visits: 4077
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;-)
Kruti Kansara
Kruti Kansara
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1022 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"
Florian Reischl
Florian Reischl
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8807 Visits: 3934
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
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13166 Visits: 4077
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
winash
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3104 Visits: 1883
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
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13166 Visits: 4077
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;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search