Hello all,
Can anyone please tell me what query to use for joining two tables in two databases together?
Basically, we have two databases called 'system' and 'site' respectively. The 'system' database has a table called 'users' and each user has a unique id which is also linked to the tables in the 'site' database.
The only problem i have got is, I want to be able to get the actual user names not the unique id and the only way to get this from my SQL statement is to be able to link the uniqueid for each user in the 'site' database to their corresponding uniqueid in the 'system' database.
On this server I have a few databases. I need to run the below query against each database. Since the server has approx 50 databases this will take time.
Is there any way for me to modify this so as it will automatically run against each database one at a time and then create a single output file with all the results.
I do not want it to run against all of the databases at the same time as it will kill my server so it must run and gather results on each database at a time.
QUERY to be run
SELECT RelativeFileName
FROM Collection, Saveset
WHERE Collection.CollectionIdentity = Saveset.CollectionIdentity
Hi,I've written a job to export user and database permissions for alld/b's on a server. As you can see below, the T-SQL commands are thesame for each d/b. Can anyone assist with regard to re-writing this sothat any new d/b's added do not require ammending the job (loop)?Thx,GC.use mastergoSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use msdbgoSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use test1goSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use test2goSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Hi, I have looked and loked for the answer to this question and made no progress. I want to install MSDE on one of our servers, however, when I went to install it, I found that there was another MSDE already installed and running (it was an agent running with our backup software Veritas). Is it alright to create another database and have 2 running simultaneous on the same data engine? Is there any type of limitations that I sould know about - such as number of allowed accesses at one time? Any help is greatly appreicated.
Script only displays the result for 'Master' database.
What changes are required for script to display result for all databases on the instance?
SELECT DB_Name() AS DatabaseName, OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.page_count >1000 ORDER BY indexstats.avg_fragmentation_in_percent DESC
Hi, i read article on how to install the AdventureWorks on my PC, basically i downloaded the sample db from microsoft website, but after i install several times, i seems cant get to see it in my sql server 2005, please assist on this, i have been craking head for this. Thanks :)
The enterprise edition of SQL server includes some advanced BI features, for example the fuzzy lookup feature of IS. If the IS package lives on an enterprise edition of SQL server and the database the package it is targeting lives on a standard edition of SQL server can the advanced features be used? Can you run a fuzzy look against a database on a standard edition of SQL server when th IS package lives on an enterprise edition of SQL server? THANKS!
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar) GO Update Provider_APC_2004_05 set EmAdmsCount12mths = (Select COUNT(*)-1 from Combined_Admissions where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or (Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and (Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND Combined_Admissions.AdmMethod like 'Emergency%')-- and -- CA.NHSorPrivate = 'NHS')) FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
1) is there any way to run a query over a query without having to create a table with the results of the first query? (would drop table work? If so, how?
2) how can i define input variables the same way i do in excel? I am trying to run a couple of simulations based on 2 core inputs (in excel i would just do a data table)
Wonder if you could help. Am working in an environment where I have an output table in a new SQL db. The queries to populate and update this table reference tables in another db on the same server. So effectively, my SQL inserts and update join tables across databses. PROBLEM - they seem to take forever to run! Any ideas / experience with cross db joins queries???????
Hi guys. I'm looking for help to run a query that should come out looking like this
unit reports_to
Development Dept Representative Div Systems Group Development Dept Design Section Systems Group Production Spec Section Systems Group Proposal Section Systems Group
I can't see any way of doing this query though I know the PARENT is the reports_to column and that the UNITNO is the Department but can't find any way to run the query. Hoping you guys can help
Presumably, an RDL data set is only queried once before generating a report. And presumably, this is true even if the data set feeds a list control. However, I'd like to know if I can cause a data set to be queried repeatedly from a list control. I need to do this, because the data set contains aggregate functions that would be impacted by each "pass" the list control would make.
Is it possible to make a data set queried repeatedly in this fashion?
I've put together the following query, but it has been unsuccessful running it so far.
Code:
select * from Contractors.dbo.Contacts where VendorNo in (select No_ from [BMIS Live Database].dbo.[BMIS Live Database$Vendor] where [Name] like '%of%')
I receive the following error when I run it in Query Analyzer:
Code:
Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation conflict for equal to operation.
I'm trying to grab values from the column, "No_" in the Bmis.. database then only select data from the Contractors database if the VendorNo column holds one of the values grabbed from the Bmis.. database. I think my syntax is just completely wrong but hopefully someone might pick up on my mistakes. Any help would be greatly appreciated, thanks!
We are having atleast 7 - 8 servers in SQL Server 2000 and 2005 and each constituting around 2- to 30 databases and each database consists with a column related to date format.
We have problem in checking the LEAP YEAR significance in each and every database of every table to verify as there is no problem with the FEBRUARY 29th, to comply with.
I need to check the query which will do for all the databases and their in-built tables.
Can anyone one help me in this regard to check the above criteria.
We have adedicated MS-SQL Server for our CRM database and we have another MS-SQL database server for in house applications, written in c#.Net 2.0. To avoid duplicating information already in our CRM database, it would be nice if we could build a query that combined columns from both data sources.Example
Code Snippet
CRM database on Server1 Table Accounts
Columns AccountID UniqueIdentifier Primary Key
Name NvarChar(50)
Application Database on Server2 Table Transaction
Columns
TransactionID UniqueIdentifier Primary Key
AccountID UniqueIdentifier Amount Money
Would it be possible to create a T-SQL statement that will select
From Server1.CRM.Accounts INNER JOIN Server2.Application.Transaction
ON (Server1.CRM.Accounts.AccountID = Server2.CRM.Transaction.AccountID)
I appreciate there will need to be 2 connections and therefore some credential information would need to be passed too.
Is anything like this possible? I do not wish to copy the Name field from Server1.CRM.Accounts to Server2..Application.Transaction as this is duplication and any change to the value of Server1.CRM.Accounts.Name would not be reflected in the results.
I have a pretty complex query that aggregates lots of data and inserts multiple rows of that data into a reporting table. When I call this SPROC from SQL Server Management Studio, it executes in under 3 seconds. When I try to execute the same SPROC using .NET's SqlCommand object the query runs indefinitely until the CommandTimeout is reached. Why would this SPROC behave differently with the same inputs, but being called from .NET? Thanks for your help!
Ok, here's a funky one That I can't find an expanation for. If I go into EM and choose a table from a database and return all rows, I get immediate results and can start browsing records. If I go into query analyzer and do a select * on the same table, it takes up to 20-25 minutes to return the result set. This used to only take like 5 mins. What gives? Anyone seen this before?
I have two SQL server one in the office and other on remote location. I have some data on SQL in the office and some data on remote location and need to query the data.
SQL Server 7 When i ran a query in query analyzer i am getting the below error. once i closed and opened the query analyzer and ran the same query it worked fine.
pls the error which i got in first time.
Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream [Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream [Microsoft][ODBC SQL Server Driver]TDS buffer length too large [Microsoft][ODBC SQL Server Driver]TDS buffer length too large [Microsoft][ODBC SQL Server Driver]TDS buffer length too large [Microsoft][ODBC SQL Server Driver]TDS buffer length too large [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server [Microsoft][ODBC SQL Server Driver]TDS buffer length too large [Microsoft][ODBC SQL Server Driver]TDS buffer length too large [Microsoft][ODBC SQL Server Driver]TDS buffer length too large
Can someone please let me know how i can set up a query to execute once a week lets say on tuesdays at 6 am... i do not see how i can set this. can anyone help me please
Hi,I have a DLL that I want to run from the Query Analyzer. I tried thefollowing:USE master;EXEC sp_addextendedproc BLAH, '\Other-ServerPathToBlahBlah.dll'and I get the error:ODBC: Msg 0, Level 16, State 1Cannot load the DLL \Other-ServerPathToBlahBlah.dll, or one ofthe DLLs it references. Reason: 126(The specified module could not befound.).The DLL was written in Cobol. The "Other-Server" in the Path above isthe server that Cobol (and the DLL) is located on.I looked at the dependencies for the DLL and it includes (notsurprisingly) Cobol Dll's. It should find those on the other server.What can I do?Thanks!
I'm trying to optimize a long running (several hours) query. This query is a cross join on two tables. Table 1 has 3 fields - ROWID, LAt and Long. Table 2 has Name, Addr1,Addr2,City,State,Zip,Lat,Long.
Both tables has LatRad - Lat in radians, LonRad- Lon in Radians. Sin and Cos values of Lat and Lon are calulated and stored to be used in the distance formula.
What I'm trying to do here is find the nearest dealer (Table 2) for each of Table 1 rows. The Select statement takes a long time to execute as there are about 19 million recs on table 1 and 1250 rows in table 2. I ran into Log issues- filling the transaction log, so I'm currently using table variables and split up the process into 100000 recs at a time. I cross join and calculate the distance (@DistValues) and then find the minimum distance (tablevar2) for each rowid and then the result is inserted into another Table (ResultTable).
distance=3963.1*Case when cast(S.SinLat * T.SinLat + S.CosLat * T.cosLat * cos(T.Lonrad - s.Lonrad) as numeric(20,15)) not between -1.0 and 1.0 then 0.0 else acos(cast(S.SinLat * T.SinLat + S.CosLat * T.cosLat * cos(T.Lonrad - s.Lonrad) as numeric(20,15))) end
from dbo.TopNForProcess T , dbo.Table2 S where Isnull(T.Lat,0) <> 0 and Isnull(T.Lon,0)<> 0
Insert into @MinDistance
Select DataSeqno,Min(distance) From @DistValues Group by DataSeqno
Insert into ResultTable (DataSeqno,Lat2,Lon2,StoreNo,LAt1,Long1,distance)
Select D.DataSeqno, D.Lat2, D.Lon2, D.StoreNo, D.LAt1, D.Long1, M.distance from @DistValues D Inner Join @MinDistance M on D.DataSeqno = M.DataSeqno and D.Distance = M.Distance
I created a View called TopNForProcess which looks like this. This cut down the processing time compared to when I had this as the Subquery.
SELECT TOP (100000) DataSeqno, lat, Lon, LatRad, LonRad, SinLat, cosLat, SinLon, CosLon FROM Table1 WHERE (DataSeqno NOT IN (SELECT DataSeqno FROM dbo.ResultTable)) AND (ISNULL(lat, 0) <> 0) AND (ISNULL(Lon, 0) <> 0)
I have indexes on table table1 - Rowid and another one with Lat and lon. Table2 - Lat and Long.
Is there any way this can be optimized/improved? This is already in a stored procedure.
