Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sojarat
Starting Member
11 Posts |
 Posted - 2008-03-28 : 17:34:19
|
| Hi,Anyone happen to know why I could be able to execute an sProc, which exists in multiple databases, and I get results against 1 DB and none in the other ?Background:I developed a report using one database, and then switched the settings of my Data Source over to another DB I need to use & test on as well. The first DB I used will bring me back expected results, the 2nd returns nothing (empty result set).Is there some embedded data source reference that is messing me up here ?I have tried closing out and re-opening the SOlution to ensure it refreshed data connection. I have tried deleteing and recreating the data set using the 2nd database as well.Additional Info:Same sProc loaded to both DBsSame valid SQL credentials supplied to both DBsSame valid parameter values supplied for each sProc executionExecution in SQL Studio returns results in both DBs using above credentials and matchin parameter value.Thanks,Sojo |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-29 : 02:24:57
|
| Are SP's in both db & tables in same schema? And have you checked whether you've used the same version of SP in both db.(not your local vesrion on one & dbo version on other) |
 |
|
|
Sojarat
Starting Member
11 Posts |
Posted - 2008-03-29 : 17:17:45
|
| Thanks for the response...Yes, both are in the same schema (dbo), and both are the same version.I also noticed something else afterwards... On the DB which actually returns data it only returns 5 of 15 rows in the Data screen in BIDS/VS when compared to an identical call in SQL Studio. This comparison was done using the exact call to the sProc produced from a Profiler trace of BIDS when exec'ing the sProc.And I have set no row filters or row counts to restrict the number of rows being returned. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-30 : 03:20:32
|
| Are both the databases in same version of SQL server? ALso i guess you had look at SP resuts by running it in data tab of report rather than in preview mode. |
|
|
|
Sojarat
Starting Member
11 Posts |
Posted - 2008-03-30 : 19:45:34
|
| Both databases are on the same server, both in the same compatibility mode so the SQL versions are the same. They are more or less a copy of each other with slight differences in the amount of data in each.Each execution/test of the sProc has been in the data tab page, not through the preview. |
|
|
|
|
|
|
Execution of sProc in Bus Dev Studio