Greetings,
If I use a "select into" to clone a table, all attributes are created
correctly, however, if I use the same statement across a linked server,
my identity column loses its IDENTITY specification.
Is this a known issue or basic functionality of using "select into"
with linked servers?
Kurt
Hello,
I use a XML Data flow item transformation in SSIS (XML Source). Here is an example of the input of xml file content:<extract date="2007-12-05"> <counters> <counter category="dispatcher" name="server1"> <runtime>6</runtime> <queue>3</queue> <maxrequest>8</maxrequest> <color>blue</color> <host> <name>svo2555</name> <path>\dispatcher</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> <counter category="gateway" name="server1"> <runtime>1</runtime> <queue>10</queue> <maxrequest>10</maxrequest> <color>purple</color> <host> <name>svo2555</name> <path>\gateway</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> </counters></extract>
How do I catch the extract date in the transformation? SSIS only shows me the <counters> element and not the <extract> one. Why? Is there a way to get it?Regards,
Hi,
I have an issue with one of my production servers. I have a few production servers, and they all have the same objects. I do not want to create custom objects for each one, rather keep them all standard.
However, due to environment issues, I have one which does not have any linked servers.
I have a proc which queries either locally or across the linked server depending on the value of a switch. I want to install this proc on the server with no linked servers but it obviously fails.
I can guarantee the proc will never be called with the switch 'on' on that particular server. We still want to install this proc and call it with a switch of 0.
Please can somebody come up with something really clever.
The proc goes something like this:
CREATE PROC WhatEver @Switch int
AS
IF @Switch = 1
BEGIN
SELECT * FROM LinkedServer.Db.dbo.MyTable
END
ELSE BEGIN
SELECT * FROM dbo.MyTable
END
Thanks in advance
I want to insert attibute att1 in field F1 . Value for this attribute is content of another field in this table (F2).
My query :
update MyTable
set F1.modify
('insert attribute att1 {sql:column("F2")} into (/ROOT/Node1)[1]')
Where F1 Is not null
But I get this error :
XML well-formedness check: Duplicate attribute 'att1'. Rewrite your XQuery so it returns well-formed XML.
How do I check the douplicate attribute ?
Hi all,
I am importing SQL 2000 tables from a developer install of SQL 2000. There are no destination tables to append just new tables being brought in.
I use the All Tasks > Import (DTS) service to do this. I have noticed that the Identity columns do not maintain the Identity attributes. I have to reset them after I import new tables. Is this normal? or is there a bug here?
I checked the MS KB with no success.
Anyone have some info on this?
Thanks.
I finally have my server linked, figured out my date issed with the decode statement and now I get a missing Expression error from Oracle.
Here is the statement:
SELECT *
FROM OPENQUERY(PROD_ORACLE,'
SELECT LEFT(CUST_ORDER_STUS_NME, 25) as CUST_ORDER_STUS_NME,
LEFT(SRVC_ORDER_STUS_NME, 25) as SRVC_ORDER_STUS_NME,
LEFT(Ser_ORD, 20) AS Ser_ORD,
LEFT(CUST_ORDER_NME, 25) as CUST_ORDER_NME,
LEFT(SRVC_ORDER_NME, 25) as SRVC_ORDER_NME,
LEFT(CLS_ALLOWED_NBR, 2) as CLS_ALLOWED_NBR,
LEFT(NOC_TO_NOC_NME, 3) as NOC_TO_NOC_NME,
LEFT(CHARS_ID, 20) as CHARS_ID,
LEFT(PRI_DNS_QTY, 5) as PRI_DNS_QTY,
LEFT(SCNDY_DNS_QTY, 5) as SCNDY_DNS_QTY,
LEFT(ORDER_TYPE_CD, 10) as ORDER_TYPE_CD,
LEFT(ACTY_NME, 25) as ACTY_NME,
replace(LEFT(CUST_A_NME, 50), ''|'', ''-'') as CUST_A_NME,
LEFT(RLAT_CKT_ID, 8) as RLAT_CKT_ID,
DECODE (BILL_CLR_DT, GREATEST(BILL_CLR_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), BILL_CLR_DT, NULL),
DECODE (CMPLT_DT, GREATEST(CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), CMPLT_DT, NULL),
REPLACE(replace(LEFT(CMNT_TXT, 1000), char(10), '' ''), ''|'', ''-'') as CMNT_TXT,
LEFT(CUST_H1_ID, 9) as CUST_H1_ID,
LEFT(CUST_CMS_ID, 8) as CUST_CMS_ID,
LEFT(Circuit_ID, 30) as Circuit_ID,
DECODE (SO_CMPLT_DT, GREATEST(SO_CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), SO_CMPLT_DT, NULL),
LEFT(DNS_ADMIN_NME, 30) AS DNS_ADMIN_NME,
LEFT(DNS_ADMIN_PHN_NBR, 15) AS DNS_ADMIN_PHN_NBR,
LEFT(DNS_ADMIN_EMAIL_ADDR, 128) AS DNS_ADMIN_EMAIL_ADDR,
LEFT(CONTACT, 30) AS CONTACT,
LEFT(GOVT_CD, 10) AS GOVT_CD,
LEFT(SOTS_ID, 30) AS SOTS_ID,
LEFT(RAS_USER_BLK_NBR, 20) AS RAS_USER_BLK_NBR,
LEFT(VSYS_QTY, 10) AS VSYS_QTY,
LEFT(ZONE_QTY, 10) AS ZONE_QTY,
LEFT(PLCY_QTY, 10) AS PLCY_QTY,
LEFT(PIC_CODE, 6) AS PIC_CODE,
LEFT(SO_Order_Entry_Nbr, 15) as SO_Order_Entry_Nbr,
LEFT(SO_Rlat_Order_Entry_Nbr, 15) as SO_Rlat_Order_Entry_Nbr,
'' '' as filler
From PROD_ORACLE..RDBADM.CUST_SRVC_ORDER_V')
Here is Query Analyzers response:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'OraOLEDB.Oracle'.
[OLE/DB provider returned message: ORA-00936: missing expression]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandPrepare:repare returned 0x80040e14].
ANY help is greatly appreciated!!
Hey there..
I need to insert some data into a linked server where I need to insert the Identity field. When I try to turn IDENTITY INSERT on, I get this error
The object name 'Server-SQL.MyDatabase.dbo.MyTable' contains more than the maximum number of prefixes. The maximum is 2.
The line I try to execute is this...
SET IDENTITY_INSERT [Server-SQL].MyDatabase.dbo.MyTable ON
From my searching around about this error, the workaround seems to be aliasing the table name, but I can't really see how to use an alias in this situation.
Thanks a bunch!
Greg
I'm trying to define a linked server using Microsoft OLE DB Provider for Data Mining Services. MSDMine data provider is missing. Do you know where I can get this? Thanks in advance.
I'm have a Xeon 64bit proc. using SP2. And had installed:
SQLServer2005_ADOMD_x64.msi, SLQServer2005_ASOLEDB9_x64.msi,SQLServer2005_OLAPDM_x64.msi, and SQLServer2005_XMO_x64.msi.
I have inherited a system which uses Identity numbers for primary key fields, but also displays them for the user to see. The user has noticed missing numbers in the sequence, sometimes just a few numbers and sometimes as many as 1000 numbers missing.
As far as I can tell the few numbers missing could possibly be explained by users deleting records or the number being generated on a save, but the save then failing. But I can't explain the huge jump of 1000.
I can find no reference to the RESEED in the code...
Is there a way to check for missing identity numbers in a Primary Key column? I have some databases that are not fully normalized and want to check on tables that might have had some records deleted. Thank you.
We are facing the following issue, several machines/users that are executing very often a command similar to :
INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo');
SELECT SCOPE_IDENTITY() AS Table_ID;
Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables
These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.
HI,
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
thanks
MJ
Does SELECT @@identity works with in SQL Server compact edition? I have tried in SQL management sql query. It does work however when i use in VS.NET 2005 / Orcas beta1 , the generated tableAdapter does not recongize the syntax. So any workaround with it ? or any alternative that i can return the new inserted ID in my application.
Thanks.
Hello,
I created a linked server to Oracle on SQL-Server 2005. I have stored the remote login username and password in the security settings (option "...Be made using this security context" in the Security tab of the Linked Server Properties.
The "Test connection" function works fine, but a select doesn't for every table the user owns. What we found out is: if the table consists only of columns defined as varchar2, the select works. As soon as the table contains only one column defined as number (without scale and precision), it doesn't work. The error displayed is:
The OLE DB provider "MSDAORA" for linked server "NEXUS_FVADM" supplied inconsistent metadata for a column. The column "WEBVORZUGSTYP" (compile-time ordinal 2) of object ""FVADM"."AKZ"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.
The Column WEBVORZUGSTYP is defined as number.
Any ideas?
Regards,
Ralf
I am trying to find out what records on in one DB and not the other. This SQL statement works find if the DBs are on the same Server. If I replace on the DBs with a linked DB (on another server) I get the following error.
SELECT *
FROM [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price]
WHERE NOT EXISTS
(SELECT [LocalDB].[dbo].[Sales Price].*
FROM [LocalDB].[dbo].[Sales Price]
WHERE ([LocalDB].[dbo].[Sales Price].[Item No_] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Item No_]) AND
([LocalDB].[dbo].[Sales Price].[Unit of Measure Code] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Unit of Measure Code]) )
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Item No_" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Unit of Measure Code" could not be bound.
I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
I have 2 SQL 2000 servers. One has been added as a linked server.
They both have a db called claims. If I am on server 1 and want to write a sql statement I am stuck with the write syntax
I tried select patient.* from testedi..claims.
The server is linked using the sa user. I know this is simple but in a hurry and stuck. THe servers are on same domain.
Thanks
I have a historical table on a dedicated SQL Server (let's call it the reporting db) that is populated every morning with production data that does not already exist. The data in the prod table is purged after 7 days and nothing is ever deleted from the historical table. I have set up the linked server between the two 2008 SQL Servers, but when I try to run this simple query from the reporting DB, it takes more than 5 minutes and still "executing". I eventually have to cancel it:
-- INSERT INTO Temp_Import_historical
SELECT TOP 1 *
FROM [192.168.1.100].ProdDB.dbo.Temp_Import_historical a
WHERE NOT EXISTS (select [Temp_Import_ID] from Temp_Import_historical where a.[Temp_Import_ID] = Temp_Import_historical.[Temp_Import_ID])
I have omitted the INSERT statement on purpose, since I can't even get to output 1 row. Why this is such a resource intensive query?
I re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
Any ideas?
In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.
However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.
I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.
I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).
How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?
Thank you!
Hello,
i am facing a bizarre problem, accessing data from a remote server which has been linked to my sql.
My SQL server is 2005 and the remote server is SQL 2000.
i have linked the remote server (called LinkedServer) so that when i run this query:
SELECT * FROM LinkedServer.SomeDB.dbo.SomeTable
executes successfully! However, when i run a similar query like this:
SELECT Column1 AS Col1,
LinkedServer.SomeDB.dbo.SomeTable.Column2 as Col2,
Alias.Column3 as Col3
FROM LinkedServer.SomeDB.dbo.SomeTable
INNER JOIN LinkedServer.SomeDB.dbo.SomeOtherTable1 ON LinkedServer.SomeDB.dbo.SomeTable.Column3 = LinkedServer.SomeDB.dbo.SomeOtherTable1.Column3
INNER JOIN LinkedServer.SomeDB.dbo.SomeOtherTable2 AS Alias
ON LinkedServer.SomeDB.dbo.SomeTable.Column3 = Alias.Column3
It gives me this error:
The multi-part identifier "LinkedServer.SomeDB.dbo.SomeTable.Column2" could not be bound.
The multi-part identifier "LinkedServer.SomeDB.dbo.SomeTable.Column3" could not be bound.
I have noticed that this error is generated only for the selected columns whose path has either been repeated (e.g. "LinkedServer.SomeDB.dbo.SomeTable.Column2" as opposed to "Column2") or for columns which are from aliased tables.
What is going on?!?!?!??!?!?!
Any Help would be tremendously appreciated!!!! 8..)
I created a linked server on server A to server B as follows...
USE master;
GO
EXEC sp_addlinkedserver
'B',
N'SQL Server'
GO
...in mgt studio (in a connection to A) from my local where as a person with sys admin rights on both A and B I'm having no problems connecting to either and running queries. When I run a query from a connection on A as select * from .[a db on B] .[an owner on B].[a table on B], I get an error that says Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
I believe both A and B are in the same domain
the default behavior that I saw checked in the linked server's security properties was "connection will be made using the logins current security context".
when change the default and clicked and entered "this security context" as domainuserid and password, rerun the query, I get error Login failed for user 'domain nameuserid'.
is there a simple way to get this to work, at least for test purposes? I even tried playing with the login mappings but nothing seemed to work.
Please help
We have an application written in MS Access. The tables are linked to a SQL 2000 database.
The problem is that sometimes insert a new record in a table freezes and times out after a while without anything has happened.
When installing the application the *mdb file is copied over to the C drive and an ODBC connection is written to the registry.
The application is used by many in the company.
We have problems on tables defined with IDENTITY columns. Can this be our problem and how can we solve it?
Regards Anna-Lena
I am assigning temporary Ids in my table, and right now I am assigning new ones via find the MAX(Temp_Id), stripping out the number and adding one.
Since these are temporary I would like to be able to reuse older ones when they are no longer in the table.
For example I have:
ID0001
ID0002
ID0003
ID0006
My code right now will add ID0007. I would like to re-use ID0004 and ID0005 since they're no longer in use. How do I go about do this?
Hey there everyone,
I'm sure there's a good reason for this, I just have no idea what it is.
If I run a SELECT * statement on one of my tables, the result set is missing one of the records.
If I SELECT that specific row (by identifier or anything else really), it returns just fine.
Any kind of select (e.g. WHERE ID > X) fails to return that specific row.
Any idea why this might be happening?
Thanks in advance for any guidance!
In sql 2000 one could right-click a table (under Enterprise Manager) and have it return the "top n" rows in the table. That feature seems to be missing from Management Studio. Am I correct or is it just hiding somewhere?
TIA,
Barkingdog
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'Machine123Instance456',
@srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'
i am getting an 'Incorrect syntax' error while setting @@identity into a variable
declare @vcount int
set @vcount =Select @@identity
can someone help me?
regards,
Hi guys,
I've a question about the 'select into' clause when using an identity column with an order by clause.
I've attached a sample of code at the bottom.
I've recently read an article (http://support.microsoft.com/kb/273586) where it states that you can't guarantee the order of the identity columns using this statement, however I'm not sure whether this applies to 2005 or not.
Using this statement, I always get the correct order.
----------
begin
CREATE TABLE #x (FileName varchar(50))
INSERT INTO #x(FileName) SELECT 'E'
INSERT INTO #x(FileName) SELECT 'A'
INSERT INTO #x(FileName) SELECT 'C'
INSERT INTO #x(FileName) SELECT 'B'
INSERT INTO #x(FileName) SELECT 'D'
SELECT * FROM #x
SELECT identity(int,1,1) AS ID, FileName
INTO #y
FROM #x
ORDER BY FileName
SELECT * FROM #y
DROP TABLE #x
DROP TABLE #y
end
----------
Can anyone tell me if it is safe to use rely on the ordering of this statement in 2005, is there an article which discusses this ?
Sean
I have the following query that has been working so far. I modifiedit slightly to work from another source table (new_products). Now,SQL Server complains that the identity is inherited already:"Cannot add identity column, using the SELECT INTO statement, to table'dbo.my_products', which already has column 'id' that inherits theidentity property."Any suggestions for a work aorund?This is the query:SELECT IDENTITY(INT,1,1) as seq_number, prod_number, prod_nameINTO my_productsFROM ( SELECT prod_number, prod_nameFROM new_productsWHERE ...ORDER BY ...) sub_table
View 1 Replies View RelatedHi just a beginner question.. is this the same? SELECT @@IDENTITYandselect scope_identity() as returnidentity"; Thanks!
View 5 Replies View RelatedIs there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry