|
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: TCP Provider, error: 0 -
|
|
|
|
|
also this error was displayed too at some point!!
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provi
|
|
|
|
|
I have seen a lot of people all over the web with this same problem so I will let you know how I fixed my problem with it and maybe it will help someone!
Firstly, go into IIS Manager and click on your website or directory.. on the right hand panel there is a connection strings option - dbl click on it and it will tell you what your default local sql server is set to.. somehow mine got all screwed up so that was part of my problem.. hopefully that is ALL of your problem (some of you anyhow).. 
For me it was more complex however.. I was also trying to do personalization for anonymous users.
Thus, in my web.config there were two sets of connection strings..
one was labelled thus:
<appSettings>
<add key="ConnectionString" value="server=localhost;database=caleb.mdb;uid=;password=;" />
</appSettings>
..so I thought I was using MS Access.. but there was another Connection String attribute like this..
Somewhere else I found this kind of connection string..
<connectionStrings>
<add name="LocalhostCTP6" connectionString="Data Source=localhost;Initial Catalog=CTP6;User ID=dnn;Password=dnn" providerName="System.Data.SqlClient" />
<add name="DynamicConnection" connectionString="Data Source=localhost;Initial Catalog=CTP6;User ID=dnn;Password=dnn" />
</connectionStrings>
<appSettings>
<add key="ConnectionInfo" value="server=(local);database=Northwind;Integrated Security=SSPI" />
</appSettings>
(see this article[^]
If you arent sure which connection string is actually being used by your program, you can use this code..
[^]
..and to complicate things more, an external file can be involved too as it was in my case!!
<appSettings file="testlabsettings.config"/>
and as if things werent already complicated enough it is possible to add connection strings on the fly at run time see
http://www.kodyaz.com/articles/add-connection-string-to-web-configuration-file.aspx[^]
(luckily for me, noone was doing this as far as I know!!)
So there you have it guys.. if that doesnt solve your problem I will eat my hat!
|
|
|
|
|
I've just installed sql server 2008 but still not working.
I have a feeling that the server name is not correct. Can you tell me how to write the server name??
I have access to the server and the database on the server. When I access the database locally I write for example ("MyServer,portnumber")
but remotely, I can't seem to access it using the same server name.
|
|
|
|
|
You might try visiting ConnectionStrings.com[^], a very useful site.
I know that you are not using a connection string but the site shows many variations on the machinename/servername combination.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
I've tried everything. Still cannot connect. I used the IP address of the server but still can't connect.
Is there anything I can do on the server to enable remote connection..I've done everything I can thing of..am I missing something?
|
|
|
|
|
|
Nada Adel wrote: Do I have to download sql server 2008 on my pc to connect to the database remotely instead of sql server 2005?
yes, u r right. u can't connect to sql server 2008 via sql server 2005. i also faced this problem. and in fact sql server 2005 express can not upgraded to sql server 2008 so first u have to uninstall sql server 2005 then install sql server 2008.
|
|
|
|
|
thanks a lot, I'll install sql server 2008 and try again.
|
|
|
|
|
I have a feeling that the server name is not correct.
How can I get the correct server name to connect to?
I have access to the server, can you tell me how to get the server name if I want to connect remotely??
|
|
|
|
|
have u enabled TCP/IP Connection on the server? it's required to connect it remotely.
|
|
|
|
|
yes I did...but the default port is not 1433, it is set to another port - 1512.
So I added that port to firewall exceptions.
I just wanna make sure I did this right. I added a rule for the port 1512. It is a TCP not UDP right?
|
|
|
|
|
We have a database on a SQL 2005 server. We'd like to take an exact copy of it, on the same server (yes, I know this isn't the ideal place for a true dev system), giving the result a name such as mydbname_dev, so as to use it for test purposes.
When I right-click on the database, I see under Tasks an option called "Copy database", which sounds perfect. Only, it doesn't work. Examination of log files tells me that "The login already has an account under a different user name." OK. More Googling suggests that I need to "remove the default associated objects transfer for LOGINS". This means using the "Select Database Objects" screen. Which I can't find... and further investigation shows that it doesn't appear if you're copying to the same server.
Is it really impossible to use "copy database" to simply copy a database?
Any suggestions as to a better way of going about this? I want the new copy to be identical in every way except name to the production database, so we can use it to test out some scripts before we run them on the real thing, and I'd like the copy method to be as simple as possible, to avoid the chances of me introducing an error by typo, or by omitting some vital step.
|
|
|
|
|
Make a backup, and restore it under a new name 
|
|
|
|
|
And that'll preserve all my access control and so on? OK, thanks - will do.
|
|
|
|
|
hei all,
I'm now working in Norwegian environment where date formate is like this DD.MM.YYYY so when i write query in SQL 2000 like this
SELECT joinDate<br />
FROM myTable<br />
WHERE (joinDate>= '01.01.2009') AND (joinDate<= '01.01.2010')
it gives error
And I cant convert date format. Any ideat will be most welcome.
Thanks
Syed Shahid Hussain
|
|
|
|
|
The answer is to always use ISO 8601[^] date format when dealing with databases.
So your query becomes:
SELECT joinDate
FROM myTable
WHERE (joinDate>= '2009-01-01') AND (joinDate<= '2010-01-01')
|
|
|
|
|
Thanks.
But the problem is that from application how can i change the date format, should i wirite a function that
get dd.mm.yyyy and then
return into yyyy-mm-dd
Any suggestion from u.
Thanks
Syed Shahid Hussain
|
|
|
|
|
If joinDate is a DateTime type, then this should work:
joinDate >= CONVERT(DATETIME, '01.01.2009', 104)
This is fully documented in the SQL Server documentation, which is all available on line.
|
|
|
|
|
oh really thanks David
its great ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Syed Shahid Hussain
|
|
|
|
|
Parse the strings to DateTime and use a parameterized query.
|
|
|
|
|
thanks
Syed Shahid Hussain
|
|
|
|
|
Hi,
I am using SQl 2005 for setting up a job which has 2 steps:-
Step 1: Executes a SP which sends out result to a file
Type : T-SQL
Command :
DECLARE @cmd varchar(2000),
SET @cmd = 'osql -E -S CONDOR -s";" -w2000 -d Arion -Q"EXEC dbo.ENRSP_Applicants" -o D:\Applicants.txt'
EXEC master..xp_cmdshell @cmd
Step 2: Mailing the txt file to the user
Type : Operating System(CmdExec)
Command :
"D:\MSSQL\JOBS\EmailFile.cmd" D:\Applicants.txt aa@yahoo.com ss@yahoo.com "Applicants Report"
This works fine but the user want some modification to the current Job
* the email subject to include the date also
* Wants the result set to be dumped into a CSV file and to attach the file to the mail
* Can you also tell me how to include a message to the mail plz?
I know its easy to get this all done using a SSIS but Can these be done using an SQL Job , if so can you plz help me with it?
Thanks in advance.
Warm regards,
Payal
modified on Thursday, September 24, 2009 11:10 PM
|
|
|
|
|
I have a SQL 2005 database and a SQL 2008 database. I want a simple SQL statement that will create/copy a table and its contents from 2005 into 2008 that I can use in a C# program. I know I can export using Mgmt Studio but I want to do in a a program. I am able to perform a SELECT INTO [MyDatabase2].[dbo].[mytable] SELECT * FROM [Mydatabase1].[dbo].[mytable] when they exist in different databases in the same machine and only in the same Database Instance but how do I across multiple instances or multiple machines.
Can anyone help ?
Thanks
|
|
|
|
|
Use this to connect to other server
EXEC sp_addlinkedsrvlogin 'server', 'false', 'userLocal', 'userRemote', 'remotePassword'
Code should look like this:
SELECT INTO [server].[MyDatabase2].[dbo].[mytable] FROM [Mydatabase1].[dbo].[mytable]
modified on Friday, September 25, 2009 2:06 AM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
