Multiple Db Query Call From Within Different Context Into #temp Table
Mar 21, 2007
The first query returns me the results from multiple databases, the
second does the same thing except it puts the result into a #temp
table? Could someone please show me an example of this using the first
query? The first query uses the @exec_context and I am having a
challenge trying to figure out how to make the call from within a
different context and still insert into a #temp table.
DECLARE @exec_context varchar(30)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @exec_context = @DBNAME + '.dbo.sp_executesql '
set @sql = N'select top 10 * from products'
exec @exec_context @sql
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
-------------------------------------------------------------------------------------
CREATE TABLE #Test (field list here)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
ORDER BY NAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'
INSERT INTO #Test
exec (@sql)
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
SELECT * from #Test
DROP TABLE #Test
Loop through #Temp_1 -Execute Sproc_ABC passing in #Temp_1.Field_TUV as parameter -Store result set of Sproc_ABC into #Temp_2 -Update #Temp_1 SET #Temp_1.Field_XYZ= #Temp_2.Field_XYZ End Loop
It appears scary from a performance standpoint, but I'm not sure there's a way around it. I have little experience with loops and cursors in SQL. What would such code look like? And is there a preferable way (assuming I have to call Sproc_ABC using Field_TUV to get the new value for Field_XYZ?
If an application makes a connection under a given user, performs the following: - calls a stored procedure which performs an insert, - the insert causes a trigger to be fired - the trigger causes a sql statement to execute against a different database
Questions: * Does the user who called the procedure, also need permissions to this 2nd database ? * What security context does this trigger get executed ?
I have a stored procedure that is being called by my .Net application. The user account that makes the connection has execute permission to the procedure. However, the procedure does an insert which causes a trigger to fire. This trigger does a sql statement against a table in a different database, which the user who is calling the procedure, does not have permissions to access. Im getting an exception when I call the procedure, and it basically says there was a permission error with the user accessing this other database.
I have the following query and I would like to combine it into one query instead of using several temp tables.
IF OBJECT_ID('Tempdb..#a') IS NOT NULL DROP TABLE #a SELECT * INTO #a FROM #Web_table w WITH(NOLOCK) WHERE w.generated_date IS NOT NULL IF OBJECT_ID('Tempdb..#b') IS NOT NULL DROP TABLE #b
I have to select several field values from a table and need to assign them to different variables in my SP.Here's what I do now: declare @ReceiverEmail nvarchar(50) SET @ReceiverEmail=(SELECT Email FROM Users WHERE UserCode=@UserCodeOwner) declare @UsernameSender nvarchar(50) SET @UsernameSender=(SELECT Username FROM Users WHERE UserCode=@UserCodeOwner)As you can see I have to search the Users table twice: once for the Email and a second time for the Username...and all that based on the SAME usercode...:SSo, is there an option where I only have to search the table once and return the Email and UserName fields and assign them to my variables (without using a temp table....)?
What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?
I have a temp table with the following columns and data
drop table #temp create table #temp (id int,DLR_ID int,KPI_ID int,Brnd_ID int) insert into #temp values (1,2343,34,2) insert into #temp values (2,2343,34,2) insert into #temp values (3,2343,34,2)
[Code]....
I use the rank function on that table and get the following results
select rank() over (order by DLR_ID,KPI_ID,BRND_ID ) Rown,* from #temp
I am interested only in Rown and Id columns. For each Rown number, I need to get the min(ID) in the second column and the duplicate ID should be in 3rd column as shown below.If i have 3 duplicate IDs , I should have 3 rows with 2nd column being the min(id) and 3rd column having one of the duplicate ids in ascending order(as shown in Rown=6)
Simple example: declare @tTable(col1 int) insert into @tTable(col1) values (1) select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
I have three queries that I use to determine what the party type of a person.
They will be either a child, family member or an associated party and I need to go to three different tables to find this out. I set a field "relationship" to child, family member or associated party depending on the query results.
I would like to fill a temp table with the results so I can use it in reporting.
Here is my code:
Code: SELECTvap.partyID, relationship INTO#Relationship SELECTvap.partyID, Relationship = 'Child' FROMVolunteerActivityParty vap JOIN VolunteerActivity va ON va.VolunteerActivityID = vap.VolunteerActivityID JOIN
Here is my query which lists all orders for products supplied by Supplier-3. A typical Query on the Northwind database i wrote is like this..
Select * FROM [Order Details] WHERE ProductID in (Select ProductID From Products where SupplierID = 3)
The subquery in Red was used in multiple places in one of my Stored Procedures..
So what i thought was - use a temp table to store the resultset from this subquery, and then use the temp table instead of querying the Products table everywhere..
My Query looked something like this..
Declare @ProductIDs TABLE (ProductID int)
INSERT INTO @ProductIDs Select ProductID From Products where SupplierID = 3
Select * FROM [Order Details] WHERE ProductID in (Select ProductID FROM @ProductIDs)
Well, I expected an increase in performance with the latter approach, but seems my Stored Procedure is taking more time with the second solution..
Would be glad to see ne explanation on this behavior..
I am querying a table in oracle, the server connection to the Oracle database is determined by a criteria. Though how can I put the results from the oracle query into a temp table ?
This is the code i'm using for the query:
DECLARE @cmd VARCHAR(500) declare @Year varchar(25) set @Year = '2006'
We are seeing a regression bug with the Microsoft JDBC driver 1.2 CTP.
Using this driver, we don't seem to be able to call stored procedures which return a result set, if those stored procedures use temporary tables internally.
The 1.2 CTP driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases. The previous 1.1 driver, suceeds in both cases.
Here is a test case which demonstrates the problem (with IP addresses and logins omitted). The prDummy stored procedure being called is quite simple, and I've copied it below:
Code Snippet
public class MicrosoftJDBCDriverCallingStoredProceduresTest extends TestCase {
// CREATE PROCEDURE [dbo].[prDummy] // AS // // CREATE TABLE #MyTempTable ( // someid BIGINT NOT NULL PRIMARY KEY, // userid BIGINT, // ) // // SELECT 1 as TEST2, 2 as TEST2 // GO
public void testStoredProcedureViaDirectJDBC() { Connection conn = null; String driverInfo = "<unknown>"; String dbInfo = "<unknown>"; try { // Set up driver & DB login... Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String connectionUrl = "jdbc:sqlserver://xxx.xxx.xxx.xxx:1433"; Properties dbProps = new Properties(); dbProps.put("databaseName", "xxxxxx"); dbProps.put("user", "xxxxxx"); dbProps.put("password", "xxxxxx"); // Get a connection... conn = DriverManager.getConnection(connectionUrl, dbProps); driverInfo = conn.getMetaData().getDriverName() + " v" + conn.getMetaData().getDriverVersion(); dbInfo = conn.getMetaData().getDatabaseProductName() + " v" + conn.getMetaData().getDatabaseProductVersion(); // Perform the test... CallableStatement cs = conn.prepareCall("{CALL prDummy()}"); cs.executeQuery(); // If the previous line executes okay, the test is passed... System.out.println("Accessing "" + dbInfo + "" with driver "" + driverInfo + "" calls the stored procedure successfully."); } catch (Exception e) { // Fail the unit test... fail("Accessing "" + dbInfo + "" with driver "" + driverInfo + "" fails to call the stored procedure: " + e.getMessage()); } finally { // Close the connection... try { if (conn != null) conn.close(); } catch (Exception ignore) { } } } } The output of this test under both drivers and accessing both databases is as follows:
Code Snippet
Accessing "Microsoft SQL Server v8.00.2039" with driver "Microsoft SQL Server 2005 JDBC Driver v1.1.1501.101" calls the stored procedure successfully.
Accessing "Microsoft SQL Server v9.00.3042" with driver "Microsoft SQL Server 2005 JDBC Driver v1.1.1501.101" calls the stored procedure successfully.
Accessing "Microsoft SQL Server v8.00.2039" with driver "Microsoft SQL Server 2005 JDBC Driver v1.2.2323.101" fails to call the stored procedure: The statement did not return a result set.
Accessing "Microsoft SQL Server v9.00.3042" with driver "Microsoft SQL Server 2005 JDBC Driver v1.2.2323.101" fails to call the stored procedure: The statement did not return a result set.
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL DROP TABLE #Pyr GO SELECT vst_int_id, [4981] AS Primary_Ins, [4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.
Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2 ( [PART] ASC, [ORG] ASC, [SPLRNAME] ASC, [REPITEM] ASC, [RFQ] ASC,
I think this is a very simple question, however, I don't know the answer. What is the difference between a regular Temp table and a Global Temp table? I need to create a temp table within an sp that all users will use. I want the table recreated each time someone accesses the sp, though, because some of the same info may need to be inserted and I don't want any PK errors.
I was wondered what the most efficient way to do the following in MS SQL (2000, 2005, and above): Psudo Code: DECLARE @var1, @var2, @var3 VARCHAR(100) SET @var1, @var2, @var3 = (SELECT var1, var2, var3 FROM Table WHERE [ID] = @someID) <-- Returns only one row Right now I'm making a call for every variable. I should play around with temp tables but seems like allot of overhead code just to get 3 values. I'm thinking there's a simple way to do this. Thanks for any ideas
I would like to display the person_ID, the total row for formation actif = 1 by person and formation actif = 0 by person and experience actif = 1 by person and experience actif = 0 by person
the result must be: person_ID nbFormationActif NbFormationInactif NbExpActfi 1 2 5 4 2 1 2 5 ... NbExpInactif 0 3
I have doing a simple query who display total experience actif and inactif for all people but now i'm must to do the SAME THING in the SAME QUERY FOR FORMATION and i'm still block!!! someone can help me ?
SELECT PERSONNE.Person_ID ,COUNT(PERSONNE.Person_ID) AS totExpAct, totExpInactif FROM ( SELECT PERSONNE.Person_ID ,COUNT(PERSONNE.Person_ID) AS totExpInactif FROM PERSONNE INNER JOIN EXPERIENCE ON PERSONNE.Person_ID = EXPERIENCE.Personne AND EXPERIENCE.Actif = 0 GROUP BY PERSONNE.Person_ID ) PERSONNE INNER JOIN EXPERIENCE ON PERSONNE.Person_ID = EXPERIENCE.Personne AND EXPERIENCE.Actif = 1 GROUP BY PERSONNE.Person_ID, totExpInactif
The reason it is poorly designed is the table is used to hold questions and answers, all with a 1:1 relationship. Instead of having ID, ProductType, Question, Answer they have unfortunately adopted the approach of the above i.e:
id 1 thisID 3 parentid nuLL DESCRIPTION: this is a question
id 20 thisID 3_1 parentID 3 DESCRIPTION: this is the answer to the question above
So I am writing a sproc that does this using a temp table. I got this far:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:Spencer -- ============================================= ALTER PROCEDURE [dbo].[GetFAQs] -- Add the parameters for the stored procedure here @ProductType varchar(255) AS BEGIN SET NOCOUNT ON; -- Insert statements for procedure here CREATE TABLE TEMP ( IDINT, thisIDvarchar(50) null, parentIDvarchar(50) null, Titlevarchar(255) null, DescriptionQvarchar(8000) null, DescriptionAvarchar(8000) null, ProductTypevarchar(255) null, )
SELECT ID, thisID, parentID, Title, DescriptionQ, DescriptionA, ProductType FROM A2Z WHERE ProductType = @ProductType AND parentID IS NULL END GO
This gets all my questions for that product type.
What I need to do is load the questions into my temp table and then run through the a2z table again gaining the answers to the questions (the parentid holds the question ID). The answers then will also get loaded into the temp table.
What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:
FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7] WHERE [table4].[field2]=[table1.field2]{this is an inner join} [table4].[field2]=[table2.field2]{this is an inner join} [table4].[field2]=[table3.field2]{this is an inner join} [table4].[field2]=[table5.field2]{this is an inner join} [table5].[field3]=[table6.field2]{this is an inner join} [table5].[field4]=[table7.field2]{this is needs to be a left join}
As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.
Hey,I am having some confusion about how to formulate this particularquery.I have 2 tables. Table A has 4 columns say a1,a2,a3,a4 with thecolumns a1,a2,a4 forming the primary key. Table B again has 3 columnswith b1,b2,b3,b4 and like before, b1,b2 and b4 form the primary key.All columns are of the same datatype in both tables. Now I want to getrows from table A which are not present in table B. Whats the best wayof doing this?Thanks--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict235166.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815725
I want to get a column count several times in one query using differentfilters but can't work out how to do it - can anyone point me in the rightdirection?For example, how would combine these two selects into one query that willlist the total and filtered actions:SELECT COUNT(actions) as actioncount, locationFROM mytableGROUP BY locationSELECT COUNT(actions) as actioncount, locationFROM mytableWHERE mycondition IS NULLGROUP BY location
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?