Dear All.
As we all know withing SQL Server there is a facility to
automatically create the script to create databases, this
is something that we have been using successfully for the
past 3 years.
Lately however we have experienced a problem. On some
views it gets the name wrong, so instead of
vwPostCodeDetails, the automatic generater puts
vwPostCode.
Can anyone out there offer some guidence on what we need
to do ?
Thanks
PeterI think this is the problem if you rename the view / or stored procedure using EM or sp_rename.
When you do this underlying text in syscomments does not changes. What you have to do is "generate
the script only for these views" (which will consists of old name) update the script with the new
name that needs to be. drop these problematic view and recreate them with the new name and then
generate the new script.
--
- Vishal
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:043901c380ee$970dc660$a001280a@.phx.gbl...
> Dear All.
> As we all know withing SQL Server there is a facility to
> automatically create the script to create databases, this
> is something that we have been using successfully for the
> past 3 years.
> Lately however we have experienced a problem. On some
> views it gets the name wrong, so instead of
> vwPostCodeDetails, the automatic generater puts
> vwPostCode.
> Can anyone out there offer some guidence on what we need
> to do ?
> Thanks
> Peter
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Problem with Alter table disable trigger all
Hi,
I am replicating few production databases and I am running into a problem with "Alter table tblname disable trigger all". I get the following error: Cannot alter the table 'tblname' because it is being published for replication. I am aware that when you want to add or drop a column, you need to use some system stored procedures to do so. But how can I disable the triggers before running some queries/statement on a table that is being published to a subscriber?
I really apreciate you help,
Regards,
SasIt seems you have to use sp_dropsubscription and then sp_droparticle to discontinue replication of the table, do you alter, then use sp_addarticle and sp_addsubscription to add continue replication. If there is an easier way, I'd be interested to know as well.
http://www.databasejournal.com/features/mssql/article.php/1477441|||UPDATE sysobjects SET replinfo=0 WHERE xtype='U' AND name='YOUR TABLE'
It would disable the table in the publication.
When u r done with ur work, Add the table back into Publication through Enterprise-Manager.
However sometimes it goes messed-up and SQL doesn't allow to add the table back into publication. In that case u may need to reinialize the subscriptions followed by the SNAPSHOT.
Regards!!
I am replicating few production databases and I am running into a problem with "Alter table tblname disable trigger all". I get the following error: Cannot alter the table 'tblname' because it is being published for replication. I am aware that when you want to add or drop a column, you need to use some system stored procedures to do so. But how can I disable the triggers before running some queries/statement on a table that is being published to a subscriber?
I really apreciate you help,
Regards,
SasIt seems you have to use sp_dropsubscription and then sp_droparticle to discontinue replication of the table, do you alter, then use sp_addarticle and sp_addsubscription to add continue replication. If there is an easier way, I'd be interested to know as well.
http://www.databasejournal.com/features/mssql/article.php/1477441|||UPDATE sysobjects SET replinfo=0 WHERE xtype='U' AND name='YOUR TABLE'
It would disable the table in the publication.
When u r done with ur work, Add the table back into Publication through Enterprise-Manager.
However sometimes it goes messed-up and SQL doesn't allow to add the table back into publication. In that case u may need to reinialize the subscriptions followed by the SNAPSHOT.
Regards!!
Tuesday, March 20, 2012
Problem with "SQL Server does not exist" error
I have a couple of databases developed and being used in MSDE. The
db's are used and maintained from a couple of programs written in
Visual Basic 6 (using an ADO connection) (these programs run on a
separate computer).
I've just set up a new computer and installed SQL Server Express 2005
on it. I copied the .MDF files from the MSDE unit and attached them to
SQLExpress.
Now, when I try to connect to the databases, I get the "SQL Server
does not exist or access is denied" error. I have, of course, modified
the connection string such that the Data Source property is using the
correct computer name.
I've looked at everything I can think of but obviously I'm missing
something fundamental here. Any suggestions as to what I might be
doing wrong?
Thanks.
On Mon, 20 Nov 2006 16:22:39 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:
>hi Martin,
>Martin wrote:
>please verify the instance name is correct (in the form
>ComputerName\InstanceName), the remote computer can access the lan and the
>remote SQLExpress installed computer, firewall (both) have been set with the
>appropriate exception(s) allowing connectivity and trafic (both the DBEngine
>used port and the SQLBrowser, if used), the SQLExpress network protocols
>have been enabled, the Surface Area Configuration ahs been set to allow
>remote connections...
Thanks, Andrea. I've gone thru your list but still no luck.
* the name of the new computer is "CATServer" (it's not actually a
"server" - it's running WinXP Pro with all current service packs and
updates). I've tried setting the Data Source to both "CATSERVER" and
"CATSERVER\SQLEXPRESS". Neither way seemed to make any difference.
* the remote computer CAN access CATServer. I have one folder on
CATServer shared and it can be viewed in Windows Explorer. Also, I can
ping CATServer successfully.
* there are no firewalls running on either computer.
* the network protocols: Shared Memory, Named Pipes and TCP/IP are
enabled; VIA is not enabled.
* the Surface Area Configuration was something I did not know anything
about. Remote Connections were set to "Local Connections Only". I
changed that to "Local and Remote" (for both TCP/IP and Named Pipes)
and then stopped and restarted the server service. It made no
difference. I also re-booted the computer but that, too, made no
difference.
Any other thoughts?
|||On Mon, 20 Nov 2006 18:20:18 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:
>hi Martin,
>Martin wrote:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;328306&Product=sql
Thanks again. I've gone through that fairly well but still haven't
been able to get connected.
I did stumble on to something though: I started the "SQL Server
Browser". Now, when my VB6 program tries to connect, I'm getting a
different error message: "Login failed for user 'userid'" (where
userid is the userid that's in my connect string).
Does this mean that SQL Server Browser has to be running in order for
my program to connect?
Also, when I posted earlier, I was using msado 2.5 in my VB program.
One of the things in the page you referenced mentioned that I needed
at least version 2.6. I have 2.7 (and 2.8) on the machine where VB is
running so I changed the reference to 2.7. That did not seems to make
any difference however. Does that shed any light on the problem I
might be up against?
|||On Mon, 20 Nov 2006 07:58:39 -0700, Martin <martinvalley@.comcast.net>
wrote:
>I have a couple of databases developed and being used in MSDE. The
>db's are used and maintained from a couple of programs written in
>Visual Basic 6 (using an ADO connection) (these programs run on a
>separate computer).
>I've just set up a new computer and installed SQL Server Express 2005
>on it. I copied the .MDF files from the MSDE unit and attached them to
>SQLExpress.
>Now, when I try to connect to the databases, I get the "SQL Server
>does not exist or access is denied" error. I have, of course, modified
>the connection string such that the Data Source property is using the
>correct computer name.
>I've looked at everything I can think of but obviously I'm missing
>something fundamental here. Any suggestions as to what I might be
>doing wrong?
>Thanks.
I finally got this working - sorta...
I had to do three things: I added "\SQLExpress" to the Data Source
name in the client program's connection string. Also in the client
program, I upgraded to version 2.8 of MSADO. On the server side of
things, I added the Login that I was trying to use from the client
side.
With these changes, I am now able to access the data on the server as
needed from the client programs.
With one major exception:
After I got everything working, I recompiled one of the VB programs
and installed it on another computer (call it computer B for
reference). But, when I run it, it encounters the exact same error
condition that I had originally! IOW, this thing works from computer A
but not from computer B.
Yes, computer B has version 2.8 of MSADO on it (I downloaded and
installed a verification tool and checked it out). Computer B has
access to the server computer where SQL Express is running (they're
all on the same LAN).
I'm stumped. Anyone have any ideas what the problem might be?
|||>I finally got this working - sorta...
>I had to do three things: I added "\SQLExpress" to the Data Source
>name in the client program's connection string. Also in the client
>program, I upgraded to version 2.8 of MSADO. On the server side of
>things, I added the Login that I was trying to use from the client
>side.
>With these changes, I am now able to access the data on the server as
>needed from the client programs.
>With one major exception:
>After I got everything working, I recompiled one of the VB programs
>and installed it on another computer (call it computer B for
>reference). But, when I run it, it encounters the exact same error
>condition that I had originally! IOW, this thing works from computer A
>but not from computer B.
>Yes, computer B has version 2.8 of MSADO on it (I downloaded and
>installed a verification tool and checked it out). Computer B has
>access to the server computer where SQL Express is running (they're
>all on the same LAN).
>I'm stumped. Anyone have any ideas what the problem might be?
>
OK, got it going...
Apparently, I have to have the SQL Server Browser service running in
order to connect from computer "B".
Why the Browser service is not needed by computer "A", I have no idea.
|||On Thu, 23 Nov 2006 12:09:01 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:
>hi Martin,
>Martin wrote:
>SQLBrowser is not needed for local connections as they are not resolved over
>the TCP/IP stack but via shared memory and the port is directly resolved via
>local access...
>try modifying the network protocol (SQL Server Management Studio Express,
>connection dialog, "Options", "Network Protocol" = TCP/IP... you will not
>ba able to connect from local client as well..
I assume by "local connections" you mean "on the same physical
computer where SQL Server is running"? If so, then that is NOT the
case here. Both computer "A" and computer "B" in my situation are
"remote" - that is, they are separate computers on the LAN.
Again, it would seem to me that if one can connect, then the other
should be able to also.
db's are used and maintained from a couple of programs written in
Visual Basic 6 (using an ADO connection) (these programs run on a
separate computer).
I've just set up a new computer and installed SQL Server Express 2005
on it. I copied the .MDF files from the MSDE unit and attached them to
SQLExpress.
Now, when I try to connect to the databases, I get the "SQL Server
does not exist or access is denied" error. I have, of course, modified
the connection string such that the Data Source property is using the
correct computer name.
I've looked at everything I can think of but obviously I'm missing
something fundamental here. Any suggestions as to what I might be
doing wrong?
Thanks.
On Mon, 20 Nov 2006 16:22:39 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:
>hi Martin,
>Martin wrote:
>please verify the instance name is correct (in the form
>ComputerName\InstanceName), the remote computer can access the lan and the
>remote SQLExpress installed computer, firewall (both) have been set with the
>appropriate exception(s) allowing connectivity and trafic (both the DBEngine
>used port and the SQLBrowser, if used), the SQLExpress network protocols
>have been enabled, the Surface Area Configuration ahs been set to allow
>remote connections...
Thanks, Andrea. I've gone thru your list but still no luck.
* the name of the new computer is "CATServer" (it's not actually a
"server" - it's running WinXP Pro with all current service packs and
updates). I've tried setting the Data Source to both "CATSERVER" and
"CATSERVER\SQLEXPRESS". Neither way seemed to make any difference.
* the remote computer CAN access CATServer. I have one folder on
CATServer shared and it can be viewed in Windows Explorer. Also, I can
ping CATServer successfully.
* there are no firewalls running on either computer.
* the network protocols: Shared Memory, Named Pipes and TCP/IP are
enabled; VIA is not enabled.
* the Surface Area Configuration was something I did not know anything
about. Remote Connections were set to "Local Connections Only". I
changed that to "Local and Remote" (for both TCP/IP and Named Pipes)
and then stopped and restarted the server service. It made no
difference. I also re-booted the computer but that, too, made no
difference.
Any other thoughts?
|||On Mon, 20 Nov 2006 18:20:18 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:
>hi Martin,
>Martin wrote:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;328306&Product=sql
Thanks again. I've gone through that fairly well but still haven't
been able to get connected.
I did stumble on to something though: I started the "SQL Server
Browser". Now, when my VB6 program tries to connect, I'm getting a
different error message: "Login failed for user 'userid'" (where
userid is the userid that's in my connect string).
Does this mean that SQL Server Browser has to be running in order for
my program to connect?
Also, when I posted earlier, I was using msado 2.5 in my VB program.
One of the things in the page you referenced mentioned that I needed
at least version 2.6. I have 2.7 (and 2.8) on the machine where VB is
running so I changed the reference to 2.7. That did not seems to make
any difference however. Does that shed any light on the problem I
might be up against?
|||On Mon, 20 Nov 2006 07:58:39 -0700, Martin <martinvalley@.comcast.net>
wrote:
>I have a couple of databases developed and being used in MSDE. The
>db's are used and maintained from a couple of programs written in
>Visual Basic 6 (using an ADO connection) (these programs run on a
>separate computer).
>I've just set up a new computer and installed SQL Server Express 2005
>on it. I copied the .MDF files from the MSDE unit and attached them to
>SQLExpress.
>Now, when I try to connect to the databases, I get the "SQL Server
>does not exist or access is denied" error. I have, of course, modified
>the connection string such that the Data Source property is using the
>correct computer name.
>I've looked at everything I can think of but obviously I'm missing
>something fundamental here. Any suggestions as to what I might be
>doing wrong?
>Thanks.
I finally got this working - sorta...
I had to do three things: I added "\SQLExpress" to the Data Source
name in the client program's connection string. Also in the client
program, I upgraded to version 2.8 of MSADO. On the server side of
things, I added the Login that I was trying to use from the client
side.
With these changes, I am now able to access the data on the server as
needed from the client programs.
With one major exception:
After I got everything working, I recompiled one of the VB programs
and installed it on another computer (call it computer B for
reference). But, when I run it, it encounters the exact same error
condition that I had originally! IOW, this thing works from computer A
but not from computer B.
Yes, computer B has version 2.8 of MSADO on it (I downloaded and
installed a verification tool and checked it out). Computer B has
access to the server computer where SQL Express is running (they're
all on the same LAN).
I'm stumped. Anyone have any ideas what the problem might be?
|||>I finally got this working - sorta...
>I had to do three things: I added "\SQLExpress" to the Data Source
>name in the client program's connection string. Also in the client
>program, I upgraded to version 2.8 of MSADO. On the server side of
>things, I added the Login that I was trying to use from the client
>side.
>With these changes, I am now able to access the data on the server as
>needed from the client programs.
>With one major exception:
>After I got everything working, I recompiled one of the VB programs
>and installed it on another computer (call it computer B for
>reference). But, when I run it, it encounters the exact same error
>condition that I had originally! IOW, this thing works from computer A
>but not from computer B.
>Yes, computer B has version 2.8 of MSADO on it (I downloaded and
>installed a verification tool and checked it out). Computer B has
>access to the server computer where SQL Express is running (they're
>all on the same LAN).
>I'm stumped. Anyone have any ideas what the problem might be?
>
OK, got it going...
Apparently, I have to have the SQL Server Browser service running in
order to connect from computer "B".
Why the Browser service is not needed by computer "A", I have no idea.
|||On Thu, 23 Nov 2006 12:09:01 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:
>hi Martin,
>Martin wrote:
>SQLBrowser is not needed for local connections as they are not resolved over
>the TCP/IP stack but via shared memory and the port is directly resolved via
>local access...
>try modifying the network protocol (SQL Server Management Studio Express,
>connection dialog, "Options", "Network Protocol" = TCP/IP... you will not
>ba able to connect from local client as well..
I assume by "local connections" you mean "on the same physical
computer where SQL Server is running"? If so, then that is NOT the
case here. Both computer "A" and computer "B" in my situation are
"remote" - that is, they are separate computers on the LAN.
Again, it would seem to me that if one can connect, then the other
should be able to also.
Monday, March 12, 2012
Problem when rebuild system databases for a clustered instance of
Has anyone rebuilt system databases for a SQL2005 clustered instance?
I have followed the code from "To rebuild system databases for a clustered."
section at http://msdn2.microsoft.com/en-us/library/ms144259.aspx, and keep
getting message saying to add more parameters. I added the parameter
whenever it required, "Group", then "addnode", even INSTALLSQLDATADIR = "S:\"
which data should go.
The summary.txt says "Setup succeeded with the installation". But in the
files\setup_*_core.log for both node2, I have this error:
Error: Action "LaunchLocalBootstrapAction" threw an exception during
execution. Error information reported during run:
"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe"
finished and returned: 0
Aborting queue processing as nested installer has completed
Message pump returning: 0
I want to know whether anyone has similar problem or succeed when rebuild
system DBs for a cluster.
What are all the parameters needed for rebuilding sys databases on a
cluster? Is there anything should be aware of?
Hi Julia
Did you manage to resolve this problem? I am experiencing exactly the
same thing
.
Cheers
Jesse
*** Sent via Developersdex http://www.codecomments.com ***
|||No. I have contacted Microsoft but no answer. I would recommend you report
this problem as well to them and get them to look into it.
"Jesse Easton" wrote:
>
> Hi Julia
> Did you manage to resolve this problem? I am experiencing exactly the
> same thing.
> Cheers
> Jesse
> *** Sent via Developersdex http://www.codecomments.com ***
>
I have followed the code from "To rebuild system databases for a clustered."
section at http://msdn2.microsoft.com/en-us/library/ms144259.aspx, and keep
getting message saying to add more parameters. I added the parameter
whenever it required, "Group", then "addnode", even INSTALLSQLDATADIR = "S:\"
which data should go.
The summary.txt says "Setup succeeded with the installation". But in the
files\setup_*_core.log for both node2, I have this error:
Error: Action "LaunchLocalBootstrapAction" threw an exception during
execution. Error information reported during run:
"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe"
finished and returned: 0
Aborting queue processing as nested installer has completed
Message pump returning: 0
I want to know whether anyone has similar problem or succeed when rebuild
system DBs for a cluster.
What are all the parameters needed for rebuilding sys databases on a
cluster? Is there anything should be aware of?
Hi Julia
Did you manage to resolve this problem? I am experiencing exactly the
same thing
.Cheers
Jesse
*** Sent via Developersdex http://www.codecomments.com ***
|||No. I have contacted Microsoft but no answer. I would recommend you report
this problem as well to them and get them to look into it.
"Jesse Easton" wrote:
>
> Hi Julia
> Did you manage to resolve this problem? I am experiencing exactly the
> same thing
.> Cheers
> Jesse
> *** Sent via Developersdex http://www.codecomments.com ***
>
Wednesday, March 7, 2012
Problem Viewing Databases in Enterprise Manager
I am using SQL Server 7.0 desktop version. I have a remote server registered in SQL Enterprise Manager. I can see the server itself and the main folders under it such as Databases, Management, Data Transformation Services, Security, and Support Services. However, when I click on the plus sign next to databases or try to view the databases it comes up "(No Items)". I am set up as a owner of one of the databases on this machine and I think I have access to all databases in this machine. Is there anything I can do to solve this problem?
I am not having this problem with any other registered SQL servers.I made sure the Show system databases and system object box was checked. It is checked. I still can not see anything.
-- anonymous@.discussions.microsoft.com wrote: --
Not sure if this will solve your problem, but give it a
try:
Right click on server name, select Edit sql server
registration properties and check "Show system databases
and system objects" box.
This should atleast show you master and other system
databases, if nothing else is wrong.
>--Original Message--
>>I am using SQL Server 7.0 desktop version. I have a
remote server registered in SQL Enterprise Manager. I can
see the server itself and the main folders under it such
as Databases, Management, Data Transformation Services,
Security, and Support Services. However, when I click on
the plus sign next to databases or try to view the
databases it comes up "(No Items)". I am set up as a
owner of one of the databases on this machine and I think
I have access to all databases in this machine. Is there
anything I can do to solve this problem?
>>I am not having this problem with any other registered
SQL servers.
>.
>
I am not having this problem with any other registered SQL servers.I made sure the Show system databases and system object box was checked. It is checked. I still can not see anything.
-- anonymous@.discussions.microsoft.com wrote: --
Not sure if this will solve your problem, but give it a
try:
Right click on server name, select Edit sql server
registration properties and check "Show system databases
and system objects" box.
This should atleast show you master and other system
databases, if nothing else is wrong.
>--Original Message--
>>I am using SQL Server 7.0 desktop version. I have a
remote server registered in SQL Enterprise Manager. I can
see the server itself and the main folders under it such
as Databases, Management, Data Transformation Services,
Security, and Support Services. However, when I click on
the plus sign next to databases or try to view the
databases it comes up "(No Items)". I am set up as a
owner of one of the databases on this machine and I think
I have access to all databases in this machine. Is there
anything I can do to solve this problem?
>>I am not having this problem with any other registered
SQL servers.
>.
>
Subscribe to:
Posts (Atom)