Error executing Packaga from job

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Date 24/01/2007 12:30:28
Log Job History (Carga datos ACH)

Step ID 1
Server ATLANTE\SQL2005
Job Name Carga datos ACH
Step Name Carga de datos de ach
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: ATLANTE\SYSTEM. The package execution failed. The step failed.

Maybe is the user that it tried to execute the package as?

How can I change it?

please delete this post. sorry for repeating|||I can't, but can someone merge some of Luis' threads that all revolve around the same issue?

Thanks,
Phil|||

this article describe solution to most comon issues when executing the package:

http://support.microsoft.com/kb/918760

|||

Hello, when I try to change the package protection level to server storage I got this

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.

|||Are you storing the packages as XML files or inside the DB? if you are using xmls files you can use DonSaveSensitive and the use package configurations to set the conection strings at run time as described in method 4 in that article.

Error executing extended stored procedure: Invalid Parameter

When I try to connect to sql server instance I received this error:
TITLE: Microsoft SQL Server Management Studio
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
ADDITIONAL INFORMATION:
Error executing extended stored procedure: Invalid Parameter
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
Server, Error: 22001)
when i click refresh button i am able to see my schema, but i can't see the
properties of my base and any table properties. I receive error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
Error executing extended stored procedure: Invalid Parameter
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
Server, Error: 22001)
--
BUTTONS:
OK
--
also the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names is
locked and can not be opened.
After a reboot the key is now readable and the 2005 instance can be
connected to without these messages.
But after a while it does it again.
does anyone have any idea what is going wrong?Hi
My guess is that you have some group policy or Anti-virus/ISD system that is
locking down this registry key.
John
"nikolakg" wrote:
> When I try to connect to sql server instance I received this error:
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Error executing extended stored procedure: Invalid Parameter
> Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
> Server, Error: 22001)
>
> when i click refresh button i am able to see my schema, but i can't see the
> properties of my base and any table properties. I receive error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Error executing extended stored procedure: Invalid Parameter
> Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
> Server, Error: 22001)
>
> --
> BUTTONS:
> OK
> --
>
> also the registry key
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names is
> locked and can not be opened.
> After a reboot the key is now readable and the 2005 instance can be
> connected to without these messages.
> But after a while it does it again.
> does anyone have any idea what is going wrong?

Error during restore operation

Hi all,
I installed SQL server 2000 on my machine first.
Then i just installed SQL server 2005 tools on above it to get
management studio access.
Now i am trying to restore a database on my machine from backup which
i took from server running SQL server 2005.
i am getting the error-
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
Server, Error:3205)
Does anybody know how do i go about to resolve it.
Thanks a lot.
I did some Google which suggests you get this error if you try to restore a 2005 backup to 2000.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<nitinluthra@.gmail.com> wrote in message
news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
> Hi all,
> I installed SQL server 2000 on my machine first.
> Then i just installed SQL server 2005 tools on above it to get
> management studio access.
> Now i am trying to restore a database on my machine from backup which
> i took from server running SQL server 2005.
> i am getting the error-
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
> Server, Error:3205)
> Does anybody know how do i go about to resolve it.
> Thanks a lot.
|||I reproduced the error, that's exactly an error message you get when you try
to restore a backup that is taken from a SQL Server 2005 instance.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzMom43nIHA.3652@.TK2MSFTNGP03.phx.gbl...
>I did some Google which suggests you get this error if you try to restore a
>2005 backup to 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <nitinluthra@.gmail.com> wrote in message
> news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
>
sql

Error during restore operation

Hi all,
I installed SQL server 2000 on my machine first.
Then i just installed SQL server 2005 tools on above it to get
management studio access.
Now i am trying to restore a database on my machine from backup which
i took from server running SQL server 2005.
i am getting the error-
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
Server, Error:3205)
Does anybody know how do i go about to resolve it.
Thanks a lot.I did some Google which suggests you get this error if you try to restore a 2005 backup to 2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<nitinluthra@.gmail.com> wrote in message
news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
> Hi all,
> I installed SQL server 2000 on my machine first.
> Then i just installed SQL server 2005 tools on above it to get
> management studio access.
> Now i am trying to restore a database on my machine from backup which
> i took from server running SQL server 2005.
> i am getting the error-
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
> Server, Error:3205)
> Does anybody know how do i go about to resolve it.
> Thanks a lot.|||I reproduced the error, that's exactly an error message you get when you try
to restore a backup that is taken from a SQL Server 2005 instance.
--
Ekrem Önsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzMom43nIHA.3652@.TK2MSFTNGP03.phx.gbl...
>I did some Google which suggests you get this error if you try to restore a
>2005 backup to 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <nitinluthra@.gmail.com> wrote in message
> news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
>> Hi all,
>> I installed SQL server 2000 on my machine first.
>> Then i just installed SQL server 2005 tools on above it to get
>> management studio access.
>> Now i am trying to restore a database on my machine from backup which
>> i took from server running SQL server 2005.
>> i am getting the error-
>> Too many backup devices specified for backup or restore; only 64 are
>> allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
>> Server, Error:3205)
>> Does anybody know how do i go about to resolve it.
>> Thanks a lot.
>

Error during database restore

Hi,

I'm trying to restore a database backup but I get this error. What does it mean exactly?

Thanks!
TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'WHIDBEY1'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1314.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The media set has 2 media families but only 1 are provided. All members must be provided.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3132)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1314&EvtSrc=MSSQLServer&EvtID=3132&LinkId=20476

BUTTONS:

OK

what this usually means is that you backed up your database to two different files, but are only supplying one file during your restore. Is this the case? Did you create a database backup to multiple files? If so, you need all of them to do the restore.

|||I have backed up the database to a file in one server and trying to restore the backup file into another database in another server. While restoring i am getting the same error. Can you tell me what could be the issue.

Error during database restore

Hi,

I'm trying to restore a database backup but I get this error. What does it mean exactly?

Thanks!
TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'WHIDBEY1'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1314.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The media set has 2 media families but only 1 are provided. All members must be provided.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3132)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1314&EvtSrc=MSSQLServer&EvtID=3132&LinkId=20476

BUTTONS:

OK

what this usually means is that you backed up your database to two different files, but are only supplying one file during your restore. Is this the case? Did you create a database backup to multiple files? If so, you need all of them to do the restore.

|||I have backed up the database to a file in one server and trying to restore the backup file into another database in another server. While restoring i am getting the same error. Can you tell me what could be the issue.

Error deploying report with SQL Server (Visual Studio) 2005

Hi All,
I'm trying to deploy a report that have just been created in Visual
Studio 2005 (for Microsoft CRM) I'm getting an error message
******************************
The project cannot be deployed because no target server is specified.
Provide a value for the TargetServerURL property in the property page
of this project.
******************************
I'm sorry I'm new with this Visual Studio 2005 ... I'm not finding the
place to put the TargetServerURL value in that property page ... The
worst thing is that it is probably right under my nose ... ;-)
Anybody can help a dummy ? ... ThanksProject -> your Project Name Properties (Last Menu Item) on the pop up
window you'll find what you're looking for.
Manny
Frank wrote:
>Hi All,
>I'm trying to deploy a report that have just been created in Visual
>Studio 2005 (for Microsoft CRM) I'm getting an error message
>******************************
>The project cannot be deployed because no target server is specified.
>Provide a value for the TargetServerURL property in the property page
>of this project.
>******************************
>I'm sorry I'm new with this Visual Studio 2005 ... I'm not finding the
>place to put the TargetServerURL value in that property page ... The
>worst thing is that it is probably right under my nose ... ;-)
>Anybody can help a dummy ? ... Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200701/1

Error deploying a CLR Stored Procedure that uses a web service

Ok, first some background.

I am writing my first (complex) CLR Stored Procedure using Visual Studio 2005.

This SP worked fine until I added code to make a web service call. That web service is a wrapper web service I created because the actual web service I need to call uses System. Web.Extensions which was not available in my VS2005 Database Project.

At first I was getting the standard "External Access Assembly" errors, so I created a new user (was using SA) and assigned database ownership to the new user, then assigned permissions to that user. This worked to get it deployed, but I get the following error when its run:

<code>

System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

System.IO.FileLoadException:

at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)

at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)

at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)

at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)

at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)

at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, S

</code>

Anyone have any ideas?

Thanks!

Dave Borneman

Solution Architect,

anyWare Mobile Solutions.

OK, so when you are using Web services the .NET framework will dynamically generate an assembly based on your proxy code. Dynamic assembly generation is not allowed inside SQL Server (SQLCLR).

What you need to do is to pre-generate the proxy assembly by using the sgen tool and deploy that generated assembly into the database.

Look at this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74480&SiteID=1 and specifically the 6:th message in that thread how to achieve the pre-generation.

Niels

Error creating Report Model based upon Oracle Database

In MS Visual Studio, when creating a new Report Model Project, after defining a datasource to an Oracle database (and successfully testing it), and a simple datasource view (1 table), when I click "Run" in Report Model Wizard, I receive the following error:
"ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }"

It does not appear that one can create Report Models from an Oracle database (since the SQL being used to query the Oracle database cannot be edited and contains syntax errors?). Is this the case?

Thank you.

Models for Oracle are not currently supported.

BTW, what version of Oracle are you using?

|||

Tried it on Oracle 9.2 and 10.1.

Thank you.

|||I have also encountered the identical problem. Has a resolution been provided yet?|||Report Models support Oracle starting from SQL 2005 SP2. Please check SP2 docs for more details.|||I do not see anything for SQL Server 2005 SP2, where can I find more info.|||

Alexandre

Where is SQL Server 2005 SP2? We can not find any reference to this release.

|||

Will it be supported by SP 2? When can we expect SP 2?

Thanks,

Roman

|||

Report Models for Oracle 9 and above will be supported in Yukon SP2.
Public SP2 CTP is on its way. Keep an eye on http://microsoft.com/sqlserver

|||

What is CTP?

Thank you.

|||CTP is community tech preview. sort of Beta|||Has anyone been succesful getting this to work on
SQL Server 2005 Service Pack 2 CTP (November 2006)?
|||I've installed SQL Server 2005 SP2 and still the same error message pops up. Anyone been able to fix it?

Error creating Report Model based upon Oracle Database

In MS Visual Studio, when creating a new Report Model Project, after defining a datasource to an Oracle database (and successfully testing it), and a simple datasource view (1 table), when I click "Run" in Report Model Wizard, I receive the following error:
"ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }"

It does not appear that one can create Report Models from an Oracle database (since the SQL being used to query the Oracle database cannot be edited and contains syntax errors?). Is this the case?

Thank you.

Models for Oracle are not currently supported.

BTW, what version of Oracle are you using?

|||

Tried it on Oracle 9.2 and 10.1.

Thank you.

|||I have also encountered the identical problem. Has a resolution been provided yet?|||Report Models support Oracle starting from SQL 2005 SP2. Please check SP2 docs for more details.|||I do not see anything for SQL Server 2005 SP2, where can I find more info.|||

Alexandre

Where is SQL Server 2005 SP2? We can not find any reference to this release.

|||

Will it be supported by SP 2? When can we expect SP 2?

Thanks,

Roman

|||

Report Models for Oracle 9 and above will be supported in Yukon SP2.
Public SP2 CTP is on its way. Keep an eye on http://microsoft.com/sqlserver

|||

What is CTP?

Thank you.

|||CTP is community tech preview. sort of Beta|||Has anyone been succesful getting this to work on
SQL Server 2005 Service Pack 2 CTP (November 2006)?
|||I've installed SQL Server 2005 SP2 and still the same error message pops up. Anyone been able to fix it?sql

Error creating Report Model based upon Oracle Database

In MS Visual Studio, when creating a new Report Model Project, after defining a datasource to an Oracle database (and successfully testing it), and a simple datasource view (1 table), when I click "Run" in Report Model Wizard, I receive the following error:
"ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }"

It does not appear that one can create Report Models from an Oracle database (since the SQL being used to query the Oracle database cannot be edited and contains syntax errors?). Is this the case?

Thank you.

Models for Oracle are not currently supported.

BTW, what version of Oracle are you using?

|||

Tried it on Oracle 9.2 and 10.1.

Thank you.

|||I have also encountered the identical problem. Has a resolution been provided yet?|||Report Models support Oracle starting from SQL 2005 SP2. Please check SP2 docs for more details.|||I do not see anything for SQL Server 2005 SP2, where can I find more info.|||

Alexandre

Where is SQL Server 2005 SP2? We can not find any reference to this release.

|||

Will it be supported by SP 2? When can we expect SP 2?

Thanks,

Roman

|||

Report Models for Oracle 9 and above will be supported in Yukon SP2.
Public SP2 CTP is on its way. Keep an eye on http://microsoft.com/sqlserver

|||

What is CTP?

Thank you.

|||CTP is community tech preview. sort of Beta|||Has anyone been succesful getting this to work on
SQL Server 2005 Service Pack 2 CTP (November 2006)?
|||I've installed SQL Server 2005 SP2 and still the same error message pops up. Anyone been able to fix it?

Error creating Report Model based upon Oracle Database

In MS Visual Studio, when creating a new Report Model Project, after defining a datasource to an Oracle database (and successfully testing it), and a simple datasource view (1 table), when I click "Run" in Report Model Wizard, I receive the following error:
"ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }"

It does not appear that one can create Report Models from an Oracle database (since the SQL being used to query the Oracle database cannot be edited and contains syntax errors?). Is this the case?

Thank you.

Models for Oracle are not currently supported.

BTW, what version of Oracle are you using?

|||

Tried it on Oracle 9.2 and 10.1.

Thank you.

|||I have also encountered the identical problem. Has a resolution been provided yet?|||Report Models support Oracle starting from SQL 2005 SP2. Please check SP2 docs for more details.|||I do not see anything for SQL Server 2005 SP2, where can I find more info.|||

Alexandre

Where is SQL Server 2005 SP2? We can not find any reference to this release.

|||

Will it be supported by SP 2? When can we expect SP 2?

Thanks,

Roman

|||

Report Models for Oracle 9 and above will be supported in Yukon SP2.
Public SP2 CTP is on its way. Keep an eye on http://microsoft.com/sqlserver

|||

What is CTP?

Thank you.

|||CTP is community tech preview. sort of Beta|||Has anyone been succesful getting this to work on
SQL Server 2005 Service Pack 2 CTP (November 2006)?|||I've installed SQL Server 2005 SP2 and still the same error message pops up. Anyone been able to fix it?

Error creating Report Model based upon Oracle Database

In MS Visual Studio, when creating a new Report Model Project, after defining a datasource to an Oracle database (and successfully testing it), and a simple datasource view (1 table), when I click "Run" in Report Model Wizard, I receive the following error:
"ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }"

It does not appear that one can create Report Models from an Oracle database (since the SQL being used to query the Oracle database cannot be edited and contains syntax errors?). Is this the case?

Thank you.

Models for Oracle are not currently supported.

BTW, what version of Oracle are you using?

|||

Tried it on Oracle 9.2 and 10.1.

Thank you.

|||I have also encountered the identical problem. Has a resolution been provided yet?|||Report Models support Oracle starting from SQL 2005 SP2. Please check SP2 docs for more details.|||I do not see anything for SQL Server 2005 SP2, where can I find more info.|||

Alexandre

Where is SQL Server 2005 SP2? We can not find any reference to this release.

|||

Will it be supported by SP 2? When can we expect SP 2?

Thanks,

Roman

|||

Report Models for Oracle 9 and above will be supported in Yukon SP2.
Public SP2 CTP is on its way. Keep an eye on http://microsoft.com/sqlserver

|||

What is CTP?

Thank you.

|||CTP is community tech preview. sort of Beta|||Has anyone been succesful getting this to work on
SQL Server 2005 Service Pack 2 CTP (November 2006)?|||I've installed SQL Server 2005 SP2 and still the same error message pops up. Anyone been able to fix it?

Error creating new SSIS project

When I start a new project the BI environment cannot open the DTSX package and I get only one tab with the message:

"microsoft visual studio is unable to load this document. Object reference not set to an instance of an object".

I tried uninstall and install of BI Development Studio as well as a repair install of Visual Studio 2005. None of which helped. Please help me to get this fixed.

Kind regards,

Neeva

Just to let you know, this problem seemed to have disappeared when I had to install SQL Server Express for a completely unrelated reason. I still don't understand it, but oh well..

Error creating new database diagrams in upgraded database

(I'm using MS SQL Server Management Studio, SQL Server version 9.0.3054)
I restored a database originally created in sql 2000 to a sql 2005 server. I
also set the compatibility level to sql 2005 (90). The owner is sa. When I
try to create a new database diagram, I first get the message "This database
does not have one or more of the support objects required to use database
diagramming. Do you wish to create them?" I click Yes to that. Then I get
the message "Violation of UNIQUE KEY constraint 'UK_principal_name'. Cannot
insert duplicate key in object 'dbo.sysdiagrams'. The statement has been
terminated. (Microsoft SQL Server, Error: 2627)".
I can create diagrams for AdventureWorks. Is there a way to fix this other
than recreating the database from scratch as sql 2005?
Thanks.
WWW.ROVA.COMM
Thankfully, the diagramming objects are benign and you can remove them with
little ill effect. I'm not sure if some of these will fail if they didn't
come over from 2000, but the whole script should run and then you should be
able to say yes to that prompt sufccessfully.
DROP PROCEDURE sp_upgraddiagrams;
GO
DROP PROCEDURE sp_helpdiagrams;
GO
DROP PROCEDURE sp_helpdiagramdefinition;
GO
DROP PROCEDURE sp_creatediagram;
GO
DROP PROCEDURE sp_renamediagram;
GO
DROP PROCEDURE sp_alterdiagram;
GO
DROP PROCEDURE sp_dropdiagram;
GO
DROP FUNCTION fn_diagramobjects;
GO
DROP TABLE sysdiagrams;
GO
I asked for a button to facilitate this a long time ago, but not too many
people seem to think it is a good idea.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125014
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:A16BFF97-E832-4154-8391-712B1C415BE8@.microsoft.com...
> (I'm using MS SQL Server Management Studio, SQL Server version 9.0.3054)
> I restored a database originally created in sql 2000 to a sql 2005 server.
> I
> also set the compatibility level to sql 2005 (90). The owner is sa. When I
> try to create a new database diagram, I first get the message "This
> database
> does not have one or more of the support objects required to use database
> diagramming. Do you wish to create them?" I click Yes to that. Then I get
> the message "Violation of UNIQUE KEY constraint 'UK_principal_name'.
> Cannot
> insert duplicate key in object 'dbo.sysdiagrams'. The statement has been
> terminated. (Microsoft SQL Server, Error: 2627)".
> I can create diagrams for AdventureWorks. Is there a way to fix this other
> than recreating the database from scratch as sql 2005?
> Thanks.
> --
> WWW.ROVA.COMM
|||I was unable to reproduce this issue, btw. I created a database in 2000,
created a diagram, backed it up, then restored on 2005, set a valid owner,
changed cmptlevel to 90, expanded database diagrams node in object explorer,
said yes to the prompt, and I was able to modify the existing diagram and
create a new one. How many diagrams had you created in 2000 before you
backed up the database and restored it in 2005?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:A16BFF97-E832-4154-8391-712B1C415BE8@.microsoft.com...
> (I'm using MS SQL Server Management Studio, SQL Server version 9.0.3054)
> I restored a database originally created in sql 2000 to a sql 2005 server.
> I
> also set the compatibility level to sql 2005 (90). The owner is sa. When I
> try to create a new database diagram, I first get the message "This
> database
> does not have one or more of the support objects required to use database
> diagramming. Do you wish to create them?" I click Yes to that. Then I get
> the message "Violation of UNIQUE KEY constraint 'UK_principal_name'.
> Cannot
> insert duplicate key in object 'dbo.sysdiagrams'. The statement has been
> terminated. (Microsoft SQL Server, Error: 2627)".
> I can create diagrams for AdventureWorks. Is there a way to fix this other
> than recreating the database from scratch as sql 2005?
> Thanks.
> --
> WWW.ROVA.COMM
|||I tried that script, running it against my database (and against master (!)
just in case). None of those procedures existed, I think. I got this for each
one:
Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist or
you do not have permission. (I'm running as 'SA' by the way.)
I tried creating diagrams again but got the same error.
I only had 4 or 5 diagrams, which I don't really care about. I just wanna
make some new ones.
Thanks for the quick response.
WWW.ROVA.COMM
"Aaron Bertrand [SQL Server MVP]" wrote:

> Thankfully, the diagramming objects are benign and you can remove them with
> little ill effect. I'm not sure if some of these will fail if they didn't
> come over from 2000, but the whole script should run and then you should be
> able to say yes to that prompt sufccessfully.
> DROP PROCEDURE sp_upgraddiagrams;
> GO
> DROP PROCEDURE sp_helpdiagrams;
> GO
> DROP PROCEDURE sp_helpdiagramdefinition;
> GO
> DROP PROCEDURE sp_creatediagram;
> GO
> DROP PROCEDURE sp_renamediagram;
> GO
> DROP PROCEDURE sp_alterdiagram;
> GO
> DROP PROCEDURE sp_dropdiagram;
> GO
> DROP FUNCTION fn_diagramobjects;
> GO
> DROP TABLE sysdiagrams;
> GO
> I asked for a button to facilitate this a long time ago, but not too many
> people seem to think it is a good idea.
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125014
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "MCG" <MCG@.discussions.microsoft.com> wrote in message
> news:A16BFF97-E832-4154-8391-712B1C415BE8@.microsoft.com...
>
>
|||Sorry, it should have been
DROP <object> SYS.<object_name>, e.g. (most importantly, I think):
DROP TABLE sys.sysdiagrams;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:31A0508C-6D52-4543-BDC0-CE6FDFE3324C@.microsoft.com...[vbcol=seagreen]
>I tried that script, running it against my database (and against master (!)
> just in case). None of those procedures existed, I think. I got this for
> each
> one:
> Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist
> or
> you do not have permission. (I'm running as 'SA' by the way.)
> I tried creating diagrams again but got the same error.
> I only had 4 or 5 diagrams, which I don't really care about. I just wanna
> make some new ones.
> Thanks for the quick response.
> --
> WWW.ROVA.COMM
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||> Sorry, it should have been
> DROP <object> SYS.<object_name>, e.g. (most importantly, I think):
> DROP TABLE sys.sysdiagrams;
Ignore that, I need more sleep.
|||Oh, and what happens when you run:
SELECT * FROM dbo.sysdiagrams
?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:31A0508C-6D52-4543-BDC0-CE6FDFE3324C@.microsoft.com...[vbcol=seagreen]
>I tried that script, running it against my database (and against master (!)
> just in case). None of those procedures existed, I think. I got this for
> each
> one:
> Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist
> or
> you do not have permission. (I'm running as 'SA' by the way.)
> I tried creating diagrams again but got the same error.
> I only had 4 or 5 diagrams, which I don't really care about. I just wanna
> make some new ones.
> Thanks for the quick response.
> --
> WWW.ROVA.COMM
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||When I run SELECT * FROM dbo.sysdiagrams on my app's database I get this:
Invalid object name 'dbo.sysdiagrams'. There's only one sys table in my
database: dbo.dtproperties.
Thanks again for your help.
WWW.ROVA.COMM
"Aaron Bertrand [SQL Server MVP]" wrote:

> Oh, and what happens when you run:
> SELECT * FROM dbo.sysdiagrams
> ?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "MCG" <MCG@.discussions.microsoft.com> wrote in message
> news:31A0508C-6D52-4543-BDC0-CE6FDFE3324C@.microsoft.com...
>
>
|||MCG (MCG@.discussions.microsoft.com) writes:
> I tried that script, running it against my database (and against master
> (!) just in case). None of those procedures existed, I think. I got this
> for each one:
> Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist
> or you do not have permission. (I'm running as 'SA' by the way.)
> I tried creating diagrams again but got the same error.
> I only had 4 or 5 diagrams, which I don't really care about. I just wanna
> make some new ones.
Sounds like there is some funny data in dtproperties, which causes the
index violation. While it could be interesting to file a bug for it,
the quickest way to get it working is probably to simply drop
dtproperties:
DROP TABLE dtproperties
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
|||Erland, the table that is coming up with the error is sysdiagrams, not
dtproperties. At least, that's what the error message in the OP said.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9954F23EE870DYazorman@.127.0.0.1...
> MCG (MCG@.discussions.microsoft.com) writes:
> Sounds like there is some funny data in dtproperties, which causes the
> index violation. While it could be interesting to file a bug for it,
> the quickest way to get it working is probably to simply drop
> dtproperties:
> DROP TABLE dtproperties
>
> --
> 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

Error creating login - Incorrect syntax near 'LOGIN'.

Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
EshaWorks for me. The two most likely cause I can think of is there's a
non-printable character somewhere that didn't get copied to the post or you
have a Beta version of SQL Server 2005 (or SQL Server 2000) that had a
different CREATE LOGIN syntax.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Esha" <eshhyasi@.gmail.com> wrote in message
news:1151465435.377462.192620@.p79g2000cwp.googlegroups.com...
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
>
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
>
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>|||Esha,
CREATE LOGIN is a new T-SQL statement in SQL Server 2005.
Since you are using SQL Server 2000, you need to use sp_addlogin
or sp_grantlogin. Management Studio should use the version-appropriate
T-SQL depending on whether you are adding a login to a 2000 instance
or a 2005 instance. If this is not working for you, can you tell us what
steps you are taking in Management Studio in your attempt to create
the new login?
Steve Kass
Drew University
Esha wrote:

>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>|||Hi Steve,
Thanks for the quick response.
I just verified and you are right. My database engine is SQL server
2000 but my management studio is 2005. I used sp_addlogin and it
worked.
I created a new user for the new login from the management studio and I
assigned the roles of db_datareader and db_datawriter. If I need to
allow the user, read and write access only, but not "execute" access
for scripts, do I need to assign any other roles apart from the above
two?
Thanks a lot
Esha
Steve Kass wrote:
> Esha,
> CREATE LOGIN is a new T-SQL statement in SQL Server 2005.
> Since you are using SQL Server 2000, you need to use sp_addlogin
> or sp_grantlogin. Management Studio should use the version-appropriate
> T-SQL depending on whether you are adding a login to a 2000 instance
> or a 2005 instance. If this is not working for you, can you tell us what
> steps you are taking in Management Studio in your attempt to create
> the new login?
> Steve Kass
> Drew University
> Esha wrote:
>|||Thanks Roger,
I was using management studio 2005 to connect to SQL server 2000 and
hence it was not working. I used sp_addlogin as suggested by Steve and
it worked.
Thanks a lot,
Esha
Roger Wolter[MSFT] wrote:
> Works for me. The two most likely cause I can think of is there's a
> non-printable character somewhere that didn't get copied to the post or yo
u
> have a Beta version of SQL Server 2005 (or SQL Server 2000) that had a
> different CREATE LOGIN syntax.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Esha" <eshhyasi@.gmail.com> wrote in message
> news:1151465435.377462.192620@.p79g2000cwp.googlegroups.com...|||Esha,
Whether the user can "execute" a script (a T-SQL statement?) depends on
what the script does. A data reader can execute a SELECT statement,
and a data writer can execute DELETE, UPDATE, or INSERT statements.
You can grant or deny permissions on individual stored procedures, tables,
and other objects more specifically with GRANT and DENY. The security
models for 2000 and 2005 are somewhat different, and I suggest you refer to
Books Online for the appropriate version of your database instance for more
details.
SK
Esha wrote:

>Hi Steve,
>Thanks for the quick response.
>I just verified and you are right. My database engine is SQL server
>2000 but my management studio is 2005. I used sp_addlogin and it
>worked.
>I created a new user for the new login from the management studio and I
>assigned the roles of db_datareader and db_datawriter. If I need to
>allow the user, read and write access only, but not "execute" access
>for scripts, do I need to assign any other roles apart from the above
>two?
>Thanks a lot
>Esha
>Steve Kass wrote:
>
>
>

Error creating login - Incorrect syntax near 'LOGIN'.

Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
EshaThis is a multi-part message in MIME format.
--060001040307010407040902
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
to a SQL 2000 instance, regardless of which SQL client tool you use, you
need to use sp_addlogin or sp_grantlogin (depending on whether it's a
standard SQL login or a trusted login we're talking about). I think the
GUI in Management Studio ought to take care of this automatically (ie.
use different commands depending on the server version) but if you want
to use T-SQL yourself then you need to write the correct statements for
the SQL version the server is running.
--
*mike hodgson*
http://sqlnerd.blogspot.com
eshhyasi@.gmail.com wrote:
>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>
--060001040307010407040902
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
<br>
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
logins to a SQL 2000 instance, regardless of which SQL client tool you
use, you need to use sp_addlogin or sp_grantlogin (depending on whether
it's a standard SQL login or a trusted login we're talking about). I
think the GUI in Management Studio ought to take care of this
automatically (ie. use different commands depending on the server
version) but if you want to use T-SQL yourself then you need to write
the correct statements for the SQL version the server is running.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
<blockquote
cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
type="cite">
<pre wrap="">Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
Esha
</pre>
</blockquote>
</body>
</html>
--060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
> >Hi,
> >
> >I am using Sql server 2005 and when I try to create a new login from
> >the sql server mgmnt studio, I get this error.
> >
> >Msg 170, Level 15, State 1, Line 1
> >Line 1: Incorrect syntax near 'LOGIN'.
> >
> >I have logged in as "sa" and the login statement that I am using is:
> >
> >CREATE LOGIN test WITH PASSWORD = 'beta'
> >
> >The product version is 8.00.2039.
> >
> >Anybody knows whats wrong'
> >
> >Thanks for helping out.
> >
> >Esha
> >
> >
> >
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about). I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
> >Hi,
> >
> >I am using Sql server 2005 and when I try to create a new login from
> >the sql server mgmnt studio, I get this error.
> >
> >Msg 170, Level 15, State 1, Line 1
> >Line 1: Incorrect syntax near 'LOGIN'.
> >
> >I have logged in as "sa" and the login statement that I am using is:
> >
> >CREATE LOGIN test WITH PASSWORD = 'beta'
> >
> >The product version is 8.00.2039.
> >
> >Anybody knows whats wrong'
> >
> >Thanks for helping out.
> >
> >Esha
> >
> >
> >
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about). I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Even you are using Management Studio, you are connected to a SQL Server 2000
instance (according to your product version). You should connect to a SQL
Server 2005 instance to use create login.
Ben Nevarez, MCDBA, OCP
Database Administrator
"eshhyasi@.gmail.com" wrote:
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>

Error creating Database Diagrams

I'm using MS SQL Server Management Studio 2005 to manage both SQL 2000 and SQL 2005 server. When I connect to SQL 2000 Server database and try to do a Database Diagram, I got the following error:

SQL Server 2005 database diagrams and SQL Server 2000 database diagrams are created and rendered differently. Because of these differences, SQL Server Management Studio cannot work with SQL Server 2000 diagrams. Use SQL Server 2000 Enterprise Manager.

When a database containing diagrams is upgraded to SQL Server 2005, the diagrams will be upgraded the first time that the diagrams folder is opened.

You must be a member of the db_owner database role to upgrade database diagrams.

Any idea how to resolve this? I do not have admin rights to the SQL Server 2000.

Use SQL Server 2000 to administer the on the SQL Server 2000 Server stored diagrams.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Error Creating Data Source View in VS 2005 BI Tool for SQL Server 2005 View with > 32 Columns

I keep getting an error message in Visual Studio 2005 (SQL Server Business Intelligence Development Studio) = "Cannot have more than 32 columns" when I use the wizard to create a data source view using a view I created in a SQL Server 2005 database, which I use as a data source. The view does have more than 32 columns; however, I have been successful in creating data source views for other DB views with more than 32 columns. It seems that for some DB views with more than 32 columns the wizard ignores this problem, but for others - admittedly with a larger number of columns - it does not.

Any insights and potential workarounds? Thanks!

Please file a bug on http://connect.microsoft.com/SQLServer/Feedback and we'll look into this. If possible, please include a SQL DDL script that will create the source database which causes problems.

As a possible work around, you can try creating the DSV without this table and then adding the table in the DSV editor and see if that works.

Thanks

|||

Thanks. I will file a bug report, as you suggested. Regarding your suggestion, I forgot to mention that I tried that too and got the same error message. But it gave me another idea to try, which worked. I created the data source view using the wizard without selecting a table or a view from the data source, and then I used the New Named Query menu option to re-create the view (i.e., using the same SQL statement to create the view in the SQL Server 2005 DB). That did the trick! (BTW, there is another issue with the New Named Query pop-up window when you enter a SQL query and click on the OK button without entering a name for the query.)

FYI, below are the message and the program location details for the error related to the maximum number of columns in a data source view:

Message:

Cannot have more than 32 columns. (Microsoft Visual Studio)

Program Location:

at System.Data.DataKey..ctor(DataColumn[] columns, Boolean copyColumns)
at System.Data.UniqueConstraint.Create(String constraintName, DataColumn[] columns)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV()

Error Creating Data Source View in VS 2005 BI Tool for SQL Server 2005 View with > 32 Col

I keep getting an error message in Visual Studio 2005 (SQL Server Business Intelligence Development Studio) = "Cannot have more than 32 columns" when I use the wizard to create a data source view using a view I created in a SQL Server 2005 database, which I use as a data source. The view does have more than 32 columns; however, I have been successful in creating data source views for other DB views with more than 32 columns. It seems that for some DB views with more than 32 columns the wizard ignores this problem, but for others - admittedly with a larger number of columns - it does not.

Any insights and potential workarounds? Thanks!

Please file a bug on http://connect.microsoft.com/SQLServer/Feedback and we'll look into this. If possible, please include a SQL DDL script that will create the source database which causes problems.

As a possible work around, you can try creating the DSV without this table and then adding the table in the DSV editor and see if that works.

Thanks

|||

Thanks. I will file a bug report, as you suggested. Regarding your suggestion, I forgot to mention that I tried that too and got the same error message. But it gave me another idea to try, which worked. I created the data source view using the wizard without selecting a table or a view from the data source, and then I used the New Named Query menu option to re-create the view (i.e., using the same SQL statement to create the view in the SQL Server 2005 DB). That did the trick! (BTW, there is another issue with the New Named Query pop-up window when you enter a SQL query and click on the OK button without entering a name for the query.)

FYI, below are the message and the program location details for the error related to the maximum number of columns in a data source view:

Message:

Cannot have more than 32 columns. (Microsoft Visual Studio)

Program Location:

at System.Data.DataKey..ctor(DataColumn[] columns, Boolean copyColumns)
at System.Data.UniqueConstraint.Create(String constraintName, DataColumn[] columns)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV()