2

I've got this script that is working properly and the output is correct when I execute the stored procedure. But its not working when I use it through codesmith, when I perform generate output the stored procedure will return an integer not a result.

Here is the part that is causing the problem:

SELECT *
INTO #UsersConversations
FROM Conversations c 
WHERE c.pid IN
    (SELECT DISTINCT ConvID FROM ConversationUsers WHERE UserID = @UserId)

I'm using SQL Server 2008, CodeSmith v4

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Alaa.Ali
  • 1,142
  • 3
  • 11
  • 26
  • 3
    Side note: you don't need to use `DISTINCT` on a query being used inside an `IN()` expression. At best, it's redundant. At worst, the server may actually do the work to make the result set distinct before evaluating the `IN()` expression. (I'm fairly sure the optimizer is smart enough to remove it, but why take the chance?) – Damien_The_Unbeliever Dec 19 '12 at 07:52
  • The distinct in my case has no effect what so sever. i added it in a desperate try to solve the issue. anyhow with or without the distinct the behaviour is just the same. i moved it anyhow. – Alaa.Ali Dec 19 '12 at 08:00

1 Answers1

2

I work for CodeSmith Tools. The following detailed thread will describe the issue you are running into and how to resolve it.

Blake Niemyjski
  • 3,192
  • 3
  • 23
  • 40
  • 1
    That was really helpful, great post , many thanks :) It definitely helped me solve the issue.. – Alaa.Ali Jan 01 '13 at 19:59
  • link is no longer valid. I have an account and am logged in. Possibly the forums have been turned off ? – greg Oct 30 '16 at 19:21
  • The community site has been removed as it was super out dated. Generator looks at the returned metadata without executing the sproc. You'll need to set FMT off and exec the sprocs with null parameter as shown here: http://stackoverflow.com/questions/21434827/no-fields-for-dynamic-sql-stored-procedure-in-ssrs-with-set-fmtonly Do you get a result? – Blake Niemyjski Oct 31 '16 at 18:17