.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Accessing table in DBO schema from Stored Procedure in other Schema

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
 
I am experiencing a strange issue related to schema qualification at a customer. No matter what we've tried we cannot recreate the issue internally. I am hoping someone can help. We create a Stored Procedure on a schema called "Import". In this stored procedure, we have numerous references to tables on the "dbo" schema, but we have not qualified them (I know this is not best practice, but it's legacy code and we're not going to change it right now). This has been used at other customers without issue. This one new customer is getting an issue where it does not seem to find the dbo table when trying to create the stored procedure. If we take the query out of a stored proceure and just run it as a T-SQL script, it works. So, the code below works select * from Import.Dirsync a join Inventory i on a.Audit_InventoryID = i.InventoryID But this does not. It complains that the column "InventoryID" on the table "Inventory" does not exist. create procedure Import.Test as select * from Import.Dirsync a join Inventory i on a.Audit_InventoryID = i.InventoryID My understanding is that any object on the dbo schema will be resolved. Again, this is working on all other servers with our databases. Any suggestions? Thanks Craig Bryden - Please mark correct answers


View Complete Post


More Related Resource Links

Managing TypedDataSets when there is a change in backend Table / Stored Procedure / Database schema

  
Hi All, I am just curious to know if there is any easier way to synchronize the TypedDataSets with the chages made to backend Table / Stored Procedure schema.  I use Typed DataSets in my project extensively and I found them very useful and easier to code, but difficult to maintain  The difficuly I have always faced is whenever there is a change in a backend database table structure or stored procedure that is linked to any of the Typed DataSet in our project requires to recreate whole DataSet again by scrapping the old one. The backend changes will not be reflected unless and untill you recreate the whole stuff again. Is there any easier way to synchronize those typed datasets with the changes made to backend database schema or any other workaround that will not required to recreate the whole DataSet again.Hope above question makes sense. Any ideas.

Resolve Schema in Stored Procedure

  

I am trying to figure out if it's possible to resolve schemas for tables within a Stored Procedure.  If not, I was wondering if maybe I can find a better solution here.  Here is my scenario.

I have an application that will access a SQL database that will be used by different organizations within my company.  The database will only a have a few tables but it has many stored procedures that will call them.  The different organizations will each have their own SQL User and schema with the tables duplicated for each schema.  While each schema will have its own set of tables to separate data between the organizations, I do not want to make a copy of each stored procedure since the logic will be the same.  I would like the schema to be resolved by the user that is accessing the database.  If I do inline SQL within the application it resolves with no problem but using a Stored Procedure will always run it under the owner of the sp, causing it to throw an invalid table name error.

Is there anyway to resolve this or is this just the functionality of MS SQL?  Are there any workarounds out there (other than Dynamic SQL) that I can use?  Thanks in advance for your help and please let me know if I need to provide more information.


DTA doesn't find referenced table form stored procedure with SP_executesql

  
Using the SQL2008R2 profiler and DTA connected to a SQL2000 server. I created a tuning tracefile with profiler for a certain db on the sql2000 server because I need to tune 1 table in the database referenced by 3 parameterized stored procedures which built a query depending on the parameters and is executed with an sp_executesql statement. When I run the DTA loading the tracefile I see the exec stored procedure events with it's parameters and the message Event does not reference any tables. And that's it. The DTA generates no Recommendatioin at all. How can I get the exact sql statements in the tracefile so the DTA can do what he needs to do? Regards

Accessing IO resources from CLR stored procedure

  
Hi There I am trying to write a CLR stored procedure that will return all the data about the fixed drives on a SQL server. Here is my code: using   System; using   System.Data; using   System.Data.SqlClient; using   System.Data.SqlTypes; using   Microsoft.SqlServer.Server; using   System.IO;   public   partial class StoredProcedures { [Microsoft.SqlServer.Server. SqlProcedure]   public static void Test() {   SqlPipe sp = SqlContext.Pipe;   String strCurrentTime = DiskSpace(); sp.Send(strCurrentTime); }   //[Microsoft.SqlServer.Server.SqlFunction(SystemDataAccess =SystemDataAccessKind.Read)]   //[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert, Unrestricted =true)]   public static string DiskSpace() {   new System.Security.Permissions.SecurityPermission(System.Security.Permissions.PermissionState.Unrestricted).Assert();   String Drives = "";   DriveInfo[] allDrives = DriveInfo.GetDrives();   foreach (DriveInfo d in allDrives) { Drives = ( "Drive " + d.Name);   //Console.WriteLine(" File type: {0}", d.DriveType);   if (d.IsReady == true) { Drives += " Volume label: " + d.VolumeLabel;   //Console.WriteLine(&qu

how to include a user defined table type as input for stored procedure

  
Hi ,  I have a user defined table type which i need to pass as input parameter to the stored procedure .How can i do that?

Could not complete cursor operation because the table schema changed

  
Microsoft SQL Server  2000 - 8.00.2039 Got this error: Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000 Is this a known issue?  I suspect the application logic may cause this error. Please advise. Thanks a lot!  

How do I insert values into different colunms but same row in a table using stored procedure

  
Hi everyone, I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table. For instance: AS BEGIN INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1) END When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1, Number1 is '0' and vice-versa. what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please? Many thanks in advance.

How do I insert values into different colunms but same row in a table using stored procedure

  
Hi everyone, I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table. For instance: AS BEGIN INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1) END When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1, Number1 is '0' and vice-versa. what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please? Many thanks in advance.

Schema Specific Procedure Calls

  
Hi , I have two schemas Say S1 and S2 . I have two procedures inside each of this schema say S1.P1 and S1.P2 for S1 and S2.P1 and S2.P2 for S2 . Is it possible to call S1.P2 from S1.P1 ? . it is possible to do this like EXEC [S1].P2 , but Wat I want to know is to 1) call EXEC statement directly without [S1] prefix and execute the target Procedure which belong to the same schema of the calling Procedure ?. Currently wat I am facing is when I try to call EXEC P1 it looks for Default Schema (dbo.P1) and throwing a "procedure not found" error . 2)Is there a way to get the current schema name inside  the SP while the  SP is executing ?

create procedure [User_Name].sp_myprocedure. Error since I use dbo schema

  
I am developing with VS2010. When I generate a store procedure (DATASET>RIGHT CLICK ADD>SELECT>NEW STORE PROCEDURE) for a data table of a dataset from VS2010, the enviroment generates this: IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectQuery' AND user_name(uid) = 'DOMAIN\USER-LOGIN')  DROP PROCEDURE [DOMAIN\USER-LOGIN'].SelectQuery GO CREATE PROCEDURE [DOMAIN\USER-LOGIN'].SelectQuery AS  SET NOCOUNT ON; SELECT contract_id, product_id, contract_description FROM dbo.TContracts GO The creation of the procedure ends with erros since my schema is dbo. So far, I work around copying and pasting the store procedure changing the schema and starting again from the beginning: DATASET>RIGHT CLICK ADD>SELECT>FROM EXISTING STORE PROCEDURE. Is there any way to configure VS2100 to generate the scripts avoinding to prefix the supposed schema of my user. Many thanks.

Stored Procedure utilizing table variable

  
I'm creating a stored procedure that will take the results of a temp table and check for existence using the IN keyword.  The code looks like this: ALTER PROCEDURE [dbo].[studentBranchLogin] -- Add the parameters for the stored procedure here @userName varchar(50), @userPass varchar(20) AS BEGIN DECLARE @dActive bit; DECLARE @TT TABLE (dept int, active bit); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO @TT(dept, active) SELECT deptid, active FROM srcuDeptId WHERE active = TRUE -- Insert statements for procedure here SELECT srcuid FROM srcuLogins WHERE logon = @userName AND password = @userPass AND srcuid IN @TT END I get a Msg 170, Level 15 state 1 Line 24: Incorrect syntax near '@TT'. So while I'm sure the @TT temp table isn't being read, I find very few resources for answer what the proper syntax should be.   Any assistance is greatly appreciated.

Passing Table Variable to Stored Procedure

  
My stored procedure expects a uniqueidentifier as a parameter.  However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifier SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid) EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

How to calculate a SQL Server performance of a query based upon table schema, table size, and availa

  
Hi What is the best way to calculate (without actual access to a SQL Server) the processing speed of a query (self-inner-join) based upon table schema, table size, and hardware (CPU, RAM, Drives)? ThanksThanks Jeff in Seattle

Cannot call Table Valued Function from Dynamic stored procedure statement

  
Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Schema comparer mistake in table-valued function (Nullability in column definition)

  
Hello. In my database i have table valued function. In its definition nullability of column is not set directly. This column has a user-defined data type. So the question is: is this column nullable? As i can see here: http://msdn.microsoft.com/en-us/library/ms174979.aspx   When column nullability is not explicitly specified, column nullability follows the rules shown in the following table. Column data type Rule Alias data type The Database Engine uses the nullability that is specified when the data type was created. To determine the default nullability of the data type, use sp_help. It should be not null, as user defined type is declared as not null. SMO says, that column is not null, but schema compare says it is nullable. Who is wrong and why?

setting schema as dataset to a table

  
hiii have an already existing schema (in a xsd file). now i need to prepare a report, so in the rdlc page i take a table. now how can i set the schema as a dataset to the table?(i'm getting an error -- "the table 'table1' is in the report body but the report has no data set.  Data regions are not allowed in reports without datasets.").  

insert result of stored procedure in new table

  
hi how can i insert the result of stored procedure in the new table. Best Regards. Morteza
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend