#Table Not Found Error Using Stored Procedure For DataSet
Aug 29, 2007
Hi all
I have a procedure where I am inserting some elements into #Table and then finally get the datset I need.
Now when I am using this procedure as dataset to my report, it throws up the following error:
Invalid object Name "#TEMP2".
The data that I retrieve is similar to the data that I get from this query in the post by Manivannan.D.Sekaran
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871478&SiteID=1
Is it because my columns are generated on the fly that I am not able to retrieve the column headers appropriately. If so can someone suggest a way over to this?
I am not sure should I posting it here or in T-SQL Forum.
Is it possible to combine a stored procedure result set and a table into one dataset? For example, if I have a stored procedure with field "TradeID", and a table with "TradeID", can I join the them in a dataset?
Hello, I try to use Stored Procedure with SQL Server 2005. I was created a new procedure in my dbb "GetIDTransaction" set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date,,> -- Description:<Description,,> -- ============================================= ALTER PROCEDURE [dbo].[GetIDTransaction] -- Add the parameters for the stored procedure here @DateTransation NVarChar(50), @ID int OUTPUT AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
INSERT INTO Transactions_Guichet (DateTransaction, RefExpay,Montant,AccesRestreint,Valide,AdresseEmail,ClePerso,RemonteeBackOffice) VALUES(@DateTransation,'', '0','0','0','','','') SET @ID=SCOPE_IDENTITY() RETURN in SqlConnection oConnection = new SqlConnection(strConString); SqlCommand oCommand = new SqlCommand("GetIDTransaction", oConnection); oCommand.CommandType = CommandType.StoredProcedure; //Création de la dateTransaction SqlParameter oParam = oCommand.Parameters.Add("@DateTransation", SqlDbType.DateTime, 8, "DateTransation"); oParam.Value = DateTime.Now.ToString();
oParam2.Direction = ParameterDirection.Output; //Ouverture de la connection oConnection.Open();
//Récupère Id oCommand.ExecuteNonQuery(); iIDTransation = Convert.ToInt32(oParam2.Value); But i have always the same error Stored Procedure '' not found Thanks for you help
I'm trying to fill a dataset with the following code, but i'm getting an error that the stored procedure cannot be found. Do you see anything I could be doing wrong? Thanks 1 Dim ds As New DataSet 2 Dim sda As SqlDataAdapter 3 Dim strSQL As String 4 5 6 Dim conn As New SqlConnection(Application("ConnectionString")) 7 conn.Open() 8 9 strSQL = "getEmployeInfo_proc '" & strOfficeID & "'" 10 sda = New SqlDataAdapter(strSQL, conn) 11 sda.Fill(ds, "Attorneys") 12 dgResults.DataSource = ds.Tables("Attorneys") 13 dgResults.DataBind() 14 conn.Close()
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Hello and thank you for taking a moment to read this message. I am simply trying to use a stored procedure to set up a dataset. For some reason when I try to fill the dataset with the data adapter I get the following error: Compiler Error Message: BC30638: Array bounds cannot appear in type specifiers.Line 86: ' Create the Data AdapterLine 87: Dim objadapter As SQLDataAdapter(mycommand2, myconnection2) my code looks as follows for the dataset:<script runat="server">Sub ListSongs() ' Dimension Variables in order to get songs Dim myConnection2 as SQLConnection Dim myCommand2 as SQLCommand Dim intID4 As Integer 'retrieve albumn ID for track listings intID4 = Int32.Parse (Request.QueryString("id")) ' Create Instance of Connection myConnection2 = New SqlConnection( "Server=localhost;uid=jazz***;pwd=**secret**;database=Beatles" ) myConnection2.Open() 'Create Command object Dim mycommand2 AS New SQLCommand( "usp_Retrieve song_",objCon) mycommand2.CommandType = CommandType.StoredProcedure mycommand2.Parameters.Add("@ID", intID4) ' Create the Data Adapter (this is where my code fails, not really sure what to do) Dim objadapter As SQLDataAdapter(mycommand2, myconnection2) 'Use the Fill() method to create and populate a datatable object into a dataset. Table will be called dtsongs Dim objdataset As DataSet() objadapter.Fill(objdataset, "dtsongs") 'Bind the datatable object called dtsongs to our Datagrid: dgrdSongs.Datasource = objdataset.Tables("dtsongs") dgrdsongs.DataBind()</script><html><head> <title>Albumn Details</title></head><body style="FONT: 10pt verdana" bgcolor="#fce9ca"><center> <asp:DataGrid id="dgrdSongs" Runat="Server" ></ asp:DataGrid> </center></body></html> Any help or advice would be greatly appreciated. Thank You - Jason
I need to run a sequel statement in a stored procedure and get if it found any records because the next statements depend on if records were found or not. How do I do this?
I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.
CREATE PROC sp_test @id INT, @username VARCHAR(50), @prod_id INT AS
SELECT name FROM Customers WHERE id=@id SELECT street FROM Addresses WHERE customer=@username SELECT productname FROM Products WHERE id=@prod_id
It is kind of check, which has to find out if users have inserted all the necessary values or not. Thanks for any advice.
I am trying to make a dataset to use with a report. I need to get the data out of a stored procedure. I am using a temporaty table in the stored procedure, and the dataset doesnt recognize any of the colums that are output.
I have the following stored procedure for SQL Server 2000: SELECT a.firstName, a.lastName, a.emailfrom tbluseraccount ainner join tblUserRoles U on u.userid = a.useridand u.roleid = 'projLead' Now, this is not returning anything for my dataset. What needs to be added?Here is the code behind:Dim DS As New DataSetDim sqlAdpt As New SqlDataAdapterDim conn As SqlConnection = New SqlConnection(DBconn.CONN_STRING)Dim Command As SqlCommand = New SqlCommand("myStoredProcdureName", conn)Command.CommandType = CommandType.StoredProcedureCommand.Connection = connsqlAdpt.SelectCommand = CommandsqlAdpt.Fill(DS) Then I should have the dataset, but it's empty.Thanks all,Zath
Hi all, Im still relatively new to SQL Server & ASP.NET and I was wondering if anyone could be of any assistance. I have been googling for hours and getting nowhere. Basically I need to access the query results from the execution of a stored procedure. I am trying to populate a DataSet with the data but I am unsure of how to go about this. This is what I have so far:- 1 SqlDataSource dataSrc2 = new SqlDataSource();2 dataSrc2.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseConnectionString1].ConnectionString;3 dataSrc2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;4 dataSrc2.InsertCommand = "reportData";5 6 dataSrc2.InsertParameters.Add("ID", list_IDs.SelectedValue.ToString());7 8 int rowsAffected;9 10 11 try12 {13 rowsAffected = dataSrc2.Insert();14 } As you know this way of executing the query only returns the number of rows affected. I was wondering if there is a way of executing the procedure in a way that returns the data, so I can populate a DataSet with that data. Any help is greatly appreciated. Slainte, Sean
HiI have this code snippet[CODE] string connstring = "server=(local);uid=xxx;pwd=xxx;database=test;"; SqlConnection connection = new SqlConnection(connstring); //SqlCommand cmd = new SqlCommand("getInfo", connection); SqlDataAdapter a = new SqlDataAdapter("getInfo", connection); a.SelectCommand.CommandType = CommandType.StoredProcedure; a.SelectCommand.Parameters.Add("@Count", SqlDbType.Int).Value = id_param; DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { Console.WriteLine(dr[0].ToString()); }[/CODE] When I seperately run the stored procedure getInfo with 2 as parameter, I get the outputBut when I run thsi program, it runs successfully but gives no output Can someone please help me?
I am trying to use a dataset for the first time and I've run into a roadblock early. I added the dataset to the AppCode folder, set the connection string, and selected 'use existing stored procedures' in the configuration wizard. The problem is that there are three input parameters on this procedure and they're not showing up in the 'Set select procedure parameters' box. I went through several of the stored procedures and this is the case for all of them. The weird thing is that if I select the same procedure as an insert procedure then the parameters do show up. Very frustrating, any thoughts? Thanks in advance, N
I'm not sure if anybody else is having a problem with the Return Value of Stored Procedures where you get the "Specified cast not valid" error, but I think I found a "bug" in VS2005 that prevents you from having a return value other than Int64 datatype. I tried to look for the solution for myself on the forums but unfortunately I just couldn't find it. Hopefully, this will help out anyone who had come across the same problem. Basically, I have a stored procedure that I wanted to call as an Update for my ObjectDataSource that returns a Money value. Everytime I do this, I keep getting that error saying "Specified cast not valid" even when I try to change the @RETURN_VALUE data type to Currency or Money. After a long session of eye gouging moments, I decided to look at the code for my dataset. There, I noticed that the ScalarCallRetval for my StoredProcedure query was still set to System.Int64. I changed it to System.Object and, like a miracle, everything works like its suppose to. Ex. protected void SomeObjectDataSource_Updated(object sender, ObjectDataSourceStatusEventArgs e) {GrandTotalLabel.Text = ((decimal)e.ReturnValue).ToString("C"); }
The following is NOT filling the dataset or rather, 0 rows returned.....The sp.....CREATE PROCEDURE dbo.Comms @email NVARCHAR ,@num INT OUTPUT ,@userEmail NVARCHAR OUTPUT ASBEGIN DECLARE @errCode INT SELECT fldNum, fldUserEmailFROM tblCommsWHERE fldUserEmail = @email SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeENDGOAnd the code to connect to the sp - some parameters have been removed for easier read..... Dim errCode As Integer Dim conn = dbconn.GetConnection() Dim sqlAdpt As New SqlDataAdapter Dim DS As New DataSet Dim command As SqlCommand = New SqlCommand("Comms", conn) command.Parameters.Add("@email", Trim(sEmail)) command.CommandType = CommandType.StoredProcedure command.Connection = conn sqlAdpt.SelectCommand = command Dim pNum As SqlParameter = command.Parameters.Add("@num", SqlDbType.Int) pNum.Direction = ParameterDirection.Output Dim pUserEmail As SqlParameter = command.Parameters.Add("@userEmail", SqlDbType.NVarChar) pUserEmail.Size = 256 pUserEmail.Direction = ParameterDirection.Output sqlAdpt.Fill(DS) Return DS Like I said, a lot of parameters have been removed for easier read.And it is not filling the dataset or rather I get a count of 1 back and that's not right.I am binding the DS to a datagrid this way.... Dim DScomm As New DataSet DScomm = getPts.getBabComm(sEmail) dgBabComm.DataSource = DScomm.Tables(0) And tried to count the rows DScomm.Tables(0).Rows.Count and it = 0Suggestions?Thanks all,Zath
Hi, Can anyone please help me solve this problem. My functions works well with this stored procedure: CREATE PROCEDURE proc_curCourseID@studentID int ASSELECT * FROM StudentCourse WHERE mark IS NULL AND studentID = @studentID AND archived IS NULLGO But when I applied the same function to the following stored procedure CREATE PROCEDURE proc_memberDetails@memberID int ASSELECT * FROM member WHERE id = @memberIDGO I received this message Exception Details: System.Data.SqlClient.SqlException: Procedure or function proc_memberDetails has too many arguments specified.Source Error:
Line 33: SqlDataAdapter sqlDA = new SqlDataAdapter(); Line 34: sqlDA.SelectCommand = sqlComm; Line 35: sqlDA.Fill(dataSet); Line 36: Line 37: return dataSet; The function I am using is returning a DataSet as below: public DataSet ExecuteStoredProcSelect (string sqlProcedure, ArrayList paramName, ArrayList paramValue) { DataSet dataSet = new DataSet(); SqlConnection sqlConnect = new SqlConnection(GetDBConnectionString()); SqlCommand sqlComm = new SqlCommand (sqlProcedure, sqlConnect); sqlComm.CommandType = CommandType.StoredProcedure;
for (int n=0; n<paramName.Count; n++) { sqlComm.Parameters.Add(paramName[n].ToString(),Convert.ToInt32(paramValue[n])); }
SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = sqlComm; sqlDA.Fill(dataSet); return dataSet; } If this is not the correct way, is there any other way to write a function to return a dataset as the result of the stored procedure? Thanks.
I have a great deal of experience in Intrebase Stored Procedures, and there I had the FOR SELECT statement to loop through a recordset, and return the records I wish (or to make any other calculations in the loop). I'm new in MS SQL Stored Procedures, and I try to achieve the same if possible. Below is a Stored Procedure written for MS SQL, which returns me a calculated field for every record from a table, but it places different values in the calculated field. Everything is working fine, except that I receive back as many datasets as many records I have in the Guests table. I would like to get back the same info, but in one dataset:
ALTER PROCEDURE dbo.GetVal AS
Declare @fname varchar(50) Declare @lname varchar(50) Declare @grname varchar(100) Declare @isgroup int Declare @id int Declare @ListName varchar(200)
DECLARE guests_cursor CURSOR FOR SELECT id, fname, lname, grname, b_isgroup FROM guests
OPEN guests_cursor
-- Perform the first fetch. FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS =0 BEGIN if (@isgroup=1) Select @grname+'('+@lname+', '+@fname+')' as ListName else Select @lname+', '+@fname as ListName -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup
Hi all, I'm writing a CLR stored procedure that just execute a query using 2 parameters.
SqlContext.Pipe.Send can send a SqlDataReader, but if I've got a DataSet? How can I obtain a SqlDataReader from a DataSet?
Dim command As New SqlCommand(.......).....Dim ds As New DataSet()Dim adapter As New SqlDataAdapter(command)adapter.Fill(ds, "MyTable")... 'manipulating the ds.Tables("MyTable")
At this moment I have to send the table...but ds.Tables("MyTable").CreateDataReader() just give me a DataTableReader, and i can't send it with SqlContext.Pipe.Send(...
When I run the code below, I get a table with "In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user." as the contents... Public Function ReturnTable(ByVal strName As String, ByVal alParameters As ArrayList, ByVal strTable As String) As DataSet Dim sqlSP As New SqlCommand sqlSP.CommandTimeout = 120 sqlSP.Connection = sqlConn sqlSP.CommandType = CommandType.StoredProcedure sqlSP.CommandText = strName
AddParameters(sqlSP.Parameters, alParameters)
Dim dsDataSet As New DataSet()
sqlConn.Open()
Dim sqlDataAdapter As New SqlDataAdapter(sqlSP) sqlDataAdapter.Fill(dsDataSet, strTable) sqlConn.Close()
Return dsDataSet
End Function
AddParameters is a function which adds each of the parameters in the array list to the sqlSP parameter collection... In SQL Profiler, the sp is called and runs and returns results... But a dataset with one table and the above msg is returned...
ALTER PROCEDURE [dbo].[sp_STATEWLEVEL_DAILY] @STATE varchar(50),@TBLNAME varchar(50)
AS BEGIN TRANSACTION -- Start the transaction TRUNCATE TABLE @TBLNAME; SELECT t1.Date_Taken as 'DATE', t1.Time as 'TIME', t1.Main_ID as 'MAIN_ID', t1.WATER_ULEVEL as 'WATER_ULEVEL' FROM dbo.SEL t1 INNER JOIN dbo.station_info t2 ON t1.Main_ID=t2.Main_ID WHERE t2.STATE=@STATE AND t1.Date_Taken=CONVERT(VARCHAR(10), GETDATE(), 101) ORDER BY t1.Date_Taken, t1.Time
-- See if there is an error IF @@ERROR <> 0 -- There's an error b/c @ERROR is not 0, rollback ROLLBACK ELSE COMMIT -- Success! Commit the transaction
I have a big SQL Stored Procedure which works with a cursor inside of it. During the procedure the data is inserted into a table and at the end is a SELECT statement from that table. The problem is that when i create a dataset with that stored procedure and i run it in the Data tab i get the correct select, but in the Fields section of the Report I don't get the fields from the last SELECT, but the fields from the cursor. Am I doing something wrong or is this a bug and how can i fix it. Thanks!
Is it possible to use an Oracle Stored Procedure for an RDLC report. There are posts I've read that deal with RDL reports that use the data tab and command type of "Stored Procedure", but I don't have that installed. I just create a new dataset that the report uses. I can do reports just fine with SQL statements, but I want to be able to call a stored procedure...
Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as : String or binary data would be truncated.The statement has been terminated. The procedure i created is as : ALTER PROCEDURE fetchpersondetails AS CREATE Table #tempperson (personID int,FirstName nvarchar(200),LastName nvarchar(250),title nvarchar(150),Profession nvarchar(200),StreetAddress nvarchar(300), StateAddress nvarchar(200),CityAddress nvarchar(200),CountryAddress nvarchar(200),ZipAddress nvarchar(200),Telephone nvarchar(200),Mobile nvarchar(200), Fax nvarchar(200),Email nvarchar(250),NotesPub ntext,Affiliation nvarchar(200),Category nvarchar(200))
Insert into #tempperson SELECT dbo.tblperson.personID, ISNULL(dbo.tblperson.fName, N'') + ' ' + ISNULL(dbo.tblperson.mName, N'') AS FirstName, dbo.tblperson.lname AS LastName, dbo.tblperson.honor AS Title, dbo.tblperson.title AS Profession, dbo.tblperson.street + ' ' + ISNULL(dbo.tblperson.suite, N'') AS StreetAddress, dbo.tblperson.city AS cityaddress, dbo.tblperson.state AS stateaddress, dbo.tblperson.postalCode AS zipaddress, dbo.tblperson.Phone1 + ',' + ISNULL(dbo.tblperson.Phone2, N'') + ',' + ISNULL(dbo.tblperson.Phone3, N'') AS Telephone, dbo.tblperson.mobilePhone AS mobile, dbo.tblperson.officeFax + ',' + ISNULL(dbo.tblperson.altOfficeFax, N'') + ',' + ISNULL(dbo.tblperson.altOfficeFax2, N'') AS Fax, ISNULL(dbo.tblperson.Email1, N'') + ',' + ISNULL(dbo.tblperson.Email2, N'') + ',' + ISNULL(dbo.tblperson.Email3, N'') AS Email, dbo.tblperson.notes AS NotesPub, dbo.tblOrganizations.orgName AS Affiliation, dbo.tblOrganizations.orgCategory AS Category, dbo.tblCountry.countryNameFull AS countryaddress FROM dbo.tblperson INNER JOIN dbo.tblOrganizations ON dbo.tblperson.orgID = dbo.tblOrganizations.orgID INNER JOIN dbo.tblCountry ON dbo.tblperson.countryCode = dbo.tblCountry.ISOCode
I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario.
I have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.
But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.
i am not seeing the remaingin 3 resultsets, on the reports dataset.
Is it possible on the reports or not.
In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.
ALTER PROCEDURE [dbo].[ReportChart]@Num int,@patID char(16) ASbegin if @Num=2 Begin select * from table1 where patientid=@patID End else If @Num=1 Begin select * from table2 where patientid=@patID End end
While using the above stored procedure, when i bind it with Dataset. The fields corresponding to table1 are displayed in the Fields Tab of the Dataset whereas the Fields corresponding to table2 are not displayed. Please help me out . Thanks In Advance Regards Navdeep
I have a stored procedure "spDetailsByDay" which takes parameters @StartDateTime as datetime, @Day as int, @Hour as int, @Value1 as varchar(20), @value2 as varchar(20)
My report Parameters are StartDateTime as DateTime, Day as integer, Hour as integer, Value1 as string, Value2 as string, ReportType as string
I have a stored procedure that works in my dataset editor, but when i try to run the report, only the "amount" field shows up. Everything else is blank. why is this happening. Here is the stored procedure.
hai guys, i have written a stored procedure which creates a table ex: USE PUBS GO IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RC_STRPROC') DROP PROCEDURE RC_STRPROC GO USE PUBS GO CREATE PROCEDURE RC_STRPROC (@TBLNAME VARCHAR(35), @COLVAL1 VARCHAR(35), @COLVAL2 VARCHAR(35)) AS IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '@TBLNAME') DROP TABLE @TBLNAME CREATE TABLE @TBLNAME (@COLVAL1, @COLVAL2) GO it gives an syntax error at '@tblname' can u guys tell me the problem
Help! I'm very tired (and new at this) and have looked for a solution in many places. I have an Employee table with a one to many Revenue table. All revenue types are in this table. I need the goals and actuals (two different revenue types) for a datagrid.
This is the result. Because I am looking at two revenue types, the result is providing 2 rows of data instead of one. what is the best way to combine this.
Region FullName SHARP Year Ann Goal YTD Goal YTDActual Region1 Doe10, John X 2003 20400 5100 0 Select Region1 Doe10, John X 2003 0 0 3987 Select Region1 Doe11, John X 2003 29645 7411.25 0 Select Region1 Doe11, John X 2003 0 0 5377 Select
Here's my stored procedure:
CREATE PROCEDURE spFilterRegion
@RIDsent As Integer, @StatusSent As Integer, @SelectedRegion As NVARCHAR (50) Output
AS SELECT Region.CountryID, Employee.RegionID, Employee.StatusID, Employee.SHARP, CASE When Employee.SHARP = 1 THEN "X" ELSE "" END AS SHARPresult, Employee.LastName, Employee.FirstName, Employee.LastName + ', ' + FirstName AS FullName, Employee.EmployeeID, Region.RegionName, ProducerRevenue.RevenueTypeID, CASE When ProducerRevenue.RevenueTypeID = 1 Then SUM(ProducerRevenue.Revenue) ELSE 0 END AS AnnGoal, CASE When ProducerRevenue.RevenueTypeID = 1 Then SUM(ProducerRevenue.Revenue)/DATEPART(mm, GETDATE()) ELSE 0 END AS YTDGoal, CASE When ProducerRevenue.RevenueTypeID = 2 Then SUM(ProducerRevenue.Revenue) ELSE 0 END AS Actual, ProducerRevenue.YearID FROM Employee LEFT OUTER JOIN ProducerRevenue ON Employee.EmployeeID = ProducerRevenue.EmployeeID AND ProducerRevenue.YearID = DATEPART(yy, GETDATE()) - 1 AND ProducerRevenue.MonthID < DATEPART(mm, GETDATE()) AND ProducerRevenue.StatusID = 1 AND ProducerRevenue.RevenueTypeID <= 2 LEFT OUTER JOIN Region ON Employee.RegionID = Region.RegionID WHERE Employee.StatusID = @StatusSent AND Employee.RegionID = @RIDsent AND Employee.RoleID = 1 GROUP BY Region.CountryID, Employee.RegionID, Region.RegionName, Employee.RoleID, Employee.StatusID, Employee.SHARP, Employee.LastName, Employee.FirstName, Employee.EmployeeID, ProducerRevenue.RevenueTypeID, ProducerRevenue.YearID ORDER BY Region.CountryID, Employee.RegionID, Employee.RoleID, Employee.StatusID, Employee.SHARP, Employee.LastName, Employee.FirstName, Employee.EmployeeID, ProducerRevenue.RevenueTypeID, ProducerRevenue.YearID
We are facing an issue while executing a stored procedure which uses a table of current database with INNER JOIN a table of another database in same instance.
Per our requirement, we are inserting select statement output in table variable. Then applying business logic and finally showing the data from table variable.
This scenario is working exactly fine in Dev environment. But when we deployed the code in quality environment. Stored procedure does not returning OUTPUT/ (No column names) from table variable.
During initial investigation, we found that collation of these two databases are different but we added DATABASE_DEFAULT collation in the JOIN.
