|
|
I have an MS SQL Server 2005 database that uses data from another MS SQL Server database created by a third-party software company. Both are in the as instance.
I wish to create a stored procedure in my database to retrieve data from the third-party database.
The following code works fine :-
SELECT TOP (100) PERCENT NLNominalAccountID AS NominalAnalysisId, AccountNumber, AccountCostCentre AS CostCentre, AccountDepartment AS Department, AccountName<br />
FROM MMSv3_DemoData.dbo.NLNominalAccount<br />
ORDER BY AccountNumber, CostCentre, Department
The MMSv3_DemoData is the third-party database.
My problem is that I wish to my the database name an input parameter as have tried the following code :-
set ANSI_NULLS ON<br />
set QUOTED_IDENTIFIER ON<br />
go<br />
<br />
ALTER PROCEDURE [dbo].[spGetNominalAccounts]<br />
@Database AS VarChar(50)<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
SELECT TOP (100) PERCENT NLNominalAccountID AS NominalAnalysisId, AccountNumber, AccountCostCentre AS CostCentre, AccountDepartment AS Department, AccountName<br />
FROM [@Database].dbo.NLNominalAccount<br />
ORDER BY AccountNumber, CostCentre, Department;<br />
END<br />
What I get when executing the stored procedure is :-
Invalid object name '@Database.dbo.NLNominalAccount'.
Any help apprceiated.
Thanks
-- modified at 4:51 Monday 15th October, 2007
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
You have to build a T-SQL statement dynamically inside your stored procedure and then execute it using sp_executesql.
DECLARE @sql nvarchar(100)
SET @sql = 'SELECT * FROM ' + @Database + '.dbo.FOO'
EXEC sp_executesql @sql
|
|
|
|
|
The answer is always simple, when you know how.
Thanks alot, big help
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
I am getting an error in Replication between SQL Server 2005 and SQL Express when connecting to . "Connecting to Subscriber". The error message is given below. "Unable to complete login process due to delay in opening server connection". Why? Do we need to increase the logintimeout for "Connecting to Subscriber". How can we increase it?
Message
2007-10-15 06:37:58.398 Startup Delay: 8503 (msecs)
2007-10-15 06:38:06.898 Connecting to Distributor 'ACR-MANGO'
2007-10-15 06:38:06.976 Initializing
2007-10-15 06:38:06.976 Parameter values obtained from agent profile:
-bcpbatchsize 2147473647
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 1
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 5000
-querytimeout 1800
-skiperrors
-transactionsperhistory 100
2007-10-15 06:38:06.991 Connecting to Subscriber 'ACR-ANJILI\SQLEXPRESS'
2007-10-15 06:38:46.133 Agent message code 20084. The process could not connect to Subscriber 'ACR-ANJILI\SQLEXPRESS'.
2007-10-15 06:38:46.148 Category:NULL
Source: Microsoft SQL Native Client
Number: 08001
Message: Unable to complete login process due to delay in opening server connection
2007-10-15 06:38:46.148 The agent failed with a 'Retry' status. Try to run the agent at a later time.
|
|
|
|
|
I use SLQ server 2000.
I have a database but i can not log in to it. It is suspect.
Then I copy .mdf and .ldf files, then i deleted database.
Now i want to repair it. Can repair it? How do repair it?
Thanks
|
|
|
|
|
Can you do an sp_attachdb to attach the database?
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I has ever attach the database but not complete.
|
|
|
|
|
I have SQL Server Express 2005 running on Windows Server 2003. I installed SQL Express using pretty much all the default options (including Windows Authentication). The machine name is SQLBOX.
I have Visual Studio 2005 on my primary development machine; its name is DEVBOX.
I'm not in a domain environment. The account I use to log onto DEVBOX doesn't exist on SQLBOX, and vice-versa. That is, if I try to access a share, for example, on SQLBOX when I'm on DEVBOX, I'm prompted for a username/password (which I then provide as "SQLBOX/username" and that account's password). That's never been a problem.
I want to access a database on SQLBOX from within VS2005 running on DEVBOX. If I go to Server Explorer, select Data Connection, then Add Connections, I can see that SQLBOX is shown in the Server Name dropdown (meaning, DEVBOX managed to correctly enumerate all instances of SQL Server on my network).
The problem, at this point, is that if I leave the radio button on "Use Windows Authentication", I have no option to enter proper Windows credentials ("SQLBOX/username"). The "Select a database name" dropdown remains blank (since, I'm assuming, VS can't enumerate the databases on SQLBOX if it can't log on), and if I click either the OK or Test Connection buttons, VS simply returns with a timeout. I'm never prompted for a username/password, which fails because, I assume, DEVBOX/username is supplied by the system.
Does a Windows account with a matching name/password have to exist on both machines in such a scenario? Or do I have to change SQL Express so it uses SQL Server Authentication?
I've connected to other remote SQL machines with VS many times in the past, but I've always had matching accounts that existed on all machines. This is not the case here, so I'm assuming that's why this is now failing...
Incidentally, there might be something else at work here, because frankly I would've expected a more appropriate authentication error message instead of a timeout, but I've already gone through all the settings I could think of to allow remote access to the database, including using the Config Manager tool to allow both Named Pipes and TCP/IP...
-- modified at 17:45 Sunday 14th October, 2007
|
|
|
|
|
Windows authentication is really only intended for use within a domain. The fact that it also works in a workgroup where the username and password are the same on client and server is a bit of an accident, coming from NTLM's workgroup file sharing support. You cannot specify a password for Windows authentication.
You will have to allow SQL Server authentication on the server. The reason it is generally not recommended is that the passwords are sent in the clear, so can be seen with a network analyzer. It is possible to secure the channel so everything, commands, passwords and retrieved data, are all encrypted - for this you need an SSL certificate on the server. See 'Encrypting Connections to SQL Server' in Books Online (e.g. look up 'SSL' in the Index). You then need to set Encrypt='true' in the connection string.
To enable SQL Server authentication appears to need a registry change, if you don't have Management Studio available. It may be easier to rerun setup, or to download SQL Server Management Studio Express from Microsoft.
Also, you should be aware that remote connections are disabled by default in SQL Server 2005 Express and Developer Editions. To enable them, go to the Configuration Manager tool and enable TCP/IP. You can also use SQL Server Surface Area Configuration but from memory that's not available for Express Edition. This may explain why the timeout occurs rather than getting a 'login failed' error.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
This is exactly the sort of answer I was after. Thank you 10^6 times. 
|
|
|
|
|
Hi all again!
I am designing a web site ready to use both SQL and Ms Access DB.
I have a relation between say Picture and Category. When I delete a category, all the related pictures category will set to NULL. I have no problem with SQL, I set SetToNull in SQL but when I setting up relation in MS Access, I can't find any option, It raise an error when I want to delete category, how I can make MS ACC to set that field to NULL while deleting that record?
Thanks.
|
|
|
|
|
You asked this question in the ASP.NET forum on October 7th and I answered it. You shouldn't post the same question twice - continue the previous thread.
Your previous thread can be found here[^].
-- modified at 10:03 Monday 15th October, 2007
Paul Marfleet
|
|
|
|
|
OleDbConnection objConnection = null;
OleDbCommand objCmd = null;
String strConnection, strSQL;
strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
strConnection += @"Data Source=C:\use.mdb";
//strConnection += @"Data Source="+MapPath("use.mdb");
// Create and open the connection object
objConnection = new OleDbConnection(strConnection);
objConnection.ConnectionString = strConnection;
objConnection.Open();
// set the SQL string
strSQL = "INSERT INTO user (username , password ) " +"VALUES ( '1' , '9' )";
// Create the Command and set its properties
objCmd = new OleDbCommand(strSQL, objConnection);
// execute the command
objCmd.ExecuteNonQuery();
///////////////////////////////////////////////////////////
Please do tell me the solution for following Exception
/////////////////////////////////////////////////////////
An unhandled exception of type'System.Data.OleDb.OleDbException' occurred in system.data.dll
|
|
|
|
|
The database exceptions are pretty generic - to work out what's wrong, you'll need to look at the Message property of the exception. Then look that up in your database provider's documentation.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Hi ,
Could some one tell me what are the advantages of sql server 2005 batch jobs,
what are the advantages of a batch job and does they relate to ssis packages ,
any reply would be highly appreciated
|
|
|
|
|
Please define the term 'batch jobs'.
Do you mean SQL Server Agent jobs?
|
|
|
|
|
Hi all ,
I have a .net app working with sql server 2005 and this is a server-based app , I want that my app be run on windows server 2003 and the clients be run on windows xp, and I use Windows authentication mode , because it is more secure , How can I deploy my app to do these?
|
|
|
|
|
Presumably you mean that your client application itself connects directly to the database, there is no server application involved?
If you want to use Windows Authentication, you will need to ensure that Windows can authenticate the users. This means either that the clients and server are all in the same domain (or in the same Active Directory domain tree, forest, or with appropriate inter-forest trusts), or that they have the same usernames and passwords on both client and server (this is a quirk of Windows Authentication coming from workgroup file sharing). If you don't have this, you may well find SQL Server authentication easier. The main problem with that is that the passwords are sent unencrypted in the channel, and so can be observed by someone with a packet sniffer. It also means that the password is typically stored in a configuration file - anyone with access to the file can get the password.
You can solve the first problem by setting up an SSL certificate on the SQL Server and setting the Encrypt property in the connection string to true. That encrypts the entire conversation between client and server.
The second problem can be solved by encrypting the connection string in the file. See for example SectionInformation.ProtectSection in the .NET SDK documentation.
You will then need to set the users up with logins to SQL Server, and map those logins to users in the database. After that, you need to give the users permissions to manipulate the database objects, or make them members of appropriate database roles. The default for a new user is to be a member of the public role which has no permissions to act on anything. You can create a SQL Server login for a Windows security group if you prefer, which simplifies things a lot.
It's hard to do a full treatment of SQL Server security in such a small space. I had a go here[^].
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thanks for your help , but :
1)this .net app is going to run in a workgroup environment , It is wrong that all of the clients connect to sql server using same Sql server User?Or I should create a user for each client?
2)How can I Install SSL?
|
|
|
|
|
i m unable to insert record in table using OLeDb follwoing command.it is drawing unhandled expection,please do tell me the easiest way
///////////////////////////////////////////////////////////
OleDbConnection m_Connection = new OleDbConnection(Connection);
m_Connection.ConnectionString=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:use.mdb";
m_Connection.Open();
Connection ="INSERT INTO user =(1,1)";
|
|
|
|
|
3 things:
1. Don't post your questions twice.
2. Your code won't compile. The last line references a variable that hasn't been declared.
3. You should learn basic SQL syntax before attempting anything more complicated. This site[^] should help.
Paul Marfleet
|
|
|
|
|
You should create a command object, not pass the query to the constructor of the connection object.
also the syntax of the SQL statement to insert is not true. It should be something like:
INSERT INTO user values (1,1)
that if the user table has just 2 columns.
I think you really need to read about ADO.NET this can be a good place to start.[^]
|
|
|
|
|
i m unable to insert record in table using OLeDb follwoing command.it is drawing unhandled expection,please do tell me the easiest way
///////////////////////////////////////////////////////////
OleDbConnection m_Connection = new OleDbConnection(Connection);
m_Connection.ConnectionString=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:use.mdb";
m_Connection.Open();
Connection ="DELETE FROM user WHERE username = @s";
|
|
|
|
|
using System.Data.OleDb;
public class Database
{
OleDbConnection connection = new OleDbConnection();
OleDbCommand command = new OleDbCommand();
public void openConnection()
{
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\use.mdb";
connection.Open();
}
public void closeConnection()
{
if (connection.State != 0)
{
connection.Close();
}
}
public void executeNonQuery(string query)
{
command.Connection = connection;
command.CommandText = query;
openConnection();
command.ExecuteNonQuery();
closeConnection();
}
}
public class Test
{
Database ObjDatabase = new Database();
string strQuery;
objDatabase.openConnection();
strQuery = "DELETE FROM User";
objDatabase.executeNonQuery(strQuery):
objDatabase.closeConnection();
}
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
