gingercat
Posts: 48
|
| Posted: 01/16/2013, 9:51 PM |
|
I've been trying to work out why I can't seem to use Stored procedures. They are in the MS SQL database (2008 R2) and I can see them with SQL Management Studio but when I attempt to select SP as the data source in CDS it all works but there just aren't any procedures there.
I'm using ODBC as the driver for my local development connection but I'm pretty sure I used to be able to see them in earlier version of CCs (I have ver 5)
|
 |
 |
andrewi
Posts: 162
|
| Posted: 01/21/2013, 4:24 PM |
|
Are the stored procedures in the same schema as the objects (tables, views) that you can see? I think a CCS connection shows objects in a single schema.
|
|
|
|
gingercat
Posts: 48
|
| Posted: 01/21/2013, 4:35 PM |
|
No they aren't - I'm sure they were thee once tho (when I didn't need them)
|
|
|
|
andrewi
Posts: 162
|
| Posted: 01/26/2013, 11:12 AM |
|
You mean, no they aren't in the same schema?
That is, your tables may be in schema dbo - dbo.table1, dbo.view1, etc, but your stored procedures may be in a different schema - e.g. user.procedure1, user.procedure2
The Advanced tab of the CCS connection properties shows the schema of the selected connection. If your stored procedures aren't in the schema displayed on the Advanced tab you can add another connection for the required schema or move the stored procedures to a different schema.
|
|
|
|
gingercat
Posts: 48
|
| Posted: 01/29/2013, 11:00 PM |
|
They are in the right Schema and the permissions for the user allow access to Stored Procedures.
Now I'm wondering if its a limitation of using ODBC to connect to the db on my development PC
|
|
|
|
andrewi
Posts: 162
|
| Posted: 02/01/2013, 8:12 PM |
|
I've always been able to see store procedures from database on local machine (although in CCS 4.3).
If you connect to SQL Server Management Studio using the exact credentials that you are using for the design connection on CCS, can you see and execute the SPs then? If so then CCS should see the same.
Conversely, as a purely temporary measure, you could try changing the CCS design login credentials to sa user, or Administrator windows user - i.e. full credentials. If you see the SPs then you know it is a permissions problem.
|
|
|
|
|
