cr crashes when I use my procedure

[Former Member]

the procedure reads the ad trough a linked service and for this he makes a table inside the tempdb database.
If I make the table and just run the select it works

but afcourse I need uptodate information so I want to make the table at the moment of need.

see attachment

cr code.txt.zip

[Former Member]

the program doesn't respond when i use the create table part which is nessarcy. Also the eventviewer doesn't show much of usefull information

[Former Member]

Crystal Reports cannot use the data in a Tempbd  Table because that table does not exist until the procedure is running.   Crystal needs to be able to "see"  the table before the procedure runs.  

You will need to make it a permanent  table  rather than a temp table.   You can clear the contents of the table before you load it with new "Good data".  

[Former Member]

well I know how the table will look like, as for column names. Can't I just tell himbefore but still use the temp table ?

[Former Member]

No  ......  The Temp Table does not exist until the stored procedure is initiated and it ceases to exist when the procedure finishes so there is nothing for crystal to read.       ( thus the name .... it is only temporary )   Crystal can only "Read"  the results table   (Output of the stored procedure) .   You might be able to populate the results Table with the contents of the temp table but I have never had a reason to do that,  as it really counter to the reason for creating the temp table in the first place.  

[Former Member]

the temp table in this case is needed to work around the 901 limit of the active directory

[DellSC]

Does your stored procedure return a cursor?  One way around this while still using the temp table would be to do a Select from the temp table at the end of the stored proc and use that to return data to Crystal instead of trying to connect Crystal to a temp table.

-Dell

[Former Member]

the code is added to the first post as a txt, just put it in sql management studio

as part of the code I use a select at the end

[former_member292966]

Hi Joey,

I had a look at your query and I assume your actual procedure does the Create Procedure or Alter Procedure and End around all of this. 

I couldn't run it because of the domains and not being able to get access to Active Directory.  I am going to recommend you remove the Drop Tables at the end of the query.  Crystal is expecting a resultset as the final statement. 

The temp table should automatically be destroyed once the procedure is done anyways. 

Give this a try and see what happens. 

Good luck,

Brian

[Former Member]

temp tables are getting destoyed indeed, but ## tables are not. (which is needed to run this code)
tried few ways. tried to build a basic procedure around it

tried to just put this code in crystal reports

tried without drop tables (in case of the ## it's a must have)

if you wanne try the code, set up an linked server to your ad server, added the workpc to your ou or cn and edit the domain1 and lan parameter