install SQL on WinXP: create

Showing posts with label create. Show all posts

Tuesday, March 27, 2012

Can not insert into table that have IDENTITY

Hi All,
I have 2 table. Table one is CREATE TABLE [coba] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[desc] [char] (10) ).
Table two is CREATE TABLE [coba2] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[desc] [char] (10) ).
In table coba i insert data such as :
insert into coba (desc) values ('a')
insert into coba (desc) values ('b')
insert into coba (desc) values ('c')
And if i try to show with select * from coba. It will show
1 a
2 b
3 c
The problem is that i want to transfer all of the record in table coba
into coba2. I used this query "insert into coba2 select * from coba".
It show error like this "An explicit value for the identity column in
table 'coba2' can only be specified when a column list is used and
IDENTITY_INSERT is ON."
So, does anyone know how to insert all records in table coba into
coba2?
Thank you very much for your help.I have tried this out and the following will work:
INSERT INTO coba2
SELECT [desc] from coba|||Name the columns for both the INSERT statement as well as the SELECT:
INSERT INTO tbl (col1, col2)
SELECT col1, col2
FROM ...
And consider whether you want to carry over the same idenity values in the t
arget table as you have
in the source table. If so, read about SET IDENTITY_INSERT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"afang" <khokimfang@.gmail.com> wrote in message
news:1146643380.200925.54580@.e56g2000cwe.googlegroups.com...
> Hi All,
> I have 2 table. Table one is CREATE TABLE [coba] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [desc] [char] (10) ).
> Table two is CREATE TABLE [coba2] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [desc] [char] (10) ).
> In table coba i insert data such as :
> insert into coba (desc) values ('a')
> insert into coba (desc) values ('b')
> insert into coba (desc) values ('c')
> And if i try to show with select * from coba. It will show
> 1 a
> 2 b
> 3 c
> The problem is that i want to transfer all of the record in table coba
> into coba2. I used this query "insert into coba2 select * from coba".
> It show error like this "An explicit value for the identity column in
> table 'coba2' can only be specified when a column list is used and
> IDENTITY_INSERT is ON."
> So, does anyone know how to insert all records in table coba into
> coba2?
> Thank you very much for your help.
>|||Thanks Tibor for your help. It works.
Rgds,
Afang

Can not insert into table that have IDENTITY

Hi All,
I have 2 table. Table one is CREATE TABLE [coba] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[desc] [char] (10) ).
Table two is CREATE TABLE [coba2] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[desc] [char] (10) ).
In table coba i insert data such as :
insert into coba (desc) values ('a')
insert into coba (desc) values ('b')
insert into coba (desc) values ('c')
And if i try to show with select * from coba. It will show
1 a
2 b
3 c
The problem is that i want to transfer all of the record in table coba
into coba2. I used this query "insert into coba2 select * from coba".
It show error like this "An explicit value for the identity column in
table 'coba2' can only be specified when a column list is used and
IDENTITY_INSERT is ON."
So, does anyone know how to insert all records in table coba into
coba2?
Thank you very much for your help.I have tried this out and the following will work:
INSERT INTO coba2
SELECT [desc] from coba|||Name the columns for both the INSERT statement as well as the SELECT:
INSERT INTO tbl (col1, col2)
SELECT col1, col2
FROM ...
And consider whether you want to carry over the same idenity values in the target table as you have
in the source table. If so, read about SET IDENTITY_INSERT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"afang" <khokimfang@.gmail.com> wrote in message
news:1146643380.200925.54580@.e56g2000cwe.googlegroups.com...
> Hi All,
> I have 2 table. Table one is CREATE TABLE [coba] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [desc] [char] (10) ).
> Table two is CREATE TABLE [coba2] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [desc] [char] (10) ).
> In table coba i insert data such as :
> insert into coba (desc) values ('a')
> insert into coba (desc) values ('b')
> insert into coba (desc) values ('c')
> And if i try to show with select * from coba. It will show
> 1 a
> 2 b
> 3 c
> The problem is that i want to transfer all of the record in table coba
> into coba2. I used this query "insert into coba2 select * from coba".
> It show error like this "An explicit value for the identity column in
> table 'coba2' can only be specified when a column list is used and
> IDENTITY_INSERT is ON."
> So, does anyone know how to insert all records in table coba into
> coba2?
> Thank you very much for your help.
>|||Thanks Tibor for your help. It works.
Rgds,
Afangsql

Can not get sqlcmd and unable to create peer-to-peer replication.

Hi, Pros,

I am new to SQL server 2005. We have installed SQL server 2005 cluster. I tried to setup peer-to-peer replication with another standard server. Unfortunately, when I creating publication, I got error message : SQL server cound not configure "MyServer" as a Distributor [New publication Wizard] Additional information: An exception occurred whild executing a Transact_SQL statement or batch.

The Server "Myserveris already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the exisitingDistributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard. Changed database context to 'master'. [Microsoft SQL Server, Error: 14099]

I tried to use sqlcmd and I got another error message:

C:\>sqlcmd

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi

shing a connection to the server. When connecting to SQL Server 2005, this failu

re may be caused by the fact that under the default settings SQL Server does not

allow remote connections..

Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Does anyone have any idea how to get the sqlcmd work?

Thank you in advance.

Hi Vicent,

You should use "sqlcmd -S %VirtualInstanceName%" (replace %VirtualInstanceName% with the virtual server name of your cluster.

For the issue of setting up distributor, have you follow the instruction in your error message?

Peng

|||

Hi, Peng,

Thanks for your kind reply. I am so glad your command works. I got another error when I tried to drop distributor from my cluster because when I setup replication the message shows the cluster has been setup as distributor.

I use sqlcmd to drop distributor and get the error message below. Do you have any idea?

C:\>sqlcmd -S myvirtualname
1> sp_dropdistributor 'myvirtualname'
2> GO
HResult 0x1FB2, Level 16, State 5
Error converting data type varchar to bit.
1>

Thanks for your help.

|||

Hi Vincent,

The first parameter of sp_dropdistributor is not server name. Please see this page (http://msdn2.microsoft.com/en-us/library/ms173516.aspx) for the document. For example, you should say "EXEC sp_dropdistributor" or "EXEC sp_dropdistributor 1".

Peng

|||

Hi, Peng,

I tried the command EXEC, it does not work either.

C:\Documents and Settings\HCLLCLUS>sqlcmd -S myvirtualname
1> EXEC sp_dropdistributor 'myvirtualname'
2> GO
HResult 0x1FB2, Level 16, State 5
Error converting data type varchar to bit.
1>

Do you know how to fix? Do I need to log on machine instead of virtual name? I wonder how did this distributor setup. Is there any way I can check this?

Thanks,

|||

I need to mention one more thing. I did follow the error message and tried to find Disable Publication and Distributor Wizard. It was not there. It seems the only way left for me is the command line.

Thanks.

|||

It is really my bad. I use the command to drop off distributor.

1> EXEC sp_dropdistributor
2> GO
Msg 21043, Level 16, State 1, Server virtualserver, Procedure sp_dropdistributor,
Line 50
The Distributor is not installed.
1>

It is looks like the ditributor did not installed on the server. However if I tried to configure replication via wizard, the error message shows the distributor was installed.

SQL Server could not configure ‘virtualname’ as a Distributor.

Additional information.

An exception occurred while executing a Transact-SQL statement or batch.

[Microsoft.SqlServer.ConnectionInfo]

The server ‘Virtualname’ is already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the existing Distributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard. Changed database context to ‘master’. [Microsoft SQL Server Error: 14099]

|||

Could you run the following command against your cluster instance and report the result back?

SELECT srvname FROM master.dbo.sysservers WHERE srvstatus & 8 <> 0

If you get any row back, it means that your server is already defined as distributor.

Peng

|||

Hi, Peng,

I am glad to tell you. I have resolved this problem. Actually, there was a security problem when I set up replication. I went to Local Security Settings >User Rights Assignment >Lock pages in memory, I put adminstrator user in it and I was able to setup replication. Thanks for your instruction.

I have an another problem. I did not realize that it need SQL Server 2005 Enterprise edition to setup P2P replication. I had bought upraged license. However, I do not know how to upgrade SQL server 2005 standard to Enterprise edition. Does anyone know how? Thank you.

|||Run setup from the command prompt. Here are instructions on how to do this.

http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade

Can not get sqlcmd and unable to create peer-to-peer replication.

Hi, Pros,

I tried to use sqlcmd and I got another error message:

C:\>sqlcmd

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi

shing a connection to the server. When connecting to SQL Server 2005, this failu

re may be caused by the fact that under the default settings SQL Server does not

allow remote connections..

Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Does anyone have any idea how to get the sqlcmd work?

Thank you in advance.

Hi Vicent,

You should use "sqlcmd -S %VirtualInstanceName%" (replace %VirtualInstanceName% with the virtual server name of your cluster.

For the issue of setting up distributor, have you follow the instruction in your error message?

Peng

|||

Hi, Peng,

I use sqlcmd to drop distributor and get the error message below. Do you have any idea?

C:\>sqlcmd -S myvirtualname
1> sp_dropdistributor 'myvirtualname'
2> GO
HResult 0x1FB2, Level 16, State 5
Error converting data type varchar to bit.
1>

Thanks for your help.

|||

Hi Vincent,

Peng

|||

Hi, Peng,

I tried the command EXEC, it does not work either.

C:\Documents and Settings\HCLLCLUS>sqlcmd -S myvirtualname
1> EXEC sp_dropdistributor 'myvirtualname'
2> GO
HResult 0x1FB2, Level 16, State 5
Error converting data type varchar to bit.
1>

Do you know how to fix? Do I need to log on machine instead of virtual name? I wonder how did this distributor setup. Is there any way I can check this?

Thanks,

|||

I need to mention one more thing. I did follow the error message and tried to find Disable Publication and Distributor Wizard. It was not there. It seems the only way left for me is the command line.

Thanks.

|||

It is really my bad. I use the command to drop off distributor.

1> EXEC sp_dropdistributor
2> GO
Msg 21043, Level 16, State 1, Server virtualserver, Procedure sp_dropdistributor,
Line 50
The Distributor is not installed.
1>

It is looks like the ditributor did not installed on the server. However if I tried to configure replication via wizard, the error message shows the distributor was installed.

SQL Server could not configure ‘virtualname’ as a Distributor.

Additional information.

An exception occurred while executing a Transact-SQL statement or batch.

[Microsoft.SqlServer.ConnectionInfo]

|||

Could you run the following command against your cluster instance and report the result back?

SELECT srvname FROM master.dbo.sysservers WHERE srvstatus & 8 <> 0

If you get any row back, it means that your server is already defined as distributor.

Peng

|||

Hi, Peng,

|||Run setup from the command prompt. Here are instructions on how to do this.

http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade

Can not get sqlcmd and unable to create peer-to-peer replication.

Hi, Pros,

I tried to use sqlcmd and I got another error message:

C:\>sqlcmd

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi

shing a connection to the server. When connecting to SQL Server 2005, this failu

re may be caused by the fact that under the default settings SQL Server does not

allow remote connections..

Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Does anyone have any idea how to get the sqlcmd work?

Thank you in advance.

Hi Vicent,

You should use "sqlcmd -S %VirtualInstanceName%" (replace %VirtualInstanceName% with the virtual server name of your cluster.

For the issue of setting up distributor, have you follow the instruction in your error message?

Peng

|||

Hi, Peng,

I use sqlcmd to drop distributor and get the error message below. Do you have any idea?

C:\>sqlcmd -S myvirtualname
1> sp_dropdistributor 'myvirtualname'
2> GO
HResult 0x1FB2, Level 16, State 5
Error converting data type varchar to bit.
1>

Thanks for your help.

|||

Hi Vincent,

Peng

|||

Hi, Peng,

I tried the command EXEC, it does not work either.

C:\Documents and Settings\HCLLCLUS>sqlcmd -S myvirtualname
1> EXEC sp_dropdistributor 'myvirtualname'
2> GO
HResult 0x1FB2, Level 16, State 5
Error converting data type varchar to bit.
1>

Do you know how to fix? Do I need to log on machine instead of virtual name? I wonder how did this distributor setup. Is there any way I can check this?

Thanks,

|||

I need to mention one more thing. I did follow the error message and tried to find Disable Publication and Distributor Wizard. It was not there. It seems the only way left for me is the command line.

Thanks.

|||

It is really my bad. I use the command to drop off distributor.

1> EXEC sp_dropdistributor
2> GO
Msg 21043, Level 16, State 1, Server virtualserver, Procedure sp_dropdistributor,
Line 50
The Distributor is not installed.
1>

It is looks like the ditributor did not installed on the server. However if I tried to configure replication via wizard, the error message shows the distributor was installed.

SQL Server could not configure ‘virtualname’ as a Distributor.

Additional information.

An exception occurred while executing a Transact-SQL statement or batch.

[Microsoft.SqlServer.ConnectionInfo]

|||

Could you run the following command against your cluster instance and report the result back?

SELECT srvname FROM master.dbo.sysservers WHERE srvstatus & 8 <> 0

If you get any row back, it means that your server is already defined as distributor.

Peng

|||

Hi, Peng,

|||Run setup from the command prompt. Here are instructions on how to do this.

http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgradesql

Can not get sqlcmd and unable to create peer-to-peer replication.

Hi, Pros,

I tried to use sqlcmd and I got another error message:

C:\>sqlcmd

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi

shing a connection to the server. When connecting to SQL Server 2005, this failu

re may be caused by the fact that under the default settings SQL Server does not

allow remote connections..

Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Does anyone have any idea how to get the sqlcmd work?

Thank you in advance.

Hi Vicent,

You should use "sqlcmd -S %VirtualInstanceName%" (replace %VirtualInstanceName% with the virtual server name of your cluster.

For the issue of setting up distributor, have you follow the instruction in your error message?

Peng

|||

Hi, Peng,

I use sqlcmd to drop distributor and get the error message below. Do you have any idea?

C:\>sqlcmd -S myvirtualname
1> sp_dropdistributor 'myvirtualname'
2> GO
HResult 0x1FB2, Level 16, State 5
Error converting data type varchar to bit.
1>

Thanks for your help.

|||

Hi Vincent,

Peng

|||

Hi, Peng,

I tried the command EXEC, it does not work either.

C:\Documents and Settings\HCLLCLUS>sqlcmd -S myvirtualname
1> EXEC sp_dropdistributor 'myvirtualname'
2> GO
HResult 0x1FB2, Level 16, State 5
Error converting data type varchar to bit.
1>

Do you know how to fix? Do I need to log on machine instead of virtual name? I wonder how did this distributor setup. Is there any way I can check this?

Thanks,

|||

I need to mention one more thing. I did follow the error message and tried to find Disable Publication and Distributor Wizard. It was not there. It seems the only way left for me is the command line.

Thanks.

|||

It is really my bad. I use the command to drop off distributor.

1> EXEC sp_dropdistributor
2> GO
Msg 21043, Level 16, State 1, Server virtualserver, Procedure sp_dropdistributor,
Line 50
The Distributor is not installed.
1>

It is looks like the ditributor did not installed on the server. However if I tried to configure replication via wizard, the error message shows the distributor was installed.

SQL Server could not configure ‘virtualname’ as a Distributor.

Additional information.

An exception occurred while executing a Transact-SQL statement or batch.

[Microsoft.SqlServer.ConnectionInfo]

|||

Could you run the following command against your cluster instance and report the result back?

SELECT srvname FROM master.dbo.sysservers WHERE srvstatus & 8 <> 0

If you get any row back, it means that your server is already defined as distributor.

Peng

|||

Hi, Peng,

|||Run setup from the command prompt. Here are instructions on how to do this.

http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade

Can not find installable ISAM

Installed Office 2007 and I am trying to develop an import by developing a Integration Project. If I use SQL2005 to import the data to create the table or I try to develop the Integration Project I keep getting a Can not find installable ISAM. I have tried to update the MDAC but either the SP is not supported in XP SP2 or it would do anything since there is a later version already installed. This is the same when I try and install / update the Jet database engine.(4.0). Does anyone have a clue, the only thing I can find info on is Access and other db's. This appears to be a product issue but which one I don't know.i have been battling this same issue only i'm trying to import an excel spreadsheet in ssis. if you find anything, please do post here. i'll do the same.|||Have you tried using the ACE provider instead of Jet? For more info, look up the Office 2007 Connectivity article in the Connectivity post in the beginning of this forum.|||If you are trying to import from Excel, there is a source adapter called Data Defractor. It's far more adaptive that using Jet. If you Excel is basically a CSV, then Jet will suffice but anything more complex....

It does a great job of importing from Excel. See www.dataDefractor.com for more info.

Can not deploy calculation...

Hi,

I had a calculated measure which simply tries to create a Margin Profit. This is the simply Measure divided by another measure. The problem is that when I try to deploy this measure with a Parent Hierarchy of measures it will not deploy I get the error like this:

Errors and Warnings from Response
MdxScript(SSAS) (7, 8) The 'Measures' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.

Rgds.,

Hello. Can you explain what you mean by "Parent hierarchy of measures"?

For this calculation ([Margin Profit] = [Measures].[Sales]/[Measures].[Cost]) should be sufficient.

Regards

Thomas Ivarsson

|||

I've seen the error you specified occur when the cube collation sequence has "case sensitivity" set, and the name of the measures dimension in the MDX expression didn't match the case of measures as specified in the cube. As an example:

measures.[A Measure Name] = resulted in the error

Measures.[A Measure Name] = processed correctly.

Note the capital "M" in the second measures specification. Don't know ifthis is your problem, but it is one time when I have seen the error you reported.

PGoldy

|||

Hi PGoldy,

Thanks for your information.

I had to try to change the [MEASURES] to [Measures], it's work I can deploy it.

thanks very much for your help.

Cherming

Sunday, March 25, 2012

Can not created Named Calculation...

I can not seem to create a Named Calculation on a table that I have already created a Named Query on in my data source view. Any idea why this is and if there is a work around for it?

TIA

Wellman

If you have already a Named Query you can add an expression-based column to the SELECT statement using the syntax of the underlying data source.|||

Thank you for this information. Any idea why this is so? Doesn't make much sense to me.

Also, can I reference other tables as well?

TIA

Wellman

|||Which part doesn't make sense? You can view a named query as a SQL view. Just like a SQL view, your SELECT statement can include any valid syntax, including other tables, calling UDF, etc.

Can not create views, tables etc * URGENT*

Hi,

I am not getting option as 'new view' and 'new table' when I right
click on
views and tables option in VisualStudio.net IDE server explorer
sqlservers database to create new objects.

Looks like some setup issue in my database.

Thanks for your help in advance.

Rgds
CVCV (chaitu_m7@.yahoo.co.in) writes:
> I am not getting option as 'new view' and 'new table' when I right
> click on
> views and tables option in VisualStudio.net IDE server explorer
> sqlservers database to create new objects.
> Looks like some setup issue in my database.

Does this happen in every database, including tempdb? How do you connect
to the server? I don't use VS .Net to access SQL Server normally, so I
don't have much idea what it could be, but one possibility is that the
you don't have rights to create new objects in the database in question.

Personally, I prefer to use Query Analyzer to create tables, using
the CREATE TABLE statement, and this is also what I recommend in general.
In the long run, you are more effective with this than using a graphic
tool. Also, this is the only option if you want to put you tables under
source control - which you should.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Can not create unsafe assembly

I try to create assembly with UNSAFE permissions.
I granted "unsafe assembly" to my login, set TRUSTWORTHY property ON.
Now I have this error:

Could not obtain information about Windows NT group/user <MyDomain>/<MyName>, error code 0x5. (Microsoft SQL Server, Error: 15404).

How to resolve this?

Looks like your server isn't able to reach the D.C. to get information on your particular user/group...have you verified connectivity to the domain controller?

|||On a side note, it isn't recommended setting the TRUSTWORTHY property to ON if you only need to create an unsafe assembly. You can also create an asymmetric key, map a login to that key, and grant UNSAFE permission to the login to create an unsafe assembly. See http://msdn2.microsoft.com/ms345106(en-US,SQL.90).aspx for more info.|||Chad, how to verify connectivity to D.C.?|||I've gotten that error before. I think the owner of your database doesn't have permission to create assemblies. Try running sp_ChangeDBOwner 'sa' to change the owner of your database to sa.|||

Good Morning,
Is this the same Chad Boyd from Mars Hill who used to work for ATX in Caribou, ME?

ChrisRogeski@.gmail.com

Can not create objects in master

Hi,
I'm trying to create a sp in the database master, but I
cann't, I got an error message
Server: Msg 2714, Level 16, State 5, Procedure Sp_Backup,
Line 41
There is already an object named 'Sp_Backup' in the
database.
but I login as sa user and I got the same error.
could somebody help me, please? I think is a configuration
parameter.
Thanks in advance,
Javier RosasHello Javier !
The error say itself that there is alwasy a SP named SP_BACKUP in there.
Just try this to find whos owning the sp:
USE MASTER
SELECT SO.name, SU.Name
FROM sysobjects SO INNER JOIN
sysusers SU ON SO.UID = SU.UID
WHERE SO.name = 'SP_BACKUP'
THEN CHECK you is logged on:
SELECT USER_NAME()
If they are both identical (Owner and LoggedON Persons), you have to drop
the SP first
or create it for antoher user
CREATE PROCEDURE OWNER.SP_NAME
HTH, Jens Süßmeyer.|||No, the problem is that the object doesn't exist!!!
I can change the name, but the problem is the same, in fact, if I were creating a table (or another object) I got the same mistake!!
Thanks.
>--Original Message--
>Hello Javier !
>The error say itself that there is alwasy a SP named SP_BACKUP in there.
>Just try this to find whos owning the sp:
>USE MASTER
>SELECT SO.name, SU.Name
>FROM sysobjects SO INNER JOIN
> sysusers SU ON SO.UID =3D SU.UID
>WHERE SO.name =3D 'SP_BACKUP'
>THEN CHECK you is logged on:
>SELECT USER_NAME()
>If they are both identical (Owner and LoggedON Persons), you have to drop
>the SP first
>or create it for antoher user
>CREATE PROCEDURE OWNER.SP_NAME
>HTH, Jens S=FC=DFmeyer.
>
>.
>|||It seems there is an issue with your stored procedure create script.
Note that the error message is referring to line 41. However, without
the script source, it's difficult to determine the exact cause of the
error.
Also, user stored procedures should not be prefixed with ''sp_' and it's
not a good practice to create user objects in the master database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Javier Rosas" <jrosashe@.accival.com.mx> wrote in message
news:003701c36fe8$764bf0d0$a501280a@.phx.gbl...
> Hi,
> I'm trying to create a sp in the database master, but I
> cann't, I got an error message
> Server: Msg 2714, Level 16, State 5, Procedure Sp_Backup,
> Line 41
> There is already an object named 'Sp_Backup' in the
> database.
> but I login as sa user and I got the same error.
> could somebody help me, please? I think is a configuration
> parameter.
> Thanks in advance,
> Javier Rosas

Can not create New Subscription

Hi, I have a report that I would like to send out to users automatically every day at 10AM. For some reason on reporting server under the report I would to email, I see 3 folders...Data Source, History & Subscription but I am not able to click on "New Subscription" option in order to create new subscription.

First I created "Shared Schedule" specifing day, time etc information. And then I am tring to create Subscription but the option is disabled.

Is there any service pack I am missing or I am following the wrong procedure.

Please advice.

- Payal

Hi,

Can make sure that you have provided an account to run the report and not use the Integrated Security? To be able to use Subscriptions, Reporting Services requires you to provide a fixed account.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Can not create new replication after changing server name

After i changed my computer's name(eg. the original computer name is 'SERVER-1',I has changed it to 'SERVER-2'),I can not create a new replication in SQL Server 2005.The error message :

New Publication Wizard
SQL Server is unable to connect to server 'SERVER-2'.

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVER-1'. (Replication.Utilities)

What's wrong? Tongue Tied
Should I reinstall SQL Server 2005?

Hi, microzt,

It's possible to rename machine name with SQL Server 2005 installed and have SQL Server 2005 works correctly with the new machine name, however, there are some extra steps need to be performed to accomplish this besides simply renamed the machine name in OS and it only works when you don't have replication enabled before the server renaming (i.e. no distributor is setup, no publications etc).

You need to update sys.servers system catalog view with the new machine name after you renamed the OS machine name:

This can also be done if you use dedicated Admin connection (sqlcmd -A) to update sys.servers (sqlserver need to be started in single user mode for the update to work, otherwise, you will hit error that system catalog view can not be updated manually)

C:\>sqlcmd -A
1> update sys.servers set name='SERVER-2'
2> go

(1 rows affected)
Warning: System table ID 43 has been updated directly in database ID 1 and cache coherence may not h
ave been maintained. SQL Server should be restarted.
1> exit

After you successfully updated sys.servers (or sysservers), replication can be enabled correctly on the renamed SQL Server instance.

Hope That helps.

Zhiqiang

|||

The other way of change server name in sys.servers catalog view is to use sp_dropserver and sp_addserver, for example:

sp_dropserver @.server='SERVER-1'
sp_addserver @.server='SERVER-2', @.local='LOCAL'

Then restart SQL Server service and replication should work fine.

|||Thank you for your help!
The replication work fine now. Big Smile

Can not create my Publisher

Hi,
I install Sql server 2000 on a server named JMI_SQL
After Installation we decide to rename the server JMI_SQL for SQL2007
I create a Distributor on the server SQLDIST2007
Once my distibutor created i try to create my Publisher on SQL2007 and I
receive this message
Error 18482 Could not connec to server SqlDIST2007 because JMI_SQL is not
defined as a remote Server
In the Wizard when I was creating my Publisher I select SQl2007 as Publisher
and the wizard is looking for JMI_SQL which the old name of the server
I try those commands in sql query analyser to get some information
select @.@.servername Show JMI_SQL
SELECT srvname, srvid
FROM sysservers
Show this
ADAMM_DB1
BESERVER_DB2
CATALOG_DB3
JMI_SQL0
repl_distributor 4
In the tables master.sysservers I try to Change manually all the JMI_SQL for
SQL2007 Sql server dont let me change it
Is there a way that I can change the JMI_SQL for SQL2007 in the sysservers
table
I would like to avoid to uninstall and reinstall Sql Server 2000
Before trying to do something I would like to have an advise from
experimented person on how to do that.
Thanks in advance and have a nice day!
Hi Gav,
Thanks a lot for the information
I appreciate very much !
Have a nice day
"Gav" wrote:

> Using Query Analyzer, execute the following:
> Use master
> go
> sp_dropserver 'JMI_SQL', droplogins
> go
> sp_addserver 'SQL2007', LOCAL
> go
> After you have done this make sure you restart the SQL service or restart
> your computer (whichever you fancy!) and that should do it
> Regards
> Gav
> "GC" <GC@.discussions.microsoft.com> wrote in message
> news:65162A3C-16DB-43C1-884D-601056BEFAF8@.microsoft.com...
>
>
sql

Can not create Maintenance Plan - getting error message

I just setup two new SQL 2005 Stnd x64bit servers for a customer. The
original two servers running the same SQL version have been running
fine for 3 weeks now. The mian production database is attached and SQL
seems to be working fine. When I try to create a new Maintenance Plan
or use the Maint Plan wizard, I get an error message.
The error message:
TITLE: Microsoft SQL Server Management Studio
--
The action you attempted to perform on a remote instance of SQL Server
has failed because the action requires a SQL Server component that is
not installed on the remote computer. To proceed, install SQL Server
2005 Management Tools on the remote computer, and then try again. For
more information, see "How to: Install SQL Server 2005 (Setup)" in SQL
Server 2005 Books Online, or find the article on MSDN at
http://go.microsoft.com/fwlink/?LinkID=57083 . (ObjectExplorer)
For help, click: http://go.microsoft.com/fwlink/?LinkID=57083
----
I did run SCW on the new servers just like I did on the two original
servers.
Any thoughts? ThanksHi,
I get the exact same error - have you found a solution for your problem
yet?
My setup is a 2 node cluster with SQL 2005 SP1
Regards
Jon skrev:
> I just setup two new SQL 2005 Stnd x64bit servers for a customer. The
> original two servers running the same SQL version have been running
> fine for 3 weeks now. The mian production database is attached and SQL
> seems to be working fine. When I try to create a new Maintenance Plan
> or use the Maint Plan wizard, I get an error message.
> The error message:
> TITLE: Microsoft SQL Server Management Studio
> --
> The action you attempted to perform on a remote instance of SQL Server
> has failed because the action requires a SQL Server component that is
> not installed on the remote computer. To proceed, install SQL Server
> 2005 Management Tools on the remote computer, and then try again. For
> more information, see "How to: Install SQL Server 2005 (Setup)" in SQL
> Server 2005 Books Online, or find the article on MSDN at
> http://go.microsoft.com/fwlink/?LinkID=57083 . (ObjectExplorer)
> For help, click: http://go.microsoft.com/fwlink/?LinkID=57083
> ----
> I did run SCW on the new servers just like I did on the two original
> servers.
> Any thoughts? Thanks

Can not create Maintenance Plan - getting error message

I just setup two new SQL 2005 Stnd x64bit servers for a customer. The
original two servers running the same SQL version have been running
fine for 3 weeks now. The mian production database is attached and SQL
seems to be working fine. When I try to create a new Maintenance Plan
or use the Maint Plan wizard, I get an error message.
The error message:
TITLE: Microsoft SQL Server Management Studio
--
The action you attempted to perform on a remote instance of SQL Server
has failed because the action requires a SQL Server component that is
not installed on the remote computer. To proceed, install SQL Server
2005 Management Tools on the remote computer, and then try again. For
more information, see "How to: Install SQL Server 2005 (Setup)" in SQL
Server 2005 Books Online, or find the article on MSDN at
http://go.microsoft.com/fwlink/?LinkID=57083 . (ObjectExplorer)
For help, click: http://go.microsoft.com/fwlink/?LinkID=57083
----
--
I did run SCW on the new servers just like I did on the two original
servers.
Any thoughts? ThanksHi,
I get the exact same error - have you found a solution for your problem
yet?
My setup is a 2 node cluster with SQL 2005 SP1
Regards
Jon skrev:

> I just setup two new SQL 2005 Stnd x64bit servers for a customer. The
> original two servers running the same SQL version have been running
> fine for 3 weeks now. The mian production database is attached and SQL
> seems to be working fine. When I try to create a new Maintenance Plan
> or use the Maint Plan wizard, I get an error message.
> The error message:
> TITLE: Microsoft SQL Server Management Studio
> --
> The action you attempted to perform on a remote instance of SQL Server
> has failed because the action requires a SQL Server component that is
> not installed on the remote computer. To proceed, install SQL Server
> 2005 Management Tools on the remote computer, and then try again. For
> more information, see "How to: Install SQL Server 2005 (Setup)" in SQL
> Server 2005 Books Online, or find the article on MSDN at
> http://go.microsoft.com/fwlink/?LinkID=57083 . (ObjectExplorer)
> For help, click: http://go.microsoft.com/fwlink/?LinkID=57083
> ----
--
> I did run SCW on the new servers just like I did on the two original
> servers.
> Any thoughts? Thanks

Can not create maintenance plan

I have had two SQL 2005 Stnd x64bit servers running without any problems for several weeks now. I just setup two new ones last night. One the two new ones, I get an error message when I try to create a Maintenance Plan. I also get an error when I try to create a plan using the Wizard.

The error message is:
TITLE: Microsoft SQL Server Management Studio

The action you attempted to perform on a remote instance of SQL Server has failed
because the action requires a SQL Server component that is not installed on the
remote computer. To proceed, install SQL Server 2005 Management Tools on the remote
computer, and then try again. For more information, see "How to: Install SQL Server
2005 (Setup)" in SQL Server 2005 Books Online, or find the article on MSDN at
http://go.microsoft.com/fwlink/?LinkID=57083 . (ObjectExplorer)

For help, click: http://go.microsoft.com/fwlink/?LinkID=57083

Note: SQL Agent is running. So far, everything else seems to be working fine.

Any ideas?

Thanks in advance.

jtwDitto -- same problem, same server type. I observed that it was accessible prior to SP1 but not afterwards. Post-SP1 hotfixes did not solve problem either.|||

Can you make sure you have installed Integrated Service as it should be installed in order to create maintenance plans from the SQL Manager.

Thanks

|||

Hi,

I have the same issue - post sp1 on 2005 64bit. I was under the impression that sp1 removed the need for SSIS when creating maintenance plans.

I just go round in circles trying to reinstall the management tools - but it wont let me as the install cd is now an old version (RTM) and you cant slipstream SP1... do i have to install SSIS?

thanks

Luke

|||

Yes you do need to have SSIS installed to get all the right components to Maintance plans working. This is one of the fixes targeted for SP2 but for right now you need to install SSIS.

Michelle

Can not create maintenance plan

The error message is:
TITLE: Microsoft SQL Server Management Studio

For help, click: http://go.microsoft.com/fwlink/?LinkID=57083

Note: SQL Agent is running. So far, everything else seems to be working fine.

Any ideas?

Thanks in advance.

jtwDitto -- same problem, same server type. I observed that it was accessible prior to SP1 but not afterwards. Post-SP1 hotfixes did not solve problem either.|||

Can you make sure you have installed Integrated Service as it should be installed in order to create maintenance plans from the SQL Manager.

Thanks

|||

Hi,

I have the same issue - post sp1 on 2005 64bit. I was under the impression that sp1 removed the need for SSIS when creating maintenance plans.

I just go round in circles trying to reinstall the management tools - but it wont let me as the install cd is now an old version (RTM) and you cant slipstream SP1... do i have to install SSIS?

thanks

Luke

|||

Yes you do need to have SSIS installed to get all the right components to Maintance plans working. This is one of the fixes targeted for SP2 but for right now you need to install SSIS.

Michelle

Can not create maintenance plan

The error message is:
TITLE: Microsoft SQL Server Management Studio

For help, click: http://go.microsoft.com/fwlink/?LinkID=57083

Note: SQL Agent is running. So far, everything else seems to be working fine.

Any ideas?

Thanks in advance.

jtwDitto -- same problem, same server type. I observed that it was accessible prior to SP1 but not afterwards. Post-SP1 hotfixes did not solve problem either.|||

Can you make sure you have installed Integrated Service as it should be installed in order to create maintenance plans from the SQL Manager.

Thanks

|||

Hi,

I have the same issue - post sp1 on 2005 64bit. I was under the impression that sp1 removed the need for SSIS when creating maintenance plans.

I just go round in circles trying to reinstall the management tools - but it wont let me as the install cd is now an old version (RTM) and you cant slipstream SP1... do i have to install SSIS?

thanks

Luke

|||

Yes you do need to have SSIS installed to get all the right components to Maintance plans working. This is one of the fixes targeted for SP2 but for right now you need to install SSIS.

Michelle

sql

Tuesday, March 27, 2012

Can not insert into table that have IDENTITY

Can not insert into table that have IDENTITY

Can not get sqlcmd and unable to create peer-to-peer replication.

Can not get sqlcmd and unable to create peer-to-peer replication.

Can not get sqlcmd and unable to create peer-to-peer replication.

Can not get sqlcmd and unable to create peer-to-peer replication.

Can not find installable ISAM

Can not deploy calculation...

Sunday, March 25, 2012

Can not created Named Calculation...

Can not create views, tables etc * URGENT*

Can not create unsafe assembly

Can not create objects in master

Can not create New Subscription

Can not create new replication after changing server name

Can not create my Publisher

Can not create Maintenance Plan - getting error message

Can not create Maintenance Plan - getting error message

Can not create maintenance plan

Can not create maintenance plan

Can not create maintenance plan

install SQL on WinXP

Blog Archive

About Me