Can anyone know the difference between using distributed queries for transfering data and replication.
Which is more reliable and which is the better way?
If the volume of database over 2,000,000 with nearly 100 columns,which is the better method?
I have Two Access Databases connected as Linked servers through ODBC driver.
I want to run a Distributed Query .
The SQL is as follows:
SELECT Alias1.FiledNames,Alias2.FiledNames from
FROM LinkedServer1.DatabaseName1.dbo.Tablename1 AS Alias1,
LinkedServer2.DatabaseName2.dbo.Tablename2 AS Alias2
It gives me this error message.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 12 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
Am i missing something?
Thanks in Adavance
Puru
The process could not execute 'sp_repldone/sp_replcounters' on 'sqldb2008'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011).The specified LSN {00000000:00000000:0000} for repldone log scan occurs before the current start of replication in the log {001317bf:0000f736:0008}. (Source:
MSSQLServer, Error number: 18768).The process could not set the last distributed transaction. (Source: MSSQL_REPL, The process could not execute 'sp_repldone/sp_replcounters' on 'sqldb2008'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Last Night I had a Dream
And it involved my favourite Design Technique - Simplicity
So to achieve Simple Replication across geographically disparate Servers we could use:-
300 plus SQL2000 Servers enabling 1000 Concurrent Active Clients (10% actual light Activity)
WAN = National Private Secured (ping 300ms max) (128kbps leased line Min)
One Interface Server constantly Running A Simple Dynamically Built Partitioned View (removing down servers)
One Stored Proc (or more) that Synchronizes the DPV Updateable Partitioned View with
Interface Mirror Table DeNormalized Holding a Physical Copy of each of the Subscribers/Publishers/Client Sql Servers Data
The Question is:-
Was my Dream a Nightmare OR A Dream Come True ?
I Know it's down to the Network Quality to a great degree but (That's the suck it and see part of the question)
but as a form of replication it seems a very simple platform that could possibly tackle our friend
The DCP (Data Consistency Problem) with Client Update DateTime Column & frequent activation (30 Secs).
Has anyone had much experience with this type of Scaling out over a WAN ?
Is it worth a Try in the Real World ?
GW
Hi,
Anyone can send the Sql query questions i need to pratice quries on sqlserver.
Regards,
Hari Prasad
Hi Friends
I want Some Power Point Presentations In MS SQL SERVER 2000
1. Joins
2. Views
3. Basic Queries
4. Functions
5. Store Procedures and Cursors
All the above i want PPTs could you any body please send me
Here are the tables I am dealing with:
Tables
license TABLE
---------------
license_id int PK
vendor_id int FK
po_id int FK Nulls
license_type_id int FK
lic_user_id int FK Nulls
location_id int FK Nulls
lic_start_date smalldatetime Nulls
days_allowed int Nulls
serial_num varchar(50) Nulls
activation_key varchar(50) Nulls
max_users int Nulls
comments varchar(1000) Nulls
software TABLE
------------------
software_id int PK
os_id int
software_name varchar(150)
comments varchar(1000) Nulls
software_license TABLE
--------------------------
license_id int PK , FK
software_id int PK , FK
comments varchar(1000) Nulls
dept_license TABLE
---------------------
dept_id int PK , FK
license_id int PK , FK
comments varchar(1000) Nulls
purch_order TABLE
---------------------
po_id int PK
po_number varchar(50)
po_date smalldatetime
comments varchar(1000) Nulls
dept_purch_order TABLE
----------------------------
po_id int PK , FK
dept_id int PK , FK
comments varchar(1000) Nulls
I am using the following stored proceedure to try to create a useful report from those tables:
Code Block
ALTER PROCEDURE [dbo].[ListDepartmentLicenses]
@Department int,
@Software int
AS
BEGIN
SET NOCOUNT ON;
SELECT dept_license.license_id,
license.lic_start_date,
department.dept_name,
purch_order.po_number,
software.software_name
FROM dept_license
JOIN license
ON dept_license.license_id = license.license_id
JOIN department
ON dept_license.dept_id = department.dept_id
JOIN dept_purch_order
ON dept_purch_order.dept_id = dept_license.dept_id
JOIN purch_order
ON dept_purch_order.po_id = purch_order.po_id
JOIN software_license
ON dept_license.license_id = software_license.license_id
JOIN software
ON software_license.software_id = software.software_id
WHERE (department.dept_id = @Department or @Department Is Null) and
(software.software_id = @Software or @Software Is Null)
ORDER BY license.lic_start_date
END
The sproc compiles fine but I get no rows returned.
I am running simple queries against test SQL Express installation and they take a very long time to return data. I have two SQL Express instances installed on colleagues' machines to which I connect for my testing and both exhibit the same problem. The setup is Windows XP SP 2 with 2 GB RAM and 3.6 GHz CPU. I am querying a table with around 7000 records and my query is simply SELECT TOP 1000 * FROM MyTable. It takes over 10 seconds to return the recordset!
I have done the research and found posts, which talk about AUTO_CLOSE option, indexes, query execution plan, etc. I have done everything those posts recommend, but performance is still terrible. All the instances have SQL 2005 SP2 applied.
I also found that the query runs fast locally on each SQL Express instance, the problem seem to happen when I am trying to pull the data over the network.
I am really not sure what else to look for.
Thank you,
Michael
We have a test db, a staging db and a live db. I have a stored procedure that runs fine on test and staging, but throws the following error on live.
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
The stored procedure uses linked servers and a transaction.
We're using the following transaction code in the stored procedure
BEGIN
BEGIN TRANSACTION
BEGIN TRY
---
procedure stuff here
---
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity, @ErrorState, @ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
END CATCH
END
I found the following link which seems to be the problem we're experiencinghttp://support.microsoft.com/kb/937517
The link includes a workaround which is the following:
"To prevent the SQLNCLI provider from sending an attention signal to the server, use the SQLNCLI provider to consume fully any rowsets that the OLE DB consumer creates. "
How do I use the SQLNCLI provider to fully consume any rowsets?
Hello,
does the .NET framework support distributed transactions somehow?
The SqlConnection/SqlTransaction classes doesn't seem to support them...
TIA. -julio
Hi Folks,
Is there anyway of running a Store Procedure in Database A that's going against database B? I have a store procedure that does a select on table A in database A and a select on table B in database B.
The problem is that the user have exec rights to the store procedure, which is an object in database A. The store procedure won't run because of permission rights to table B in database B.
Is there anyway of queryiny table B without giving the user select rights to that table? Anyone out there had the same problem?
thank you
Joe R.
Hi to everyone!
Any experience handling a Store Procedure that has distribution transactions?
Here is a story.
We have a store procedure #1 that inserts or updates data in two tables A and B on SQL Server 7.0 then it calls another store procedure #2 that:
1. updates C table in the same database on SQL Server from Oracle Server
2. deletes that record on Oracle Server
3. inserts a record on Oracle from table A
4. inserts the same record into table D on SQL Server from Oracle Server.
People who wrote store procedure #1 put Commit Tran before executing #2.
The questions are:
1. Is it correct that we don't have Commit Tran for #2?
2. What do you think about Nested Begin - Commit Tran?
Any help is highly appreciated.
Sima
I have a ODBC data source setup for the AS400DB2 which is AS400JDE
The linked server has been created and all the tables are visible within enterprise manager.
If I had a table F0101, datasource = AS400JDE and linked server of AS400
How would I query it.
I gave tried AS400.AS400JDE.DBO.F0101, but i get the following error message.
OLE DB provider 'MSDASQL' does not contain table '"as400jde"."dbo"."f0101"'.
i added a linked sql 7 production server to my sql 7 development server so i could perform some data comparisons between the two
servers. after i linked in the server, i was able to see all the icons for the various tables in the production server. i went to query
analyzer and executed the following openquery:
select * from openquery(itdev_s08, 'select * from dbo.tablename')
it returned all the rows in the table. however, i want to comapre several different values so i needed a better way to query.
i then decided to try the distrbuted query below:
select * from linkedserver.dbname.dbo.tablename
however instead of a result set i got the error message below:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' does not contain table '"dbname"."dbo"."tablename"'.
i went to BOL and it told me the only reason i would get this error is if the object did not exist or if i did not have permission
to use it. however, i know the object exists and i have the appropriate permission becuase i can obtain a result set from the
first query in the same query analyzer window. is my syntax incorrect? what am i doing wrong?
thanks!
tammy moisan
Hi,
I am trying to execute a proc on Linked Server. Now as the Linked server name starts with 2, all of the following fails...
Any help to make it work highly appreciated...
exec [2Kxyz.SQLJobMon.dbo.usp_SQLAlertSMTPEmail]
exec 2Kxyz.SQLJobMon.dbo.usp_SQLAlertSMTPEmail
exec "2Kxyz.SQLJobMon.dbo.usp_SQLAlertSMTPEmail"
Does SQL Server actually support distributed transactions over, say, two tables in different databases but that reside on the same server? When I try to execute such a transaction in which one part of the transaction violates referential integrity and hence should not be executed, causing the rest of the transaction to, supposedly, rollback, the transaction does not roll back but instead produces an error message and executes the second, valid half of the transaction anyway.
Any help or suggestions gratefully accepted.
Hi Guys.
Many posted this message and no one anwered. i am facing the same problem now.
Got the error message
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
My MSDTC is on. I had setup linked server using OLEDB.
Microsoft did'nt give enough info or solution for this. ANybody faced this prob and solved it?
Any suggestions , comments, solutions?
-MAK
I have a procedure where it runs a procedure on another server and returns the results to the calling procedure and dumps it into a temp table..
I get the following message:
-----
Server: Msg 7391, Level 16, State 1, Procedure proc1, Line 60
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions.
[OLE/DB provider returned message: Distributed transaction error]
----
But both the servers are running the distributed transaction corordinator
example:
create procedure dbo.proc1
@param1 int
as
create table #temp
(col1 int
col2 varchar(255)
)
insert into #temp
EXEC server.database.dbo.proc2 @param1 = @param1
go
Hi all,
I am trying to merge data of 2 tables on different servers with an insert statement.
INSERT INTO SERVER1.db.owner.table
select s2.* from SERVER2.db.owner.table as s2
LEFT JOIN SERVER1.db.owner.table as s1
ON s1.key=s2.key
where s1.key is null
I got this error.
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
while the select query is giving the result.
I have done simmilar inserts on some other tables which worked fine
I have created sp_addlinkedserver.
and DTC set on both servers.
any help will be greatly appreciated
Hi All,
I am trying to use distributed transaction (using linked Server).
But getting the folloing error..
Some one please help...
following is the error...
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Hey,
I have a Sql 2000 w/ SP4 on Server 2003 running an sp that inserts and updates to a 2005 64 bit DB w SP 1 on Server 2003. The SP has worked forever. No code or server changes. Now all of the sudden I randomly get this error. It will work, then fail 2Xs, then work, then fail. Very bazaar.
Any help is appreciated! I have done the MSDTS, ran catalog sps, forced TCP/IP. I'm lost.
If I run the sp on the 64bit box it works fine, but on the 32bit box now, I get the frequent error below.
[OLE/DB provider returned message: Unspecified error]
Msg 8525, Level 16, State 1, Procedure sp_xxx_xxx_xxx, Line 1496
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
The line number in the error will change as well and is not always consistent.
Thank you!!!!
Hi everybody.
I have a question about the database performance.
Suppose we have 15 tables distributed to 3 databases. So if we want to to load some related data from diffrent tables of diffrent databases, we have to make more connections to the other databases, and it takes some time to establish the connection. but if we use 1 database with 15 tables, then we wont have this problem and we wont make more connections. but using 1 database, will grow the size of database file (*.mdf) and sure this couses to take more time of file operation like record-seek-time, record-insertion-time and etc.
which of these solutions have better performance? Distributed databases or single one?
Thanks
Hi again,
I do not know when to use Distributed Transaction.
Would you please give an example about its usage or give a tutorials about this ?
Thanks
Hi There
I realize this is probably a basic question or at least i hope so but i cannot get distributed transactions working.
BOL is just driving me in circles.
I am running SS2000, MSDTC is up and running on the server.
I have added a trigger to a table where on insert or update the triggers fires and inserts a row to a remote server.
When i execute the update or insert i get the error:
Cannot start a distributed transaction.
I have tried stating BEGIN DISTRIBUTED TRANSACTION before the update but i get the same error, i have also read up on SET REMOTE_PROC_TRANSACTIONS, but this is not a remote sp.
Can anyone please reccomend a good link as to how to get distributed transactions to work.
Thanx
I work in the data warehouse team of my organization. We are currently rearchitecting our server environment. One of the ideas on the table is to devote a separate server for ETL processing. The databases would reside on several other servers. The ETL server would run the SSIS packages. I'm questioning if this would be a good idea.
The database servers would continue to carry the load of the query processing for the ETL. But with the ETL process on a separate server, the resultsets would need to go over the network for the SSIS package to then work on them. Plus, they would then have to go back over the wire to the destination server.
Are there advantages to this setup? Does this setup have better scalability? Or, would it be better to run the ETL from either the source or destination database server?
Thanks for your consideration.
How to make a SQL Server database distributed?
View 1 Replies View RelatedI am getting the following error when with SQL Express.SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. While I was very please to see such a verbose error and directions on where to find the answer I have yet to figure out how to turn this option on... I tried sp_configure 'Ad Hoc Distributed Queries', 1 but got the following error The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.If I execute only sp_configure it does not list Ad Hoc Distributied Queries as an option. I checked the sql Books Online and it tells me to use the Surface configuration tool which SQL Express does not seem to have.... Could someone help me out with this?Thanks - Mark
View 3 Replies View Relatedi am inserting new record in linked server and i need to get the id (which is of course autonumber) of newly added record. can't i get it using SCOPE_IDENTITY( ) ? SCOPE_IDENTITY( ) seems to be returning null. so SCOPE_IDENTITY( ) doesn't work in distributed transaction?
View 2 Replies View RelatedI am using SQL server 7.0 and i have created oracle8i linked server(Using MSDAORA as provider) in it.
When i run distributed queries between SQl server and Oracle server it works fine.But when i try to run distributed transaction between two servers ( BEGIN DISTRIBUTED TRANSACTION..)it reports an error saying Distributed transactions are not supported by MSDAORA.
My question is; is it possible to run distributed transaction between SQL server and oracle server (where oracle server is a linked server in my SQL server)?
Actually i want to run this transaction in DTS package which updates and transfers data amongst various servers.
Thanx
Regards,
Rahul
I am trying to set up linked servers between several SQL 7.0 servers, but everytime I try, I get the message:
Error 18456: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'
Only thing is, I thought I was logged into both servers using my NT name. Any ideas?
Can anyone tell me why Query B (see below) works but Query A does not? When Query A is run the following error is received. I would love to know why using a subquery allows Query B to run.
Server: Msg 8623, Level 16, State 2, Line 1
Internal Query Processor Error: The query processor could not produce a query
plan.
Query A.
SELECT pt.Description,
pa.Method_Order,
os.LogAction,
Sum(pa.Amount) Total
FROM BO_PaymentAmountsApplied pa,
BO_OrderStatusLog os,
Members.members.dbo.MBR_PaymentTypes pt
WHEREpa.LogID = os.LogID AND
os.LogAction IN (1,2,3) AND
pt.PmntTypeID = pa.MethodID AND
pa.OrderID = 1526925
GROUP BY pt.Description, pa.Method_Order, os.LogAction
Query B.
SELECT pt.Description,
pa.Method_Order,
os.LogAction,
Sum(pa.Amount) Total
FROM BO_PaymentAmountsApplied pa,
BO_OrderStatusLog os,
Members.members.dbo.MBR_PaymentTypes pt
WHERE (pa.LogID = os.LogID) AND
(os.LogAction IN (1,2,3)) AND
(pt.PmntTypeID = pa.MethodID) AND
(os.orderid = pa.orderid) AND
(pa.OrderID in (select orderid from BO_PaymentAmountsApplied where orderid = 1526925))
GROUP BY pt.Description, pa.Method_Order, os.LogAction
Hi,
I am trying to use linked servers (all SQLServer 6.5 and 7.0). When I issue a distributed query utilizing the four-part qualified table name for the distributed machine and using the LIKE operator, the execution plan shows that the remote query is returning ALL the rows from the remote distributed server, then performing the LIKE filter locally on my server! Needless to say, performance sux!
Any ideas what I may be doing wrong or what settings I am unaware of?
Example query:
select * from server.database.schema.table where column1 like 'A%'
Thanx in advance for any help.
I am trying to set up linked servers between several SQL 7.0 servers, but everytime I try, I get the message:
Error 18456: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'
Only thing is, I thought I was logged into both servers using my NT name. Any ideas?