variable output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

variable output

Author

Topic

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-07-18 : 20:48:01

I can't get the variable @filename to be:
"C:\Program'+space(1)+'Files\MILLBROOK\PracticeManager\global.txt"

any ideas??

--update the global.asa file name for the lastupdate date
DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000)

Create Table globalasa (
test varchar(250))
insert into globalasa
select '<!--==Visual InterDev Generated - startspan==-->' union all
select '<!--METADATA TYPE="TypeLib" NAME="Microsoft XML, v3.0" UUID="{F5078F18-C551-11D3-89B9-0000F81FE221}" VERSION="3.0"-->' union all
select '<!--METADATA TYPE="TypeLib" NAME="Microsoft Scripting Runtime" UUID="{420B2830-E718-11CF-893D-00A0C9054228}" VERSION="1.0"-->' union all
select '<!--METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data Objects 2.6 Library" UUID="{00000206-0000-0010-8000-00AA006D2EA4}" VERSION="2.6"-->' union all
select '<!--==Visual InterDev Generated - endspan==-->' union all
select '<!-- #include file="version.inc"  -->' union all
select '' union all
select '<SCRIPT LANGUAGE=VBScript RUNAT=Server>' union all
select 'Sub Application_OnStart' union all
select '  SetApplicationVersion()' union all
select '	SetApplicationVariables()' union all	
select '' union all
select '	Dim ConnStr' union all
select '	ConnStr = "provider=SQLOLEDB.1;data source=" & Application("DBServer") _' union all
select '						& ";initial catalog=" & Application("DBName") _' union all
select '						& ";User Id=" & Application("DBUser") _' union all
select '						& ";Password=" & Application("DBPswd") & ";"' union all
select '	Application("DBConnString") = ConnStr' union all
select '	' union all
select '	Application("PatientExtras_AddOn") = False		' union all
select 'End Sub' union all
select '' union all
select 'Sub Application_OnEnd' union all
select '	Application.Contents.RemoveAll()' union all
select 'End Sub' union all
select '</SCRIPT>' union all
select '<SCRIPT LANGUAGE=VBScript RUNAT=Server>' union all
select '  SUB SetApplicationVariables()' union all
select '    Application("DBServer") = "WMG-PM-REPORTS"' union all
select '    Application("DBName") = "PracticeManager"' union all
select '    Application("DBUser") = "mbcuser"' union all
select '    Application("DBPswd") = "mbcuser"' union all
select '    Application("OnlineHelpURL") = "http://WMG-PM-REPORTS/CentricityPMHelp/"' union all
select '    Application("Caption") = "***REPORT SERVER 123- CHANGES WILL NOT BE SAVED!!*** LAST UPDATE: ' +  convert(varchar(8),getdate(),1) + '"' union all
select '  END SUB' union all
select '</SCRIPT>'

--DECLARE @FileName varchar(50)
SET @FileName = '"C:\Program'+space(1)+'Files\MILLBROOK\PracticeManager\global.txt"'
select @filename
--SET @FileName = 'C:\global.txt'
--SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM master..globalasa" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P sa -c'

EXEC master..xp_cmdshell @bcpCommand
--EXEC master..xp_cmdshell 'fc c:\global.txt C:\Program Files\MILLBROOK\PracticeManager\global.asa <NUL:'

drop table master..globalasa

--done

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-18 : 23:32:48

Can you explain what you mean by >>>I can't get the variable @filename to be:

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-18 : 23:38:41

try removing the double quote " from the @FileName, as you already have it in the @bcpCommand

SET @FileName = 'C:\Program'+space(1)+'Files\MILLBROOK\PracticeManager\global.txt'

KH
[spoiler]Time is always against us[/spoiler]

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-18 : 23:46:49

do you realize that since you are not ordering the results, they could come back in any order? which would make your asa file nonsense because you are storing each line in a separate row.

a set is unordered data. the only way to guarantee an order is with an order by clause.

that said, why on earth are you storing each line in a separate row? this all seems very strange to me.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources