Problem with Check Constraints

I am working with an evaluation copy of SQL Server 2000 for the first
time; my DB experience lies with MS Access.

I have a simple table in SQL Server (tblCompany) that has a field
called "Ticker." When new company stock tickers (i.e., MSFT for
Microsoft) are entered into the field, I'd like them in all
caps--whether the user types msft, Msft, MsFt, etc. In Access, this
was easy--simply set the Format to ">" in table design view.

In SQL Server Design Table view, I've clicked on "Manage Constraints"
and put the following code in that I found elsewhere:

([Ticker] = upper([Ticker]))

I then checked all three boxes below: "Check existing data on
creation," "Enforce constraint for replication," and "Enforce
constraint for INSERTs and UPDATEs." The first one, "Check existing
data..." is checked as I've already entered in some data in the field
in lowercase to see if the check constraint would go back and change
it to Upper Case--this because I'm wanting to ultimately migrate a
table from Access to SQL Server and ensure that all Tickers are in
Upper Case.

I'm able to do this and then save the table design with changes; but
every time, I then go and look at the table data to see if the check
constraint was applied, and each time it is not; then, I go back to
"Manage Constraints" and find that the "Check existing data..." box is
unchecked. I've gone through this SEVERAL times.

Hoping this is something simple. Apologize for my "newbieness." I've
got a "For Dummies" book in front of me as well as numerous Internet
windows open, trying to figure this out. Have checked books online on
the MSFT site as well to no avail.

Thanks in advance--

RADA constraint enforces data integrity rules but does not change existing or
newly inserted data. You need to cleanup your data and then add the
constraint to only permit uppercase values going forward.

If you want to automatically change values to upper case as they are entered
on the server side, you'll need to do this in a trigger. IMHO, this task is
better done in application code and let the database just enforce the data
integrity rule.

I don't know the details of the constraint you are adding but be aware that
case sensitivity is determined by collations in SQL 2000. The default
collation is case insensitive so you'll need to override the default
case-insensitive compare in your constraint. The example script below will
correct existing data and add a check constraint to ensure only upper case
values are allowed.

UPDATE Company
SET Ticker = UPPER('Ticker')
GO

ALTER TABLE Company
ADD CONSTRAINT CK_Ticker
CHECK (Ticker COLLATE SQL_Latin1_General_Cp1_CS_AS = UPPER(Ticker))
GO

On a side note, be aware that Hungarian notation (e.g. 'tbl' prefixes) are
frowned upon in client-server database design. The underlying database
implementation (table or view) should be transparent to database users.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"RAD" <rdavenport@.nyc.rr.com> wrote in message
news:d4119d8.0401251017.28f0b46f@.posting.google.co m...
> I am working with an evaluation copy of SQL Server 2000 for the first
> time; my DB experience lies with MS Access.
> I have a simple table in SQL Server (tblCompany) that has a field
> called "Ticker." When new company stock tickers (i.e., MSFT for
> Microsoft) are entered into the field, I'd like them in all
> caps--whether the user types msft, Msft, MsFt, etc. In Access, this
> was easy--simply set the Format to ">" in table design view.
> In SQL Server Design Table view, I've clicked on "Manage Constraints"
> and put the following code in that I found elsewhere:
> ([Ticker] = upper([Ticker]))
> I then checked all three boxes below: "Check existing data on
> creation," "Enforce constraint for replication," and "Enforce
> constraint for INSERTs and UPDATEs." The first one, "Check existing
> data..." is checked as I've already entered in some data in the field
> in lowercase to see if the check constraint would go back and change
> it to Upper Case--this because I'm wanting to ultimately migrate a
> table from Access to SQL Server and ensure that all Tickers are in
> Upper Case.
> I'm able to do this and then save the table design with changes; but
> every time, I then go and look at the table data to see if the check
> constraint was applied, and each time it is not; then, I go back to
> "Manage Constraints" and find that the "Check existing data..." box is
> unchecked. I've gone through this SEVERAL times.
> Hoping this is something simple. Apologize for my "newbieness." I've
> got a "For Dummies" book in front of me as well as numerous Internet
> windows open, trying to figure this out. Have checked books online on
> the MSFT site as well to no avail.
> Thanks in advance--
> RAD|||On 25 Jan 2004 10:17:18 -0800, rdavenport@.nyc.rr.com (RAD) wrote:

>I am working with an evaluation copy of SQL Server 2000 for the first
>time; my DB experience lies with MS Access.
>I have a simple table in SQL Server (tblCompany) that has a field
>called "Ticker." When new company stock tickers (i.e., MSFT for
>Microsoft) are entered into the field, I'd like them in all
>caps--whether the user types msft, Msft, MsFt, etc. In Access, this
>was easy--simply set the Format to ">" in table design view.
>In SQL Server Design Table view, I've clicked on "Manage Constraints"
>and put the following code in that I found elsewhere:
>([Ticker] = upper([Ticker]))
>I then checked all three boxes below: "Check existing data on
>creation," "Enforce constraint for replication," and "Enforce
>constraint for INSERTs and UPDATEs." The first one, "Check existing
>data..." is checked as I've already entered in some data in the field
>in lowercase to see if the check constraint would go back and change
>it to Upper Case--this because I'm wanting to ultimately migrate a
>table from Access to SQL Server and ensure that all Tickers are in
>Upper Case.
>I'm able to do this and then save the table design with changes; but
>every time, I then go and look at the table data to see if the check
>constraint was applied, and each time it is not; then, I go back to
>"Manage Constraints" and find that the "Check existing data..." box is
>unchecked. I've gone through this SEVERAL times.
>Hoping this is something simple. Apologize for my "newbieness." I've
>got a "For Dummies" book in front of me as well as numerous Internet
>windows open, trying to figure this out. Have checked books online on
>the MSFT site as well to no avail.
>Thanks in advance--
>RAD
That doesn't work, as can be shown with

select ticker from tblCompany where ticker = 'MSFT'

Your row will be returned regardless of the case of the data.

this may be something that can be set at the database level,
alternatively use a trigger to uppercase the data.

Something like;

create trigger instblCompany
on tblCompany
instead of insert
as
insert into tblCompany
select upper(ticker), all other columns
from inserted|||Thanks both Dan and Lyndon--I'll give it a whirl.

RAD

Problem with assebly

I have problems with referensing assebly to my report. I copy my.dll to
\MSSQL\Reporting Services\ReportManager\bin
and
\MSSQL\Reporting Services\ReportServer\bin.
I have added the assembly to the report using the Referecnce tab poniting to \MSSQL\Reporting Services\ReportServer\bin but i still get error File or assembly name AssTest, or one of its dependencies, was not found.
In assebly i have simple function and it's static, i call method like this =Namespace.Class.Method.
Please, help.
AlešI did mistake in path to dll
Aleš
"AG, NLB d.d." wrote:
> I have problems with referensing assebly to my report. I copy my.dll to
> \MSSQL\Reporting Services\ReportManager\bin
> and
> \MSSQL\Reporting Services\ReportServer\bin.
> I have added the assembly to the report using the Referecnce tab poniting to \MSSQL\Reporting Services\ReportServer\bin but i still get error File or assembly name AssTest, or one of its dependencies, was not found.
> In assebly i have simple function and it's static, i call method like this =Namespace.Class.Method.
> Please, help.
> Aleš
>
>sql

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Hi I am trying to schedule a job to copy an MDB data file from Unix server to Windows 2003 server (Accfp1_data2_server). I have created a file copy SSIS package and tested it in the SSIS Visual Studio environment where it runs ok. The package was created while logged in as a domain administrator.

I then created a job to run this package (which is stored on a folder) using the credential of the same domain administrator who has full access privilege to both of these servers. However, the job fails whenever it is run manually or scheduled? The error message displayed is given below

Message
Executed as user: FORTIES\ABCITYG.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 14:26:07 Error: 2007-09-13 14:26:12.56 Code: 0xC001401E
Source: CommunityContact - Copy MS Access Database Connection manager "CONTACT.mdb On Accfp1_data2_server"
Description: The file name "\\Accfp1_data2_server\DATA2\Arts&rec\Apps\Contacts\CONTACT.mdb" specified in the connection was not valid.
End Error Error: 2007-09-13 14:26:12.56 Code: 0xC001401D Source: CommunityContact - Copy MS Access Database Description: Connection "CONTACT.mdb On Accfp1_data2_server" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:26:07 Finished: 14:26:12 Elapsed: 5.297 seconds. The package execution failed. The step failed.

Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?

Can anyone help?

Just to check, the FORTIES\ABCITYG account is the domain administrator that has access to this share?

|||

Thanks for pointing to this. Apology for my dyslexic reading of the error message. This account does not have access. I will ask our team to look into this.

I overlooked that the job was running under FORTIES\ABCITYG (local) account. It is weird because when I created the credential I had entered a different domain admin account but I noticed that the identiry has been automatically reverted to FORTIES\ABCITYG account. In fact I recreated the credential with domainserver\abcityg but the identity for this account is automatically refreshed with FORTIES\ABCITYG again and again. Any guess?

|||

Just to emphasise the fact that I am unable to create a new credential that uses other domain user. (I used the option menu Security/Credential) . And this appears to be the root of the problem.

I can select a user who is not a user in the current server (forties) but is a domain admin (<domainserver>\admin) from "select User or Group" window. But when I click on OK button the Identity field displays 'forties\admin'. How bizzare? I would have expected it to be '<domainserver>\admin'. In fact whenever I use any other <user>from the domain user list the Identity field is replaced by forties\<user>.

If this is not a bug then how on earth could you create a Domain Level Credential?

Any suggestion?|||

That is weird. I can't repro this problem.

Do you have permissions (BOL says "Requires ALTER ANY CREDENTIAL permission to create or modify a credential. Requires ALTER ANY LOGIN permission to map a login to a credential.")

Anyway, I'm not an expert on Agent Proxy account, please try this question in Tools forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

|||

Yes, I do have full permission. As suggested by (Michael) I have added a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2160775&SiteID=1 , but it is not going anywhere.

By the way, the sql server and agent are running under LocalSystem account. Will this be the problem? Will reinstalling the SQL Server using a window domain user resolve the issue? Come on Microsoft, Please advise.

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Hi I am trying to schedule a job to copy an MDB data file from Unix server to Windows 2003 server (Accfp1_data2_server). I have created a file copy SSIS package and tested it in the SSIS Visual Studio environment where it runs ok. The package was created while logged in as a domain administrator.

I then created a job to run this package (which is stored on a folder) using the credential of the same domain administrator who has full access privilege to both of these servers. However, the job fails whenever it is run manually or scheduled? The error message displayed is given below

Message
Executed as user: FORTIES\ABCITYG.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 14:26:07 Error: 2007-09-13 14:26:12.56 Code: 0xC001401E
Source: CommunityContact - Copy MS Access Database Connection manager "CONTACT.mdb On Accfp1_data2_server"
Description: The file name "\\Accfp1_data2_server\DATA2\Arts&rec\Apps\Contacts\CONTACT.mdb" specified in the connection was not valid.
End Error Error: 2007-09-13 14:26:12.56 Code: 0xC001401D Source: CommunityContact - Copy MS Access Database Description: Connection "CONTACT.mdb On Accfp1_data2_server" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:26:07 Finished: 14:26:12 Elapsed: 5.297 seconds. The package execution failed. The step failed.

Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?

Can anyone help?

Just to check, the FORTIES\ABCITYG account is the domain administrator that has access to this share?

|||

Thanks for pointing to this. Apology for my dyslexic reading of the error message. This account does not have access. I will ask our team to look into this.

I overlooked that the job was running under FORTIES\ABCITYG (local) account. It is weird because when I created the credential I had entered a different domain admin account but I noticed that the identiry has been automatically reverted to FORTIES\ABCITYG account. In fact I recreated the credential with domainserver\abcityg but the identity for this account is automatically refreshed with FORTIES\ABCITYG again and again. Any guess?

|||

Just to emphasise the fact that I am unable to create a new credential that uses other domain user. (I used the option menu Security/Credential) . And this appears to be the root of the problem.

I can select a user who is not a user in the current server (forties) but is a domain admin (<domainserver>\admin) from "select User or Group" window. But when I click on OK button the Identity field displays 'forties\admin'. How bizzare? I would have expected it to be '<domainserver>\admin'. In fact whenever I use any other <user>from the domain user list the Identity field is replaced by forties\<user>.

If this is not a bug then how on earth could you create a Domain Level Credential?

Any suggestion?|||

That is weird. I can't repro this problem.

Do you have permissions (BOL says "Requires ALTER ANY CREDENTIAL permission to create or modify a credential. Requires ALTER ANY LOGIN permission to map a login to a credential.")

Anyway, I'm not an expert on Agent Proxy account, please try this question in Tools forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

|||

Yes, I do have full permission. As suggested by (Michael) I have added a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2160775&SiteID=1 , but it is not going anywhere.

By the way, the sql server and agent are running under LocalSystem account. Will this be the problem? Will reinstalling the SQL Server using a window domain user resolve the issue? Come on Microsoft, Please advise.

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Hi I am trying to schedule a job to copy an MDB data file from Unix server to Windows 2003 server (Accfp1_data2_server). I have created a file copy SSIS package and tested it in the SSIS Visual Studio environment where it runs ok. The package was created while logged in as a domain administrator.

I then created a job to run this package (which is stored on a folder) using the credential of the same domain administrator who has full access privilege to both of these servers. However, the job fails whenever it is run manually or scheduled? The error message displayed is given below

Message
Executed as user: FORTIES\ABCITYG.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 14:26:07 Error: 2007-09-13 14:26:12.56 Code: 0xC001401E
Source: CommunityContact - Copy MS Access Database Connection manager "CONTACT.mdb On Accfp1_data2_server"
Description: The file name "\\Accfp1_data2_server\DATA2\Arts&rec\Apps\Contacts\CONTACT.mdb" specified in the connection was not valid.
End Error Error: 2007-09-13 14:26:12.56 Code: 0xC001401D Source: CommunityContact - Copy MS Access Database Description: Connection "CONTACT.mdb On Accfp1_data2_server" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:26:07 Finished: 14:26:12 Elapsed: 5.297 seconds. The package execution failed. The step failed.

Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?

Can anyone help?

Just to check, the FORTIES\ABCITYG account is the domain administrator that has access to this share?

|||

Thanks for pointing to this. Apology for my dyslexic reading of the error message. This account does not have access. I will ask our team to look into this.

I overlooked that the job was running under FORTIES\ABCITYG (local) account. It is weird because when I created the credential I had entered a different domain admin account but I noticed that the identiry has been automatically reverted to FORTIES\ABCITYG account. In fact I recreated the credential with domainserver\abcityg but the identity for this account is automatically refreshed with FORTIES\ABCITYG again and again. Any guess?

|||

Just to emphasise the fact that I am unable to create a new credential that uses other domain user. (I used the option menu Security/Credential) . And this appears to be the root of the problem.

I can select a user who is not a user in the current server (forties) but is a domain admin (<domainserver>\admin) from "select User or Group" window. But when I click on OK button the Identity field displays 'forties\admin'. How bizzare? I would have expected it to be '<domainserver>\admin'. In fact whenever I use any other <user>from the domain user list the Identity field is replaced by forties\<user>.

If this is not a bug then how on earth could you create a Domain Level Credential?

Any suggestion?|||

That is weird. I can't repro this problem.

Do you have permissions (BOL says "Requires ALTER ANY CREDENTIAL permission to create or modify a credential. Requires ALTER ANY LOGIN permission to map a login to a credential.")

Anyway, I'm not an expert on Agent Proxy account, please try this question in Tools forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

|||

Yes, I do have full permission. As suggested by (Michael) I have added a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2160775&SiteID=1 , but it is not going anywhere.

By the way, the sql server and agent are running under LocalSystem account. Will this be the problem? Will reinstalling the SQL Server using a window domain user resolve the issue? Come on Microsoft, Please advise.

sql

problem with a table

Hi All,
When I try it to push a suscriber I got an error:
The process could not bulk copy out of table '[dbo].[OrderDetails]'.
Function sequence error
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
In the snopshot said: Running -- generating conflict schema script for
article ['name']
What is the problem here?
Tks in advance
Johnny
Nobody have an idea of this?
This problem is when I create the publication of a merge replication.
At the snapshot initialization.
Tks
Johnny
"JFB" <jfb@.newSQL.com> wrote in message
news:usaLmKy1EHA.412@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> When I try it to push a suscriber I got an error:
> The process could not bulk copy out of table '[dbo].[OrderDetails]'.
> Function sequence error
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
> In the snopshot said: Running -- generating conflict schema script for
> article ['name']
> What is the problem here?
> Tks in advance
> Johnny
>
|||This could mean that the snapshot agent timed out or was
blocked. Try stopping and restarting the snapshot agent.
If you are dealing with a large table, you may need to
manually transfer it to the subscriber. Also, can you
enable logging (http://support.microsoft.com/?id=312292)
to get a detailed output, as it may be a disk issue.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Tks Paul for you reply and help.
1. Restart the snapshot agent give me the same error.
2. The table is no large (database size is 58mb)
3. I enable logging with number 2 and I got this error... what it is mean?
Generating schema script for article '[spTestCSV]'
*** [Article:'spTestCSV'] Time generating all schema scripts: 141 (ms) ***
SourceTypeId = 5
SourceName = SERVER-SQL
ErrorCode = 3724
ErrorText = Cannot drop the procedure
'dbo.sp_sel_6475C47122084DB4A360C05743334338_pal' because it is being used
for replication.
Cannot drop the procedure 'dbo.sp_sel_6475C47122084DB4A360C05743334338_pal'
because it is being used for replication.
Disconnecting from Publisher 'SERVER-SQL'
4. I have plenty space in my Hard drive.
Regards
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:207a01c4d859$0c9ed000$a501280a@.phx.gbl...
> This could mean that the snapshot agent timed out or was
> blocked. Try stopping and restarting the snapshot agent.
> If you are dealing with a large table, you may need to
> manually transfer it to the subscriber. Also, can you
> enable logging (http://support.microsoft.com/?id=312292)
> to get a detailed output, as it may be a disk issue.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Strange - Is this error definitely produced when
running the snapshot agent (and not the merge agent)? If
it is the latter, do you have a publication of the same
table on the subscriber?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I only create the publication and only the snapshot is there. I will pull a
suscriber later.
Tks
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:07e701c4d883$d0b03220$a301280a@.phx.gbl...
> Strange - Is this error definitely produced when
> running the snapshot agent (and not the merge agent)? If
> it is the latter, do you have a publication of the same
> table on the subscriber?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql

Problem with "Transfer SQL Server Objects Task"

Hi everyone,

I'm currently trying to copy a database from one server to another (both SQL2005) using Business Intelligence Development Studio.
I've created an SSIS package. The following parameters are defined:

DropObjectsFirst true
IncludeExtendedProperties true
CopyData true
ExistingData Replace
CopySchema false
UseCollation false
IncludeDependentObjects true
CopyPrimaryKeys true
CopyForeignKeys true

The package fails with the following error:
Violation of PRIMARY KEY constraint 'PK_tblCallStatus' Cannot insert duplicate key in object 'dbo.tblCallStatus'

I know what that error means but I don't understand why I get it.
Isn't the package supposed to completely overwrite the destination database ?
It obviously does not. When I manually delete all records from 'tblCallStatus' in the destination database it works fine. I can't remember I had to do that in a SQL2000 environment using DTS.

Hope anyone can help since this is almost driving me nuts ;-)

Thanks in advance,
Kevin

I've had the same problems, and for what it's worth, here's what I found:

setting copySchema to true fixed a few problems

CopyForeignKeys.....it doesn't work, so don't do it. SSIS seems to try and create foreign keys before it has created all the primary keys, so it can sometimes fall over

check jamie thomson's article for possible workarounds of the foreign key problem:
http://blogs.conchango.com/jamiethomson/archive/2006/02/17/SSIS_3A00_-How-to-load-related-tables.aspx

do you have service pack 2 installed?

michal
|||Hi Michael,

I've also experimented with 'copySchema'. No luck so far.
Service pack 2 is installed on both servers.
I've read the article you're referring to and I just can't believe that the "Transfer... Task" isn't able to perform such a simple thing.
|||Oops, sorry for the chaos.
The previous post was also made by me. I currently have two Passport identities.
I'll try to not use the other one anymore...

*edit*
This is starting to become really weird. I've manually created a database (no tables, sps etc.) on the destination server. When I tell the "transfer... task" to copy the production database from the source server to this new database it gives me the following error:

"Cannot find the object "dbo.tblTasks" because it does not exist or you do not have permissions."

Of course the table doesn't exist. The package is supposed to create it. A lack of permissions can't be the problem as well since I'm sysadmin.

I'm really stuck here. SQL 2005 has been out for quite a while now. I just can't believe that this is a bug.
|||Hi Kevin

"Cannot find the object "dbo.tblTasks" because it does not exist or you do not have permissions."

That means that you have set 'dropObjectsFirst' to true. It's trying to drop a table that doesn't exist. Your best bet is to either drop the tables before hand and set 'dropObjectsFirst' to false, or make sure the objects exist on the target DB.

although you have SP2 on both servers, are you creating the package on one of those servers? Or do you have SP2 on the machine that you are creating the package?

Everything I've read about this suggests using backup/copy/restore, which isn't brilliant.

The only thing I can really suggest is stripping the package right down to simply copying a few tables, then rerunning it over and over adding more and more options until it falls over. Trial and error I know, but at least you will know the SSIS limitations

michal

Problem while making Setup of Project

Hello friends,
While making a setup copy of my project I got the f errors in the following files-
i)d:\winnt\system32\crpe32.dll
ii)d:\winnt\system32\msvcrt.dll
iii)d:\winnt\system32\mfc42.dll
The error is-
The destination file is in use. Please ensure all other applications are closed. You have a chance to Abort, Retry, Ignore..If U ignore again the following message-
If U ignore, the file will not be copied and application may not run properly. Do U want to ignore.
ANother error I got was-
An error occured while registering d:\winnt\system32\msado20.tlb

Please let me know how to overcome these 4 errors...By the way I developed in VB6.0 and Oracle8i with Crystal Report 7.0 for reporting...O/S is Win 2000 Advanced Server..

Bye and Thanx...Hi,
When creating setup make sure that all the applications are closed. This may solve the first three problems. For the last problem, Use Microsoft ActiveX Data objects 2.0 library in the project.

Madhivanan|||Hai

I am also facing the same problem. but Mathi's solution does not solve the problem. The same problem exists again and again.

Anyone knows, help us.

Regards,

Velayudham.|||Hi Madhi,
Thanx for ur reply...But I tried both that u suggest...Even then it's still not workin...Any other suggestion...

Velayudham if U get the solution pl let me know...|||Hi

which version of Crystal Report are you using? Try to register those dlls.

For the last error try the following

In your package and deployment look for the file setup.lst then under the [Setup1 Files] section look for the entry of Msado20.tlb, make sure that the registration is set to TLBRegister instead of DLLSelfRegister. Package and deployment wizard causes this error, just change and save your setup.lst then re-install your package

Madhivanan|||Hi,

I had got a new installer. You can try this.

Use this link: http://www.jrsoftware.org/isdl.php

have a good day. Bye.|||Hello Madhi,
Still no working ya...Pl say some other way...

Velayudham,
Thanx... I'll try...|||Hi friends, pl help me-

I implemented a s/w in an organisation..It has win 2000 advanced server as o/s, oracle 8i as RDBMS, Front End in VB 6.0 and reporting in crystal report..

Now there are two tables service_usage_detail and invoice-detail. invoice-detail has a FK to service_usage_detail...

Bfore I could updated any service_name in the invoice_detail table, if the user does any mistake..But now I'm not being able to do the updation as it gives an eror message- Parent key not found Error no 2291...

By the way bfore the problem persists I did defragmentation and check (Scandisk I suppose) of all drives of the HDD from drive property's Tools tab...Out of the drives I skiped defragmentation half way on drive H:..I have noticed that the prob arises after that...

Problem using sp_attach_db with an encypted file system

I want to run a copy of our Sql2000 production database on my WinXP
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.com/ck_database_encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files reside
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected via
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database will
> not start.
> See the below link:-
> http://www.sql-server-performance.com/ck_database_encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.com...
> > I want to run a copy of our Sql2000 production database on my WinXP
> > laptop for development. Because this database contains sensitive
> > information and the laptop cannot be physically secured, I have
> > enabled File Encryption on the project directory to protect the
> > database in the event that someone steals the laptop. To set up a
> > development environment I installed Sql2000 personal edition, and
> > disconnected the production database with:
> >
> > EXEC sp_detach_db @.dbname ='myDB'
> >
> > I then copied the mdf and log files to the encrypted directory on the
> > laptop and attempted to attach with:
> >
> > EXEC sp_attach_db @.dbname = N'myDB',
> > @.filename1 = N'C:\Projects\Data.mdf',
> > @.filename2 = N'C:\Projects\Log.ldf'
> >
> > This failed with the error message: "Device activation error. The
> > physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> > freaking me out, because the exact same command worked fine on the
> > production server to reattach the database. After some trial and
> > error, I removed file encryption on the mdf and ldf file and the
> > database attached without any problem.
> >
> > So my questions is, is this a known problem and is it possible to have
> > file encryption on an SQL database?

Problem using sp_attach_db with an encypted file system

I want to run a copy of our Sql2000 production database on my WinXP
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?
Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.co...encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.co m...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?
|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files reside
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected via
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database will
> not start.
> See the below link:-
> http://www.sql-server-performance.co...encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.co m...

Problem using sp_attach_db with an encypted file system

I want to run a copy of our Sql2000 production database on my WinXP
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.c..._encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP
12.phx.gbl>...[vbcol=seagreen]
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files resi
de
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected v
ia
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database wi
ll
> not start.
> See the below link:-
> http://www.sql-server-performance.c..._encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.com...

Problem Using Copy Database Wizard in SSMS

I am having a problem copying a database on a server back to the same server
from a remote computer using SSMS. I have 2 remote computers (WinXP) running
SSMS on the same domain as the server. I log in as an administrative user on
both, but can only run the Copy Database Wizard successfully from one of the
remote computers.
On the computer that fails, it does so at the Create Package step and
returns an error message saying "No description found". Anyone have an idea
how I should begin troubleshooting this problem? I can not find anything in
the event logs on the remote system or server that sheds a clue, or in the
SQL server logs.
Don Sivitz (DonSivitz@.discussions.microsoft.com) writes:
> I am having a problem copying a database on a server back to the same
> server from a remote computer using SSMS. I have 2 remote computers
> (WinXP) running SSMS on the same domain as the server. I log in as an
> administrative user on both, but can only run the Copy Database Wizard
> successfully from one of the remote computers.
> On the computer that fails, it does so at the Create Package step and
> returns an error message saying "No description found". Anyone have an
> idea how I should begin troubleshooting this problem? I can not find
> anything in the event logs on the remote system or server that sheds a
> clue, or in the SQL server logs.
That's not an error that I've seen. Which method are you trying to use?
Direct copy of the files, or the SMO method?
There is a CTP of SP2 out, and Microsoft claims that there are a lot of
fixes to the Copy Database Wizard. I have not tested it myself, though,
yet. I don't really want to recommend you to install beta software, but
if you are desperate you can try it.
However, there plenty of other ways of achieving what the wizard does,
so you may want to explore those options instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Problem uploading .rdl

The report server cannot process the report. The data source connection information has been deleted

I want to make a copy of a report. I go into report properties on the SSRS, click edit and download the .rdl to my computer.

I then upload the report, using a different name.

I then get the above error.

If I copy the report to the same location as the shared data source, and the above error disappears.

But now the report doesn't work - no parameters in the dropdown lists.

My question is : Why does a copy of a report, uploaded to the SSRS, not work, when the original is sitting right next to it, with the same rdl definitin, in a working state?

Are all the datasets using a single shared data source? Check that no private data sources are being used. After re-uploading the report with a different name, go to the properties page in report manager and in the data sources section make sure that you check all the datasources being used. Repoint it to the shared datasource and retype any connection strings and usernames and passwords for the data sources. I think for security reasons these are ommitted when you export the RDL.|||

Adam,

You are right. I checked the Data Sources, and there were two shared data sources that were marked invalid. However, I notice that the parameters have also been messed up. In the original report, there was no default parameter specified, whereas the new report (copied and uploaded) indicates a default parameter, which happens to be an invalid value. Because the dropdown boxes for the other parameters are linked, these dropdown boxes don't populate. My reasoning says that the uploaded .rdl should be the same as the copy I downloaded. I can see that it has been changed when referencing Data Sources. Why is there this difference in the parameters? Is this "by design"?
Question : Why does the parameter default value change when I upload the .rdl using a different name?

Question: How do I fix the problem?

|||

I notice that the new report (copy) has two problems with the parameters:

1. Parameter is no longer marked "hidden", but is marked "prompt the user"

2. Parameter contains an invalid default value.

I have changed the parameter so that it is now marked "hidden", and so that the default value is the same as the original report (which was empty). The report now works.

Perhaps somebody has an answer to the problem below (repeated from previous post):

Question : Why does the parameter default value change when I upload the .rdl using a different name?