OSQL Query Output

Dec 10, 2007

Hi All,

The command showed below provides the output shown:

set @cmd = 'osql -S bcgsql1cgsqldev -U checkdb -P checkdb -q "set nocount on; select errorlogdesc from ##errors where datediff(dd,errorlogdatetime,getdate()) = 1 and errorlogdesc like ''%Error:%''" -h-1 -w 900 -o J:ScriptsTestStuffErrorLogMsg.txt'

Here is the output:

Error: 17883, Severity: 1, State: 0

Error: 17883, Severity: 1, State: 0

I need to remove the space between the two lines so I set the -w parameter to 900 but that didn't fix it. Any idea how I can accomplish this?

Thanks

View 4 Replies

Using OSQL For Query Output (csv Formatted)

Jun 12, 2006

I'm running a query with osql, and I'm trying to get some clean output that is comma delimited. So far my line looks like this:osql -E -n -d mydb -i custom.qry -o "c:output
esults.csv" -h-1 -s ","This works off a table with only two columns. I'm still left with a lot of extra spaces between the first column output and second column output, and at the bottom the text "(50 rows affected)".So instead of this:data1a (lots of spaces here) ,data1bdata2a (lots of spaces here) ,data2bdata3a (lots of spaces here) ,data3b(50 rows affected)I want to see this:data1a,data1bdata2a,data2bdata3a,data3bThanks very much for any help. :)

View 2 Replies View Related

Row Delimiter From Osql Output

Jul 20, 2005

hi, i had been removed the row counts and the column spaces... but ifailed to remove the extra tabs between rows of data return from theosql output.how to detect the row delimiter?i noticed i can use bcp to have a more decent output file, but i don'twant to do too much of file read-write. the programming enviroment isquite easy to "capture" the output from the comand prompt.secondary, char(252), 253, 254, 255 is not being read by the commandprompt, for example, 253 turns into 132 when i decode the input.thankx.from alan.

View 1 Replies View Related

OSQL Output File Garbage

Jul 20, 2005

Everybody,I've been doing a lot of on-line research and cannot findany reference to the exact problem I'm having.Let me preface this question with the fact that I'm comingfrom an Oracle background so my approach may not be the bestway to tackle this. However, from the research I have donethis approach seems reasonable. Also, I know about theundocumented procedure sp_MSforeachtable. That can give me aresult similar to what I'm looking for but the format of theoutput is not what I need.Now the problem. I'm trying to write a reusable script to giveme a list of all the tables in a database that have 1 or more rows.My approach is to a BAT file (see script 1 below) that calls OSQLtwice, once to call a SQL script (see script 2 below) that uses theInformation_Schema views to generate the SELECT COUNT(*) statementsand fill in all the tables names in the database, write this to atemporary output file and the second OSQL command to read thetemporary output file and generate me the results formatted theway I need.The result of the first OSQL run is correct EXCEPT for 1> 2> 3> 4> 5>6> 7> 8> 9> 10> 11> 12> 13> garbage at the beginning of the file.Because of this garbage the 2nd OSQL command blows up! Anyone haveany idea what is generating this garbage?If I manually edit out the garbage and then just run the 2nd OSQLcommandI get similar garbage in the final result file (see 2nd result filebelow).In Query Analyzer, when I run the GET_TABLE_COUNT.SQL Script manuallythen take its output and copy and paste it to a new query window andrun that it works OK except for generating lots of blank lines wherethe result of the tables that have zero rows are. I am suppressingheadings but am still getting the blank lines but at least it works!Any ideas anybody? Thanks For Any HelpFYI -- SQL Server 2000 with SP3a.Bob================== Script 1 - BAT File to Call OSQL ===============@echo off@echo ************************************************** *************@echo .@echo get_table_count.bat@echo .@echo Before you run this script change to the drive and directory@echo where the input SQL script is located!@echo .@echo Input parameters:@echo 1) SQL Server userid@echo .@echo You will be prompted twice for your password!@echo .@echo The output is written to file TABLE_COUNT_RESULT.TXT@echo .@echo ************************************************** *************pauseosql -U %1 -S devkc-db -d C3T_Architecture -i get_table_count.sql -otemp_table_count_query.txt -h-1 -w500osql -U %1 -S devkc-db -d C3T_Architecture -itemp_table_count_query.txt -o table_count_result.txt -h-1 -w500del temp_table_count_result.txt@echo on================================================== ==================================== Script 2 - GET_TABLE_COUNT.SQL Script ===============set nocount onselect 'set nocount on'select 'select ''Table Name Count'''select 'select ''========== ====='''select 'select '''+ table_name+ ''', count(*) from '+ table_name+ ' having count(*) > 0 'from information_schema.tableswhere table_type = 'BASE TABLE'order by table_name================================================== ================================ Partial Result of 1st OSQL Run ==========================1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> set nocount onselect 'Table Name Count'select '========== ====='select 'ACT_ASSERTION_RULE', count(*) from ACT_ASSERTION_RULE havingcount(*) > 0select 'ACT_ASSOC', count(*) from ACT_ASSOC having count(*) > 0select 'ACT_DOC', count(*) from ACT_DOC having count(*) > 0================================================== ================================ Partial Result of @nd OSQL Run ==========================1> 2> 3> 4> ... I edited out the intervening numbers for this message.... 664> 665> 666> 667> Table Name Count========== =====.... I edited out lots of blank lines in the result for this messagebefore I get to the first table with 1 or more rows ...ARCH 6================================================== ====================

View 2 Replies View Related

ISQL And OSQL Output Lines Wrapped Around At 256 Characters?

Jul 20, 2005

I am trying to use a command line program to run a stored procedurethat generates output in a comma-delimitted format. Somehow, ISQL orOSQL always wrap the lines at 256 characters. I believe this hassomething to do with the column width switch (-w). But enlarging thecolumn width to 800 characters max still doesn't help. The followingis a stored procedure that is essentially doing what my storedprocedure is doing:create procedure MyTest asset ansi_padding onset nocount ondeclare @sTest varchar(300)-- Output three lines. Each line has 259 characters.select @sTest = "1234 6789 ... 1234 6789"print @sTestselect @sTest = "1 3 5 7 9 ... 1 3 5 7 9"print @sTestselect @sTest = "1 3 5 7 9 ... 1 3 5 7 9"print @sTestset nocount offreturn( 0 )I invoke this stored procedure using this command:isql -SMyDbSrv -E -dMyDb -w800 -x800 -h-1 -n -Q"exec MyTest"-oMyTest.txt-- or --osql -SMyDbSrv -E -dMyDb -w800 -h-1 -n -Q"exec MyTest" -oMyTest.txtBut they have the same problem. The output lines all wrap around at256 characters.Strangely, if I store the result in a temporary table, and then useSELECT to output the result from the temporary table, I will not havethat problem. Seem like the "-w" switch only works for output fromtables, but not for output coming from PRINT. Unfortunately, usingthis approach has another set of problems (one blank space in front ofeach line, "number-of-rows affected" shows up at the bottom).Therefore, I would like to stick with using PRINT statements to outputthe result.Please suggest a way to fix this line-wrapping-around problem.Thanks.Jay Chan

View 5 Replies View Related

Osql Query Error

Feb 22, 2008

All,

I am facing one problem while running below given query from SSMS or
QA while the same
query running fine while running through osql at command prompt.
while running through command prompt it is giving correct value like
0,1,2 or 3 . With SSMS everytime it is giving null as result.

exec xp_cmdshell 'osql -S SMOSVR0686 -E -Q"declare @test int
Exec master..xp_regread @rootkey = ''HKEY_LOCAL_MACHINE'',@key =
''SOFTWAREMicrosoftMSSQLServerMSSQLServer'',@val
ue_name = ''AuditLevel'',@value=@test OUTPUT select @test"'

p.s - while running at command prompt please remove extra quotes..

Any help is much appreciated

nj

View 1 Replies View Related

Looping Through Results Of A OSQL Query

Jul 23, 2005

Hello,I have the following problem.I wrote a batch file that runs a sql script on SQLServer 2000. Thisscript must be executed on several databases. This works fine.The problem is that in my database is a table that holds a databases ihave to update with this script. What i want is run a query with osqlin my batchfile to retrieve this these records so I can loop throughthem an run the script for those databases.I managed to execute the query that return the records and write themto a textfile.What i want is store the results in some kind of a resultset so i canloop through them in my batchfile.Anyone have the solutionThanks in advancePatrick

View 1 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!

But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn

View 5 Replies View Related

Correct Syntax For Query Using Osql.exe In MSDE Setup

Nov 10, 2003

I have the following .bat file that I call to register users to my MSDE instance...

echo osql -E -S %COMPUTERNAME%OfficiumInstance -Q "sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_grantdbaccess '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_addrolemember 'db_owner', '" + %COMPUTERNAME% + "ASPNET'"

My question is how do I correctly append %COMPUTERNAME% as a string to

I have tried using + signs...

"sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"

But that doesn't work.
I have looked for .bat file tutorials on the web but can't find the correct way to do this.

Greg

View 1 Replies View Related

SQL Query Output To XML Using BCP HELP!!

Aug 24, 2007

I am having trouble in writing code to save the SQL query output to an XML file.

A simple example like below is working fine and it creates an output a.xml.

------Begin Code-----
Declare @fNameTemp varchar(50)
Declare @SQL varchar(3000)
Set @fNameTemp = 'c:XMLDumpa.xml'

Set @SQL = 'bcp "Select top 10 * From test..month For XML Auto, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'
Exec master..xp_cmdshell @SQL
------End Code ------

But I am having trouble in writing when I change the Select query to a more complex one as in the example below. The code is part of SP.

------Begin Code-----
USE [test]
GO
Declare @TblName1 varchar(10)
Declare @TblName2 varchar(10)
Declare @District varchar(6)
Declare @Month varchar(3)
Declare @TblType varchar(10)
Declare @Thisyear varchar(10)
Declare @Lastyear varchar(10)

SET @TblName1 = '2006exp'
SET @TblName2 = '2005exp'
SET @District = 1
SET @Month = 2
SET @TblType = 'exp1'
set @Thisyear = '2006'
set @Lastyear = '2005'

set @TblName1 = '[' + @TblName1 + ']'
set @TblName2 = '[' + @TblName2 + ']'
set @TblType = '[' + @TblType + ']'

Declare @fNameTemp varchar(50)
SET @fNameTemp = 'C:XMLDump2.xml'
Declare @SQuery nvarchar(3000)

SET @SQuery = 'bcp "Select d.descrip_1 as [Commodity Description], ty.HS4, ty.Amount1 as ['+ @ThisYear +' Value ($)], (ty.Amount1/ty.Total1)*100 as ['+ @ThisYear +' Share (%)] ,
py.Amount2 as ['+ @LastYear +' Value ($)], (py.Amount2/py.Total2)*100 as ['+ @LastYear +' Share (%)]
FROM
(Select top 10 a.commodity1 as HS4, Sum(a.all_val_mo) as Amount1,
(select Sum(a1.all_val_mo) FROM test..' + @TblName1 + ' a1 where a1.stat_month <=' + @Month + ' and a1.district=' + @District +' ) as Total1
FROM test..' + @TblName1 + ' a where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1 Order by Amount1 DESC) ty
JOIN
(Select b.commodity1 as HS4, Sum(b.all_val_mo) as Amount2,
(select Sum(b1.all_val_mo) FROM test..' + @TblName2 + ' b1 where b1.stat_month <=' + @Month + ' and b1.district=' + @District +' ) as Total2
FROM test..' + @TblName2 + ' b where b.stat_month <=' + @Month + ' and b.district=' + @District +'
Group by b.commodity1) py on ty.HS4=py.HS4 LEFT OUTER JOIN '+ @TblType +' d on ty.HS4=d.commodity1
Order by Amount1 DESC FOR XML AUTO, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'

Exec master..xp_cmdshell @SQuery
------End Code ------

Output for above code is shown below:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

I don't know what is the mistake I am doing. I would appreciate if anyone can help me figure the error.
http://www.fiu.edu/~atmakurk/ustrade/SQL_XML1.jpg

The above query works perfectly if I remove BCP & For XML Auto part. Here is a screen shot of Successful query results.
http://fiu.edu/~atmakurk/ustrade/working_example.jpg

View 1 Replies View Related

Issues With Sql Query Output

Jun 7, 2008

Iam using the below XML request with OpenXml function in sqlserver to get field values.
But iam not able to get values for  "DeviceType,DeviceID,FeatureLink" also i want to format the value of the devicetype field
<Device Type="KWS,KL,2007,6"> it should return "KWS" alone how can i do that please advice.
DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<Response ><DetaisInfo>   <Tag/>   <InstrService>DEVICE_INFO</InstrService></DetaisInfo><Devices>   <Device Type="KWS,KL,2007,6">     <Field name="DeviceType">0.00</Field>     <Field name="DeviceID">12.00</Field>     <Field name="FeatureLink">25700</Field>   </Device>   <Device Type="SNY,KL,2007,6">     <Field name="DeviceType">1.11</Field>     <Field name="DeviceID">0.10</Field>     <Field name="FeatureLink">21667</Field>   </Device></Devices></Response>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT *FROM   OPENXML (@idoc, '/Response/Devices/Device',2)         WITH (Type       varchar(10)         './@Type',               DeviceType varchar(10) '/@name',               DeviceID   varchar(10)    '/@name',               FeatureLink     varchar(10)         '/@name')
OutPut
Type                   DeviceType     DeviceID      FeatureLink ----------                ----------             ----------          ----------- KWS,KL,200       NULL                NULL          NULLSNY,KL,200         NULL                NULL          NULL
(2 row(s) affected)
Please advice

View 6 Replies View Related

Output Query To A File

Jul 25, 2001

I have a query something like this:
select "bcp EISAT_08_18.."+name +" OUT C:"+ name+".TXT -c -t -SCJACOBI"
from sysobjects
where type = 'U'
ORDER BY NAME
When I run the above query I want to output the result of the query to a file.
Can someone help me on that?

View 8 Replies View Related

Output The Query Into A File

Jul 31, 2001

Hi all,
When I run a query in the sql query analyzer I need to write the output of that query in to another file. In Oracle its spool. Can someone help me on this please. Thank you!!!

View 1 Replies View Related

Reformatting Output From A Query

Jul 26, 2004

Hey everyone,

as of now i have a sql chunk

Code:

SELECT VIEW.DWG_NAME
FROM VIEW, qry_TURNOVER
WHERE (((VIEW.ID)=[qry_TURNOVER].[CURRENT_ID]));

which works but when i get the results it orders them according to number ( or what seems to be in the order of ID), and i need it in the order that it appears in the column CURRENT_ID

If that is not clear This is what is happening

For example, we have:

qry_TURNOVER
CURRENT_ID
4
5
1
3

VIEW
ID DWG_NAME
1 23g
2 24H
3 25J
4 26K
5 27L

As it stands,The results auto format to:

23G
25H
26K
27L

I need the results to be:

26k
27L
23G
25J

Hope that was clear, any ideas? Im not sure if this is even possible. Any hints or tips to a solution would be much appreciated. The reason why current ID is unordered is because its has other data in its table that is ordered via that way. So current ID cannot be orderd to match out out, it must be the other way around.

Thanks
Doc

View 3 Replies View Related

Query Output Into A File

Feb 27, 2006

Hi,

In a stored procedure how do I output the result of a query to a text file?

Regards,

Bharathram G

View 3 Replies View Related

Query Output In A Desired Way

Aug 11, 2006

Gopinath writes "Let us consider we have the following data in a table,

Column1 Column2
John orange
John apple
John Grape
Steve orange
Steve watermelon
Steve pineapple

in the above table, i want to query and get output in the below format

Column1 Column2
John orange
apple
Grape
Steve orange
watermelon
pineapple

is it possible? if yes, kindly let me know the query.

Thanks in advance.

Regards,
Gopi."

View 2 Replies View Related

Output Query To Xml File

Apr 13, 2006

I have an sp that runs a query in xp_cmdshell/BCP combo that puts the output into an XML file (using for xml auto hint on the end of the query).

I personally feel that I should now (under SQL 2K5) be able to do this within a query, not going any where near xp_cmdshell or bcp, and then use SSIS in the final step to ftp it up to the clients site.

It builds a command string in the sp, then runs that command string via xp_cmdshell.

I guess what I'm asking is, is it possible to run a query within an SP with the output going to an XML file on the server automatically? AND that file to be a well formed XML file?

Any suggestions?

View 4 Replies View Related

Redirecting Query Output

Feb 27, 2008

Hi

Is there any way to redirect results to file directly while executing query? Like in management studio we can redirect result to Text or File.(Option Result to File)

Regards
Arun.M

View 13 Replies View Related

Output Query Result To A CSV File

Aug 29, 2007

Hi,
I want to export/output result of a query to a CSV file using SQL Server 2005. How can i do it ? I just want to do it all using SQL Server 2005 query window without having to use some 3rd Party control or software. Is it possible and how? Is there some SP which can convert the result to a CSV File ?
Thanking you...

View 4 Replies View Related

Output Of A Query In Sql Server 2000

Oct 4, 2007

Hi,
I am using SQL SERVER 2000.
I need a procedure/ method where i need to display the output value of a particular query.
As I wrote the following procedure
CREATE proc proc1as beginDeclare @str nvarchar(100)set @str = 'select top 5 * from CT_TM_EmployeeMaster'print @strend
the output is giving "select top 5 * from CT_TM_EmployeeMaster"
I need the output of the above query. What should i do?

Thanks & Regards,
JaiShankar

View 4 Replies View Related

Simple Query Or Resultset Output

Feb 7, 2008

I'm learning ASP.net, transitioning from ColdFusion. So far so go... but there is 1 thing that I can't seem to find a simple answer to. How can I do a simple output of a query resultset to the screen or to a label?
In CF it would be simple like this:
<cfquery name="getSomeData" datasource="somedatasource">SELECT somestuffFROM sometableWHERE something = something</cfquery>
<cfoutput>#getsomedata.somestuff#</cfoutput>
FYI: using the queryname.column returns only the first row in the CF query result
I have seen examples with:
label.text = somevarname
which doesn't work for the query... but what about the query result?
thanks

View 2 Replies View Related

Trying To Get Output Parameter From TSQL Query

Apr 10, 2008

I am trying to return an ouput parameter from my query. I have tested the stored proceedure extensivly within the SQL Management Studio, and I know it works fine, so that means the error is somewhere within my code, but for the life of my I cant figure where.
Here is my stored proc:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[tblSiteVisits_FindOfficerName]
@VisitID int,
@OfficerName varchar(100) output
AS
BEGIN
-- Variables
Declare @OfficerID int
--Declare @OfficerName varchar(100)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select @OfficerID = (Select top 1
OfficerID from tblSiteVisitOfficers Where VisitID = @VisitID)

IF (@OfficerID Is Null)
BEGIN -- Get the None Registered Officer
Select @OfficerName = (Select top 1 OfficerOther from dbo.tblSiteVisitOfficers Where VisitID = @VisitID)
print 'Got unregistered Officer ' + @OfficerName
END
ELSE
BEGIN -- Get the Registered Officer
Select @OfficerName = (Select OfficerFName + ' ' + OfficerLname from dbo.tblOfficers Where OfficerID = @OfficerID)
print 'Got Registered Officer ' + @OfficerName
END
END

And here is the code I am using to access this proceedure:1 Dim blah As String
2 Dim conn2 As New SqlConnection()
3 Dim cmd2 As New SqlCommand()
4 conn2.ConnectionString = _ConnString
5 cmd2.Connection = conn2
6 cmd2.CommandType = CommandType.StoredProcedure
7 cmd2.CommandText = "dbo.tblSiteVisits_FindOfficerName"
8
9 cmd.Parameters.AddWithValue("@VisitID", Convert.ToInt32(row("VisitID")))
10 cmd.Parameters.Add("@OfficerName", SqlDbType.VarChar, 100)
11 cmd.Parameters("@OfficerName").Direction = ParameterDirection.Output
12 Try
13 conn.Open()
14 cmd.ExecuteNonQuery()
15 blah = cmd.Parameters("@OfficerName").Value.ToString()
16
17 Catch ex As Exception
18 Throw ex
19 Finally
20 conn.Close()
21 End Try
22

However there I never recieve the output value, and because of the way my database is structures, there is no possible way, that there is no output value.If anyone can help, that would be great, kind regards.

View 2 Replies View Related

Output Results Of Query To A Txt File

Apr 3, 2001

I have come across manual queries being run daily and the results saved to a txt file on the network.

I basically want to set run these up a stored procedure and set up as a scheduled task to run daily.

Does anyone know if you can automatically save the results of a query/stored procedure to a text file on a network??

Many Thanks..

View 1 Replies View Related

Sending Query Output To A File

Aug 22, 2001

HI,
I have a small problem with SQL server 7.0 where I have to write the output of a query directly to a file without the user going to the menu and clicking on save. Scenario is:
A file is having a query the results of which has to be outputted to a file like in oracle where we have spooling function. Is there any functionality that mimic the oracle functionality where by I can get the output directly to a file?
thanks for your help in advance.
thanks,
Sravan.

View 2 Replies View Related

Query Result To Output File

Jan 24, 2001

Hi All

I am using cursor in my SP, according to my condition I might be getting around 600 records all this records to send to text file, I have tried few option some how I am able to create file and I am geeting only the last record in my output file, I want to know how can I append records into output file(text file), If some one can give me some suggestion that will be great.

Thanks in advance

Regards
Ram

View 1 Replies View Related

Query Output To Flatfile Wraps

Feb 23, 2001

I am running an ISQL script every day and automatically emailing the output of a query as the body of an email, to a group of people. ISQL executes a SQL file which selects some data and the output of the ISQL is sent to a *.txt file.

The Issue is that the output of the query wraps in the flatfile. It seems that the *.txt file wraps at character 76.

Does anyone have an Idea how to prevent query output data from wrapping in the flatfile?

-thanks
-tom

View 1 Replies View Related

Using Bcp To Save Query Output In Excel

Jul 20, 2004

I am using bcp to get the query output in excel file. But I am not able to see the column headings. Is there a way to get the column heading also in the output file.

the command I am using is this.

declare @x varchar(300)

set @x = 'bcp "select * from Northwind..orders" queryout c: est.csv -S local -U sa -P passwd -c -C RAW -t "," -r '

exec master..xp_cmdshell @x

go

View 7 Replies View Related

6.5 Vs 8.0 Query Output. Please Shine Some Light.

Mar 19, 2007

Hi guys,
Quick question (I hope).
Why is there a difference in the output of this query in 6.5 campatibility vs 8.0 campatibility?

DECLARE@FooterLinevarchar(30)

select@FooterLine = rtrim(@FooterLine) + 'Run by Region'

select @footerline

6.5 returns Run by Region
8.0 returns NULL

View 2 Replies View Related

How To Get Quantity From Output Of Query Command

Jun 7, 2012

how to get the quantity from output of sql query command for example, I could get the container name by below command

select container_name from sysibmadm.snapcontainer
get the container number by
select TBSP_NUM_CONTAINERS from SYSIBMADM.SNAPTBSP_PART

now I want to get the container number by below command output result select container_name from sysibmadm.snapcontainer..so what more command should I add on above command, I mean I want to get the container number by container name from the output of above command, not by 'join'.

View 1 Replies View Related

T-SQL (SS2K8) :: Job Details In Query Output

May 1, 2015

I have a T-SQL query that outputs exactly as I wish when run via Management Studio. I now want to automate this query to run monthly via a SQL Agent Job.

I have got this to run but the resulting file is left with a header row which shows "Job 'MyJobName' : Step 1, 'My QUERY' : Began Executing 2015-05-01 10:52:01" and an extra blank row before my data (and column headers).

Is there any way of stopping these extra rows from being created using T-SQL in my query?

View 2 Replies View Related

Create Table From Query Output

May 5, 2008

I have a query that is spliting up FullName into FistName and LastName columns. Need help writing a query that dump that output into a new table.

Thanks
Kevin

View 6 Replies View Related

Create Table From Query Output

Oct 25, 2013

I have the following sql query in vb.net and ms access , how do I create a table from the query result ?

SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1

I have tried but it does not work

create table tble10 as SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1

View 14 Replies View Related