List Of Columns
Apr 18, 2007Dear Folks,
Can you please tell me the query to display the columns in a table?
thank you very much
Vinod
ADVERTISEMENT
Get Value From Columns Using List Of Tables
Feb 5, 2014how do I get value from column using list of tables?
For example, I have list:
schema_name, table_name, column_name
How do I get:
schema_name, table_name, column_name, column_value
How Do I List Columns Within Tables?
Mar 4, 2008Hi All.
I'm an Oracle DBA who's currently being asked to look at a SqlServer Database. I need a list of columns per table, but am having trouble.
I'll admit I might be being lazy here, but I'm in a hurry and using the valueable resources available to me!! Would really appreciate the sql i need to copy into the query window. Much obliged!!
I need........
Table A
Column1 Datatype
Column2 Datatype
Table B
Column1 Datatype
Column2 Datatype
etc....
Many thanks.
Select From A List Of Tables And Columns
Nov 26, 2014The following returns all base tables within the database of type "varchar":
Code:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM mydb.information_schema.columns
WHERE TABLE_SCHEMA = 'master' AND TABLE_CATALOG = 'mydb'
AND DATA_TYPE IN('varchar')"
AND TABLE_NAME IN(
SELECT TABLE_NAME FROM mydb.information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'mydb' AND TABLE_SCHEMA = 'master')
What I then want to do is... For each of these results:
Code:
select [COLUMN_NAME] from [TABLE_SCHEMA].[TABLE_NAME]
WHERE ID = 'test'
Is it possible to do this in one SQL command? Or do I manually have to do it for each in the list from my first query?
T-SQL (SS2K8) :: CTE Has More Columns Than Were Specified In Column List
Mar 22, 2014I get the following error , when I execute my CTE on SQL Server 2008 R2.CTE1 has more columns than were specified in the column list
--SQL CODE IS AS BELOW
WITH CTE1 (AUCTIONID,[Open Time], [Response SLA Broken], [Response SLA Deadline], [Response SLA Actual], [Responsetime SLA Broken Before Transfer], [Resolution SLA Broken], [Resolution SLADeadline], [Resolutiontime SLA Broken
[code]...
List Of Columns From Tables Across Databases.
Jul 23, 2005Hey guys,Couldn't find this anywhere in google.I want a list of all database column names for a specific table/viewfrom across database.I tried this...-----------------------------------------------------Select *[color=blue]>From Information_Schema.Columns[/color]-----------------------------------------------------I also tried this...-----------------------------------------------------select syscolumns.name, sysobjects.name, * from syscolumns, sysobjectswheresysobjects.id = syscolumns.idand (sysobjects.xtype='U' or sysobjects.xtype='S')-----------------------------------------------------These queries return information about the CURRENT database.But, if I want to do it ACROSS database or across servers.. how can Ido this?I will express my gratitude to everyone who is kind enough to answerthis question. (I've been stuck with this problem for a while now.)Thanks!OhMyGaw!
View 6 Replies View RelatedList Columns In A Table In SQL 2005
Apr 2, 2008Hi, I know sys.tables and sys.columns gives me a list of tables andcolumns in a SQL 2005 database.How can I list Columns in a specific Table please?Thanks in advance,Ronny
View 3 Replies View RelatedORDER BY Columns In SELECT List?
Jul 20, 2005According to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?
View 1 Replies View RelatedHow To Get List (text) Of All Tables And Columns?
Jul 20, 2005Is there a way using MS SQL Server and Enterprise Manager to get a textdocument (or perhaps even a Word document) listing all table names,column names, etc of a database?--Sugapablo------------------------------------http://www.sugapablo.com <--musichttp://www.sugapablo.net <--personal
View 2 Replies View RelatedHow To Get A List Of All Unused Columns Warnings
Feb 4, 2008Hi,
I would like to get a list of all the unused columns, I have tons of files and fields in the package probably in the hundreds and I need to know which fields I am not using to see which fields are important not to miss out, so is there any way I can get them in a text file or any other format?
Thanks
SQL Server 2012 :: List Dates In Columns
Nov 11, 2014I have a table (Event_Table) like:
EmployeeID, CustomerID, Date
1, 11, 2014-11-11
2, 13, 2014-12-10
1, 11, 2014-12-21
2, 13, 2015-01-11
1, 11, 2015-03-02
And now I would like to have a summary with a unique Employee/Customer combination and 3 Date columns like:
EmployeeID, CustomerID, Date1, Date2, Date3
1, 11, 2014-11-11, 2014-12-21, 2015-03-02
2, 13, 2014-12-10, 2015-01-11
Dates should be arranged with the first date in Date1, the next in Date2 and the third in Date3 (if there are forth and more dates I don´t care)
SQL 2012 :: Bringing Up A List Of States From Three Columns
Nov 13, 2014Supposing there are three Columns: Countries, Provinces and States.Then when the user enters “USA” for country, it will bring up a list of states, if they enter “Canada” for country, it will bring up a list of provinces, if they enter another country, it will not bring up any list?
View 2 Replies View RelatedT-SQL (SS2K8) :: Query To List Rows Into Columns?
Aug 10, 2015I have requirement to list each row into column. I have tried with pivot query but unable to get it. I am using SQL Server 2008 database.
Here is the sample data:
CREATE TABLE dbo.test (
action_id numeric,
action VARCHAR(20) NOT NULL,
[Code].....
Get A List Of Output Columns On Script Transformation
Feb 13, 2007I am using a script component to transform data. In the script component I created a bunch of fields for the output. Is there any way to loop through that list of columns? Is there code I can use in the script component to access the names, data types, data etc?
I saw a lot of informaiton on the OutputColumnCollection as part of some IDTSOuput90 thing (greek to me). As best I can guess this is for creating your own new columns, but can I see what columns are already defined via the script interface?
Selecting Only The List Of Columns From A Table Based On Input
Apr 17, 2008Hi,
i have a table like below
table:-
id col2 col3 col4 col5 col6
1 2 3 4 5 6
2 5 8 4 7 6
3 4 8 2 6 9
4 2 5 8 6 3
5 6 9 5 5 9
i want to write a stored procedure where i pass column names a parameters and i want to get result based on that
For ex:-
if i pass the parameters as
col3 and col5 where id =1 then i should the result as
id col3 col4 col5
1 3 4 5
and if i pass input as col2and col6 where id =3, the result should be
id col2 col3 col4 col5 col6
3 4 8 2 6 9
can anyone help on this??
Return A List Of New Tables Or Columns Which Do Not Exist In A Database
Mar 24, 2008
What SQL statement can i run to return a list of new tables or columns that exist in the Sales Database on Server A, which do not exist in the Sales Database in Server B.
Server A and Server B are linked servers.
I am using SQL Server 2005. Thanks.
SQL Server 2014 :: Columns List With And Without Identity Column In A Table?
Feb 24, 2015I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
Transact SQL :: How To List 3 Derived Columns Based On 4th Common Column
Nov 25, 2015I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:
Ticket Type | Company A Count | Company B Count | Company C Count
I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.
DECLARE @startdate date = '20150306'
DECLARE @enddate date = '20151031'
DECLARE @AcctGrp varchar(20) = '111'
;WITH TType
AS
(
SELECT ctp.description as TicketType
[Code] .....
If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:
TicketType
AR Request Credit
Availability/Rush
Cancel Order
Credit Card Payment
Expedite Order
Freight Quote
[Code] ...
How to get the query results? Am I even close to getting it right?
Transact SQL :: COLUMN NAME Conflicts With Type Of Other Columns Specified In UNPIVOT List
Jul 31, 2015I am trying to convert all columns to rows in sql, but giving an error.
SELECT Employee, Orders
FROM
(SELECT CisId, [Z_Id], [ModuleType]
FROM CIS) p
UNPIVOT
(Orders FOR Employee IN
(CisId, [Z_Id], [ModuleType])
)AS unpvt;
Error: The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.
SQL 2012 :: Include Columns In Index That Are In Where Clause / Select List And Join
Jun 2, 2014Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
View 4 Replies View RelatedSQL 2012 :: Number Of Variables Declared In INTO List Must Match That Of Selected Columns
Apr 28, 2015I am getting error [[Msg 16924, Level 16, State 1, Line 13
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.]] when i execute below script.
Declare @mSql1 Nvarchar(MAX)
declare @dropuser int
declare @dbname Nvarchar(max)
declare @username Nvarchar(max)
DECLARE Dropuser_Cursor CURSOR FOR
[Code] ....
T-SQL (SS2K8) :: Creating Comma Separated List Of Details From Multiple Columns?
Jun 3, 2010I am trying to find a way to add into a table a flattened (comma seperated list) of email addresses based on the multiple columns of nformation in another table (joined by customer_full_name and postcode.
This is to highlight duplicate email addresses for people under the same customer_full_name and Postcode.
I have done this using a loop which loops through concatenating the email addresses but it takes 1minute to do 1000. The table is 19,000 so this isn't really acceptable. I have tried temp tables, table variables and none of this seems to make any difference. I think that it is becuase i am joining on text columns?
Create table #tempa
(
customer_Full_Name varchar(100),
Customer_Email varchar(100),
Postcode varchar(100),
AlternateEmail varchar(max)NULL
)
insert into #tempa (customer_full_name,customer_email,postcode)
[code]....
Delete Statement For A List Of Items With Multiple Columns Identifying Primary Key
Jul 7, 2006I frequently have the problem where I have a list of items to delete ina temp table, such asProjectId Description------------- ----------------1 test12 test43 test34 test2And I want to delete all those items from another table.. What is thebest way to do that? If I use two IN clauses it will do it where itmatches anything in both, not the exact combination of the two. I can'tdo joins in a delete clause like an update, so how is this typicallyhandled?The only way I can see so far to get around it is to concatenate thecolumns like CAST(ProjectId as varchar) + '-' + Description and do anIN clause on that which is pretty nasty.Any better way?
View 2 Replies View RelatedIntegration Services :: Updating List Of Tables From CSV File Based On Values In Columns
Jun 16, 2015Here is a requirement. Need to update the columns in the tables with the latest values available in CSV.
The file is based on department so the list of tables which is under this department all the corresponding tables needs to updated.
The CSV file which is dynamic in nature the number of columns changes it has header of the columns that needs to be updated.
The destination tables are listed under department table for each department. So I have to update the columns in the tables with the values in csv.
RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page
Dec 13, 2007Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
SQL 2012 :: List All Different Values That Go With Single CAS ID To Appear As Comma Separate List
Jun 15, 2015So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)
Apr 9, 2008
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
Insert Value List Doest Not Match Column List
Apr 18, 2007HI...
I need to do a simple task but it's difficult to a newbie on ssis..
i have two tables...
first one has an identity column and the second has fk to the first...
to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!
i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"
here is the script
INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)
what's the problem ??
Items In List A That Don't Appear In List B (was Simple Query...I Think)
Jan 20, 2005Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
Select List Contains More Items Than Insert List
Mar 21, 2008I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?
Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)
Insert Only New Items From A List But Get ID's For New And Existing In The List.
Feb 12, 2008Hi,
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)
TIA
Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)
--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO
ERROR: A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List.
May 22, 2006Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List
May 10, 2006Hi All,
I have seen a few other people have this error.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".