Why Does The Index Lock My Updates In SQL Server 2005?

Mar 28, 2006

When one process has one record locked in Update-Mode then an other process can't update any other records on that table with some queries, other queries that access the same records (but with a different WHERE statement) will execute.
This problem occurs with SQL Server 2005, but it didn't with SQL Server 2000 (or any other database).

The problem:
-Process 1 locks a record in the table, and keeps it in Update-Mode, because the user is editing it. (Using OLEDB Pessimistic Cursor-locking)
-Process 2 wants to update an other record, buts gets a "Lock timeout" when using one query but not with another.

For example this query will work :
UPDATE gwseqnumber SET nextseqnr = 3 WHERE row_id = 110;

But this qeury will give me a "Lock timeout" :
UPDATE gwseqnumber SET nextseqnr = 3 WHERE name = 'REC_2';

But it is the same record!!
The record with name = 'REC_2' has the row_id = 110, both values are unique in the table.

The data:
The table [gwseqnumber] has the following CREATE statement:
CREATE TABLE GWSEQNUMBER
(
    NEXTSEQNR                      INTEGER,
    NAME                      CHAR (20),
    ADMINISTRATIONCODE             INTEGER,
    FINDHIGHESTNUMBER              CHAR (1),
    CLOSEDYN                       CHAR (1),
    ROW_ID                      INT IDENTITY(1,1) NOT NULL
);

CREATE INDEX KEY_1 ON GWSEQNUMBER (NEXTSEQNR);
CREATE UNIQUE KEY_2 ON GWSEQNUMBER (ADMINISTRATIONCODE, NAME, IDENTIFIER);
CREATE UNIQUE INDEX KEY_3 ON GWSEQNUMBER (ROW_ID);

Both KEY_2 and KEY_3 are unique, KEY_1 is not.


If I remove the index on the NEXTSEQNR column (the index named KEY_1) then both these queries will work, so it is obviously related to the index.
Altough the index is obsolete and can be removed from this table, it should not result in bogus locking errors.

Even when i removed the index not all the queries will work. (With work i mean not run into a locking error, while the record is not even locked.)



Solutions tried:
- Set the compaitiblity level of the databsae back to 80 (for SQL Server 2000 compatibility).
- I have already tried to disable Page-Locking on all the indexes of this table.


So why does the index lock my updates in SQL Server 2005?
And how do i fix it so my database does not run into these locks?

View 14 Replies


ADVERTISEMENT

Lock Down SQL Server 2005

Feb 12, 2008

Hi there,

I just wrote an article on how to Lock Down SQL Server 2005 that might interest people here. It has step-by-step instructions and SQL scripts for server and database hardening. A lot of concepts are explained along the way.

As an experiment I also have a world-accessible SQL2005 shell to let people experiment with a hardened database and see if they can do privilege escalation. A lot of the standard tricks have been tried so far and the server has held up without any problems. I wonder if that will last

Anyhow, the article is here: http://duartes.org/gustavo/articles/Lock-Down-SQL-Server-2005.aspx. I appreciate all feedback.

cheers,
Gustavo

View 3 Replies View Related

Online Rebuild Index Table Lock

May 27, 2008

I have a very large table with approximately 400 million records in it. Every 10 seconds approximately 150 insert are done on the table. I am attempting to rebuild one of the indexes (non-unique, non-clustered). But when I run a script to rebuild the index online (i have enterprise edition) the VB.NET service that is attempting to insert generates SQL timeout errors (timeout set to 30 seconds). From an article on msdn they state that long term table locks are not held for the duration of the index operation. So what am I missing because I am not close to being a DBA. I know SQL Server is not a SCADA but it is not my choice.

Here is the script for one of the rebuilds
USE [DATABASENAME]
GO
ALTER INDEX [IX_REALLY_BIG_TABLE_DT]
ON [dbo].[REALLY_BIG_TABLE] REBUILD
WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = OFF,
ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF,
ONLINE = ON )
GO



Here is the article
http://msdn.microsoft.com/en-us/library/ms188388.aspx

View 4 Replies View Related

SQL Server 2005 Problem After Updates

May 21, 2007

I'm using MS SQLEXPRESS 2005. Everyting worked fine: I could connect to it from Access and VS 2005. After updates (SQLEXPRESS SP2) I can't connect to it. Error:

Connection failed:

SQLState: 01000

SQL Server error: 2

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect))

Connection failed:

SQLState: `08001

SQL Server error: 17

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exists or access denied

It applies to SQL SERVER too.

Pls help.

View 1 Replies View Related

VB6 SQL Server 2005 Lock Problem

Mar 22, 2007

We have an old VB6 app using a SLQ Server 2005 database.

This database is also used by a web service.

When accessed just by the VB6 or WebService everything runs smoothly.

The problem occur`s when both access the database in parallel.

Our web service seem`s to encounter some very long delay for queries running just fine when is alone.

Now my knowledge of VB6 is very limited (and it will stay that way :) i`ve checked the code for access part and some parts look obscur.

so here is the part where the queries gets executed

Set m_adoRecordSet = New ADODB.Recordset
With m_adoRecordSet
.CursorLocation = iCursorLocation
.CursorType = iCursorType
.LockType = iLockType
Set .ActiveConnection = objConnection
.Open m_strSQL
End With

i looked everywhere in the code and iCursorLocation is mainly always SQLClient. i think only one time is SQLServer.

iCursorType is always SQLForwardOnly and iLockType is always SQLReadOnly.

now the curious part (for me) is that the connection (objConnection) is opened at the beginning of the app and never closed? Is that normal in VB6?

Would the cursor being sometimes on the server be the problem?

If someone could help it would be very appreciated.

If you need more details just tell me.

thanks

mateo

View 1 Replies View Related

SQLBulkCopy In A C# App Causes SQL Server 2005 To Lock Up

May 16, 2007

I have written an app that will allow you to send a query to Teradata, return the results into a Reader and then Bulk Copy that data into SQL Server 2005.



If the query results in a large dataset (ie 20,000,000 rows) is processed then while that data is being bulk copied into SQL Server, using the SQLBulkCopy class, then it prevents users on other computers from logging into SQL Server Management Studio.

Those that are already logged in are shut down also. Everything appears fine to the users but queries do not finish running.



Everything immediatly starts working as normal when either my program finishes or I shut down my program.



Is there any type of property to the SQLBulkCopy class or any other function that will prevent Management Studio from locking up?



Thanks

Robert

View 2 Replies View Related

SQL Server 2005 SP2 Windows Updates Install On Vista X64

Mar 31, 2007

I'm currently running Windows Vista x64 and SQL server 2005 on an HP laptop with a AMD Turion 64 processor. I've install all of the Windows Vista x64 update so far with no issues. However, I'm unable to install SQL server 2005 SP2 via Windows updates.



I was able to successfully download the service pack with no issues and it is currenly on my machine ready to install. The system does take a check prior to the install process beginning to perform the install.



I continuely get and "Error Code A91" when the install process is running. As a result the install does not complete successfully. When I used the "Get Help" option I get a list of possible error conditions but none of them seem be useful. I checked each one and none of them provide any help with a solution.



I've successful installed SP2 on Vista x32 system with no problem. Is this a "Windows Update" issue?



Thanks

View 11 Replies View Related

SQL Server 2005 Service Pack 1 Causes Machine Lock Up

May 30, 2006

When doing the service pack 1 install on a client side the machine is locking up on a specific file. The tech who tried it out doesn't remember the exact file name but he thinks it ended with an .msb or .msp

I checked in the C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFiles directory and none of the files have been updated.

Thoughts?

Shane

View 1 Replies View Related

Upgraded To SQL Server 2005. Updates No Longer Work. Error Could Not Find Server 'DEVDB' In Sysservers. Execute Sp_addlinkedse

Feb 28, 2006

Hello!

We just upgraded to SQL Server 2005 from SQL Server 2000. The DB was backed up using Enterprise Manager and restored with SQL Server Management Studio Express CTP. Everything went as expected (no errors, warnings, or any other indicator of problems).

The DB resides in a DB Server (Server1) and the application we are running is a Client/Server system where the AppServer resides on Server2.

During the application's operation all read, create, and delete transactions work fine but no update works. When viewing details in Trace Log I see this message after attempting any update:

Could not find server 'Server1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. (7202)

Any help is greatly appreciated,

Lucio Gayosso

View 19 Replies View Related

Adding Table To Dataset For SQL Server Mobile Causes VS 2005 To Lock Up.

Apr 20, 2006

I am running VS 2005 Professional Edition

Windows XP profession with Service Pack 2

SQL Server 2005 Developer Edition.

WHAT I HAVE DONE:

I have a database running in an instance of SQL Server.

I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication.  After a few oversights I got everything working.  The Mobile database replicated just fine.  I went back verified all data was there.  Can make queries to it.

 

PROBLEM: 

I set up a new dataset to use tables from the SQL Server Mobile database.  If I drag one of the tables to the dataset, VS 2005 simply stops responding.  It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:

Microsoft Visual Studio is Busy.

Microsoft Visual Studio is waiting for an internal operation to complete.  If you regularly encounter this delay during normal usage, please report this problem to Microsoft.

Well...  It is more than just a delay.  The environment is not using any processor its just sitting here.  And I left it running for 2.5 hours...  so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic.  The only option I have is to Kill the process.

Hopefully someone out there can help.

Additional Services running:

IIS (Whatever version comes with Windows XP Pro.  I think 5.1)

SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch

UPDATE:  I am editing this post with an update.

I noticed that my other tables get added to the dataset just fine.  It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification.  I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them.  I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.

 

View 14 Replies View Related

Update Statement Performing Table Lock Even Though Where Condition On Clustered Primary Index?

Jul 20, 2005

Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray

View 1 Replies View Related

Sql Server 2005 Compact Edition 3.1 RDA Synchronization Fails On Table With Index In Sql Server 2005 Database

Jan 21, 2008

We have been using Sql Server 2005 Compact Edition 3.1 RDA synchronization method successfully on Sql Server 2000 database. Recently we moved the database to Sql Server 2005, sync doesn't work anymore, it just hangs on one table. On further investigation, we found out that it's the index on that table that causes this. We removed the index, it works fine. We are wondering the root cause, removing the index is not a solution for us. Any thoughts?. Thanks.

View 1 Replies View Related

SOLUTION! - VB.NET 2005 Express And SQL Server 2005 - NOT Saving Updates To DB - SOLUTION!

Nov 30, 2006

VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!

-----------------------------------

The following article is bogus and confusing:

How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy'
http://msdn2.microsoft.com/en-us/library/ms246989.aspx

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

Do not copy








The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

-----------------------------------

The above article is bogus and confusing.

This is rediculous!

This is the most vague and convoluted bunch of nonsince I've ever come accross!

Getting caught out on this issue for the 10th time!
And not being able to find an exact step-by-step solution.

--------------------------

I've tried it and it doesn't work for me.

Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.



If you have a step-by-step procedure that can be reproduced this properly please PM me.

I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.

Many thanks.



This is the exact procedure I have come up with:

NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.

NOTE 2: DO NOT hand copy the database to a folder/directory in your project.

Yes, I know its hard not to do it because you want your project nice and tidy.
I just simply could NOT get it to work.
You should NOT have myData.mdf listed in the Solution Explorer. Ever.

Create a folder for your data following NOTE 2.

Copy your data to that folder. * mine was C:mydatamyData.mdf



Create a NEW project.

Remove any Data Connections. ( no matter what)

Save it.

Data | View Data Sources

Add New Data Source

select NEW CONNECTION ( No Matter what, do it!

Select the database. * again mine was C:mydatamyData.mdf

Answer NO to the question:
Would you like to copy the file to your project and modify the connection?
- NO ( no matter what - ANSWER NO ! - Absolutely NO )
Then select the tables you want in the DataSet.
and Finish.



To Test ----------

From the Solution Explorer | click the table name drop down arrow | select details
Now Drag the table name onto the form.

The form is then populated with a Navigation control
and matching Labels with corresponding Textboxes for each field in the table.

Save it.

1) Run the app.

Add one database record to the database by pressing the Add(+) icon

Just add some quick junk data that you don't mind getting lost if it doesn't save.

YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Now exit the application.

2) Run the app again.

And verify there is one record already there.

Now add a second database record to the database by pressing the Add (+) icon.

NOW add some quick junk data that you WILL intentionally loose.

*** DO NOT *** press the save icon.

Just Exit the app.

3) Again, Run the app.

Verify that the first record is still there.

Verify that the Second record is NOT there.
Its NOT there because you didn't save the data before exiting the app.

Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Also proving you must add your own code to catch the changes
and ask the user to save the data before exitiing or moving to another record.

As a side note, since vb.net uses detached datasets,
(a copy/snapshot of the dataset in memory and NOT directly linked to the database)
the dataset will reflect all changes made when moving around the detached datasets.
YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM.
Otherwise, they will simply be discarded without notice.

Whewh!

I hope this saves me some time the next time I want to start a new database project.

Oh, and uh, for anyone else reading this post.

Thanks,
Barry G. Sumpter

Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express

Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.

View 17 Replies View Related

Error: A Deadlock Was Detected While Trying To Lock Variable X For Read Access. A Lock Could Not Be Acquired After 16 Attempts

Feb 2, 2007

I simply made my script task (or any other task) fail

In my package error handler i have a Exec SQL task - for Stored Proc

SP statement is set in following expression (works fine in design time):

"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"

From progress:

Error: The Script returned a failure result.
Task SCR REIL Data failed

OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.

Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

And how did I get 4 errors? - I only set my script task result to failure

View 11 Replies View Related

How Do I Look Through The Log To See Updates To Tables In Sql Sever 2005?

Aug 22, 2006

hi. changes were made to a dababase and we need to read the log file to see how much exactly was changed and what. how do i read the log file in sql server 2005 with out buying 3rd party software?

View 5 Replies View Related

SQL Server 2005 : Piecemeal Restore With Fulltext Index

Mar 27, 2008

Hi,

I have a problem restoring a Read-Only filegroup in a database.
I've restored the Read/Write filegroups first without any problem.

Then, I am able to recover Read-Only filegroups which do not contain fulltext indexes with the command :
RESTORE DATABASE GSM FILEGROUP = 'FG_RO_20080214' WITH RECOVERY

However, for filegroups containing FullText indexes (Fulltext indexes are in the same filegroup as data used for this index), I always get the following error :

Server: Msg 3149, Level 16, State 6, Line 1
The file or filegroup "sysft_CAT_SM_20080127" is not in a valid state for the "Recover Data Only" option to be used. Only secondary files in the OFFLINE or RECOVERY_PENDING state can be processed.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

If I'm looking at sys.database_files catalog view, I can see the following
name type_desc state_desc
------------------------ ----------- -----------------
sysft_CAT_MS_20080127 FULLTEXT RECOVERY_PENDING

Does anyone have an idea, please.

Thanks in advance
Patrick

View 2 Replies View Related

SQL 2005 Rolls Back Inserts/updates

Apr 2, 2007

Hi:

We have a weird problem in our test SQL 2005 SP1 - Windows 2003 Enterprise SP1 - Server (after upgrading from SQL 2000).

While changing data via JBOSS - JDBC connection, we get all our data modifications rolled back.

Selects are fine. Same SQL 2005 Inserts/Updates through JBOSS are fine on other machines with Windows XP (JBOSS is on the same machine there, while in troubled configuration it's on its own server).

But in production imitation environment we can't modify data.

Please advise where to look for the solution.

Thanks.

View 2 Replies View Related

SQL Server 2005; Unique Constraint, Covering Index Question

Aug 9, 2006

Hi,

When I create a unique constraint, SQL Server automatically creates an index on this constraint. So when I run the following...

ALTER TABLE PersonsProjects
WITH NOCHECK ADD CONSTRAINT NoDupes UNIQUE NONCLUSTERED (PersonID, ProjectID)

...SQL Server will create a composite index on PersonsProjects called NoDupes on PersonIDand ProjectID. Thing is, I need this index to include a third column Status since most queries use this column in conjunction with PersonID and ProjectID. If there was no index on this table, I would have created it as follows:

CREATE UNIQUE INDEX NoDupes ON PersonsProjects (PersonID, ProjectID) INCLUDE (Status) WITH IGNORE_DUP_KEY

But this won't enforce the unique constraint on PersonID and ProjectID when performing inserts and updates. Is there any way of creating a unique constraint with an included column?

I would rather not have two indexes...

NoDupes: PersonID,ProjectID

New Index: PersonID,ProjectID INCLUDE Status

...so I'm trying to determine what other options that might be available...please advise.

Thanks much.

View 10 Replies View Related

Row Lock Versus Page Lock In SQL 2000.

Apr 7, 2004

Hi
We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.

Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.

At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.

So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.

Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :

Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.

How can I resolve this issue? How can I get lock on wanted rows instead of entire page?

Please advice. Thanks a bunch.

Pankaj

View 1 Replies View Related

Merge Replication Updates On Initialise (SQLServer 2005)

Jun 15, 2006

I have a merge replication scenario where the technicians are filtered by their individual codes. Each technician has his own mobile device for collecting and synchronising data.

On initialisation of the subscription, the user should only get the data that is relevant to their code.

The client is staging the rollout to all of their technicians and as the system has grown, we have noticed that there are a number (getting larger every day) of updates that are creeping into the initialisation of each subscription.

My understanding is that an initialisation will only ever apply the inserts necessary to create the subscriber database.

Is my understadning incorrect?

Is the some parameter in the creation of the publication that we have set incorrectly?

Is there something wrong with SQLServer 2005 itself?


I am very interested to hear anyones comments or advice around this issue.

Thanks
Steve

View 4 Replies View Related

Converting DB2 Timestamp Data To SQL Server 2005 - Problems With Unique Index

May 24, 2007

I am attempting to move a timestamp data column from DB2 to SQL Server 2005. Normally not a big deal but the column is part of unique index.



The DB2 timestamp has seconds of ss.ssssss but SQL Server only has ss.sss.



Most all the times entered into this column are a from an automated process so they are really close together timewise.



Here is what I have come up with so far:

1. Fast Load OLEDB with a batch of 10,000 records at a time

2. On the fail of the batch redirect rows to a regular table load OLEDB insert task

3. On the fail of the single insert redirect rows to script that ups the seconds one tick.

4. Attempt one last insert of the modified rows

5. If fail, then store the record off to a delimited text file



I am hoping to get the number of records that wind up in the delimited text file to be a very small number and not in the 1,000+.



Any help would be appreciated.

View 5 Replies View Related

'Copy Website' Function In VS 2005 Updates Or Overwrites Database?

Sep 8, 2006

I have a sql 2005 express database uploaded to my website with important information in it.

Now, I had to make some table change and need to update the online database.

I am not sure if the 'Copy Website' function in Visual Studio 2005 will update the database structure and data or will simply overwrite it.

Does anybody know the answer? If it overwrites it, would you please point me to information on how can I update the database structure and data without ruining it?

Thanks.

View 3 Replies View Related

Updates To SQLServer 2000 DLL's When 2005 Client Tools Installed

Jan 12, 2006

Hi,

I have many sql 2000 DTS packages that I support from my development workstation  running v2000 sp4. Packages are altered on the development machine and then go through a normal release mechanisms to production via testing servers etc.

I have recently installed the client tools for SQL Server 2005 on my desktop to evaluate the product.  The 2005 DB instance is running on a seperate server.

So, I have dev edition of sql 2000 and 2005 client tools (including BI Dev studio etc) on my workstation.

I have recently had to make changes to a 2000 DTS package and used my 2000 enterprise manager to do so. No Problem- saved and tested fine on my workstation. 

But when I try and release it to another server, or open the package using enterprise manager from another machine that does not have sql 2005 installed - I get an error message 'Unspecified error'.  This I've seen before when trying to open packages created in v2000 , using v7 or where the service packs are different between machines.

Digging around my workstation and comparing some of the DLLs I know to be required to distribute DTS  packages (from RDIST.txt) it seems that some of the SQL 2000 dll files have been updated by my 2005 installation. 

E.g

DTSFFILE.DLL on my machine is 2000.85.1054.0 whilst on any 'clean' 2000 machine is at version v2000.80.760.0

Surely it cant be right that SQL 2005 has newer versions of components for SQL 2000 than is available with the latest SP for the actual product! Especially considering that the installation of 2005 does not even allow you to edit 2000 DTS packages through the management studio without a 'special' download' of the feature pack,(whihc by the way does not work very well either)

So am I to conclude that you can not run side by side installations of SQL 2000 and 2005 on a single machine and expect 2000 to run as it did previously !!!

View 2 Replies View Related

Language Error When Creating FULLTEXT INDEX By Using SQL Server 2005 Management Express!

Apr 12, 2007

Hello..
When I used Microsoft SQL Server 2005 Management Studio Express to Create FULL TEXT INDEX by this code:

CREATE FULLTEXT INDEX ON txtfilestbl(txtfile) KEY INDEX PK_txtfilestbl ON ForumsArchiveLibCtlg WITH CHANGE_TRACKING AUTO

It returns this ERR MSG:

Informational: No full-text supported languages found.
Informational: No full-text supported languages found.
Msg 7680, Level 16, State 1, Line 1
Default full-text index language is not a language supported by full-text search.

I Use same this code to create FULL TEXT INDEX by using Microsoft SQL Server 2005 Management Studio, and it was working properly.
What I have to do?

View 2 Replies View Related

Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data

Dec 19, 2007

I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:




Code Block

create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.

For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.

If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.

How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?

View 4 Replies View Related

Lock Pages In Memory Option On SQL 2005 STD 64-bit With W2K3 R2 Standard 64-bit - Does It Work?

Nov 9, 2007

Hey Folks -

I'm setting up a new production SQL 2005 64-bit server running on a dedicated Windows 2003 R2 Standard system w/8GB of RAM.

I've enabled the "Lock Pages in Memory" option (http://msdn2.microsoft.com/en-us/library/ms190730.aspx) but when I run dbcc memorystatus all of my AWE counters show 0.

According to http://blogs.msdn.com/sqlprogrammability/:

"On 64 bit machines, execute 'dbcc memorystatus'. If the AWE Allocated memory is 0 then lock pages in memory privilege has not been granted to the service account or it has not taken effect. "

Does the "Lock Pages in Memory" option work on W2K3 R2 Standard? Just searching across the web I find different answers. In some places it clearly says that Lock Pages in Memory only works on W2K3 Enterprise systems. In other places it says that it should work on any Windows 2003 64-bit OS.

Does anyone have any ideas? Assuming we cannot "lock pages in memory" due to the Windows 2003 Standard OS, are there any other memory management recommendations out there?

thanks!

View 2 Replies View Related

ERROR: A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List.

May 22, 2006

Hi,
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.

View 9 Replies View Related

Can SSRS 2005 Handle Stored Procedures Or SQL Subqueries That Rreturn Rowsets Based On Multiple SQL Updates?

Nov 8, 2006

Hello,

I have a stored procedure that creates a temporary table, and then populates it using an INSERT and then a series of UPDATE statements. The procedure then returns the temporary table which will contain data in all of its columns.

When I call this procedure from SSRS 2005, the rowset returned contains data in only those columns that are populated by the INSERT statement. The columns that are set via the UPDATE statements are all empty. I read (in the Hitchhikers Guide to Reporting Services) that SSRS will only process the first rowset in a stored procedure that generates multiple rowsets. Is this true? Is this why SSRS does not retrieve data for the columns that are populated by the UPDATE statements?

Here is the stored procedure:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- File: sp_GetProgramsWatchedByDateRange.sql
-- Desc: Returns EDP program and related channel (i.e., provider) information from the IPTV Data warehouse.
-- Note that some of that data used by this procedure are obtained from the RMS_EPG database
-- which is created by an application (loadEPG) that loads the EPG data from a GLF format XML file.
-- Auth: H Hunsaker
-- Date: 11/07/2006

-- Example invocation
-- EXEC dbo.sp_GetProgramsWatchedByDateRange ...

-- Arguments/Parameters:

-- Parameter Name Type Description
-- 3. StartDate datetime First date of reporting period
-- 4. EndDate datetime Last date of reporting period
-- TerseMode bit Return all columns? (1 = no, 0 = yes)
-- 5. AsXML bit Resultset format (0 = standard, 1 = XML)
-- 6. Debug bit Debug mode (0 = off, 1 = on). Currently disabled

IF OBJECT_ID (N'dbo.sp_GetProgramsWatchedByDateRange') IS NOT NULL
DROP PROCEDURE dbo.sp_GetProgramsWatchedByDateRange
GO

CREATE PROCEDURE dbo.sp_GetProgramsWatchedByDateRange
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@TerseMode bit = 0,
@AsXML bit = 0,
@Debug bit = 0
AS
-- Notes: Much of the program content (roles, flags, etc.) that we want is not stored in the IPTV data warehouse.
-- So I am going to the RMS_EPG database to obtain that information.
-- We will have to ensure that the 2 databases are generated at the same or a matching time
-- in order to to ensure that all programID values in the data warehouse can be located in the RMS_EPG database.

-- Debug code for testing
-- DECLARE @StartDate datetime
-- DECLARE @EndDate datetime
-- DECLARE @TerseMode bit

--SET @StartDate = NULL
--SET @EndDate = NULL
--SET @TerseMode = 1

SET NOCOUNT ON

CREATE TABLE #programWatched
(
--IPTV device ID
tdeviceId uniqueidentifier NULL,
taccountId uniqueidentifier NULL,

-- Basic program information
tprogram int NULL, -- programID from EPG XML, needed to access program data in the RMS_EPG db.
tprogramId uniqueidentifier NULL, -- programID generated by IPTV
tprogramTitle varchar(150) NULL,
tprogramEpisodeTitle varchar(100) NULL,
tprogramDescription varchar(500) NULL,

toriginDateTime datetime NULL,
tduration bigint NULL,
tprogramType nvarchar(100) NULL,
tchannelCallName nvarchar(20) NULL,

--Rating
programMPAARating varchar(50) NULL,
programVCHIPRating varchar(50) NULL,
programMPAARatingVal smallint NULL,
programVChipRatingVal smallint NULL,

-- Categories
programGenre varchar(50) NULL,
programCategory1 varchar(50) NULL,
programCategory2 varchar(50) NULL,
programCategory3 varchar(50) NULL,
programCategory4 varchar(50) NULL,

-- Roles
programActor1FirstName varchar(50) NULL,
programActor1LastName varchar(50) NULL,
programActor1 varchar(100) NULL,

programActor2FirstName varchar(50) NULL,
programActor2LastName varchar(50) NULL,
programActor2 varchar(100) NULL,

programActor3FirstName varchar(50) NULL,
programActor3LastName varchar(50) NULL,
programActor3 varchar(100) NULL,

programActor4FirstName varchar(50) NULL,
programActor4LastName varchar(50) NULL,
programActor4 varchar(100) NULL,

programActor5FirstName varchar(50) NULL,
programActor5LastName varchar(50) NULL,
programActor5 varchar(100) NULL,

programActor6FirstName varchar(50) NULL,
programActor6LastName varchar(50) NULL,
programActor6 varchar(100) NULL,

programActor7FirstName varchar(50) NULL,
programActor7LastName varchar(50) NULL,
programActor7 varchar(100) NULL,

programActor8FirstName varchar(50) NULL,
programActor8LastName varchar(50) NULL,
programActor8 varchar(100) NULL,

programDirectorFirstName varchar(50) NULL,
programDirectorLastName varchar(50) NULL,
programDirector varchar(100) NULL,

programWriterFirstName varchar(50) NULL,
programWriterLastName varchar(50) NULL,
programWriter varchar(100) NULL,

programProducerFirstName varchar(50) NULL,
programProducerLastName varchar(50) NULL,
programProducer varchar(100) NULL,

-- Flags
ClosedCaption bit NULL,
InStereo bit NULL,
Repeats bit NULL,
New bit NULL,
Live bit NULL,
Taped bit NULL,
Subtitled bit NULL,
SAP bit NULL,
ThreeD bit NULL,
Letterbox bit NULL,
HDTV bit NULL,
Dolby bit NULL,
DVS bit NULL,

FlagOrdinalValue smallint NULL,

-- Channel
tchannelId int NULL,

callLetters varchar(20) NULL,
displayName varchar(50) NULL,
type varchar(50) NULL,
networkAffiliation varchar(50) NULL
)

-- I store the program watching data in a temp table because
-- data from the VIL and the Sandbox that were used to test this procedure were either incomplete or invalid.
-- Use of a temp table with a series of updates allow me more control over the result set.

IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL
INSERT INTO #programWatched (
tdeviceId,
tprogramId,
--tprogramTitle,
--tprogramEpisodeTitle,
toriginDateTime,
tduration,
--tprogramType,
--tchannelCallName,

ClosedCaption,
InStereo,
Repeats,
New,
Live,
Taped,
Subtitled,
SAP,
ThreeD,
Letterbox,
HDTV,
Dolby,
DVS
)
SELECT pw.DeviceID,
pw.programID,
--epg.program,
--epg.programTitle,
--epg.programEpisodeTitle,
pw.originTime AS 'When Watched',
pw.Duration AS 'Duration Seconds',
--epg.programType,
--epg.channelCallName,

0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.

FROM DW_EventClientProgramWatched pw
WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
AND originTime BETWEEN @StartDate AND @EndDate
ELSE
INSERT INTO #programWatched (
tdeviceId,
tprogramId,
--tprogramTitle,
--tprogramEpisodeTitle,
toriginDateTime,
tduration,
--tprogramType,
--tchannelCallName,

ClosedCaption,
InStereo,
Repeats,
New,
Live,
Taped,
Subtitled,
SAP,
ThreeD,
Letterbox,
HDTV,
Dolby,
DVS
)
SELECT pw.DeviceID,
pw.programID,
--epg.program,
--epg.programTitle,
--epg.programEpisodeTitle,
pw.originTime AS 'When Watched',
pw.Duration AS 'Duration Seconds',
--epg.programType,
--epg.channelCallName,

0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.

FROM DW_EventClientProgramWatched pw
WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system

-- AccountId/SubscriberId
UPDATE #programWatched
SET taccountId = (SELECT accountId
FROM DW_BRDB_bm_device d
WHERE d.deviceId = tdeviceId)

-- program (this is the integer program ID stored in the EPG XML, not to be confused with the IPTV programId)
-- a program can occur on multiple channels, so we filter channels where scheduleTime <= originTime <= scheculeTime + durationSecs
UPDATE #programWatched
SET tchannelCallName = (SELECT TOP 1 channelCallName
FROM DW_EPG EPG
WHERE tprogramId = EPG.programId AND toriginDateTime BETWEEN scheduleTime AND DATEADD(s, epg.durationSecs, epg.scheduleTime))

UPDATE #programWatched
SET tprogram = (SELECT TOP 1 program FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramTitle = (SELECT TOP 1 programTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramEpisodeTitle = (SELECT TOP 1 programEpisodeTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramType = (SELECT TOP 1 programType FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName)

-- Rating (otained from programValues, can also be obtained from programFlags)
UPDATE #programWatched
SET programMPAARating = (SELECT TOP 1 programValue
FROM RMS_EPG..programValue pv
WHERE tprogram = pv.programID AND pv.programValueTypeId = 9)

UPDATE #programWatched
SET programMPAARatingVal = CASE programMPAARating
WHEN 'G' THEN 10
WHEN 'PG' THEN 25
WHEN 'PG-13' THEN 30
WHEN 'R' THEN 35
WHEN 'NC-17' THEN 50
WHEN 'NRAO' THEN 60
WHEN 'NR' THEN 0
ELSE 0
END

UPDATE #programWatched
SET programVChipRating = (SELECT TOP 1 programValue
FROM RMS_EPG..programValue pv
WHERE tprogram = pv.programID AND pv.programValueTypeId = 8)

UPDATE #programWatched
SET programVChipRatingVal = CASE programVChipRating
WHEN 'TV-Y' THEN 10
WHEN 'TV-Y7' THEN 20
WHEN 'TV-G' THEN 35
WHEN 'TV-PG' THEN 40
WHEN 'TV-14' THEN 45
WHEN 'TV-MA' THEN 60
ELSE 0
END

-- Genre
UPDATE #programWatched
SET programGenre = (SELECT TOP 1 programCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType psct ON psct.programSubCategoryTypeId = pc.programCategoryId
INNER JOIN RMS_EPG..programCategoryType pct ON pct.programCategoryTypeId = psct.programCategoryTypeId
WHERE tprogram = pc.programID)

-- Categories
UPDATE #programWatched
SET programCategory1 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID)

UPDATE #programWatched
SET programCategory2 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1))

UPDATE #programWatched
SET programCategory3 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2))

UPDATE #programWatched
SET programCategory4 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2, programCategory3))

-- Roles
UPDATE #programWatched
SET programDirectorFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)

UPDATE #programWatched
SET programDirectorLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)

UPDATE #programWatched
SET programDirector = programDirectorLastName + ' , ' + programDirectorFirstName

UPDATE #programWatched
SET programWriterFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)

UPDATE #programWatched
SET programWriterLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)

UPDATE #programWatched
SET programWriter = programWriterLastName + ' , ' + programWriterFirstName

UPDATE #programWatched
SET programProducerFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)

UPDATE #programWatched
SET programProducerLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)

UPDATE #programWatched
SET programProducer = programProducerLastName + ' , ' + programProducerFirstName

UPDATE #programWatched
SET programActor1FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched
SET programActor1LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)

UPDATE #programWatched
SET programActor1 = programActor1LastName + ' , ' + programActor1FirstName

UPDATE #programWatched
SET programActor2FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName))

UPDATE #programWatched
SET programActor2LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName))

UPDATE #programWatched
SET programActor2 = programActor2LastName + ' , ' + programActor2FirstName

UPDATE #programWatched
SET programActor3FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName))

UPDATE #programWatched
SET programActor3LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleLastName NOT IN (programActor2LastName) AND programRoleLastName NOT IN (programActor2LastName))

UPDATE #programWatched
SET programActor3 = programActor3LastName + ' , ' + programActor3FirstName

UPDATE #programWatched
SET programActor4FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))

UPDATE #programWatched
SET programActor4LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))

UPDATE #programWatched
SET programActor4 = programActor4LastName + ' , ' + programActor4FirstName

UPDATE #programWatched
SET programActor5FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))

UPDATE #programWatched
SET programActor5LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))


UPDATE #programWatched
SET programActor5 = programActor5LastName + ' , ' + programActor5FirstName

UPDATE #programWatched
SET programActor6FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))

UPDATE #programWatched
SET programActor6LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))

UPDATE #programWatched
SET programActor6 = programActor6LastName + ' , ' + programActor6FirstName

UPDATE #programWatched
SET programActor7FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))

UPDATE #programWatched
SET programActor7LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))

UPDATE #programWatched
SET programActor7 = programActor7LastName + ' , ' + programActor7FirstName

UPDATE #programWatched
SET programActor8FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)
AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))


UPDATE #programWatched
SET programActor8LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)
AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))

UPDATE #programWatched
SET programActor8 = programActor8LastName + ' , ' + programActor8FirstName

-- Channel (provider) Call Letters, Display Name and Type
-- Is this correct? Should we get the channelId from the schedule table?
-- Is this efficient? View execution plan

UPDATE #programWatched
SET tchannelId = (SELECT TOP 1 c.channelId
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram)

UPDATE #programWatched
SET callLetters = (SELECT TOP 1 c.channelCallLetters
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)

UPDATE #programWatched
SET displayName = (SELECT TOP 1 c.channelDisplayName
FROM RMS_EPG..channel c
JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)

UPDATE #programWatched
SET type = (SELECT TOP 1 c.channelType
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)

UPDATE #programWatched
SET networkAffiliation = (SELECT TOP 1 c.channelNetworkAffiliation
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)

IF @TerseMode = 0
SELECT *
FROM #programWatched
ORDER BY toriginDateTime
ELSE
-- Get only Genre, title, show date/time, rating, call letters
SELECT tDeviceId, tprogramTitle, tprogramEpisodeTitle, programGenre, toriginDateTime, programMPAARating, programVCHIPRating, tchannelCallName
FROM #programWatched
ORDER BY toriginDateTime

DROP TABLE #programWatched

SET NOCOUNT OFF

GO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I also tried a query that populates some of its columns via subqueries. The query works fine when executed by the SQL Sevrer Query Analyzer,
meaning that all columns contain values, but when executed from SSRS, the columns that are poulated by the subqueries are empty, and only the columns that are not set by subqueries contain values:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT PW.DeviceID,
PW.originTime AS 'When Watched',
PW.programID,
PW.Duration AS 'Duration Seconds',

(SELECT TOP 1 programTitle FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime
AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) AS Title,

(SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND
DATEADD(second, durationSecs, DW_EPG.scheduleTime)) As program,

(SELECT TOP 1 programCategoryTypeValue
FROM RMS_EPG..programCategory PC
INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId
INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId
WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN
DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Genre,

(SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory PC
INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId
INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId
WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime
BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Category

FROM DW_EventClientProgramWatched PW
ORDER BY DeviceId, programId, originTime

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Any help is appreciated

View 3 Replies View Related

A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List

May 10, 2006

Hi 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".

View 1 Replies View Related

How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..

Oct 30, 2007

Hi...
 I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
 
this is my sproc...
 ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.

AS

SET NOCOUNT ON

DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int

SET @Sproc = OBJECT_NAME(@@ProcId)

SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END

SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END

SET NOCOUNT OFF
 
So how do i do multiple inserts and updates using this stored procedure...
 
Regards
Karen

View 5 Replies View Related

Difference Between Index Seek &&amp; Index Scan &&amp; Index Lookup Operations?

Oct 20, 2006

please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio

thank you in advance

View 3 Replies View Related

More Efficient Updates Of SQL Server

Feb 6, 2001

If I have 5000 rows to update with different values based upon the primary key, is there a better way to do it than 5000 separate update statements?

View 2 Replies View Related

Mass Updates In SQL Server

Oct 11, 2007

Does anyone know what the best way to do mass updates in SQL server is? I am currently using the methodology suggested in this article

http://www.tek-tips.com/faqs.cfm?fid=3141

But the article is assuming that once I update a field it is going to have a value that is NOT NULL. So I can loop through and update the rows that have a NOT NULL value. But my updated rows do contain NULL values, in this case what is the best way to go about this???

***************************************
Here is my code. I want to avoid using Upd_flag becos
after the following code runs I need to reset that flag
before I run my next query
***************************************

--Set rowcount to 50000 to limit number of inserts per batch
Set rowcount 50000

--Declare variable for row count
Declare @rc int
Set @rc=50000

While @rc=50000
Begin

Begin Transaction

--Use tablockx and holdlock to obtain and hold
--an immediate exclusive table lock. This usually
--speeds the insert because only one lock is needed.


update t_PGBA_DTL With (tablockx, holdlock)
SET t_PGBA_DTL.procedur = A.[Proc code],
t_PGBA_DTL.Upd_flag = 1
FROM t_PGBA_DTL
INNER JOIN CPT_HCPCS_I9_PROC_CODES A
ON t_PGBA_DTL.PROC_CD
= A.[Proc code]
WHERE t_PGBA_DTL.Upd_flag = 0


--Get number of rows updated
--Process will continue until less than 50000
Select @rc=@@rowcount

--Commit the transaction
Commit
End

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved