Another instance of SQL Server 2000 was moved from Windows 2000 Server
"B" old to Windows 2003 Server "B" new, by restoring a backed
up copy from old to new.
The system has cross server updates, where Server "A" is updating
records in very large tables on Server "B".
After the move from Server "B" old to Server "B" new, suddenly
the cross server updates fail, but only when they are conducted within
a Begin and Commit Transaction batch.
The system works when I remove the Begin and Commit Transaction, and no
longer have them run in a batch.
Also, I had a cross server update, having "A" update records in
"B", from a delete trigger residing on "A" table. Deletes
against the "A" table fail so long as the trigger remains. Once I
remove the delete trigger from the "A" table, I can now delete
records on the "A" table.
SQL Server 2000 runs on Windows 2000 Server €œA€?.
Another instance of SQL Server 2000 was moved from Windows 2000 Server €œB€? old to Windows 2003 Server €œB€? new, by restoring a backed up copy from old to new.
The system has cross server updates, where Server €œA€? is updating records in very large tables on Server €œB€?.
After the move from Server €œB€? old to Server €œB€? new, suddenly the cross server updates fail, but only when they are conducted within a Begin and Commit Transaction batch.
The system works when I remove the Begin and Commit Transaction, and no longer have them run in a batch.
Also, I had a cross server update, having €œA€? update records in €œB€?, from a delete trigger residing on €œA€? table. Deletes against the €œA€? table fail so long as the trigger remains. Once I remove the delete trigger from the €œA€? table, I can now delete records on the €œA€? table.
When I check out > 2000 customer databases cross 8 SQL 2000 servers, I wish I could use "Select * from " + @server + "." + @databaseName + ".information_Schema.coulmns where column_name = " + @column_name.
It did not works in this way. However, why it works on "Select * from serverA.master.information_schema.columns"? It is also a cross server query but is on Master DB.
My support team inform me that my servers have been updated and rebooted on monday. Now I need to run some cross db queries today and I dont seem to be able to connect anymore. I am getting the (dreaded apparant but not surprisingly) Msg 17, Level 16, State 1, Line 1 [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Now first thing I did was try to drop both linked servers and add again. I am thinking that the particular server has a dynamic ip which I have determined. Ive noticed a work around on MSFT but it say about using a connection string with IP.
Can anyone help me work this out? id appreciate it.
I have a table with results from several inspection tests. The Pass/Fail parameter in the cross-tab output would be Pass if all tests for that serial number are Pass, and Fail if any of the results for that serial number are Fail.
How would I create a cross-tab query in SQL Server?
Table for input to cross-tab query: [Inspection Data] SerialNumber ParamName Result Pass/Fail
I want a query which is used in sql server like access query
TRANSFORM Sum(Q_DayBook.Debit) AS SumOfDebit SELECT Q_DayBook.Purticular, Sum(Q_DayBook.Debit) AS [Total Of Debit] FROM Q_DayBook GROUP BY Q_DayBook.Purticular PIVOT Q_DayBook.CDate;
hi, here I create a cross tab query in access 97 this is the code
TRANSFORM Count([1].ORD_NBR) AS [The Value] SELECT [1].SRMGR_NAME, Count([1].ORD_NBR) AS [Total Of ORD_NBR] FROM 1 GROUP BY [1].SRMGR_NAME PIVOT [1].ASR_SUPP;
I export this table to sqlerver 6.5 and run the same code. IT DIDNOT WORK My question is how can I implement crosstab query in sql server 6.5?
Recently the powers-that-be migrated the largest databases from one server to another, more powerful server while keeping some support data on the original server. My problem: I need to run queries on tables spanning both database servers. Unfortunately, I can't find any documentation on how to do this. Does anyone have any ideas?
Hi, can someone please explain some more how to use cross-validation in SQL Server 2008 (CTP)? I read here that it "is under Accuracy Charts in Business Intelligence Development Studio, in addition to being accessible programmatically via a stored procedure call". What is the stored procedure and input parameters? I'm actually interested in doing so inside an Integration Services package, in a data flow task.
I need to update serverB.databaseB.tableB.columnB value based on serverA.databaseA.tableA.columnA value change,
the update trigger in serverA.tableA works fine in updating a testing databaseB.tableB.columnB in the same serverA.
serverB is as the linked server in ServerA and DTC is on. when change the trigger to point to serverB.databaseB.... error:-------------------------------------------- Server: Msg 7395, Level 16, State 2, Procedure trInsUpdDel_InboundCannedMessages, Line 169 Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF. [OLE/DB provider returned message: Cannot start more transactions on this session.]
I am trying to compare the data from 2 different servers. It is the same table. I did a select * and put the data into 2 different text files. I then used the Windows NT findstr utility to compare them to find the differences. I used the command findstr /v /i /x /g:file1 file2 > file3, where file1 is the larger table, and file2 is the smaller one. I want to find the records in file2 which are different or do not exist. I recieved no data back. I have ASCII characters in the text files. My question is does anyone know of a better way to do this, or how to make this command work?
Hi! I am using the Cross Validation tab in BIDS. Can you explain why my Liklihood Log Score is a negative number (according to BOL - meaning it is worse than a random guess) when my lift chart shoes that the alogorithm is significantly better than a random guess.
Also, the BOL definition for truenegative and falsenegative are hard for me to interpret. If I have a target state of "Yes", can you please put those definitions in yes/no terms for me?
I am a newbie in using MS SQL server with analysis services. There seems to be no 'cross-validation' tool in MS SQL which is frequently used in data mining and even statistics. Is there anyone having similar difficulties? Is there any solution like a small scripts to divide the given dataset with multiple folds? Your valuable comments and feedbacks would be appreciated.
I was reading Kenneth Fisher's and Dwain Camps' articles on unpivoting using cross apply... And I can actually get them to work....
CREATE TABLE #TxCycle( Cycle INT NOT NULL, PatientID INT NOT NULL, ALOPECIA TINYINT, Causality1 TINYINT, Relatedness1 TINYINT,
[Code] ....
The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand? Dwain does this
-- DDL and sample data for UNPIVOT Example 2 CREATE TABLE #Suppliers (ID INT, Product VARCHAR(500) ,Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500) ,City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500))
Can this be adapted if you don't know all the column names beforehand? (Likely not). Back in the dark ages, when I was working on a database like this, it was in Access, and I could loop over the fields collection and evaluate each field name. (Yes, I know you're not supposed to store information in field names, but I inherited that mess!)
I'm unable to reproduce the error. when they upgrade their OS and SQL EXPRESS to a more recent version the error disappears.
The error is: Incorrect syntax near '.'
the query in question resembles this:
Select column1, column2 from Table1 T cross apply function(t.column4,t.column5) F where column3 = 'XXXX'
I made sure that the compatibility level is greater than 90 this error is happening on SQL2005 SP2 as well as SQL2008 with SP2 (but not all clients are suffering from the same problem)
Can it be the .net framework? Although the machines had .net framework 3.52.
Can the OS be an issue? The OS' seem to be old, Windows Server 2008 SP2
I've tried to reproduce the error by setting up virtual machines with same OS and SQL but, again, can't reproduce.
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that -- two queries against a columnstore index that each execute in batch mode -- will continue to execute in batch mode when inner joined. -- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows IF OBJECT_ID('dbo.IntCount') IS NOT NULL DROP FUNCTION dbo.IntCount;
I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream". OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?
Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.
create table a (id int, name varchar(10)); create table b(id int, sal int); insert into a values(1,'John'),(1,'ken'),(2,'paul'); insert into b values(1,400),(1,500);
select * from a cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal 1John500 1ken500 2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
update xxx_TableName_xxx set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit: Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit: Could it be to do with this linked server setting? Collation Compatible right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane... (0 row(s) affected) (1 row(s) affected) How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect) (1 row(s) affected) If anyone has any ideas where to look next, I'd appreciate it. Thanks
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
I have client machine in one domain, say A domain , on which I have installed .net 2.0 without any problem. I have report server running in other domain (B domain) where I have deployed few models which will be used from domain A to create ad hoc reports by launching Report Builder. I can access other reports in domain B from A
But when I click on Report Builder ..it tries to connect to server in domain B but fails to connect and shows following log in a text pop up
I know, problem is because of cross domain communication but question is then how to fix it ? what changes I need to do for that in firewall, domain etc etc settings?
Here is the error, I have highlighted lines which might give some clue:
ERROR SUMMARY Below is a summary of the errors, details of these errors are listed later in the log. * Activation of http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application resulted in exception. Following failure messages were detected: + Downloading http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application did not succeed. + The remote server returned an error: (500) Internal Server Error.
COMPONENT STORE TRANSACTION FAILURE SUMMARY No transaction error was detected.
WARNINGS There were no warnings during this operation.
OPERATION PROGRESS STATUS * [12/19/2006 6:49:58 AM] : Activation of http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application has started.
ERROR DETAILS Following errors were detected during this operation. * [12/19/2006 6:50:14 AM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype) - Downloading http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application did not succeed. - Source: System.Deployment - Stack trace: at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next) at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles() at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState) at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation) at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation) at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options) at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut) at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state) --- Inner Exception --- System.Net.WebException - The remote server returned an error: (500) Internal Server Error. - Source: System - Stack trace: at System.Net.HttpWebRequest.GetResponse() at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
COMPONENT STORE TRANSACTION DETAILS No transaction information is available.
I'm new to this forum. This 'problem' has occured many times, but I've always found a way around it. I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values. Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank? Thank you very much.
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000 UPDATE [dbo].[CC_Info_T] SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' WHERE [Acct_Num_CH] IS NOT NULL WHILE @@ROWCOUNT > 0 BEGIN SET rowcount 10000 UPDATE [dbo].[CC_Info_T] SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' WHERE [Acct_Num_CH] IS NOT NULL END SET rowcount 0
Hi, I'm just starting to learn SQL, just on the querying side and have what I think is quite a tricky function. It may be simple for you guys.
I want to make a report based on three tables. Enrolment, Course and Student. Its pretty easy until I need to crosstab it.
It's more complex than this (more fields but this gives the gist) Basically I want AcademicYearId, StudentID, coursecode, completionstatusid which would give the following.
I have managed to group it by academicyearid, coursecode and completionstatusid and done the counts ok but I want to twist the completionstatusid so each type of status has its own column like this
