.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

:XML ON oprion in SQLCMD

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
 

Where do I place the XML: ON command in the sqlcmd string to get it to work. So far I have been fruitless.

 

I am trying to export out the single nVarChar(MAX) column from the table listed. It is over 8000 chars long and am

told by developers it could generate a file upwards of 50MB.

 

SO I figured the :XML sqlcmd command may do the trick.

 

If someone knows of a better way, please drop that to me also.

 

Here is the code I just attempted:


CODE===============

 

EXEC Master..xp_CmdShell 'sqlcmd  -S(local) -E -Q":XML ON SET NOCOUNT ON SELECT * FROM MyDB.dbo.MyTable" -

oC:\MyFile.txt -h-1'

 

CODE===============

 

OK, a bit of history here. The column we are pulling from is set as a nVarChar(MAX).

 

The column is populated by another SP that dynamically builds an XML string. Some of the XML strings that are built

can become very large (from our dev team, they can generate a file nearing 50MB in some cases.

 

Now the XML string that was dynamically build and which is contained in the nVarChar(MAX) field looks GREAT!

 

I am able to use sqlcmd to export it to a text


View Complete Post


More Related Resource Links

SQLCMD: supress "Changed database context to . . ."

  
Product:  2008 SQL Server Standard Edition. Shell:     SQLCMD (i.e. sqlcmd -i"test.sql")Script (test.sql):   use Master;   print $(SQLCMDERRORLEVEL)The screen output is always:   Changed database context to 'Master'   0However I want the above output to be (only):   0I have tried calling SQLCMD with the -m command line parameter set to each of the {-m-1, -m 0, -m 1} settings, but none work. I have tried: set SQLCMDERRORLEVEL = each of {-1,0,1,25), but none workAnybody know the secret?Thanks in advance,Jerry

SQLCMD: Changed Database Context messages

  
Is there a way to suppress the "Changed Database Context to " messages in SQLCMD?  For example, I get the following: 1> use master2> goChanged database context to 'master'.1>I am trying to migrate some legacy batch files that called isql and used the output files.  These extra "Changed Database Context to " messages are causing our scripts to break, and I was hoping to avoid having to write something extra to handle these messages.Thanks.

sqlcmd, cannot connect to server

  
I have SQL Server installed on a machine called SQLDEV. I log on to that machine and I run the command sqlcmd -s SQLDEV -Q "select * from <db>.<schema>.<some_table> -E This command works and returns a result set.   Next, I go to a different machine. On that machine I can use SQL Mgmt Studio to log on to SQLDEV using window authentication. However, if I run the same command as above, I get some kind of a connection error. The error says, ".... When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections .....   Any suggestions/ideas why I am getting this error?

messages from sqlcmd

  
Would someone please explain this behavior? I'm trying to understand why the messages are coming back the way they are in the following examples. Create a simple table to which we will be inserting some values. create table test_table ( col1 int ) Next, create a sql script that inserts a bunch of integers to the above table. insert into test_table values(1) insert into test_table_ values(2) insert into test_table_ values(3) insert into test_table_ values(4) And save this file. I saved it as c:\dba\admin\test_script.sql Next, run this script from a command window using sqlcmd as follows. sqlcmd -S <your_server> -d <your_db> -i c:\dba\admin\test_script.sql   What message do you get back in the command window? I get "(1 rows affected)" for the first insert statement in the script, but nothing about the other 3 inserts. However, when I select from the table, I do see that all 4 insert statments happened. So, why am I seeing an incomplete message? Now, for the heck of it, I put a "GO" between the insert statements in test_script.sql like so. insert into test_table values(1) go insert into test_table values(2) go insert into test_table values(3) go insert into test_table values(4) Now, run the sqlcmd again like so, sqlcmd -S <your_server> -d <your_db> -i c:\dba\admin\test_script.sql What messages do you get? I get 4 lines of

Can connect via SSMS but not SQLCMD/OSQL

  
I can connect to my SQL 2008 Express Edition server using SSMS.  I cannot connect using OSQL or SQLCMD and my application is having sporadic connection issues.  When I attempt to connect via OSQL, the following error is returned: Error: 18452, Severity: 14, State: 1.  Login failed.  The login is from an untrusted domain and cannot be used with windows authentication. The following are errors present in the SQL errorlog:  Error: 17806, Severity: 20, State: 2.  SSPI handshake failed with error code 0x8009030c while establishing a connection iwth integrated security; the connection has been closed. [CLIENT: <local machine>] Error: 18452, Severity: 14, State: 1.  Login failed.  The login is from an untrusted domain and cannot be used with windows authentication. I see an error in the application event log: Event 6037, LsaSrv  The program sqlservr.exe, with the assigned process ID 1788, could not authenticate locally by using the target name MSSQLSvc/{srvnm}.{workgrp}.net:{SQLinstancenm}.  The target name used is not valid.  A target anme should refer to one of the local computer names, for example, the DNS host name.  Try a different target name. Has anyone else experienced this issue - what else can I look into?      

sqlcmd - linkedserver - ALL queries fails after FIRST error from remote server - ANONYMOUS LOGON

  
Hello, I have very strange problem with sqlcmd , it stop working after first error returned from remote sql server (like permission denied), lets try show what I'm doing, configuration is quite simple C- client (windows 2003 x64, standalone sqlcmd client sp3) S1 - server1(windows 2003 x64, sql 2005 sp3) S2 - server2 (windows 2003 x64, sql 2005 sp3) I need connect via middle Linked Server (S1) from C to to S2, each machines have the same LOCAL Windows account C\LSUser, S1\LSUser, S2\LSUser with the same password, LSUser is not domain account, linked server is configured for Windows Account Impersonation (checked Impersonate) Example, query executed from client C: 1>exec [LS1].[DB].[dbo ].[ListTab1]; - works well, no problem 1>exec [LS1].[DB].[dbo ].[ListTab2]; - works well, no problem ok , now try modify permission on S2, I explicitly deny execute procedure [DB].[dbo ].[ListTab1] on S2 to S2\LSUser, 1>exec [LS1].[DB].[dbo ].[ListTab1]; Msg 229, Level 14, State 5, Server S2, Procedure ListTab1, Line 1 The EXECUTE permission was denied on the object 'ListTab1', database 'DB', schema 'dbo'. expected message :), but look now: 1> exec [LS1].[DB].[dbo ].[ListTab2]; Msg 18456, Level 14, State 1, Server S2, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. all remote queries STOP working from client C 1> select top 10 * from [L

VBNET2008 SQLCMD.Parameters

  
Hi Good Guys, I need your help with sample coding. Please help me. I am trying to insert a new record into SQL SERVER 2000 TBLSales table using SQL Command Parameters but encounter this error message: Incorrect syntax near the Keyword 'Where' This coding that generate the above error message    strSqlInsert &= "  Where  (SalesID <> @SalesId ) " ------------------------------------------------------------------ Here is the over all Coding that I need your help. Private Sub FSaveNewData() Dim strSqlInsert As String = Nothing Try sqlconn = New SqlConnection(connstr) sqlconn.Open() strSqlInsert &= " Insert into TblSales ( SalesID, CustomerName, SaleQty, SalePrice ) " strSqlInsert &= " Values ( @SalesID, @CustName, @SalesQty, @SalesPrice ) " strSqlInsert &= " Where (SalesID <> @SalesId ) " sqlcmd = New SqlCommand(strSqlInsert, sqlconn) With sqlcmd.Parameters .AddWithValue("@SalesId", SqlDbType.Int).Value = Convert.ToInt32(intSalesId) .AddWithValue("@CustName", SqlDbType.NVarChar).Value = CType(Me.txtCustomerName.Text, String) .AddWithValue("@SalesQty", SqlDbType.Int).Value = Convert.ToInt32(Ctype(me.txtSalesQty.text, integer) .AddWithValue("@SalePrice", SqlDbType.Money).Value = Convert.ToDouble(CType(Me.txtSalesPric

SQLCMD results in a hexadecimal value for a SELECT FORXML AUTO

  
Hi,
I have a stored procedure, that uses FORXML AUTO to reutrn an XML document.
When I call it from SQLCMD, it results in a hexadecimal value (0x....) instead of an XML text.
When I used to call it from OSQL, it resulted in a correct XML text.
When I call it from SQL Mgmt Studi or Query Analyser, the reult is also correct, an XML text.

The way I call SQLCMD is:
sqlcmd -E -S"SQLCLU01\INST01" -d"SFA" -Q"exec ReportGetDataAsXML 6760910"

The result is:
0x44085200650070006F0...

Any suggestions?
BalintN

exec sqlcmd and bat files in a single bat fiel . How can I do it?

  

Hi all

I cannot seem to work out how to put it together I get all sorts of errors

 I would like to create a single bat file that execute 3 bat files and runs 10 sql scripts.

Any examples? help thanks a lot


Thanks for your help

Sql Server Agent failed to executed a scheduled bat file that contains sqlcmd command

  

Hi All:

I am currently using Windows 2003 R2, Standard Edition.
I have Microsoft Sql Server 2005 Installed with the latest service pack which is sp3.
I logged into the operating system as an administrator. The windows administrator id is "test2".
I logged into the Microsoft Sql Server 2005 database using "Microsoft Sql Server Management Studio" as an "sa" which is database admin.
I placed a bat file called "test1.bat" in the directory c:\test3, the file "test1.bat" contains the following content:

sqlcmd -b -e -Q "ALTER INDEX ALL ON [JOHNDATABASE].dbo.TABLE2 REBUILD"

In the dos-prompt, I can run the "test1.bat" batch file with no errors.
It gives me the following output:
c:\test3\sqlcmd -b -e -Q "ALTER INDEX ALL ON [JOHNDATABASE].dbo.TABLE2 REBUILD"
ALTER INDEX ALL ON [JOHNDATABASE].dbo.TABLE2 REBUILD

Using "Sql Server Agent", I created a new job called "Scripts1".
For the "General" link, I have the following values:
Name:test2
Owner:SA
Category:Database Maintenance

For the step link, I have the following values:
Step Name:test2
Type:Operating system(CmdExec)
Run As:SQL Agent Service Account
Command:
sqlcmd -b -e -Q "ALTER INDEX ALL ON [JOHNDATABASE]

Sql Server Agent failed to executed a scheduled bat file that contains sqlcmd command

  

Hi All:

I am currently using Windows 2003 R2, Standard Edition.
I have Microsoft Sql Server 2005 Installed with the latest service pack which is sp3.
I logged into the operating system as an administrator. The windows administrator id is "test2".
I logged into the Microsoft Sql Server 2005 database using "Microsoft Sql Server Management Studio" as an "sa" which is database admin.
I placed a bat file called "test1.bat" in the directory c:\test3, the file "test1.bat" contains the following content:

sqlcmd -b -e -Q "ALTER INDEX ALL ON [JOHNDATABASE].dbo.TABLE2 REBUILD"

In the dos-prompt, I can run the "test1.bat" batch file with no errors.
It gives me the following output:
c:\test3\sqlcmd -b -e -Q "ALTER INDEX ALL ON [JOHNDATABASE].dbo.TABLE2 REBUILD"
ALTER INDEX ALL ON [JOHNDATABASE].dbo.TABLE2 REBUILD

Using "Sql Server Agent", I created a new job called "Scripts1".
For the "General" link, I have the following values:
Name:test2
Owner:SA
Category:Database Maintenance

For the step link, I have the following values:
Step Name:test2
Type:Operating system(CmdExec)
Run As:SQL Agent Service Account
Command:
sqlcmd -b -e -Q "ALTER INDEX ALL ON [JOHNDATABASE]

Backups from SQLcmd ??

  

I am working with SQL Express and found myself surprised when I tried to automate a backup...  No Agent.

So I am trying to create a simple backup script to run from the command line and probably schedule through the scheduled task manager.

here is my problem.  I get the following error :

Msg 2812, Level 16, State 62, Server FIREFLY\SQLEXPRESS, Line 1
Could not find stored procedure 'B'.

Here is the batch file

sqlcmd -i c:\temp\test\DBbackup.sql -o c:\temp\test\output.txt -S FIREFLY\SQLEXPRESS

And here is the sql input file :

BACKUP DATABASE [DNNDEV] TO  DISK = 'C:\temp\test\dnndev.bak' WITH NOFORMAT, NOINIT,  NAME = 'dnndev-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

If I put a USE statement in front of my backup statement - the error message changes to

Could not find stored procedure 'U'.

So it would appear to list the first character encountered.

As a sanity check I created a similar process which does a select * from a table and I do not get an error saying :Could not find stored procedure 'S'. - instead all is well and I get my output and no error

If I try the backup command in SQLCMD interactively it works.

so what the heck am I missing ?

Thank you for any help...

 


SQLCMD Miscellaneous Commands :r

  

I have a batch file that executes Test.sql. In Test.sql I have following sql statements.

The question is can I pass parameters to the SQL script that I am calling using command ":r".

--Test.sql file contains this script 

IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'NewDB')

DROP DATABASE NewDB

GO

CREATE DATABASE NewDB

GO

:On Error exit

:r c:\Scripts\CREATE_TABLES.sql [parameters...]

 


SQLCMD timestamp output file

  

Hi All,

   I am using SQLCMD to execute a script and then output the results to a csv file. Is it possible to name the output file with a timestamp each interval that it is executed (such as every 30 minutes that the command is executed to timestamp like "output 10/25/10 830.csv"?

 

Thank you in advance,


Consecutive SQL sqlcmd GO commands

  

What happens when a sqlcmd GO command is followed by another GO command?  Here's an example:

 

GO

sp_extendedproperty N'_database_tools_support', N'log', N'schema', N'dbo', N'table', N'__RefactorLog'

GO

 

GO

SSMS and SQLCMD won't connect to valid SQL Instances

  

I have had Windows 7 for over a year now and a few months ago I was unable to connect to any SQL Server.  It worked at one time and I don't recall changing anything other than normal Windows updates.  What's really strange about this problem is that It just hangs.  If I put a valid SQL server it will hang and never throw an error.  NEVER.  It hangs indefinately.  If I put an invalid SQL server it will error out as it should.  I have spent 80 hours on this by now and am ready to re-install the operating system to see if it helps.  These are the things I have tried:

  • Uninstalling and re-installing SSMS
  • Used OSQL and SQLCMD to no avail
  • Disabling the firewall
  • Pinging the servers directly.  And I get a response.
  • Used the IP address of the server instead of the name
  • Disabling port 1433 on the firewall
  • I can create an ODBC connection to these instances
  • Begging and pleading with the PC

 


SQLCMD.exe does not execute files sporadically

  

I use SQLcmd.exe to execute stored procedure on a target database (SQL version:10.0.2531.0 X64). This method has always been reliable. In the recent times I have observed that a few SQL files (sporadically) when executed through sqlcmd.exe seems to have never been executed on the database but sqlcmd.exe does not print any errors.

When I run them again through sqlcmd.exe they seem to succeed, there is nothing wrong with the sql scripts. Has someone seen this issue happen? Am I missing something?

I use the following options,

SQLCMD.exe -S <> -E -d <> -i <""> -I


Regards- Muralidhar | If my response helped, please help me by marking my response as the answer and voting as appropriate...
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend