... I meant this star: 'SELECT * FROM tstadvdbf.ASFSTAG0' maybe if you reduce the fields it will cut out the field causing trouble. Some other ideas: 1. Compare the query plan for the working and failing case and see if there is any difference (for the linked server part of it) 2. try select * into table from yourview (I know know its a star ;P ) and see what data types you get in the new table. Maybe this will shed some light on it.
E
ElectricLlama
@ElectricLlama
Posts
-
Odd problem (conversion or openquery?) only appearing in a stored procedure but not when running it adhoc -
Which database (free or open source) is password protected? [modified]Yes all of those databases allow you to 'password' protect yuor database in some manner, but the DBA on site always knows this password.
-
Which database (free or open source) is password protected? [modified]What exactly do you mean by password protected? You can download SQL Server Express for free (4Gb data file limit). It has security - i.e. you need a login to get into the database. Anyone can pick the MDf (data) file and attach it and use the data.... unless you encrypt the data with or write your own encryption algorithm.
-
JOIN and WHERE condiitonAFAIK SQL Server applies it before or after the join depending on what it thinks would give you the quickest result.
-
Sending Email using SQL JobFirstly: is a CSV file acceptable? This opens just in Excel but is easier to create from SQL than an Excel file. If an Excel file is required then you should probably use SSIS (which allows all of this winderfule logic to be implemented)
-
Odd problem (conversion or openquery?) only appearing in a stored procedure but not when running it adhocIMHO the only way to debug this stuff is hack it down to its simplest level and start adding columns until the error occurs. You will either find a mysterious undocumented bug or an obvious headslapping one. So maybe progressively alter your SP as follows: SELECT 'A' As C1, 'B' As C2, 'C' As C3 FROM T_ASFSTAG0 WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee) /* AND ((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or (isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103))) */ Does that work? keep adding bits in till it breaks. Also having a select * anywhere in code makes me nervous so maybe it could help things if you alter your view to select specific columns from the table.