Could Stored Procedure or Trigger
choose another database that is on difference engine? For example, I
have database A on engine or instance name Server1 that has a stored
procedure Stored_Proc_1 and in this stored procedure I want to access
some table on database B on engine Server2. I have username and
password on Server2.
I know that we can access different database on the same engine or
instance name, using to methods:
1. Using 3-part names ex: on the sp we say Select * from
myDb.dbo.myTable
2. Creating a string variable and build up the syntax that needed, ex:
Declare @.myQuery nvarchar(2000)
set @.myQuery = ' USE ' + @.dbname + CHAR(13) + 'SELECT * from
myTable'
Exec sp_executesql @.myQueryHi Lemune,
You can configure a linked server and then use 4 part name to access objects
on a different server.
Server_Name.DB_Name.Schema.Object
Note that there is a lot more overhead involved in this type of query
(distributed query) than accessing objects in DBs on the same server
instance.
Look in Books On Line for "Configuring linked servers"
HTH
Ami
"Lemune" <alfredosilitonga@.gmail.com> wrote in message
news:1148281973.632093.46050@.i39g2000cwa.googlegroups.com...
> Could Stored Procedure or Trigger
> choose another database that is on difference engine? For example, I
> have database A on engine or instance name Server1 that has a stored
> procedure Stored_Proc_1 and in this stored procedure I want to access
> some table on database B on engine Server2. I have username and
> password on Server2.
> I know that we can access different database on the same engine or
> instance name, using to methods:
> 1. Using 3-part names ex: on the sp we say Select * from
> myDb.dbo.myTable
> 2. Creating a string variable and build up the syntax that needed, ex:
> Declare @.myQuery nvarchar(2000)
> set @.myQuery = ' USE ' + @.dbname + CHAR(13) + 'SELECT * from
> myTable'
> Exec sp_executesql @.myQuery
>|||This problem occur because I develop a program for sms services. My
program use database to store incoming massage and out massage, If the
massage is having the right keyword it will do some task. And the task
is involved with another database. If according to myself I would
rather make my database on my client database (just adding my table to
my client database) or maybe adding my database to my client database
engine. But for some reason, my client refuses that method. So, I have
so make stored procedure on my program database engine that will
connect to my client database. I use this method so that I don't have
to change my program code each time my client use that rule :-).
Showing posts with label engine. Show all posts
Showing posts with label engine. Show all posts
Tuesday, March 20, 2012
could not start the database engine service in Sql Server 2005
Dear All,
I am not able to start the sql server 2005 database engine services...
Sql server 2005 server is installed in one machine(SERVER) and we have 10 client machine connecting to the server
When i try to start the service from the Service.msc in the SERVER i am getting the following error
===================================
Cannot connect to KEYSKILL.
===================================
An error has occurred 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) (.Net SqlClient Data Provider)
Error Number: 2
Severity: 20
State: 0
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
Could any one help me to resolve the error
Thanks in Advance
Lalitha
Look in the Windows event log, there might be a hardware failure which is described in the event log. If you are not sure what to do with the log, send them over for investigation.Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Could you post Errorlog & Event log information which will provide more info to sort out the problem.|||
HI Vidya,
This is the ERROR LOG information
2007-07-17 10:28:38.56 Server Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86)
Mar 3 2007 18:40:02
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2007-07-17 10:28:38.56 Server (c) 2005 Microsoft Corporation.
2007-07-17 10:28:38.56 Server All rights reserved.
2007-07-17 10:28:38.56 Server Server process ID is 3332.
2007-07-17 10:28:38.56 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-07-17 10:28:38.56 Server This instance of SQL Server last reported using a process ID of 6000 at 7/17/2007 10:28:33 AM (local) 7/17/2007 4:58:33 AM (UTC). This is an informational message only; no user action is required.
2007-07-17 10:28:38.56 Server Registry startup parameters:
2007-07-17 10:28:38.56 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-07-17 10:28:38.56 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-07-17 10:28:38.56 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-07-17 10:28:38.57 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-07-17 10:28:38.57 Server Detected 2 CPUs. This is an informational message; no user action is required.
2007-07-17 10:28:38.75 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-07-17 10:28:38.76 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-07-17 10:28:39.79 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-07-17 10:28:39.79 Server Database Mirroring Transport is disabled in the endpoint configuration.
2007-07-17 10:28:39.81 spid5s Starting up database 'master'.
2007-07-17 10:28:39.93 spid5s SQL Trace ID 1 was started by login "sa".
2007-07-17 10:28:39.95 spid5s Starting up database 'mssqlsystemresource'.
2007-07-17 10:28:39.98 spid5s Error: 17207, Severity: 16, State: 1.
2007-07-17 10:28:39.98 spid5s FCB:
pen: Operating system error 21(The device is not ready.) occurred while creating or opening file 'E:\sql\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation.
2007-07-17 10:28:39.98 spid5s Error: 17204, Severity: 16, State: 1.
2007-07-17 10:28:39.98 spid5s FCB:pen failed: Could not open file E:\sql\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf for file number 1. OS error: 21(The device is not ready.).
2007-07-17 10:28:39.98 spid5s Error: 5120, Severity: 16, State: 101.
2007-07-17 10:28:39.98 spid5s Unable to open the physical file "E:\sql\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf". Operating system error 21: "21(The device is not ready.)".
2007-07-17 10:28:40.00 spid5s Error: 17207, Severity: 16, State: 1.
2007-07-17 10:28:40.00 spid5s FileMgr:
tartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\sql\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
2007-07-17 10:28:40.00 spid5s File activation failure. The physical file name "E:\sql\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf" may be incorrect.
2007-07-17 10:28:40.00 spid5s Error: 945, Severity: 14, State: 2.
2007-07-17 10:28:40.00 spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2007-07-17 10:28:40.00 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
and i didnt find the event log file....
could u please tell me the path...
Regards
Lalitha
|||Examine the location
E:\sql\MSSQL.1\MSSQL\DATA\mssqlsystemresource.*"
Does the file exist ?
Does the service account have the appropiate permissions to access it ?
Does the directory exists ?
is the directory on a shared network drive ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
Friday, February 17, 2012
Could anyone attach the AdventureWorksDW to their SQL Server database engine?
Hi, all here,
I got a problem when trying to attach the AdventureWorksDW sample database to the SQL Server database engine. Error occured as:could not find row in sysindexes for database ID 21, object ID 1, index ID 1, Run DBCC CHECKTABLE on sysindexes. The sample database was downloaded from
http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en.
So could anyone help me to solve this problem tho? thanks a lot for any guidance and help.
Hi Hellen,
Try isntall from scripts.. look this URL on BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/7de6ceb0-be7a-4309-894b-a0963dc6c27b.htm
thx
Nilton
Hi, Nilton, thanks a lot for your guidance.
My problem is: during the set up of the SQL Server 2005, the sample database was not selected to be installed, thus I actully not need to reinstall it in this way. Since currently I dont have the CD available tho.
My question is: the sample database is actually available online now, and I have got the database .mdf and .ldf files, while I cant attach the database to the SQL Server then? Thanks a lot in advance for your further guidance.
|||Hi Helen,
Try the follow:
1. Create the AdvantureWorksDW on SQL Server with the same name and size that your .mdf and .ldf files. (note the path)
2. Detach the created database.
3. Locate files of the create database e replace it with you mdf and ldf files.
4. Try attach the database
I think that is should work
regards
Nilton Pinheiro
Hi, Nilton,
thanks a lot. I have tried all the ways you taught me, but it just did not work. I tried different database files as well, but none of them could be attached to my SQL Server, it must be the problem of the Server tho. But I am not sure where is indeed the problem. Really need help.
Thanks a lot.
|||Hi Helen,
You it applied the SP1 for the SQL Server 2005? If not, to apply and later tries again.
http://www.microsoft.com/downloads/details.aspx?familyid=CB6C71EA-D649-47FF-9176-E7CAC58FD4BC&displaylang=en
Otherwise try a previous version of sample (December 2005).
http://www.microsoft.com/downloads/details.aspx?familyid=9697AAAA-AD4B-416E-87A4-A8B154F92787&displaylang=en
regards
Nilton Pinheiro
www.mcdbabrasil.com.br
Hi, Nilton,
Thanks a lot. I have tried ages to fix that problem, as you taught me tring to create a database first and then detach it, then replace its .mdf and .ldf files with my .mdf and .ldf files, then trying to attach the database again with SQL Scripts in Server management studio, but only those files created on my local host can be attached, other database files from outside of my local computer wont work. The error occured as I posted above. So what can I try to attach database files from outside of my local computer then? Really need help. Thanks a lot.
Kind regards,
|||I am having the same exact problem and have been unable to find a resolution. Please post if/when you find something out.
Thanks,
Mike
Could anyone attach the AdventureWorksDW to their SQL Server database engine?
Hi, all here,
I got a problem when trying to attach the AdventureWorksDW sample database to the SQL Server database engine. Error occured as:could not find row in sysindexes for database ID 21, object ID 1, index ID 1, Run DBCC CHECKTABLE on sysindexes. The sample database was downloaded from
http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en.
So could anyone help me to solve this problem tho? thanks a lot for any guidance and help.
Hi Hellen,
Try isntall from scripts.. look this URL on BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/7de6ceb0-be7a-4309-894b-a0963dc6c27b.htm
thx
Nilton
Hi, Nilton, thanks a lot for your guidance.
My problem is: during the set up of the SQL Server 2005, the sample database was not selected to be installed, thus I actully not need to reinstall it in this way. Since currently I dont have the CD available tho.
My question is: the sample database is actually available online now, and I have got the database .mdf and .ldf files, while I cant attach the database to the SQL Server then? Thanks a lot in advance for your further guidance.
|||Hi Helen,
Try the follow:
1. Create the AdvantureWorksDW on SQL Server with the same name and size that your .mdf and .ldf files. (note the path)
2. Detach the created database.
3. Locate files of the create database e replace it with you mdf and ldf files.
4. Try attach the database
I think that is should work
regards
Nilton Pinheiro
Hi, Nilton,
thanks a lot. I have tried all the ways you taught me, but it just did not work. I tried different database files as well, but none of them could be attached to my SQL Server, it must be the problem of the Server tho. But I am not sure where is indeed the problem. Really need help.
Thanks a lot.
|||Hi Helen,
You it applied the SP1 for the SQL Server 2005? If not, to apply and later tries again.
http://www.microsoft.com/downloads/details.aspx?familyid=CB6C71EA-D649-47FF-9176-E7CAC58FD4BC&displaylang=en
Otherwise try a previous version of sample (December 2005).
http://www.microsoft.com/downloads/details.aspx?familyid=9697AAAA-AD4B-416E-87A4-A8B154F92787&displaylang=en
regards
Nilton Pinheiro
www.mcdbabrasil.com.br
Hi, Nilton,
Thanks a lot. I have tried ages to fix that problem, as you taught me tring to create a database first and then detach it, then replace its .mdf and .ldf files with my .mdf and .ldf files, then trying to attach the database again with SQL Scripts in Server management studio, but only those files created on my local host can be attached, other database files from outside of my local computer wont work. The error occured as I posted above. So what can I try to attach database files from outside of my local computer then? Really need help. Thanks a lot.
Kind regards,
|||I am having the same exact problem and have been unable to find a resolution. Please post if/when you find something out.
Thanks,
Mike
Subscribe to:
Posts (Atom)