Printing command lines
-
Hello everybody,
I have a Procedure which printing for me command lines I will use later (as SP itself). The output as txt file for some reason cutting my command lines in a half! This way I am not able to copy my output to QA and run it - I need to line it up!
set @sql = 'EXEC sp_attach_db
''TESTManager'','''+@DataDrive+':\MSSQL\Data\TESTManagerProd.mdf'','''+@
LogDrive+':\MSSQL\data\TESTManagerProd_log.ldf'''
EXEC(@SQL)
set @sql = ''
the
shows where it was cut in my txt file from one line to the second one...When I am running the same in QA - everything goes great! and if I am copying the output to the notepad txt - no any cuts!
Thanks!
-
Can we see the code that generates this?
-
CREATE Procedure SP_AttachDB2Drives
as
declare @name varchar(50)
declare @file varchar(100)
declare @log varchar(100)
Declare @sql varchar(200)
Declare @sp-2 varchar(200)
Set @sp-2 = 'CREATE Procedure Databases_Attach2Drives @datadrive char(1), @LogDrive char(1)
as
Declare @sql varchar(800)'
DECLARE curNAMES CURSOR for
SELECT DBName,DataFile,LogFile
FROM DatabaseNames
begin
OPEN curNAMES
print @sp-2
FETCH next FROM curNAMES INTO @name,@file,@log
while @@FETCH_STATUS = 0
begin
set @sql='set @sql = ''EXEC sp_attach_db '''''+LTRIM(RTRIM(@name))+''''',''''''+@DataDrive+'''+LTRIM(RTRIM(@file))+''''',''''''+@LogDrive+'''+LTRIM(RTRIM(@log))+'''''''
EXEC(@SQL)
set @sql = '''''
print @sql
FETCH NEXT FROM curNAMES INTO @name,@file,@log
end
CLOSE curNAMES
end
DEALLOCATE curNAMES
drop table DatabaseNames
GO
-
Using the cursor the SP is printing for me all command lines for every database I have, then I am just running this printed output as SP.
-
I thought the issue might be because you are running it in Query Analyzer; by default, text is limited to 256 chanracters until you change it to allow up to 8000 chars:
Query Analyzer>>Tools>>Options>>Results Tab>>Change "Maximum Characters per Column to 8000 instead of 256.
upon rereading your message, that's not the case; could it be that this variable should be 8000 and not 800?
Declare @sql varchar(800)'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I changed to 8000 - the same output. I thought about OSQL limitations, maybe it's the output something...limit... can not find any option or property to modify it
-
One more: when I ran that SP into Report (in QA) and saved as .rpt file - everything went out GREAT! all command lines are good and no cuts. HOW can I declare in the SP that I need output in .rpt format (or txt) or how to run that SP to get the output and not using the osql? did not find yet...
Thanks!
-
Are you using the -w switch with OSQL? The default column width is around 72 characters per column. Try using -w 2000 (or some other number you determine is large enough) at the end of your OSQL command.
-
mkeast!!! You made my day!!!WOW!!!
it worked!
It says in BOL:
Allows the user to set the screen width for output. The default is 80 characters. When an output line has reached its maximum screen width, it is broken into multiple lines.
I had no idea it can be modified...silly me
THANKS!!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply