Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts

Thursday, March 29, 2012

ETL from multiple Access databases -SSIS?

Howdy Folks,

I have an ETL project that I want to ask a question about. I want to loop through a folder containing several Access databases, and extract and load each one into a single SQL Server 2005 database. The table layout is the same for all the Access dbs, as well as the SQL db. The number of Access dbs in the folder, their records, and their names will change. Is there a way i can use the SSIS Foreach tool to move through the folder and set the connection string inside the loop?

Thanks for your reply,

Chris

Yes, you can do this.

Use the ForEach File Enumerator to enumerate your list of files. you can use wildcards (e.g. *.mdb)

Store the file path of each file in a variable. You can then use the contents of that variable to dynamically build your connection string using an expression.

Lots of information via Google about this stuff if you want to go hunting or ask here with any pertinent questions.

Regards

-Jamie

|||

Thanks Jamie (alot ) for your suggestion. I tried using the Foreach File Enumerator with the root path for the Access files and a *.mdb wildcard. Then I created a mapped string variable varFileNameto hold the file path. Inside the loop i have my Data Flow Task. In the data source for the task, I was using the variable in an expression as the ConnectionSting property which didn't work. So I added an OLE DB connection string in the expression like "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::varFileName] but i don't think that's the answer.

I've been googling on this but can't find solutions for multiple .mdb's, most of waht i find is dealing with flat files. Great blogs btw.

|||

Should be able to find all you need here: http://www.connectionstrings.com/

-Jamie

|||

Yep, i went there. Thanks Jamie. My string was ok. Actually what got me running was to set the Foreach Loop Container>Properties>Execution>DelayValidation = True. Works great running the SSIS package on the box with SQL server (as opposed to OLE DB) destinations.

Tuesday, March 27, 2012

Estimating cluster memory requirement

Hi,
I am aggregating 6-8 SQL Server 2000 databases to a cluster of SQL
Server consisting of two nodes, each with four 3.1GHz Xeon CPUs. How do
I estimate the memory requirement for each node? Do I simply add the
memory utilization of each SQL Server instance on each individual SQL
server? Or, is there some other formula to arrive at this figure?
Thanks,
- Siddhartha
It actually works the other way around. Put as much memory as you can in
each node. SQL will generally run better the more memory you give it. Then
divide the memory amongst the instances. Remember, during a failover, all
your instances will "stack" onto one server so you must plan for that
situation. Also you will need to leave 1-2GB for the OS and support
applications. Use the Performance Monitor tool to watchoverall memory use.
I like to use Page Life Expectancy as a measurement for memory pressure
within SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Siddhartha Jain" <sid@.netmagicsolutions.com> wrote in message
news:ufRmfc17FHA.2616@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am aggregating 6-8 SQL Server 2000 databases to a cluster of SQL
> Server consisting of two nodes, each with four 3.1GHz Xeon CPUs. How do
> I estimate the memory requirement for each node? Do I simply add the
> memory utilization of each SQL Server instance on each individual SQL
> server? Or, is there some other formula to arrive at this figure?
> Thanks,
> - Siddhartha
|||Thanks for the reply Geoff. I don't intend to run multiple instances per
node but only one instance per node. So lets say I have six servers now,
each using 2GB currently for the SQL server instance, then do I size 6GB
for the SQL instance per cluster node (in a cluster of two nodes).
- Siddhartha
Geoff N. Hiten wrote:
> It actually works the other way around. Put as much memory as you can in
> each node. SQL will generally run better the more memory you give it. Then
> divide the memory amongst the instances. Remember, during a failover, all
> your instances will "stack" onto one server so you must plan for that
> situation. Also you will need to leave 1-2GB for the OS and support
> applications. Use the Performance Monitor tool to watchoverall memory use.
> I like to use Page Life Expectancy as a measurement for memory pressure
> within SQL Server.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Siddhartha Jain" <sid@.netmagicsolutions.com> wrote in message
> news:ufRmfc17FHA.2616@.TK2MSFTNGP15.phx.gbl...
>
>
|||During a cluster node failure event, the instances will all migrate to a
single node. That is how it wirks. You will need to divide the memory so
that both instances can live on a single node. As for total memory
required, there is no exact rule. SQL likes more memory so adding memory
won't hurt. Watch the performance counters to check memory pressure.
GNH
"Siddhartha Jain" <sid@.netmagicsolutions.com> wrote in message
news:%23l$s1u27FHA.744@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks for the reply Geoff. I don't intend to run multiple instances per
> node but only one instance per node. So lets say I have six servers now,
> each using 2GB currently for the SQL server instance, then do I size 6GB
> for the SQL instance per cluster node (in a cluster of two nodes).
> - Siddhartha
>
> Geoff N. Hiten wrote:
sql

Monday, March 26, 2012

Establish connection to the server

Hi,

I have a problem editing my SQL databases in Visual Studio 2005, i have searched the web for answers, but those were not clear enough to me, somthing about changing my settings for SQL server, but i didn't follow it all to well, the current error mesage I get when i click in the Server Explorer on my data connections is:

An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I do have followed some tips, bit end to this error mesage every time. Bevore i followed that tips the error said:

(provider: TCP/IP, error ... i dont remember anymore...)

I have tried to delete SQL and Visual Studio but it didn't help, and reinstall it.

Please can somebody help me, i am realy fFrustrated about the fact i cant fix it!By default SQL Server Express does not have remote connections enabled. You can do this using the following instructions: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx|||Yes, i have seen these instructions before, and i apply them again. But again without any succes. the error i now get is:

Connecting to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId=49251

I downloaded the file, open it. It recognizes the Named Instance as SQLExpress but then it said:

Name: Microsoft SQL Server 2005 Express Edition (SQLEXPRESS)

Allowed Action: Maintain (Action is only available from Add or Remove Programs)

Reason: Cannot maintain product, to maintain the product go to Add or Remove Programs

And the things i have in my Add or Remove Programs are:

Microsoft SQL Server 2005
Microsoft SQL Server 2005 Mobile
Microsoft SQL Server Native Client
Microsoft SQL Server Settup supporting files
Microsoft SQL Server VSS writer

What must i do?

|||

I encountered the same error message while trying to create a data connection from Visual Studio 2005 using the Server Explorer.

This is what I did:

# 1. Started Visual Studio 2005

# 2. Opened the Server Explorer (View | Server Explorer)

# 3. Right-clicked the Data Connections node

# 4. Selected Create New SQL Server Database... from the context menu

# 5. Selected my computer from the Server Name dropdown list

# 6. Selected authentication method (Use Windows Authentication)

# 7. Typed a name in the New Database Name field

# 8. Hit the OK button

That's when I received the following error message

An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error:40 – Could not open a connection to SQL Server)

It made absolutely no sense. I wasn't trying to connect to a remote computer. I was simply trying to connect to the SQL Server 2005 Express Edition which was installed on my local computer during the Visual Studio 2005 installation.

After numerous attempts on several computers, I finally realized what I was doing wrong. By adding the database instance name to the server name (# 5), it actually worked. Instead of selecting <server name> from the dropdown list, I entered <server name>\SQLExpress manually. That was obviously the problem.

SQL Server 2005 Express Edition is obviously installed as a named instance, since one has to provide the instance name in addition to the server name (even when there is only one instance running on the requested server).

|||

Hi

Have to tried

sp_configure 'remote admin connections',1

go

Reconfigure

go

Establish connection to the server

Hi,

I have a problem editing my SQL databases in Visual Studio 2005, i have searched the web for answers, but those were not clear enough to me, somthing about changing my settings for SQL server, but i didn't follow it all to well, the current error mesage I get when i click in the Server Explorer on my data connections is:

An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I do have followed some tips, bit end to this error mesage every time. Bevore i followed that tips the error said:

(provider: TCP/IP, error ... i dont remember anymore...)

I have tried to delete SQL and Visual Studio but it didn't help, and reinstall it.

Please can somebody help me, i am realy fFrustrated about the fact i cant fix it!By default SQL Server Express does not have remote connections enabled. You can do this using the following instructions: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx|||Yes, i have seen these instructions before, and i apply them again. But again without any succes. the error i now get is:

Connecting to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId=49251

I downloaded the file, open it. It recognizes the Named Instance as SQLExpress but then it said:

Name: Microsoft SQL Server 2005 Express Edition (SQLEXPRESS)

Allowed Action: Maintain (Action is only available from Add or Remove Programs)

Reason: Cannot maintain product, to maintain the product go to Add or Remove Programs

And the things i have in my Add or Remove Programs are:

Microsoft SQL Server 2005
Microsoft SQL Server 2005 Mobile
Microsoft SQL Server Native Client
Microsoft SQL Server Settup supporting files
Microsoft SQL Server VSS writer

What must i do?

|||

I encountered the same error message while trying to create a data connection from Visual Studio 2005 using the Server Explorer.

This is what I did:

# 1. Started Visual Studio 2005

# 2. Opened the Server Explorer (View | Server Explorer)

# 3. Right-clicked the Data Connections node

# 4. Selected Create New SQL Server Database... from the context menu

# 5. Selected my computer from the Server Name dropdown list

# 6. Selected authentication method (Use Windows Authentication)

# 7. Typed a name in the New Database Name field

# 8. Hit the OK button

That's when I received the following error message

An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error:40 – Could not open a connection to SQL Server)

It made absolutely no sense. I wasn't trying to connect to a remote computer. I was simply trying to connect to the SQL Server 2005 Express Edition which was installed on my local computer during the Visual Studio 2005 installation.

After numerous attempts on several computers, I finally realized what I was doing wrong. By adding the database instance name to the server name (# 5), it actually worked. Instead of selecting <server name> from the dropdown list, I entered <server name>\SQLExpress manually. That was obviously the problem.

SQL Server 2005 Express Edition is obviously installed as a named instance, since one has to provide the instance name in addition to the server name (even when there is only one instance running on the requested server).

|||

Hi

Have to tried

sp_configure 'remote admin connections',1

go

Reconfigure

go

Esport databases

I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
and SQL server 2000 on a new machine how can transfer the old SQL 6.5
databaes to new SQL server 2000 on a new nachine
Yousuf Khan
Programmer
You can use several methods.
Backup and restore to newserver or Copy both mdf and ldf file to new server
and attach the database or use DTS.
any more question pls reply
hope this will help
Herbert
"Yousuf" wrote:

> I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
> and SQL server 2000 on a new machine how can transfer the old SQL 6.5
> databaes to new SQL server 2000 on a new nachine
> --
> Yousuf Khan
> Programmer
|||Herbert thanks for your reply
can you please write me the exact procedure shifting from old server to new
server with upgraded version I am having Tape Device or can i transfer of
import from 6.5 to 2000
Yousuf Khan
Programmer
"Herbert" wrote:
[vbcol=seagreen]
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new server
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
|||U take full backup to ur local server drive. Copy that backup to new server
and using the All Tasks => restore option restore the DB to new server.
For backup and restore refer BOL
Herbert
"Yousuf" wrote:
[vbcol=seagreen]
> Herbert thanks for your reply
> can you please write me the exact procedure shifting from old server to new
> server with upgraded version I am having Tape Device or can i transfer of
> import from 6.5 to 2000
> --
> Yousuf Khan
> Programmer
>
> "Herbert" wrote:
|||6.5 didn't have mdf files, and the storage format was different from the new architecture. So copy
db files or backup doesn't work.
SQL2K has an upgrade wizard that can upgrade from 6.5. Or, use DTS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:CC047EAF-C9A5-4E92-B1E8-4C368296F52E@.microsoft.com...[vbcol=seagreen]
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new server
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
|||Hi,
As Tibor pointed out due to architectural changes you can not DETACH and
ATTACH the databases, as well as BACKUP and RESTORE will not
work.
The best method is to use the SQL server 2000 upgrade wizard.This converts
your Schema, logins, users, data and all other objects to sql 2000.
Every thing automatic.
Detailed steps from books online:-
How to perform a SQL Server version 6.5 to SQL Server 2000 upgrade using a
direct pipeline (SQL Server Upgrade Wizard)
Note To run the SQL Server Upgrade Wizard, you must have an instance of
Microsoft SQL ServerT 2000 already installed on your computer.
To perform a SQL Server version 6.5 to SQL Server 2000 upgrade by named pipe
1.. On the Start menu, point to Programs/Microsoft SQL Server-Switch,
click SQL Server Upgrade Wizard, and then click Next.
2.. In the Data and Object Transfer screen, accept the default selections,
including Named pipe, and then click Next. Verification options are
recommended, but not required. Click Help for information.
3.. On the Logon screen, in the Server name box in the Export server (6.5)
group box, enter the name of the local or remote computer on which Microsoft
SQL Server version 6.5 is installed.
In the Administrator password ('sa') box, enter the sa password for SQL
Server 6.5, and then click Next.
Unless you have changed it already, the default system administrator
password for SQL Server 2000 is blank. Although the SQL Server 2000 default
for the sa password is blank, to conform with best security practices, it
should be changed to a strong password at the first opportunity.
For Import Server (2000), the server name is filled in. Enter the optional
startup arguments, if you want. Click Help for information. When you are
finished setting options, click Next.
4.. In the message box asking if you want to continue, click Yes if you
are ready to upgrade. The SQL Server Upgrade Wizard shuts down SQL Server
6.5 and starts SQL Server 2000.
5.. In the Code Page Selection screen, accept or change the default
settings, and then click Next.
6.. In the Database Selection screen, include the databases to upgrade.
Move any databases you do not want upgraded at this time to the Exclude
list, and then click Next.
Converting all databases is recommended.
7.. In the Database Creation dialog box, select Use the default
configuration or edit the default, and then click Next.
Click Edit to examine and make changes to the proposed disk configuration
within the layout utility. In the Proposed Database Layout box, make changes
as needed. Click Advanced to view Object Details and Drive Summary. When you
are finished, click Accept to return to the SQL Server Upgrade Wizard.
8.. In the System Configuration screen, in System objects to transfer,
select the object types to transfer from SQL Server 6.5 to SQL Server 2000:
a.. Server configuration
Login and remote login registrations and server configuration options
relevant to SQL Server 2000 are transferred as part of the version upgrade.
b.. Replication settings
All articles, subscriptions and publications of each selected database,
plus the distribution database, if any, are transferred and upgraded.
c.. SQL Executive settings
All tasks scheduled by SQL Executive are transferred and upgraded so
that SQL Server 2000 can schedule and run those tasks in SQL Server Agent.
9.. In the System Configuration screen, in Advanced settings, for ANSI
Nulls, select:
a.. Off, if ANSI nulls should not be used when stored procedures are
created. This is the default.
b.. On, if ANSI nulls should be used when stored procedures are created.
10.. In Quoted identifiers, select one of these options, and then click
Next:
a.. Mixed (or don't know), if some of your objects were created with
QUOTED_IDENTIFIER set to ON and others with it set to OFF, or if you are not
sure how they were created.
b.. Off, if all objects should be compiled with QUOTED_IDENTIFIER set to
OFF.
c.. On, if all objects should be compiled with QUOTED_IDENTIFIER set to
ON.
11.. In the Completing the SQL Server Wizard screen, view the summary of
choices you have made. Click View warnings and choices in notepad to open a
text version of the upgrade script. If all options are correct, click
Finish.
The SQL Server Upgrade Script Interpreter screen appears, with information
on the progress of the upgrade.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eSmyRu7dFHA.1684@.TK2MSFTNGP09.phx.gbl...
> 6.5 didn't have mdf files, and the storage format was different from the
> new architecture. So copy
> db files or backup doesn't work.
> SQL2K has an upgrade wizard that can upgrade from 6.5. Or, use DTS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:CC047EAF-C9A5-4E92-B1E8-4C368296F52E@.microsoft.com...
>
begin 666 note.gif
M1TE&.#EA# `+`(#_`(2&`,# P"'Y! $```$`+ `````,``L`0 (:C(\(H'S[
68%R0&ED;M7,'[%S2YW#1)VJ;4P``.P``
`
end

Esport databases

I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
and SQL server 2000 on a new machine how can transfer the old SQL 6.5
databaes to new SQL server 2000 on a new nachine
--
Yousuf Khan
ProgrammerYou can use several methods.
Backup and restore to newserver or Copy both mdf and ldf file to new server
and attach the database or use DTS.
any more question pls reply
hope this will help
Herbert
"Yousuf" wrote:

> I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 serve
r
> and SQL server 2000 on a new machine how can transfer the old SQL 6.5
> databaes to new SQL server 2000 on a new nachine
> --
> Yousuf Khan
> Programmer|||Herbert thanks for your reply
can you please write me the exact procedure shifting from old server to new
server with upgraded version I am having Tape Device or can i transfer of
import from 6.5 to 2000
--
Yousuf Khan
Programmer
"Herbert" wrote:
[vbcol=seagreen]
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new serve
r
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
>|||U take full backup to ur local server drive. Copy that backup to new server
and using the All Tasks => restore option restore the DB to new server.
For backup and restore refer BOL
--
Herbert
"Yousuf" wrote:
[vbcol=seagreen]
> Herbert thanks for your reply
> can you please write me the exact procedure shifting from old server to ne
w
> server with upgraded version I am having Tape Device or can i transfer of
> import from 6.5 to 2000
> --
> Yousuf Khan
> Programmer
>
> "Herbert" wrote:
>|||6.5 didn't have mdf files, and the storage format was different from the new
architecture. So copy
db files or backup doesn't work.
SQL2K has an upgrade wizard that can upgrade from 6.5. Or, use DTS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:CC047EAF-C9A5-4E92-B1E8-4C368296F52E@.microsoft.com...[vbcol=seagreen]
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new serve
r
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
>

Esport databases

I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
and SQL server 2000 on a new machine how can transfer the old SQL 6.5
databaes to new SQL server 2000 on a new nachine
--
Yousuf Khan
ProgrammerYou can use several methods.
Backup and restore to newserver or Copy both mdf and ldf file to new server
and attach the database or use DTS.
any more question pls reply
hope this will help
Herbert
"Yousuf" wrote:
> I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
> and SQL server 2000 on a new machine how can transfer the old SQL 6.5
> databaes to new SQL server 2000 on a new nachine
> --
> Yousuf Khan
> Programmer|||Herbert thanks for your reply
can you please write me the exact procedure shifting from old server to new
server with upgraded version I am having Tape Device or can i transfer of
import from 6.5 to 2000
--
Yousuf Khan
Programmer
"Herbert" wrote:
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new server
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
> > I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
> > and SQL server 2000 on a new machine how can transfer the old SQL 6.5
> > databaes to new SQL server 2000 on a new nachine
> > --
> > Yousuf Khan
> > Programmer|||U take full backup to ur local server drive. Copy that backup to new server
and using the All Tasks => restore option restore the DB to new server.
For backup and restore refer BOL
--
Herbert
"Yousuf" wrote:
> Herbert thanks for your reply
> can you please write me the exact procedure shifting from old server to new
> server with upgraded version I am having Tape Device or can i transfer of
> import from 6.5 to 2000
> --
> Yousuf Khan
> Programmer
>
> "Herbert" wrote:
> > You can use several methods.
> >
> > Backup and restore to newserver or Copy both mdf and ldf file to new server
> > and attach the database or use DTS.
> >
> > any more question pls reply
> >
> > hope this will help
> >
> > Herbert
> >
> >
> > "Yousuf" wrote:
> >
> > > I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
> > > and SQL server 2000 on a new machine how can transfer the old SQL 6.5
> > > databaes to new SQL server 2000 on a new nachine
> > > --
> > > Yousuf Khan
> > > Programmer|||6.5 didn't have mdf files, and the storage format was different from the new architecture. So copy
db files or backup doesn't work.
SQL2K has an upgrade wizard that can upgrade from 6.5. Or, use DTS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:CC047EAF-C9A5-4E92-B1E8-4C368296F52E@.microsoft.com...
> You can use several methods.
> Backup and restore to newserver or Copy both mdf and ldf file to new server
> and attach the database or use DTS.
> any more question pls reply
> hope this will help
> Herbert
>
> "Yousuf" wrote:
>> I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003 server
>> and SQL server 2000 on a new machine how can transfer the old SQL 6.5
>> databaes to new SQL server 2000 on a new nachine
>> --
>> Yousuf Khan
>> Programmer|||Hi,
As Tibor pointed out due to architectural changes you can not DETACH and
ATTACH the databases, as well as BACKUP and RESTORE will not
work.
The best method is to use the SQL server 2000 upgrade wizard.This converts
your Schema, logins, users, data and all other objects to sql 2000.
Every thing automatic.
Detailed steps from books online:-
How to perform a SQL Server version 6.5 to SQL Server 2000 upgrade using a
direct pipeline (SQL Server Upgrade Wizard)
Note To run the SQL Server Upgrade Wizard, you must have an instance of
Microsoft® SQL ServerT 2000 already installed on your computer.
To perform a SQL Server version 6.5 to SQL Server 2000 upgrade by named pipe
1.. On the Start menu, point to Programs/Microsoft SQL Server-Switch,
click SQL Server Upgrade Wizard, and then click Next.
2.. In the Data and Object Transfer screen, accept the default selections,
including Named pipe, and then click Next. Verification options are
recommended, but not required. Click Help for information.
3.. On the Logon screen, in the Server name box in the Export server (6.5)
group box, enter the name of the local or remote computer on which Microsoft
SQL Server version 6.5 is installed.
In the Administrator password ('sa') box, enter the sa password for SQL
Server 6.5, and then click Next.
Unless you have changed it already, the default system administrator
password for SQL Server 2000 is blank. Although the SQL Server 2000 default
for the sa password is blank, to conform with best security practices, it
should be changed to a strong password at the first opportunity.
For Import Server (2000), the server name is filled in. Enter the optional
startup arguments, if you want. Click Help for information. When you are
finished setting options, click Next.
4.. In the message box asking if you want to continue, click Yes if you
are ready to upgrade. The SQL Server Upgrade Wizard shuts down SQL Server
6.5 and starts SQL Server 2000.
5.. In the Code Page Selection screen, accept or change the default
settings, and then click Next.
6.. In the Database Selection screen, include the databases to upgrade.
Move any databases you do not want upgraded at this time to the Exclude
list, and then click Next.
Converting all databases is recommended.
7.. In the Database Creation dialog box, select Use the default
configuration or edit the default, and then click Next.
Click Edit to examine and make changes to the proposed disk configuration
within the layout utility. In the Proposed Database Layout box, make changes
as needed. Click Advanced to view Object Details and Drive Summary. When you
are finished, click Accept to return to the SQL Server Upgrade Wizard.
8.. In the System Configuration screen, in System objects to transfer,
select the object types to transfer from SQL Server 6.5 to SQL Server 2000:
a.. Server configuration
Login and remote login registrations and server configuration options
relevant to SQL Server 2000 are transferred as part of the version upgrade.
b.. Replication settings
All articles, subscriptions and publications of each selected database,
plus the distribution database, if any, are transferred and upgraded.
c.. SQL Executive settings
All tasks scheduled by SQL Executive are transferred and upgraded so
that SQL Server 2000 can schedule and run those tasks in SQL Server Agent.
9.. In the System Configuration screen, in Advanced settings, for ANSI
Nulls, select:
a.. Off, if ANSI nulls should not be used when stored procedures are
created. This is the default.
b.. On, if ANSI nulls should be used when stored procedures are created.
10.. In Quoted identifiers, select one of these options, and then click
Next:
a.. Mixed (or don't know), if some of your objects were created with
QUOTED_IDENTIFIER set to ON and others with it set to OFF, or if you are not
sure how they were created.
b.. Off, if all objects should be compiled with QUOTED_IDENTIFIER set to
OFF.
c.. On, if all objects should be compiled with QUOTED_IDENTIFIER set to
ON.
11.. In the Completing the SQL Server Wizard screen, view the summary of
choices you have made. Click View warnings and choices in notepad to open a
text version of the upgrade script. If all options are correct, click
Finish.
The SQL Server Upgrade Script Interpreter screen appears, with information
on the progress of the upgrade.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eSmyRu7dFHA.1684@.TK2MSFTNGP09.phx.gbl...
> 6.5 didn't have mdf files, and the storage format was different from the
> new architecture. So copy
> db files or backup doesn't work.
> SQL2K has an upgrade wizard that can upgrade from 6.5. Or, use DTS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:CC047EAF-C9A5-4E92-B1E8-4C368296F52E@.microsoft.com...
>> You can use several methods.
>> Backup and restore to newserver or Copy both mdf and ldf file to new
>> server
>> and attach the database or use DTS.
>> any more question pls reply
>> hope this will help
>> Herbert
>>
>> "Yousuf" wrote:
>> I m having WIN NT 4 and SQl server 6.5 i wanted to shift to WIN 2003
>> server
>> and SQL server 2000 on a new machine how can transfer the old SQL 6.5
>> databaes to new SQL server 2000 on a new nachine
>> --
>> Yousuf Khan
>> Programmer
>
begin 666 note.gif
M1TE&.#EA# `+`(#_`(2&`,# P"'Y! $```$`+ `````,``L`0 (:C(\(H'S[
68%R0&ED;M7,'[%S2YW#1)VJ;4P``.P``
`
end

Wednesday, March 21, 2012

Errors with combined use of transactional and merge replication - SQL2005

I am investigating the feasibility of a configuration with 3 databases on SQL2005

DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions

DB_B is a subscriber database which subscribes to pub_txn

DB_B is also a publisher which serves up merge publication pub_merge

DB_C is a subscriber database which pulls pub_merge

===============================

Updates on DB_A are successfully replicated to DB_B

Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B

===============================

Updates on DB_B are successfully replicated to both DB_A and DB_C

===============================

Updates on DB_C initially failed with the error

Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0
The server principal "repllinkproxy" is not able to access the database
"DB_C" under the current security context.

I then changed the login repllinkproxy to be a db_owner in DB_C

I now get the error

Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23
Invalid object name 'dbo.MSreplication_objects'.

=================================

I have three questions as a result
1) Is there anything fundamentally wrong with what I am trying to achieve?

2) Why is update on DB_A not reaching DB_C

3) Why can't I update DB_C?

Any suggestions gratefully received

aero1

Just to clarify - I am replicating the same table between all the databases - without any filtering|||quick question - in which order did you create the publications/subscriptions? For the tran pub, are you using immediate updating subscriber, or queue updating subscriber?|||

Setup order was:

1) Transactional publication

2) Transactional subscription (immediate updating)

3) Merge publication

4) Merge subscription

|||

Some progress:

I have got rid of the errors on updating DB_C

- My initial setup was unrepresentative with all the databases, DB_A, DB_B and DB_C on the same server

- I dropped and recreated the whole setup with DB_C on a separate machine - which is more representative of what I am ultimately trying to achieve

- There were then no errors when attempting to update DB_C

However, updates on DB_A don't reach DB_C and updates on DB_C don't reach DB_A.

Updates on DB_B reach DB_A and DB_C OK

|||

Hi aero1, republishing is not supported when using updatable subscriptions, this is documented in SQL 2005 Books Online topic "Updatable Subscriptions for Transactional Replication". Your best bet is to implement Peer-to-Peer instead of updatable subscriptions.

|||

Hi Greg

Thanks - I am investigating peer to peer

aero1

|||

Hello *,

I have a problem very similar to this post, so I don’t open a new thread. The only different think is I use transactional publication WITHOUT updateable subscriptions.

I can cascade n SQL Servers 2005 and every update, insert and delete command change every beneath Database (already described by aero1). Because the end-users work with mobile clients the last Replication must be from type Merge. When I change some data on the top level server these changes comes only to the last transactional subscriber:

Example:

ServerA.DBA ===TR==>ServerB.DBB==TR==>ServerC.DBC==MR==>MobileDB

When I change a datarow on DBB the changes are available on DBC but not on MobileDB (after pull the data from there).

I hope someone can help me

Regards

Markus

|||

You need to set this article property to true: published_in_tran_pub

See the articles below and that should solve your problem of merge subscribers not receiving the data changes. However note that if you make updates at the merge subscribers, they will not flow all the way to the Tran root publisher and can cause non-convergence.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl_11et.asp

http://msdn2.microsoft.com/en-us/library/ms152559.aspx

|||

The issues that I had only involved 2 databases communicating with transactional replication - with merge subscribers hanging off one of these

For this scenario, bidirectional transactional replication seems to work well - but customisation of the replication stored procs is needed to handle conflicts.

Markus appears to have 3 databases communicating with transactional replication. I am not sure whether a chain of 3 bidirectionally replicating databases would work, but I can't think of any reason why it shouldn't. If it did then the merge changes would flow back to the root tran publisher.

Note that peer to peer wouldn't work in my scenario because the updates could not be partitioned suitably

Errors with combined use of transactional and merge replication - SQL2005

I am investigating the feasibility of a configuration with 3 databases on SQL2005

DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions

DB_B is a subscriber database which subscribes to pub_txn

DB_B is also a publisher which serves up merge publication pub_merge

DB_C is a subscriber database which pulls pub_merge

===============================

Updates on DB_A are successfully replicated to DB_B

Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B

===============================

Updates on DB_B are successfully replicated to both DB_A and DB_C

===============================

Updates on DB_C initially failed with the error

Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0
The server principal "repllinkproxy" is not able to access the database
"DB_C" under the current security context.

I then changed the login repllinkproxy to be a db_owner in DB_C

I now get the error

Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23
Invalid object name 'dbo.MSreplication_objects'.

=================================

I have three questions as a result
1) Is there anything fundamentally wrong with what I am trying to achieve?

2) Why is update on DB_A not reaching DB_C

3) Why can't I update DB_C?

Any suggestions gratefully received

aero1

Just to clarify - I am replicating the same table between all the databases - without any filtering|||quick question - in which order did you create the publications/subscriptions? For the tran pub, are you using immediate updating subscriber, or queue updating subscriber?|||

Setup order was:

1) Transactional publication

2) Transactional subscription (immediate updating)

3) Merge publication

4) Merge subscription

|||

Some progress:

I have got rid of the errors on updating DB_C

- My initial setup was unrepresentative with all the databases, DB_A, DB_B and DB_C on the same server

- I dropped and recreated the whole setup with DB_C on a separate machine - which is more representative of what I am ultimately trying to achieve

- There were then no errors when attempting to update DB_C

However, updates on DB_A don't reach DB_C and updates on DB_C don't reach DB_A.

Updates on DB_B reach DB_A and DB_C OK

|||

Hi aero1, republishing is not supported when using updatable subscriptions, this is documented in SQL 2005 Books Online topic "Updatable Subscriptions for Transactional Replication". Your best bet is to implement Peer-to-Peer instead of updatable subscriptions.

|||

Hi Greg

Thanks - I am investigating peer to peer

aero1

|||

Hello *,

I have a problem very similar to this post, so I don’t open a new thread. The only different think is I use transactional publication WITHOUT updateable subscriptions.

I can cascade n SQL Servers 2005 and every update, insert and delete command change every beneath Database (already described by aero1). Because the end-users work with mobile clients the last Replication must be from type Merge. When I change some data on the top level server these changes comes only to the last transactional subscriber:

Example:

ServerA.DBA ===TR==>ServerB.DBB==TR==>ServerC.DBC==MR==>MobileDB

When I change a datarow on DBB the changes are available on DBC but not on MobileDB (after pull the data from there).

I hope someone can help me

Regards

Markus

|||

You need to set this article property to true: published_in_tran_pub

See the articles below and that should solve your problem of merge subscribers not receiving the data changes. However note that if you make updates at the merge subscribers, they will not flow all the way to the Tran root publisher and can cause non-convergence.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl_11et.asp

http://msdn2.microsoft.com/en-us/library/ms152559.aspx

|||

The issues that I had only involved 2 databases communicating with transactional replication - with merge subscribers hanging off one of these

For this scenario, bidirectional transactional replication seems to work well - but customisation of the replication stored procs is needed to handle conflicts.

Markus appears to have 3 databases communicating with transactional replication. I am not sure whether a chain of 3 bidirectionally replicating databases would work, but I can't think of any reason why it shouldn't. If it did then the merge changes would flow back to the root tran publisher.

Note that peer to peer wouldn't work in my scenario because the updates could not be partitioned suitably

Errors when running DBCC CheckCatalog command

One of my users reported an error when testing against one of our Development
Servers, against a user databases. The error the application generated
contained the following:
Could not find row in sysindexes for database ID 9, object ID 1284406774,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
I then ran DBCC CHECKDB, which came back clean.
Then, I ran DBCC CHECKALLOC, which came back clean.
Next, I ran DBCC CHECKCATALOG, which gave the following errors:
Server: Msg 2513, Level 16, State 2, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOMMENTS' and 'SYSOBJECTS'.
DBCC results for 'current database'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I was not too worried, since this was a DEV server, but then I ran the DBCC
CHECKCATAOG command against our QA and then our Production server, and all
of them gave me the same error for the DBCC CHECKCATAOG command.
Any ideas on what I can try next? I don't think this is a corrupt index and
I can't run DBCC DBREINDEX against a system table.
Thank you in advance.
Sam
Are you on 7.0? If so, search KB for 2513, I found a few articles. If you are on 2000, I suggest you
open a case with MS Support for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:9FF30A6F-9DC1-4DE6-838E-8BA9D1FD15F4@.microsoft.com...
> One of my users reported an error when testing against one of our Development
> Servers, against a user databases. The error the application generated
> contained the following:
> Could not find row in sysindexes for database ID 9, object ID 1284406774,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
> I then ran DBCC CHECKDB, which came back clean.
> Then, I ran DBCC CHECKALLOC, which came back clean.
> Next, I ran DBCC CHECKCATALOG, which gave the following errors:
> Server: Msg 2513, Level 16, State 2, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOMMENTS' and 'SYSOBJECTS'.
> DBCC results for 'current database'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I was not too worried, since this was a DEV server, but then I ran the DBCC
> CHECKCATAOG command against our QA and then our Production server, and all
> of them gave me the same error for the DBCC CHECKCATAOG command.
> Any ideas on what I can try next? I don't think this is a corrupt index and
> I can't run DBCC DBREINDEX against a system table.
> Thank you in advance.
> Sam
>
>
sql

Errors when running DBCC CheckCatalog command

One of my users reported an error when testing against one of our Development
Servers, against a user databases. The error the application generated
contained the following:
Could not find row in sysindexes for database ID 9, object ID 1284406774,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
I then ran DBCC CHECKDB, which came back clean.
Then, I ran DBCC CHECKALLOC, which came back clean.
Next, I ran DBCC CHECKCATALOG, which gave the following errors:
Server: Msg 2513, Level 16, State 2, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOMMENTS' and 'SYSOBJECTS'.
DBCC results for 'current database'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I was not too worried, since this was a DEV server, but then I ran the DBCC
CHECKCATAOG command against our QA and then our Production server, and all
of them gave me the same error for the DBCC CHECKCATAOG command.
Any ideas on what I can try next? I don't think this is a corrupt index and
I can't run DBCC DBREINDEX against a system table.
Thank you in advance.
SamAre you on 7.0? If so, search KB for 2513, I found a few articles. If you are on 2000, I suggest you
open a case with MS Support for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:9FF30A6F-9DC1-4DE6-838E-8BA9D1FD15F4@.microsoft.com...
> One of my users reported an error when testing against one of our Development
> Servers, against a user databases. The error the application generated
> contained the following:
> Could not find row in sysindexes for database ID 9, object ID 1284406774,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
> I then ran DBCC CHECKDB, which came back clean.
> Then, I ran DBCC CHECKALLOC, which came back clean.
> Next, I ran DBCC CHECKCATALOG, which gave the following errors:
> Server: Msg 2513, Level 16, State 2, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOMMENTS' and 'SYSOBJECTS'.
> DBCC results for 'current database'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I was not too worried, since this was a DEV server, but then I ran the DBCC
> CHECKCATAOG command against our QA and then our Production server, and all
> of them gave me the same error for the DBCC CHECKCATAOG command.
> Any ideas on what I can try next? I don't think this is a corrupt index and
> I can't run DBCC DBREINDEX against a system table.
> Thank you in advance.
> Sam
>
>

Errors when running DBCC CheckCatalog command

One of my users reported an error when testing against one of our Developmen
t
Servers, against a user databases. The error the application generated
contained the following:
Could not find row in sysindexes for database ID 9, object ID 1284406774,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
I then ran DBCC CHECKDB, which came back clean.
Then, I ran DBCC CHECKALLOC, which came back clean.
Next, I ran DBCC CHECKCATALOG, which gave the following errors:
Server: Msg 2513, Level 16, State 2, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOMMENTS' and 'SYSOBJECTS'.
DBCC results for 'current database'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I was not too worried, since this was a DEV server, but then I ran the DBCC
CHECKCATAOG command against our QA and then our Production server, and all
of them gave me the same error for the DBCC CHECKCATAOG command.
Any ideas on what I can try next? I don't think this is a corrupt index and
I can't run DBCC DBREINDEX against a system table.
Thank you in advance.
SamAre you on 7.0? If so, search KB for 2513, I found a few articles. If you ar
e on 2000, I suggest you
open a case with MS Support for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:9FF30A6F-9DC1-4DE6-838E-8BA9D1FD15F4@.microsoft.com...
> One of my users reported an error when testing against one of our Developm
ent
> Servers, against a user databases. The error the application generated
> contained the following:
> Could not find row in sysindexes for database ID 9, object ID 1284406774,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
> I then ran DBCC CHECKDB, which came back clean.
> Then, I ran DBCC CHECKALLOC, which came back clean.
> Next, I ran DBCC CHECKCATALOG, which gave the following errors:
> Server: Msg 2513, Level 16, State 2, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOMMENTS' and 'SYSOBJECTS'.
> DBCC results for 'current database'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I was not too worried, since this was a DEV server, but then I ran the DBC
C
> CHECKCATAOG command against our QA and then our Production server, and al
l
> of them gave me the same error for the DBCC CHECKCATAOG command.
> Any ideas on what I can try next? I don't think this is a corrupt index an
d
> I can't run DBCC DBREINDEX against a system table.
> Thank you in advance.
> Sam
>
>

Wednesday, February 15, 2012

Error: Server doesnot exist or Access denied

We are host with windows 2003 server and SQL 2000 DB server. Our clients are
trying to connect to their individual databases using SQL webadmin or
Enterprise manager tool and they get the following message:
"SQL server doesnot exist or access denied.
ConnectionOpen(Connect())"
This is the message from SQL enterprise manager. The workstation is XP SP1.
Any suggestions>> Any suggestions
Oh the usual:
1. Check to make sure that they are using the right username/password
combination.
2. Make sure the client is not behind a firewall that prevents comm
over the SQL Server port.
3. Make sure the same applies for the server.
4. Make sure that they are using the right authentication type (sql or
win).
5. Make sure that the server is configured to use the right auth mode.
6. Make sure that the server's host name appears in the list of
available server to connect to (see 2 & 3).
HTH
AM|||Actually we found out that the SQL is not listening on any TCP/IP port but
only listening to named pipes. Any idea of how to make SQL Server listen to
any tcp/ip ports?
"AM" wrote:

> Oh the usual:
> 1. Check to make sure that they are using the right username/password
> combination.
> 2. Make sure the client is not behind a firewall that prevents comm
> over the SQL Server port.
> 3. Make sure the same applies for the server.
> 4. Make sure that they are using the right authentication type (sql or
> win).
> 5. Make sure that the server is configured to use the right auth mode.
> 6. Make sure that the server's host name appears in the list of
> available server to connect to (see 2 & 3).
> HTH
> AM
>|||In Enterprise Manager, right click on the DB, select properties. In
ther dialog box, int he General tab, on the bottom click on Network
configuration. In the dialog box that pops up, in the general tab,
check to see if TCP/IP is listed on the box on the right. If not select
it from the box on the left and click on enable. YOu can edit the
properties to set up the port to listen on.
HTH
AM|||You can use Server Network Utility to set the tcp port for the sql instance.
-oj
"Snehal Rana" <SnehalRana@.discussions.microsoft.com> wrote in message
news:E263310C-BB72-483D-BE16-DA854F38CB40@.microsoft.com...[vbcol=seagreen]
> Actually we found out that the SQL is not listening on any TCP/IP port but
> only listening to named pipes. Any idea of how to make SQL Server listen
> to
> any tcp/ip ports?
>
> "AM" wrote:
>

Error: Server doesnot exist or Access denied

We are host with windows 2003 server and SQL 2000 DB server. Our clients are
trying to connect to their individual databases using SQL webadmin or
Enterprise manager tool and they get the following message:
"SQL server doesnot exist or access denied.
ConnectionOpen(Connect())"
This is the message from SQL enterprise manager. The workstation is XP SP1.
Any suggestions>> Any suggestions
Oh the usual:
1. Check to make sure that they are using the right username/password
combination.
2. Make sure the client is not behind a firewall that prevents comm
over the SQL Server port.
3. Make sure the same applies for the server.
4. Make sure that they are using the right authentication type (sql or
win).
5. Make sure that the server is configured to use the right auth mode.
6. Make sure that the server's host name appears in the list of
available server to connect to (see 2 & 3).
HTH
AM|||Actually we found out that the SQL is not listening on any TCP/IP port but
only listening to named pipes. Any idea of how to make SQL Server listen to
any tcp/ip ports?
"AM" wrote:
> >> Any suggestions
> Oh the usual:
> 1. Check to make sure that they are using the right username/password
> combination.
> 2. Make sure the client is not behind a firewall that prevents comm
> over the SQL Server port.
> 3. Make sure the same applies for the server.
> 4. Make sure that they are using the right authentication type (sql or
> win).
> 5. Make sure that the server is configured to use the right auth mode.
> 6. Make sure that the server's host name appears in the list of
> available server to connect to (see 2 & 3).
> HTH
> AM
>|||In Enterprise Manager, right click on the DB, select properties. In
ther dialog box, int he General tab, on the bottom click on Network
configuration. In the dialog box that pops up, in the general tab,
check to see if TCP/IP is listed on the box on the right. If not select
it from the box on the left and click on enable. YOu can edit the
properties to set up the port to listen on.
HTH
AM|||You can use Server Network Utility to set the tcp port for the sql instance.
--
-oj
"Snehal Rana" <SnehalRana@.discussions.microsoft.com> wrote in message
news:E263310C-BB72-483D-BE16-DA854F38CB40@.microsoft.com...
> Actually we found out that the SQL is not listening on any TCP/IP port but
> only listening to named pipes. Any idea of how to make SQL Server listen
> to
> any tcp/ip ports?
>
> "AM" wrote:
>> >> Any suggestions
>> Oh the usual:
>> 1. Check to make sure that they are using the right username/password
>> combination.
>> 2. Make sure the client is not behind a firewall that prevents comm
>> over the SQL Server port.
>> 3. Make sure the same applies for the server.
>> 4. Make sure that they are using the right authentication type (sql or
>> win).
>> 5. Make sure that the server is configured to use the right auth mode.
>> 6. Make sure that the server's host name appears in the list of
>> available server to connect to (see 2 & 3).
>> HTH
>> AM
>>

Error: Server doesnot exist or Access denied

We are host with windows 2003 server and SQL 2000 DB server. Our clients are
trying to connect to their individual databases using SQL webadmin or
Enterprise manager tool and they get the following message:
"SQL server doesnot exist or access denied.
ConnectionOpen(Connect())"
This is the message from SQL enterprise manager. The workstation is XP SP1.
Any suggestions
>> Any suggestions
Oh the usual:
1. Check to make sure that they are using the right username/password
combination.
2. Make sure the client is not behind a firewall that prevents comm
over the SQL Server port.
3. Make sure the same applies for the server.
4. Make sure that they are using the right authentication type (sql or
win).
5. Make sure that the server is configured to use the right auth mode.
6. Make sure that the server's host name appears in the list of
available server to connect to (see 2 & 3).
HTH
AM
|||Actually we found out that the SQL is not listening on any TCP/IP port but
only listening to named pipes. Any idea of how to make SQL Server listen to
any tcp/ip ports?
"AM" wrote:

> Oh the usual:
> 1. Check to make sure that they are using the right username/password
> combination.
> 2. Make sure the client is not behind a firewall that prevents comm
> over the SQL Server port.
> 3. Make sure the same applies for the server.
> 4. Make sure that they are using the right authentication type (sql or
> win).
> 5. Make sure that the server is configured to use the right auth mode.
> 6. Make sure that the server's host name appears in the list of
> available server to connect to (see 2 & 3).
> HTH
> AM
>
|||In Enterprise Manager, right click on the DB, select properties. In
ther dialog box, int he General tab, on the bottom click on Network
configuration. In the dialog box that pops up, in the general tab,
check to see if TCP/IP is listed on the box on the right. If not select
it from the box on the left and click on enable. YOu can edit the
properties to set up the port to listen on.
HTH
AM
|||You can use Server Network Utility to set the tcp port for the sql instance.
-oj
"Snehal Rana" <SnehalRana@.discussions.microsoft.com> wrote in message
news:E263310C-BB72-483D-BE16-DA854F38CB40@.microsoft.com...[vbcol=seagreen]
> Actually we found out that the SQL is not listening on any TCP/IP port but
> only listening to named pipes. Any idea of how to make SQL Server listen
> to
> any tcp/ip ports?
>
> "AM" wrote: