Hi All:
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.
thanks
-D
Hi all,
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.
"Impossible is Nothing"
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
001 Test1 3.43 Pass
001 Test2 3.27 Pass
001 Test3 2.97 Pass
002 Test1 2.88 Pass
002 Test2 3.01 Pass
002 Test3 4.22 Fail
003 Test1 3.11 Pass
003 Test2 2.91 Pass
003 Test3 3.28 Pass
Cross-tab Query Output:
Test1 Test2 Test3 Pass/Fail
001 3.43 3.27 2.97 Pass
002 2.88 3.01 4.22 Fail
003 3.11 2.91 3.28 Pass
Thanks,
Sam
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;
I am attempting to remember the string to dump/restore a database to a different physical server.
I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?
Thanks
I am attempting to remember the string to dump/restore a database to a different physical server.
I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?
Thanks
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?
any one can help I really do appreciate
Ali
Hi,
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?
Thanks!
The system has worked for two years.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 backedup copy from old to new.The system has cross server updates, where Server "A" is updatingrecords in very large tables on Server "B".After the move from Server "B" old to Server "B" new, suddenlythe cross server updates fail, but only when they are conducted withina Begin and Commit Transaction batch.The system works when I remove the Begin and Commit Transaction, and nolonger 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. Deletesagainst the "A" table fail so long as the trigger remains. Once Iremove the delete trigger from the "A" table, I can now deleterecords on the "A" table.Thanks. Dotnet Fellow
View 9 Replies View Related 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.
Thanks,
Gustavo Frederico
The system has worked for two years.
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.
Thanks. Dotnet Fellow
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.]
thanks for the help
David
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?
View 1 Replies View RelatedHi! 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?
Thanks.
Ann
Dear forum users,
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.
Minnetongka
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 have 2 databases on the same server
One has a function, the other has the tables and views
using dba
select dbo.function(t.column) as x from dbb.dbo.table as t
gives m an invalid object name of dbo.table
using dba
select top 1 * from dbb.dbo.table works fine.
also if i create the function on the same db it works.
I have created SSIS package to transfer table data(1500 rows tbl size=140 MB).
To transfer data across the network its taking around 1 hr.
I have tried by removing indexes and constraints on destination table.
How should I improve the speed as the table is very small.
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.
writing a cross join query with one table:
Cities(City_name, X_coordinate, Y_coordinate)
the result should be all combinations without reverse column returns
SELECT * FROM [dbo].[Cities] as P1
Cross JOIN [dbo].[cities] as p2
where (p1.City_name != p2.City_name) and ???
for example if there are three Cities as A,B,C the result should be: A->B, A->C, B->C (without the returns B->A, C->A, C->B)
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;
[Code] .....
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.
Following is the query that I'm running:
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.
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:
------------------------------------------------------------------------------------------
PLATFORM VERSION INFO
Windows : 5.2.3790.65536 (Win32NT)
Common Language Runtime : 2.0.50727.42
System.Deployment.dll : 2.0.50727.42 (RTM.050727-4200)
mscorwks.dll : 2.0.50727.42 (RTM.050727-4200)
dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
dfshim.dll : 2.0.50727.42 (RTM.050727-4200)
SOURCES
Deployment url : http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application
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 should've posted this thread here....
http://www.dbforums.com/t1005070.html
I need to format the following tables:
tbEmployees
EmployeeID | fName | lName
-------------------------------------------
jdoe | Joe | Doe
bsmith | Blake | Smith
tbDepartments
DepartmentID | Department
-------------------------------
ENG | Engineering
DET | Detailing
tbDepartmentEmployees
fkEmployee ID | fkEmployeeID
------------------------------
ENG | jdoe
DET | bsmith
tbProjects
ProjectID |
---------------
1001
tbProjectTeam
fkProjectID | fkEmployeeID | fkDepartmentID
-------------------------------------------
1001 | jdoe | ENG
1001 | bsmith | DET
To the following view :
vProjects
ProjectID | Engineer | Detailer
-------------------------------------
1001 | Joe Doe | Blake Smith
Any Ideas?
Mike B
Is it possible change to cross tab using code?
Before code:
NAMETYPEVALUE
AAAHEIGHT66
AAAWEIGHT198
After code
NAMEHEIGHTWEIGHT
AAA66198
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.
AcademicYearId StudentId coursecode completionstatusid
05/06 912345 FWFT1 CONTINUING
05/06 914587 FWFT1 CONTINUING
05/06 987654 FWFT1 WITHDRAWN
05/06 954376 FWFT1 CONTINUING
05/06 934512 FWFT1 TRANSFERRED
05/06 923000 FWFT1 TRANSFERRED
04/05 945109 FWFT1 COMPLETED
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
Year CourseCode Students Continuing Withdrawn Transferred Completed
05/06 FWFT1 6 3 1 2 0
04/05 FWFT1 1 0 0 0 1
Can anyone help me with the the extra columns please.
thought my question was misinterpreted.actual table can be smaller or larger. but,within a procedure need to turn a created #temptablesimilar to(examid is in asc order):(id, p/f, examid, examname)1 f xxxx xxxxx1 f xxxx xxxxx1 f xxxx xxxxx2 p3 p4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx(note, id = 4 has 7 failed exams).to a table with up to five columns of failedexamnames:1 f xxxxx xxxxx xxxxx2 p2 p4 f xxxxx xxxxx xxxxx xxxxx xxxxxcan get rid of exameid if helps.--Sent by ricksql from yahoo subpart from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com/cgi/content/new
View 2 Replies View Related Hi!
I m using sql 2005.I have a table as showing below.
year
Region
loan_amt
pur_id
purpose
1981
Andhra pradesh
$20,000.00
1
Animal Husbandary
1981
Arunachal Pradesh
$110,000.00
1
Animal Husbandary
1981
Assam
$240,000.00
1
Animal Husbandary
1981
Bihar
$75,000.00
1
Animal Husbandary
1981
Chhattisgarh
$55,500.00
1
Animal Husbandary
1981
Gujarat
$77,500.00
1
Animal Husbandary
1982
Goa
$44,888.00
1
Animal Husbandary
1982
Himachal pradesh
$4,000.00
1
Animal Husbandary
1982
Himachal pradesh
$20,000.00
1
Animal Husbandary
1982
Jammu and kashmir
$30,000.00
1
Animal Husbandary
1882
Jharkhand
$35,000.00
1
Animal Husbandary
1982
Karnataka
$40,000.00
1
Animal Husbandary
1982
Kerala
$20,000.00
1
Animal Husbandary
1982
Madhya pradesh
$5,000.00
1
Animal Husbandary
I want to produce report as by using crosstab query as showing bellow
Year Asam Hyadrabad goa arunachal pardesh etc............
1981 1000.00 2000.00 8000.2 00000 000000 ......
1981
'
'
Is it possible by crosstab query ?or please suggest me another way as early as possible.
Thanx in advance.
Abhishek
I wanna develope a report using cross tab query. I have table wit the name spend in which i have spenddate and there is also a amount field which will show the sum of amount field against months, i wanna develop repport in this format
merchant name jan 2007 feb 2007 dec 2007 jan 2008 feb2008
test 45 50 25 10 11
Please can any one help me how to do this this is very urgent ?
I'm trying to run the cross tab query so that it returns only one row. If there is data I want to dump in the field value (fText), otherwise I put in a dash. The problem I'm having is that it's returning multiple rows when I want a single one. I could create a # table and dump the data in, but it seems a bit long winded.
Many thanks.
SELECT distinct (CASE WHEN f.TypeID=1 THEN 1 ELSE 0 END) f1,
(CASE WHEN f.TypeID=2 THEN f.fText else '-' END) f2,
(CASE WHEN f.TypeID=3 THEN f.fText else '-' END) f3,
(CASE WHEN f.TypeID=4 THEN f.fText else '-' END) f4,
(CASE WHEN f.TypeID=5 THEN f.fText else '-' END) f5,
(CASE WHEN f.TypeID=6 THEN f.fText else '-' END) f6,
(CASE WHEN f.TypeID=7 THEN f.fText else '-' END) f7,
(CASE WHEN f.TypeID=8 THEN f.fText else '-' END) f8,
(CASE WHEN f.TypeID=9 THEN f.fText else '-' END) f9,
(CASE WHEN f.TypeID=10 THEN f.fText else '-' END) f10
FROM tblTest f WHERE (f.fID=219)
" I have two sql server2000 database named db1 and db2.
i have a user named 'user1' who has permission in both database.I have used a 'Select * from tableOne'in db1 when i have this table 'tableOne'in db1.
now this table was droped and created in db2.
what i need is i should log in to db1 and access the same select statement which is there in application used by my clients.
i have created a view in db1 with the same name as
'create view tableOne as select * from db2..tableOne'
now i can access.
Is there some othere way with out creating view?