Database

24-Jul-14 1:41

Hello everyone,

I have a problem with my SSIS 2012, if I put in made a OLDBE Source with a complex query or composite sample
AS WITH TT
(
SELECT DISTINCT
e.UniqueID e. [Web Id] from Elements e) SELECT
DISTINCT *
'Class' + CAST (ROW_NUMBER () OVER (PARTITION BY [Web Id] ORDER BY [class] DESC) AS NVARCHAR) AS ClassNum
from TT

I have this error:
Exception From HRESULT: 0xC0202009
Error at Get Products SSIS Error Code DTS_E_OLEBDERROR. A OLE DB error has occured. Error code: 0x80004005
An OLE DB record is available. Source "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Syntax Error, permission violation, or nonspecific Reviews another error"
see Exhibit attached
Note that this same package works wonders on my colleague's machine with vs 2010

I do not understand the error!!

modified 24-Jul-14 8:04am.

Richard Deeming24-Jul-14 2:57

24-Jul-14 2:57

This is an English-language forum. Please post your questions in English.
Il s'agit d'un forum de langue anglaise. S'il vous plaît envoyer vos questions en anglais.
https://translate.google.com/[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

amioni24-Jul-14 3:04

24-Jul-14 3:04

done

Richard Deeming24-Jul-14 3:13

24-Jul-14 3:13

Is your package running as a SQL Agent job? You might need to change the permissions on the TEMP directory:
http://support.microsoft.com/kb/933835[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

amioni24-Jul-14 4:00

24-Jul-14 4:00

i run it from VS2012 so i dont know if it s with sql agent or not (beginner )
and where i can find Temp directory

Richard Deeming24-Jul-14 4:12

24-Jul-14 4:12

If you're running it from Visual Studio, then it's not using SQL Agent, so that's probably not the problem.

There seem to be lots of potential causes for that error message:

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

amioni24-Jul-14 4:25

24-Jul-14 4:25

all of this properties are Ok and as i said this same package works wonders on my colleague's machine with vs 2010
i dont think it s a problem with connection string or else

GuyThiebaut24-Jul-14 3:44

GuyThiebaut

24-Jul-14 3:44

amioni wrote:
e.UniqueID e. [Web Id] from Elements

I am not seeing a comma between the two columns you reference, is that correct?

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

amioni24-Jul-14 3:51

24-Jul-14 3:51

it s just an example of a query so dont worry it s with comma in my package, and it s not the problem

GuyThiebaut24-Jul-14 4:26

GuyThiebaut

24-Jul-14 4:26

Always read the error message - Syntax Error, permission violation, or nonspecific Reviews another error

There is either:
An error in the sql - you can check this by running the sql in isolation.
A permission issue - harder to track, I would test this by creating a simple SSIS package and try to run it from your code.
Non specific error - eliminate the first two possibilities then look into this.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

amioni24-Jul-14 5:09

24-Jul-14 5:09

NB : when i make this query in Stored procedure and i call it in the oledb source it work fine but not when i make it directly

GuyThiebaut24-Jul-14 5:43

GuyThiebaut

24-Jul-14 5:43

I don't understand what that means - I will need more information to be able to help you.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

jschell24-Jul-14 12:44

jschell

24-Jul-14 12:44

amioni wrote:
NB : when i make this query in Stored procedure and i call it in the oledb source it work fine but not when i make it directly

A user (database) can have different permissions based on direct sql versus stored procs.

amioni24-Jul-14 22:54

24-Jul-14 22:54

i am working with sa user (dev pc)

amioni2-Sep-14 0:52

2-Sep-14 0:52

hi,

i answer myself because i found the solution:
with sql server native client 10 we can't add param with subquery
but this problem is resolved with SQL native client 11 (sql server 2012)

thank you

Wink | ;)

sql server

saharmirzaei23-Jul-14 9:12

saharmirzaei

23-Jul-14 9:12

hi i found a Gregorian date conversion function in sql server but i dont know how i can use it!
could you help me?

Richard Deeming23-Jul-14 9:17

23-Jul-14 9:17

Not without seeing it.

What are you trying to achieve? All date/datetime types in MSSQL already use the Gregorian calendar. Are you trying to convert to a Julian date? A different calendar system?

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

saharmirzaei23-Jul-14 23:30

saharmirzaei

23-Jul-14 23:30

i wanna convert english calender to persian calender.i got this function but i cant use it.I am not a professional i MSSQL

PIEBALDconsult23-Jul-14 9:32

PIEBALDconsult

23-Jul-14 9:32

Just put it back where you found it.

You'll never get very far if all you do is follow instructions.

saharmirzaei23-Jul-14 23:26

saharmirzaei

23-Jul-14 23:26

thank you for your help Smile | :)