Please could I have some advice I am running a query on the live node of a cluster it takes well over an hour to run even at a quite ish time when CPU is bellow 50% memory disk queues all normal. But when I run it the other node it take less than a minute.
I know it would probably take longer on the live due to having users on but not that much longer, there is no blocking why is there such a huge differences.
I want to check a record in my table which has time ins and time outs feilds. I want to check on insertionof any new record that a new record should not be inserted on same timings and before 45 mins of timein and after 45 mins of timeout. How do i do it sql query ?
I'm new to SQL and I'm trying to write a stored procdeure in my current database that queries a different database on a different server. What is the best way to do this?
I am currently trying to write a SELECT that returns the difference between 2 identical tables in 2 different SQL databases on 2 different SQL servers.
I believe I have setup the linked servers properly.
Here is the select I wrote;
select * from [dbnameA].dbo.Item where not exists (select * from [dbnameB].dbo.ItemSD Where [dbnameA].dbo.Item.IMA_ItemID = [dbnameB].dbo.ItemSD.IMA_ItemID)
The error I receive is;
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbnameB.dbo.ItemSD'.
Any suggestions are greatly appreciated!! Thanks, Michiel
i have two servers where i have logged in, hr and transport
i want to write a query in query analyser of the hr server to retrieve information in the transport server. how will i do it
i tried with select * from servername.databasename.permission.tablename
i get a message
Server: Msg 7202, Level 11, State 2, Line 1 Could not find server 'transport' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
For example I have 15 records in which accountID is a PK. AccountID 1,2,3,4,5 is linked on server1. I want to find out records that are not linked. So its record 6 - 15. Can someone show the stored procs for this scenario? Or the query statement to do this... Im new to linked servers. Thanks alot.
Hi, I'm trying to write an update query across two servers but getting getting two errors. The first server is called: blatweb2 The second server is called: blbr-teamserv This is the query I'm trying to use:1 update blatweb2.dbo.knousefoods.products a 2 Set LuckyLeafPieFillingSort = 3 (Select LuckyLeafPieFillingSort from blbr-teamserv.dbo.knouse.products b where a.productname = b.productname) I'm getting the following errors. Do you know what could be wrong? ThanksMsg 102, Level 15, State 1, Line 1Incorrect syntax near 'a'.Msg 102, Level 15, State 1, Line 3Incorrect syntax near '-'.
I know you can execute stored procedures on remote servers in SQL Server 6.5, but can you run regular queries as well using the 4 part name qualification?
Example: select * from server1.database1.dbo.table1
If not, is there another way I can manipulate data on remoter servers?
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 successfully connected to a sybase 11 database and have successfully run a couple of open query statements against this database, I have now placed this open query in a stored procedure and it works well, but when I want to pass parameter variables has part of my open query it does not like it. It asks me to declare the variable which is a parameter of the stored procedure. Is there any way I can pass in a variable value has part of my query
************************************************** *********************8 CREATE PROCEDURE qse_check_label_projectid @projectid char(18) AS
Select * from openquery(MRTEST32,'SELECT Project_id FROM DBO.MRT_PROJECT WHERE Project_id = @PROJECTID ') ************************************************** *************************** Please I believe there should be a way to pass a value through this open query function!
select top 10 * from dccs_danville.INTDCCS_DV.gdb.tblload select top 10 * from dccs_danville.INTDCCS_DV.dbo.tblload select top 10 * from dccs_danville.INTDCCS_DV...tblload select top 10 * from dccs_danville.INTDCCS_DV.tblload select top 10 * from dccs_danville...tblload select top 10 * from dccs_danville.INTDCCS_DV.tblload
also tried EXEC sp_tables_ex 'dccs_danville' tblload is able to be queried.
I am trying to design a package that needs to compare two tables in two diiferent servers. Basically I need to insert records into one server by comparing existing records with second table in other server. Is ther any way I can do it with out using Linked server?. Both tables have same structure.
I am trying to create a query that will get data from two diffrent SQL servers. I am trying to link the two servers by using the sp_addlinkedserver. This is the code I use:
USE master; GO EXEC sp_addlinkedserver   N'SC00SRVERP501R1',   N'SQL Server'; GO
and it executes successfully. But when I try to run my query I get the error message "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'."As far as I understand I need to provide some credentials to the linked server, but how do I do that ?
I have a query that I need to run where I join two tables that both reside on different servers. I use an INNER JOIN statement to attempt to join these tables, but for some reason I am getting the following error message...
"
Cannot resolve collation conflict for equal to operation."
The query is as follows...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation FROM mrcsmis.INFORMATION_SCHEMA.TABLES INNER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME) WHERE (TABLE_TYPE = 'BASE TABLE')
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1. After applying SP2, we get the following msg when running a query against the linked server. I cannot find anything in the SP2 doc that indicates a change of behavior for linked servers. Any ideas ?
Bill
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'DBAmp.DBAmp' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I need a query to find out the server uptime and downtime of the server from MOM database, i don't know in which tables MOM actually stores this infomation.
I need this very urgently.
Thanks in advance
You can use this code to find out the information stored in the MOM tables:- ############################################################################ create PROC [dbo].[SearchMyTables]
We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions. After applying SP2, we get the following error message when running a query against the linked server. I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers. Any ideas ?
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. Toby
I can easily query multiple servers using the multi-server query function in Central Management Server and write some of the results to logging tables. I would like to be able to do this via a scheduled job. So far I am finding that even setting up Master/Target Servers this may not work and the only workaround is either using SSIS, SQLCMD (by basically hard coding the servername) and possibly Powershell.
tell me if they have been successful just using standard jobs and querying against multiple servers?
If I can't save the results to a 'central' database/table (I can do this when in SSMS), but can still query against multiple servers I was thinking I could write the results to a CSV file that a SSIS job picks up.
I have attempted using SSIS to iterate through servers and have been plagued with intermittent connection issues when using a For...Loop container.
Greetings,I have 3 servers all running SQL Server 2000 - 8.00.818. Lets callthem parent, child1, and child 2.On parent, I create a view called item as follows:CREATE view Item asselect * from child1.dbchild1.dbo.Item union allselect * from child2.DBChild2.dbo.ItemOn child1 and child2, I have a table "item" with a column named "id"datatype uniqueidentifier (and many other columns). There is anon-clustered index created over column "id".When I connect to the parent server and select from the viewSelect id, col1, col2, …. From item where id =‘280A33E0-5B61-4194-B242-0E184C46BB59'The query is distributed to the children "correctly" (meaning itexecutes entirely (including the where clause) on the children serverand one row is returned to the parent).However, when I select based on a list of idsSelect id, col1, col2, …. From item where id in(‘280A33E0-5B61-4194-B242-0E184C46BB59',‘376FA839-B48A-4599-BC67-25C6820FE105')the plan shows that the entire contents of both children item tables(millions of rows each) are pulled from the children to the parent,and THEN the where criteria is applied.Oddly enough, if I put the list of id's I want into a temp tableselect * from #bv1id------------------------------------280A33E0-5B61-4194-B242-0E184C46BB59376FA839-B48A-4599-BC67-25C6820FE105and thenSelect id, col1, col2, …. From item where id in (select * from #bv1)the query executes with the where criteria applied on the childrendatabases saving millions of rows being copied back to the parentserver.So, I have a hack that works (using the temp table) for this case, butI really don't understand the root cause. After reading online books,in a way I am confused why ANY of the processing is done on thechildren servers. I quote:================================================Remote Query ExecutionSQL Server attempts to delegate as much of the evaluation of adistributed query to the SQL Command Provider as possible. An SQLquery that accesses only the remote tables stored in the provider'sdata source is extracted from the original distributed query andexecuted against the provider. This reduces the number of rowsreturned from the provider and allows the provider to use its indexesin evaluating the query.Considerations that affect how much of the original distributed querygets delegated to the SQL Command Provider include:•The dialect level supported by the SQL Command ProviderSQL Server delegates operations only if they are supported by thespecific dialect level. The dialect levels from highest to lowest are:SQL Server, SQL-92 Entry level, ODBC core, and Jet. The higher thedialect level, the more operations SQL Server can delegate to theprovider.Note The SQL Server dialect level is used when the providercorresponds to a SQL Server linked server.Each dialect level is a superset of the lower levels. Therefore, if anoperation is delegated to a particular level, then Queries involvingthe following are never delegated to a provider and are always it isalso delegated to all higher levels.evaluated locally:•bit•uniqueidentifier================================================This suggests to me that any query having where criteria applied to adatatype uniqueidentifier will have the where criteria applied AFTERdata is returned from the linked server.Any ideas on the root problem, and a better solution to get the queryand all the where criteria applied on the remoted linked server?Thanks,Bernie
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive, DB_NAME(mf.database_id) AS DatabaseName, db.create_Date, DateDiff(day, db.create_date, getDate()) Age, sum((mf.size*8))/1024 SizeMB
[Code] ...
How would I best accomplish this if I want to implement it using a TSQL procedure?
can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server. 2) Linking x64 or IA64 servers to x86 servers. 3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are: ------------------------------ TCP Provider: An existing connection was forcibly closed by the remote host. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure". (Microsoft SQL Server, Error: 10054) ------------------------------ And ------------------------------ The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB". OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
Hi, We have two servers one is the LIVE SERVER and another one is TEST SERVER. The Dumps from LIVE SERVER has been restored onto TESTSERVER. Now, when I try to run some applications in TESTSERVER it starts executing it in LIVE SERVER. When I checked up the SELECT @@SERVERNAME value in TEST SERVER, it still refers to TEST SERVER only.
Is there any other table or value I need to check. Can anyone help me on this.
I need to archive about a years worth of data from 4 tables, but as available disk space is very limited on the source server, I need to copy out the selected records to a seperate server. Is this possible and how.
does anyone have a script that I can adapt to collect the status of all my servers please? I am managing 20+ servers, plus MSDE installs with various applications and its now a nightmare tracking each one. I am building a central database where information such as this can be referenced but i need to populate it and then updated it regularly, hopefully with a scheduled job and some nifty sp's.
any ideas how i can achieve it if the scripts are not already around? i use PRINT @@VERSION currently, can i execute this in a different format to assess a different server from the one running the process?
