I am trying to execute the following query in MS SQL Server 2000.
SELECT 1 AS Tag, NULL as parent,
'x' AS [A!1!B],
NULL AS [C!48],
NULL AS [RepurchaseDetails!49]
UNION ALL
SELECT 48, 1,
NULL, 'y', NULL
UNION ALL
SELECT 49, 48,
NULL, NULL, 'z'
ORDER BY
TAG ASC
FOR XML EXPLICIT
I get the error:
Server: Msg 6806, Level 16, State 2, Line 1
Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.
However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
the query works just fine. It however still fails for
RepurchaseDetailz, or RepurchaseDetailzz
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET ( 'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES', 'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR, ''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item' COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns) ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order) DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120)) insert into #temptable SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10 --column name declare @cname varchar(30)
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
I have the following query created in MS Access that I would like to convert to MS SQL Server, but I can't get the IF logic to work properly, any help you can provide would be greatly appreciated.
I am getteing need help Query analyzer error Unable to connect server local Msg17, level 16,state 1 ODBC SQL server driver [DBNETLIB]SQL server does not exist
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9) THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA], SUM(CASE WHEN a.AttendanceStatus IN (7) THEN 1 ELSE 0 END) AS Cancelled, gp.CCGRegion FROM dbo.tblGP_Practices AS gp INNER JOIN
[Code] ....
I wanted to add anohter column where it counts the results of the followign query:
SELECT dbo.tblPatient.NHSnumber, dbo.tblPatient.DateOfBirth, dbo.tblPatient.DateReferralReceived, dbo.tblGP_Practices.OrganisationCode FROM dbo.tblPatient INNER JOIN dbo.tblGP_PatientLink ON dbo.tblPatient.PatientID = dbo.tblGP_PatientLink.PatientID INNER JOIN dbo.tblGP_Practices ON dbo.tblGP_PatientLink.GPPracticeID = dbo.tblGP_Practices.GPPracticeID LEFT OUTER JOIN dbo.tblAppointments ON dbo.tblPatient.PatientID = dbo.tblAppointments.PatientID WHERE (dbo.tblAppointments.PatientID IS NULL)
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005: The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message: Msg 18483, Level 14, State 1, Line 1 Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out? (I include the reproduce steps for the first error message, followed by my resolution that generates the second error message) ====== Reproduce steps for the first error message ======
On the ComputerAInstanceA instance, run the following statement to create a database and a table: CREATE DATABASE DatabaseA GO USE DatabaseA GO CREATE TABLE TestTable(Col1 int, Col2 varchar(50)) GO INSERT INTO TestTable VALUES (1, 'Hello World') GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table: CREATE DATABASE DatabaseB GO USE DatabaseB GO CREATE TABLE TestTable (Col1 int, Col2 varchar(50)) GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement: USE DatabaseB GO CREATE PROCEDURE InsertA AS BEGIN SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable END GO
On the ComputerAInstanceA instance, run the following statement: USE DatabaseA GO INSERT INTO TestTable EXEC LNK_ServerB.DatabaseB.dbo.InsertA GO Then I receive the first error message.
======= My resolution that generates the second error message =======
On the ComputerBInstanceB instance, run the following statement: sp_addserver 'ComputerAInstanceA' GO sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE' GO USE DatabaseB GO CREATE PROCEDURE InsertA AS BEGIN SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable END GO
On the ComputerAInstanceA instance, run the following statement: USE DatabaseA GO INSERT INTO TestTable EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA] GO Then I receive the second error message.
Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.
Here is my linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too) EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null
Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.
SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')
OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType". Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".
My SQL 7.0 server is currently querying the SAM database on the PDC for Windows NT authentication. How can I force it to use the SAM database on the server(BDC) that I specify?
i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
if it is possible to run a distributed query against 2000 from 2005, what would the OPENDATASOURCE parameters look like? I'd like to be able to pivot without copying my older 2000 db to 2005 or using linked servers..
For reference, here's an example of a distrib query that reads excel...
ie SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint SET @Value = 22 SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below: .
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used
The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.
Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that select * from Alias1.db1.dbo.table1 and select * from Server1.db1.dbo.table1 should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-) This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
I have a query slower in SQL Server 2005 than in SQL Server 2000. I have a database in SQL 2000, I put it on the same server, but with SQL 2005 and the query take 5 seconds instead of 0 seconds. The DB compatibility is SQL Server 2000 (I tried with 2005 and result is the same). Execution plan seems right and I tried to change some DB options without results. It is weird, when I remove left join on MandatsEx, it take 2 seconds. The view currentEmployeeLevelHistoric returns 45 000 rows and mandatsex has 0 rows.
Here is the sample:
Select ls.EmployeNiveau.pk_EmployeNiveauID as NoNiveau, IsNull(nullif(ls.Traduction.Description, ''), ls.TraductionDefaut.Description) + ' (' + ls.currentLevelHIstoric.NoNiveau + ')' As Nom, ls.currentEmployeeLevelHistoric.DebAssign As DateAssignationDebut, ls.EmployeNiveau.assignmentReason As AssignmentReason, ls.currentEmployeeLevelHistoric.FinAssign As DateAssignationFin, ls.MandatsEx.noDossier, ls.MandatsEx.pk_MandatID, '' As Period, ls.currentEmployeeLevelHistoric.NiveauPrincipal, ls.currentEmployeeLevelHistoric.pk_emplNiveauHisto_Id, ls.EmployeNiveau.fk_NiveauID_Niveaux, ls.EmployeNiveau.No_Ent_leg, IsNull(nullif(TradPere.Description, ''), TradDefautPere.Description) + ' (' + NiveauPere.NoNiveau + ')' As NomSup, ls.EmployeNiveau.No_Ent_leg + ls.EmployeNiveau.no_divisio + ls.EmployeNiveau.no_sec_eco + ls.EmployeNiveau.no_etabli + ls.EmployeNiveau.no_mat as employeeScope, case when ls.mandatExternalisation.fk_mandatID_MandatsEx is null then 2 else 1 end as ContractCategory From ls.currentEmployeeLevelHistoric Inner Join ls.EmployeNiveau on ls.currentEmployeeLevelHistoric.pk_EmployeNiveauID = ls.EmployeNiveau.pk_EmployeNiveauID Inner join ls.currentLevelHistoric On ls.EmployeNiveau.fk_NiveauID_Niveaux = ls.currentLevelHistoric.fk_niveauID_niveaux Left Outer Join ls.TraductionDefaut On ls.currentLevelHIstoric.fk_TraductionID_TraductionDefaut = ls.TraductionDefaut.pk_NoTraduction Left Outer Join ls.Traduction On ls.TraductionDefaut.pk_NoTraduction = ls.Traduction.No_Traduction and ls.Traduction.Langue = 1 Left Join ls.MandatsEx on ls.EmployeNiveau.fk_MandatID_MandatsEx = ls.MandatsEx.pk_MandatID Left Join ls.mandatExternalisation on ls.MandatsEx.pk_MandatID = ls.mandatExternalisation.fk_mandatID_MandatsEx left Join ls.Niveaux as NiveauPere on NiveauPere.niveauID = ls.currentLevelHIstoric.supId Left Outer Join ls.TraductionDefaut As TradDefautPere On NiveauPere.fk_TraductionID_TraductionDefaut = TradDefautPere.pk_NoTraduction Left Outer Join ls.Traduction As TradPere On TradDefautPere.pk_NoTraduction = TradPere.No_Traduction and TradPere.Langue = 1 Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158 and (convert(varchar, ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and (ls.currentEmployeeLevelHistoric.FinAssign is null or convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))
I need to query data in a table located on Server "A" from Server "B". I do not want to use replication. The only way I know of to do this is have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".
I need your help. I have 2 different databases. One of them is in the SQL Server 2000 and the other on is in the SQL Server 2005. I need to make a query out of these 2 databases. Is there any way that I can do this?
I am currently connect to S1 but I want to Query a table from S2 without creating a linked server. Is it possible.Can someone please post some information on how this can be accomplished. ETL, Lookup ,Stored procedure anything is ok. I am using SQL Server 2005.This is a very urgent requirement.
-- Get the new Customer Identifier, return as OUTPUT param SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned -- users. IF @FK_UserIDList IS NOT NULL EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record -- Retrieve Address reference into @AddressId -- EXEC spInsertForUserNote -- @FK_UserID, --@NoteID, -- @BeenRead -- @Fax, -- @PKId, -- @AddressId OUTPUT
COMMIT TRANSACTION
-------------------------------------------------- GO
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1 FROM testsupplierSCNCR I am getting the result of 01/01/2007
I'm not sure this is the right forum for this thread, but hopefully the Admins can move it if it isnt.
Here is my situation.
I had a web server with a web page that referenced an Excel spreadsheet. (a href="c:webservernamexyz.xls" target=main)
there is a query to retreive data in the ss from a Access Database.
I had to move the website from one server to another, which included moving the data from an Access Database to a SQL 2000 Database, which sits on a SQL Server.
There is a DSN created on the web server for the connection. From the web server, I can open the Excel Sheet and it queries the database fine.
My issue is that now when someone hits the webpage from their computer, they get the spreadsheet, but since they dont have a DSN on thier machine, the get an error stating "[Microsoft][ODBC Driver Manager] Datasource name not found and no default driver specified."
Is there a way to specify to use the DSN on the web server so I wouldnt have to create a DSN on every machine that would need to access it?
Hey,I'm not sure how I do this, basically I want to do is:select * from table1where nameField and numberField not in (select nameField and numberField from table2)Can I do a not in on two fields like this?
I am having one table in MS-Access say “Table1?Table1 Structure as Follows : -
ColA ColB
1
A
1
B
1
C
2
A
2
B
2
C
3
A
3
B
3
C My MS-Access query : - SELECT First(ColA), First(ColB) FROM Table1 GROUP BY ColA;Result : - 1 A 2 A 3 A I am using this same Table & Query and Same Result in the SQL server but I am getting following output (but its wrong) Output : - 1 A Here I want Following output in the SQL Server: - 1 A 2 A 3 A Please help me on this issue
say I have table like this zip dma 50111 511 50111 512 50111 513 50211 511 50211 514 50211 515 and so on..there are like 48000 entries on this table. Now there are occassions where there is only one zip code listed one time. How would I get all the zip codes that are at least more than 1 time in the table. Like this case it would be 50111, 50211. I wouldn't care if there is a zip code that exist only 1 time.
I have all of the system and user databases from a previous 6.5 installation (NOT Backup files. These are straight copies of the operational databases!)
Here's what i need to know:
Is it possible to start sql server 6.5 alongside SQL server 2k?
If so, is it possible to log into sql server 6.5 in single user mode and attach all of the previous databases to rescue all the data that they contain?
I hope that you can help because the information that these databases contain are vital to the operation of our helpdesk.
Just a note: I inherited these from my boss, just incase anyone asks "why didn't you just back them up?"
Hi can anybody pls. help me...i have these code below to query all data in my 'docs_ref' table where in it has 5 records in it but when i try to execute it and count the record retrieved it always give me a -1 value...what seems to be the problem...any inputs will be greatly appreciated. thanks in advanace!!!
Dim oConn As ADODB.Connection Dim rs As ADODB.Recordset Set oConn = New ADODB.Connection Set rs = New ADODB.Recordset oConn.Open "Provider=sqloledb;" & _ "Data Source=absetsdk;" & _ "Initial Catalog=BCMDDB;" & _ "User Id=bcmd;" & _ "Password=bcmd" If oConn.State = adStateOpen Then MsgBox "SUCCESSFUL!!!", vbOKOnly strSQL = "Select * from docs_ref" rs.Open strSQL, oConn MsgBox rs.RecordCount, vbOKOnly rs.Close oConn.Close
Need help with a query.I need to get a date from table1 where the symbol is equal to OQY7 butthe symbol in table table2 is OQY07.How do I get it to look for the first three characters plus the fifthcharacter by skipping the fourth character?
Backend I am using is MsAccess. If you are trying it out, try out in SQL view in MsAccess/Queries. I found solutions for SQL Server, but those functions are not available in MSAccess. Whats the way to do it?
If someone could let me know asap...that would be great!!! THANKS!
