Connect to Local

Hey Guys,

I have a SQL connection string question. In my web.config I don't know what to put for the server. I have SQL Server 2005 installed locally.

<connectionStrings>
<add name="database" providerName="System.Data.SqlClient" connectionString= "server=whatgoeshere?;database=test;uid=sa;pwd=password" />
</connectionStrings>

I'm using VS 2005.

Thanks in advance.

-Marc

Try:
(local)

|||

thanks for the reply.

I'm trying to see if the connection string works using "local" but I'm having trouble with my code.

When I add this line: Dim sqlConn as new SQLconnection the "SQLconnection" errors.

I've tried importing SQL at the top but still doesn't work. Is there some reference I need to add?

Thanks.

|||

C# is case sensitive.

It should be Dim sqlConn as new SqlConnection();

|||

I'm actually using VB.

On this line: Dim SqlConn as New SqlConnection() the "SqlConnection()" has the blue line under it indicating an error.

I'm trying to figure out if I need to Import a namespace or maybe when I installed VS2005 I did something wrong.

Thanks.

|||

Try importing:
System.Data.SQLClient

|||

augustwind thanks for the help... it works. Using local for the db and using the import you suggested.

I have a quick question that you might be able to anwer. It VS 2003 whenever I would create a new proj it would store it in the C:\Inetpub\wwwroot blah blah blah. But when I create a new proj in VS2005 it saves it somewhere else. Do you know why that is?

Thanks again.

|||

Sorry - I know where - but I don't know why - -

connect to another server from a stored proc

I have a stored proc that receives the connection string detials like servername, dbname, tablename,userid, pwd as parameters. how do i connect to that particular database on that server with the userid and pwd details ?

thanks
D.found my answer..used OPENDATASOURCE.

thanks for anyone that tried.

Connect timeout?

Hi all
I set the connection timeout property in my connection string but it dosen't
effect.
my connection time out in 30 sec..I set it to above 30
but it dosen't effect..
hi,
perspolis wrote:
> Hi all
> I set the connection timeout property in my connection string but it
> dosen't effect.
> my connection time out in 30 sec..I set it to above 30
> but it dosen't effect..
I usually get the required result(s)..
see http://www.connectionstrings.com/, "read more" frame,
Connect Timeout
-or-
Connection Timeout
, thus,
connection_string = "....;Connection Timeout = n;"
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply
|||I know how can I use it..but it dosen't have any impact in connection
string..
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:4pukiiFkoabnU1@.individual.net...
> hi,
> perspolis wrote:
> I usually get the required result(s)..
> see http://www.connectionstrings.com/, "read more" frame,
> Connect Timeout
> -or-
> Connection Timeout
> , thus,
> connection_string = "....;Connection Timeout = n;"
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.bizhttp://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> -- remove DMO to reply
>
|||hi,
perspolis wrote:
> I know how can I use it..but it dosen't have any impact in connection
> string..
have you read Stefan Berglund answer in other ng?
(BTW, please do not multipost, crosspost instead, if pertinent and required
:D)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply

connect timeout , is it works ?..

hye
i set in the connection string "connect timeout=400" and the application send error after 30sec ... why ?

*in the sql server properties , the connection time set to 0 .
*i know the run time of the query is very long , but in this case its doesn't matter :)

You are setting the timeout for the connection, that is, how long does it try to connect before timeout. If you are using a Command object, there is a CommandTimeout property that you can set to allow the command to actually run longer before timeout.

connect string problem

hello,

i use ms sql server 2005 evaluation version. i just want to get an asp
project to work. not to edit, just to work, on my local machine. for
that i use asp studio 2005. (which processes the asp files). i am
almost done, asp is processed, runs, even server connection is up.

but for hours, i cannot establish a working connection, i cannot login
to the ms sql server from the asp scripts. i try and try, nothing
works. i always get [note: this is my own translation from german, not
the actual english error msg] 'error when logging in user bla. this
user isn't assigned to any trusted sql connection.'

i am absolutely new to ms sql and asp. i myself actually develop in
php/apache/mysql. i don't have time to go deeper - i just want to run
this project, for viewing only. *please can anyone tell me what and
where exactly i have to set in ms sql server management studio 9.0*
?!!!

what i did so far:

- set the auth mode to "sql server and win";
(however the manager is connected through win auth because of that
very error)
- my conn string currently is
PROVIDER=SQLOLEDB;DATA SOURCE=DANIEL; Initial Catalog=dbo; User
ID=bla;Password=fasel; (dbo is the database name, DANIEL is the mssql
server name)
- in managemnet studio, in security>auth (?, anmeldungen), where sa
etc is, i created bla, with password=fasel; and in databases>dbo i
added bla to users (i hope so/it looks like).

i also tried integrated security =true or =sspi, instead of user and
pw, resulting in a different error: like "error while processing a
multistep ole db thing, check out the ole db status values. " i would
really like to do that - if I knew how and where! =)

sorry i dont understand all these options and dont want to - just
desperately wish to get this blo*** =) asp project to work. isnt there
any simple solution? any simple setting to do...??!! thanx such a
lot!!!!!!!

daniel

pps win xp pro sp2...perhaps I could get out if I knew where exactly to find really
exact error log?! =) ole db status and whatever... thanx again!!|||Daniel Loose (no@.reply.de) writes:

Quote:

Originally Posted by

but for hours, i cannot establish a working connection, i cannot login
to the ms sql server from the asp scripts. i try and try, nothing
works. i always get [note: this is my own translation from german, not
the actual english error msg] 'error when logging in user bla. this
user isn't assigned to any trusted sql connection.'
>
i am absolutely new to ms sql and asp. i myself actually develop in
php/apache/mysql. i don't have time to go deeper - i just want to run
this project, for viewing only. *please can anyone tell me what and
where exactly i have to set in ms sql server management studio 9.0*
?!!!
>
what i did so far:
>
- set the auth mode to "sql server and win";
(however the manager is connected through win auth because of that
very error)
- my conn string currently is
PROVIDER=SQLOLEDB;DATA SOURCE=DANIEL; Initial Catalog=dbo; User
ID=bla;Password=fasel; (dbo is the database name, DANIEL is the mssql
server name)
- in managemnet studio, in security>auth (?, anmeldungen), where sa
etc is, i created bla, with password=fasel; and in databases>dbo i
added bla to users (i hope so/it looks like).

In Management Studio, in the Object Explorer, right-click the server
itself, and select Properties. Find Security to the left. On the top
of this page, you have two choices for Server authentication. Select
SQL Server and Windows Authentication mode, the second option. You
need to restart the server for it to take effect.

Once you've done this, use the connect string with User ID and Password.

Windows authentication is usually the recommended means of authentication,
but I believe that for ASP and ASP .Net solutions, SQL authentication
is preferred.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx||||
|In Management Studio, in the Object Explorer, right-click the server
|itself, and select Properties. Find Security to the left. On the top
|of this page, you have two choices for Server authentication. Select
|SQL Server and Windows Authentication mode, the second option. You
|need to restart the server for it to take effect.
|
|Once you've done this, use the connect string with User ID and Password.
|

thank you. actually i had already done this. i now also added a login
name u1 with pass p1 to server>security>anmeldung (in english? login?
authent?), and somehow fortunately managed to let u1 appear in
server>dbo>security>users, i.e. below my database. my connstring now
is

PROVIDER=SQLOLEDB;DATA SOURCE=DANIEL;DATABASE=dbo;User
ID=u1;Password=p1

(DANIEL is the mssqlserver name, dbo the database name).

note that from the original project the connstring was

PROVIDER=SQLOLEDB;DATA SOURCE=xyz;UID=xxx;PWD=yyy;DATABASE=zzz

i also tried uid instead of user id and pwd instead of password, which
i assume are just aliases?, that is, i now use the same structure as
in the original project, the objects mentioned in the connstring seem
to exist, the sql auth seems to work since now the server manager
after restart logs in with sql auth, using u/p = u1/p1 -- but, it
still doesnt work. but, the problem appearance slightly differs now:
still, ie takes 5 minutes to "load" the page - but then, no error
message at all appears anymore, nor does the page itself - just a
blank screen. whats up??

i guess, only error logs could still help me out? where to find them?
where to get more details? thanx again!!!!|||Daniel Loose (no@.reply.de) writes:

Quote:

Originally Posted by

i also tried uid instead of user id and pwd instead of password, which
i assume are just aliases?, that is, i now use the same structure as
in the original project, the objects mentioned in the connstring seem
to exist, the sql auth seems to work since now the server manager
after restart logs in with sql auth, using u/p = u1/p1 -- but, it
still doesnt work. but, the problem appearance slightly differs now:
still, ie takes 5 minutes to "load" the page - but then, no error
message at all appears anymore, nor does the page itself - just a
blank screen. whats up??
>
i guess, only error logs could still help me out? where to find them?
where to get more details? thanx again!!!!

If you get blank page in your browser, I would suspect that there is a
problem with the HTML sent to the broweser. For help to debug that part,
you may be better of in an ASP forum.

If it takes five minutes before you get that far, I would guess this is
because it takes five minutes to run the query. You can verify in Mgmt
Studio that you are connected by running sp_who2. You should be able
to identify your process.

As for why it takes five minutes to run the query, that it's impossible
to tell with the meager information given.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanx again!

|If you get blank page in your browser, I would suspect that there is a
|problem with the HTML sent to the broweser. For help to debug that part,
|you may be better of in an ASP forum.

It's rather the five minutes, and *then* blank screen. the asp is
simply the 3 connect lines.

|
|If it takes five minutes before you get that far, I would guess this is
|because it takes five minutes to run the query. You can verify in Mgmt
|Studio that you are connected by running sp_who2.

sorry for my stupid question: *where* to run sp_who2 and how? =)

But apparently a connection is not established.

You should be able
|to identify your process.
|

Please also see my new posting where I just discovered that all user
status sql server auth are disabled, and I dont know why - since for
the server , sql server auth *is* enabled ("sql server auth and win
auth")

thanx again for you efforts!|||Daniel Loose (no@.reply.de) writes:

Quote:

Originally Posted by

It's rather the five minutes, and *then* blank screen. the asp is
simply the 3 connect lines.

Not that I know much ASP, but if all you do is to connect, I would
not expect much output...

In any case, since you have not posted any code, and have given a very
vague description of what you are doing, don't execpt very accurate
guesses.

Quote:

Originally Posted by

sorry for my stupid question: *where* to run sp_who2 and how? =)

From a query window in Management Studio.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

connect by DSN

Hi,

how can i connect MSSQL through System DSN. i know how to create DSN. but i don't know how to code the connection string.

regards,

SeeConnectionStrings.com.

Ryan

Connect 2 DropDownList and Received "Input string was not in a correct format"

I have 2 DropDownList, the first one to display the product category and the other one to display the products based on the selected category. However, when I run code I received the following error message:Input string was not in a correct format.

Please find the code below.

Thanks,

Andy.

<%@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Default.aspx.cs"Inherits="_Default" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"

DataTextField="Name"DataValueField="Name">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:Assignment2ConnectionString %>"

SelectCommand="SELECT [Name] FROM [Category]"></asp:SqlDataSource>

<br/>

<asp:DropDownListID="DropDownList2"runat="server"DataSourceID="SqlDataSource2"

DataTextField="Name"DataValueField="Name">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:Assignment2ConnectionString %>"

SelectCommand="SELECT [Name] FROM [Product] WHERE ([CategoryID] = @.CategoryID2)">

<SelectParameters>

<asp:ControlParameterControlID="DropDownList1"Name="CategoryID2"PropertyName="SelectedValue"

Type="Int32"/>

</SelectParameters>

</asp:SqlDataSource>

</div>

</form>

</body>

</html>

Assume you have a categoryID field in your Category table: You add CategoryID to the SELECT statement and changeDataValueField="CategoryID" .

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"

DataTextField="Name"DataValueField="CategoryID">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:Assignment2ConnectionString %>"

SelectCommand="SELECT [Name],[CategoryID] FROM [Category]"></asp:SqlDataSource>

<br/>

conn string

What is the impact of the differences in the following 2 connection strings:

Initial Catalog=xxx;Data Source=xxxxxx;Trusted_Connection=True;

Driver={SQL Server};Server=xxxxx;database=xxx;Trusted_Connection=yes;

To outline the differences I'm concerned with are

Missing Driver on first string

Difference in Trusted_Connection parameter (true vs. yes)

Initial Catalog vs. database parameter

See my response in the [SQL Server Data Access] forum.

You may also find this url a useful resource: www.connectionstrings.com

Confusion with date attributes and member name / value

Hi,

I'm just wondering about how the best approach is to create a date member... The member name has to be a string, so there seams to be no way that a client application can decide how to display a date (by it's regional settings). You have to do the formatting in the DSV. You can use some formatting with the member value but this isn't used by client applications by default (is there any application using it out there, yet?).

So what's the best approach here?

Thanks,

Hi Thomas,

Could Translations help you (i.e defining different captions for a given date, by locale)?

|||

Deepak,

yes that might be a solution... However this might be a problem with other datatypes as well... I just think about an attribute "Size" which might be formatted in differently in different regions (with a comma or point as decimal seperator, ...). So everything has to be done with translations? Not a very good approach...

Thanks anyway...

Conflicts and String or Binary Data would be truncated

I have an application using SQL Server 2000 and Delphi
for the front end. I have 2 SQL Server, on each side of a
WAN and 15 offline users with Laptops that sync using
Merge replication. This app has been running for about 6
months now. Lately, I am getting a few conflicts saying
that the row was inserted or updated at the Laptop, but
could not be updated or inserted at the Server - String
or Binary Data would be truncated. The Merge agent
reports no errors, saying that the session succeeded. I
have a Trigger that updates a Datestamp field, but I have
the "NOT FOR REPLICATION" line at the top. I do not get
the error using the app, only when the user replicates.
Out of 15 users, 3 are getting the message in the
conflicts screen.
Any ideas.
Thanks,
John,
2 possibilities I can think of:
are the schemas identical on publisher and subscribers? If you have done a
nosync subscription with different schemas, this could be the cause.
If the schemas are the same, then there is a known issue if you are not yet
on sp3:
http://support.microsoft.com/default...ticle%3D319258
HTH,
Paul Ibison
|||Well we are at service pack 3 (I had them go into query
analyzer)and the schemas seem to be identical. It only
seems to happen to 3 of our laptop users and no on e els
gets the errors

>--Original Message--
>John,
>2 possibilities I can think of:
>
>are the schemas identical on publisher and subscribers?
If you have done a
>nosync subscription with different schemas, this could
be the cause.
>If the schemas are the same, then there is a known issue
if you are not yet
>on sp3:
>http://support.microsoft.com/default.aspx?scid=%
2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D31 9258
>HTH,
>Paul Ibison
>
>.
>
|||OK - can you try using DATALENGTH on hte problematic row's text data to
compare to the publisher. Also, can you check that the collations are the
same between publisher and subscriber (at column level).
Regards,
Paul Ibison
|||I will look into this on Monday. I really appreciate
your input.
Thank You
>--Original Message--
>OK - can you try using DATALENGTH on hte problematic
row's text data to
>compare to the publisher. Also, can you check that the
collations are the
>same between publisher and subscriber (at column level).
>Regards,
>Paul Ibison
>
>.
>

Conflicts and "String and Binary Datawould be truncated"

I have an application using SQL Server 2000 and Delphi
for the front end. I have 2 SQL Server, on each side of a
WAN and 15 offline users with Laptops that sync using
Merge replication. This app has been running successfully
for about 6 months now.
Lately, I am getting a few conflicts from 3 of the 15
offline users saying that the row was inserted or updated
at the Laptop, but could not be updated or inserted at
the Server - String or Binary Data would be truncated.
The Merge agent reports no errors, saying that the
session succeeded. I have a Trigger that updates a
Datestamp field when the app makes an update, but I have
the "NOT FOR REPLICATION" line at the top. I do not get
the error using the app, only when the selected users
replicates. Out of 15 users, 3 are getting the message in
the conflicts screen.
Thanks for the Help.
Steve
Steve,
it could be a service pack issue. Have a look at
http://support.microsoft.com/default...icle%3D319258.
Also, you could do some logging from the trigger to a separate table, just
to confirm that it is/isn't firing during the replication process.
HTH,
Paul Ibison