Date: Fri, 8 Apr 2011 14:09:50 +0000
Reply-To: "DUELL, BOB (ATTCINW)" <bd9439@ATT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "DUELL, BOB (ATTCINW)" <bd9439@ATT.COM>
Subject: Re: oledb connection trouble
In-Reply-To: <20110407154531.2C55DECE@resin18.mta.everyone.net>
Content-Type: text/plain; charset="utf-8"
Jeff:
I forgot to mention one other thing; get rid of the "--" style comments inside your code. I would remove all comments between the parentheses and just submit "pure code". If you must have comments, use the "/* comment */" style.
Mary:
Thanks for the info about tsql. I've never used SQL Server before; I use Oracle and Teradata every day.
Good luck,
Bob
-----Original Message-----
From: Mary [mailto:mlhoward@avalon.net]
Sent: Thursday, April 07, 2011 3:46 PM
To: DUELL, BOB (ATTCINW)
Cc: SAS-L@LISTSERV.UGA.EDU
Subject: Re: oledb connection trouble
tlsql (transact SQL) is the language to SQL Server, like PL/SQL is the language to Oracle. I don't have SQL server right now, but I don't believe that you can pass the code to a stored procedure in transact SQL to SQL Server via pass-through SQL and have it execute it; but if you could rewrite it as Bob suggests into pure SQL or even a series of SQL statements then you probably can do that. Thus the declare and the set, being part of tlsql, can't remain.
-Mary
--- bd9439@ATT.COM wrote:
From: "DUELL, BOB (ATTCINW)" <bd9439@ATT.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: oledb connection trouble
Date: Thu, 7 Apr 2011 20:59:25 +0000
Note sure what tsql is, but try this:
First, remove these two lines from the code in the EXECUTE block:
DECLARE @AsOfDate datetime
SET @AsOfDate = getdate()
My guess is that this is a way to define a constant with the current system date. They really are separate statements and I don't think you can use SAS that way. So, create a SAS macro variable with the same value formatted as an ANSI date string like this:
%let AsOfDate=DATE %str(%')%sysfunc(putn(%sysfunc(today()),yymmdd10.))%str(%');
If you run this today, the value of AsOfDate will be this string: DATE '2011-04-07'
I'm assuming tsql will recognize that as a date constant. If it uses some other method just change the macro variable definition appropriately.
Finally, change your code to reference the macro variable instead of the derived variable (line 1149 in your log):
,&AsOfDate as AsOfDate
With any luck, your code will now run. However, since you are doing it in an EXECUTE block, nothing will happen. Your code is a query which will return rows but you haven't said what to do with the result. If you want SAS to do something you need to turn it into a query.
In other words, instead of an EXECUTE statement, use a SELECT statement. To display the results in your OUTPUT window do this:
select *
from connection to OLEDB
(
<<put your query here>>
);
To create a SAS dataset with the result, just add "create table <sasdatasetname> as" in front of the select.
The only problem with this that comes to mind is if the system date on the computer where you are running SAS is different from whatever date is on the database server. I mention this only because I have to deal with time zones a lot because I access remote databases all over the place. If your database is in your same locale this shouldn't be a problem.
Good luck,
Bob
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jeff
Sent: Wednesday, April 06, 2011 1:07 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: oledb connection trouble
I have a big chuck of tsql included in execute( myTSQL). I run mytsql in sql
server, it run very well but I got error msg when I put it into sas. here
are the log:
How can I fix this problem?
Thanks
Jeff
1109
1110
1111 proc sql noprint;
1112 Connect to OLEDB client.;
1113
1114 Execute
1115 (
1116 DECLARE @AsOfDate datetime
1117 SET @AsOfDate = getdate()
1118
1119 SELECT DISTINCT ppc.PxUpdateDatetimeOld as LastUpdateDate
1120 ,ppc.PxUpdateDatetimeNew as UpdateDate
1121 ,ppc.ParticipantIdNew as ParticipantId
1122 ,p.pid
1123 ,p.AlereId
1124 ,p.ClientId
1125 ,m.DOB
1126 ,m.GENDER
1127 ,dc.Client_Name as ClientName
1128 ,ppc.ProgramCategoryIdNew as ProgramCategoryId
1129 ,cpcc.Program_Category_Name as ProgramCategory
1130 ,ppc.ProgramStatusIdNew as ProgramStatusId
1131 ,mps.Value as ProgramStatus
1132 ,ppc.PrimaryProgramIdNew as PrimaryProgramId
1133 ,dp.Program_Name as PrimaryProgram
1134 ,ppc.EnrolledDateNew as EnrolledDate
1135 ,ppc.ENROLLEDDATENEW_FORMATTED as EnrolledDateNew
1136 ,ppc.ProgramReasonIdNew as ProgramReasonId
1137 ,mpr.ProgramReason_Value as ProgramReason
1138 ,ppc.InterventionLevelIdNew as InterventionLevelId
1139 ,dil.Intervention_Level as InterventionLevel
1140 ,ppc.InterventionTypeIdNew as InterventionTypeId
1141 ,dit.Intervention_Type_Name as InterventionType
1142 ,ppc.ProgramExpiredDateNew as ProgramEndDate
1143 ,convert(datetime,substring(ppc.ProgramExpiredDateNew,1,8) +' '
1144 + substring(ppc.ProgramExpiredDateNew,10,2) + ':'
1145 + substring(ppc.ProgramExpiredDateNew,12,2) + ':'
1146 + substring(ppc.ProgramExpiredDateNew,14,6),121) as
ProgramEndDateConv
1147 ,ppc.CreateDate
1148 ,dateadd(ms,-3,dateadd(mm,datediff(m,0,ppc.CreateDate)+1,0)) as
CreateYearMonth
1149 ,@AsOfDate as AsOfDate
1150 INTO #test
1151 FROM ATLENTPODSSQL1.Apollo_Stage.PRPC.Participant_Program_Changelog
ppc with(nolock)
1152 INNER JOIN ATLENTPODSSQL1.Apollo_Stage.PRPC.Participants p
with(nolock)
1153 ON ppc.ParticipantIdNew = p.ParticipantId
1154 INNER JOIN ATLENTPODSSQL1.Apollo_Stage.PRPC.CFG_Program_Category_CDR
cpcc
1154! with(nolock)
1155 ON ppc.ProgramCategoryIdNew = cpcc.Program_Category_Id
1156 AND cpcc.IsActive = 1
1157 INNER JOIN ATLENTPODSSQL1.ShareviewODS.dbo.DIM_Client dc with(nolock)
1158 ON p.ClientId = dc.SRC_Client_Id
1159 AND dc.DIM_Sys_Src_Id = 30 -- Apollo Stage
1160 --AND dc.SRC_Client_Id = 500006 -- BSC
1161 INNER JOIN ATLENTPODSSQL1.Apollo_Stage.PRPC.Master_Program_Status mps
with(nolock)
1162 ON ppc.ProgramStatusIdNew = mps.ProgramStatusId
1163 INNER JOIN ATLENTPODSSQL1.ShareviewODS.dbo.DIM_Program dp with(nolock)
1164 ON ppc.PrimaryProgramIdNew = dp.SRC_Program_Id
1165 AND dc.DIM_Sys_Src_Id = dp.DIM_Sys_Src_Id
1166 AND dp.SRC_Program_Id IN (5 -- CHF
1167 ,6 -- Diabetes
1168 ,7 -- CAD
1169 ,8 -- COPD
1170 ,9 ---Asthma
1171 )
1172 AND dp.IsActive = 1
1173 INNER JOIN ATLENTPODSSQL1.Apollo_Stage.CDR.Membership m with(nolock)
1174 ON p.PID = m.PID
1175 LEFT OUTER JOIN ATLENTPODSSQL1.Apollo_Stage.PRPC.Master_Program_Reason
mpr
1175! with(nolock)
1176 ON ppc.ProgramReasonIdNew = mpr.ProgramReasonId
1177 LEFT OUTER JOIN ATLENTPODSSQL1.ShareViewODS.dbo.Dim_Intervention_Level
dil
1177! with(nolock)
1178 ON ppc.InterventionLevelIdNew = dil.SRC_Intervention_Level_Id
1179 AND dp.DIM_Sys_Src_Id = dil.DIM_Sys_Src_Id
1180 LEFT OUTER JOIN ATLENTPODSSQL1.ShareViewODS.dbo.Dim_Intervention_Type
dit
1180! with(nolock)
1181 ON ppc.InterventionTypeIdNew = dit.SRC_Intervention_Type_Id
1182 AND dp.DIM_Sys_Src_Id = dit.DIM_Sys_Src_Id
1183 WHERE p.pid > 0
1184
1185
1186 ) by OLEDB;
ERROR: Execute error: ICommand::Execute failed. : The multi-part identifier
"dit.Intervention_Type_Name" could not be bound.: The multi-part
identifier
"dil.Intervention_Level" could not be bound.: The multi-part
identifier
"mpr.ProgramReason_Value" could not be bound.: The multi-part
identifier
"dp.Program_Name" could not be bound.: The multi-part identifier
"mps.Value" could
not be bound.: The multi-part identifier "m.GENDER" could not be
bound.: The
multi-part identifier "m.DOB" could not be bound.
1187 create table rolled as select * from connection to OLEDB
1188 (
1189 select *
1190 from test
1191
1192 ) ;
ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : Deferred
prepare could not be
completed.: Statement(s) could not be prepared.: Invalid object name
'#kg'.
1193
1194 disconnect from OLEDB;
1195
1196 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.56 seconds
cpu time 0.03 seconds
| 
