LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (April 2011, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page