Big problem connecting SQLEXPRESS within a vb net application

Hello.
I've an application that has to do a bunch of "SELECT" and INSERT into a
couple of tables. On my machine everything works fine, but if I try to
deploy the app on a VM for a test i have many probs.
The most common is "(Named Pipes Provider, error: 40 - Could not open a
connection to SQL
Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
type
'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
Catalog=Clienti;Integrated
Security=True", where "Clienti" is the DB, but i tried the string I found on
http://www.connectionstrings.com/?carrier=sqlserver2005 too.
It's being absurd...please give me an help...thanks...Ah, did you enable TCP/IP on the target server? Why are you using named
pipes? If you do, you must enable additional ports in the firewall (if there
is one).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"THE JOK3R" <a@.a.a> wrote in message
news:%23GL$eDNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> Hello.
> I've an application that has to do a bunch of "SELECT" and INSERT into a
> couple of tables. On my machine everything works fine, but if I try to
> deploy the app on a VM for a test i have many probs.
> The most common is "(Named Pipes Provider, error: 40 - Could not open a
> connection to SQL
> Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
> type
> 'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
> connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
> Catalog=Clienti;Integrated
> Security=True", where "Clienti" is the DB, but i tried the string I found
> on http://www.connectionstrings.com/?carrier=sqlserver2005 too.
> It's being absurd...please give me an help...thanks...|||"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Ah, did you enable TCP/IP on the target server? Why are you using named
> pipes? If you do, you must enable additional ports in the firewall (if
> there is one).
I both did everything...enabled TCP/IP and Remote Connection, opened port
1431 default port...|||I suggest you walk through the list of issues posted in my blog--there is a
whitepaper there that discusses these
issues.http://betav.com/blog/billva/2006/0...com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"THE JOK3R" <a@.a.a> wrote in message
news:O8v9ZKOjHHA.4936@.TK2MSFTNGP03.phx.gbl...
>
> "William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
> news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> I both did everything...enabled TCP/IP and Remote Connection, opened port
> 1431 default port...

Big problem connecting SQLEXPRESS within a vb net application

Hello.
I've an application that has to do a bunch of "SELECT" and INSERT into a
couple of tables. On my machine everything works fine, but if I try to
deploy the app on a VM for a test i have many probs.
The most common is "(Named Pipes Provider, error: 40 - Could not open a
connection to SQL
Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
type
'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
Catalog=Clienti;Integrated
Security=True", where "Clienti" is the DB, but i tried the string I found on
http://www.connectionstrings.com/?carrier=sqlserver2005 too.
It's being absurd...please give me an help...thanks...
Ah, did you enable TCP/IP on the target server? Why are you using named
pipes? If you do, you must enable additional ports in the firewall (if there
is one).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"THE JOK3R" <a@.a.a> wrote in message
news:%23GL$eDNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> Hello.
> I've an application that has to do a bunch of "SELECT" and INSERT into a
> couple of tables. On my machine everything works fine, but if I try to
> deploy the app on a VM for a test i have many probs.
> The most common is "(Named Pipes Provider, error: 40 - Could not open a
> connection to SQL
> Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
> type
> 'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
> connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
> Catalog=Clienti;Integrated
> Security=True", where "Clienti" is the DB, but i tried the string I found
> on http://www.connectionstrings.com/?carrier=sqlserver2005 too.
> It's being absurd...please give me an help...thanks...
|||"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Ah, did you enable TCP/IP on the target server? Why are you using named
> pipes? If you do, you must enable additional ports in the firewall (if
> there is one).
I both did everything...enabled TCP/IP and Remote Connection, opened port
1431 default port...
sql

best way to add column not null

Hi.

I've read up on this, and have something that works, but I was wondering if
there is anything I'm overlooking with this.

Situation is:

I have a bunch of tables.. I need to modify table2 as part of an upgrade of a
database schema.

I am using T-SQL scripts to do the trick which I'm writing myself.

I need to add a new varchar(8) column that is not null to the primary key.
I have a default I would like to use for the initial ddl modification.
I want to get rid of the default after the modification is complete, but leave
the column not null for future operations.
..

(Some if the code I'm using I took from one of Erlands posts.. hope I'm not
abusing it).
Here is the code I'm using now.. it basically adds the column 'institution_id'
as not null along with a default.
Then I jump through a couple of hoops trying to get rid of the default.
Finally I setup the primary key again.

I can only feel I'm supposed to be maybe using a constraint column with a name
to do this easier/more properly.

set @.dynamicsql = ' alter table institution_xref add institution_id
varchar(60) not null default ''' + @.default_institution_id + ''' '
EXEC (@.dynamicsql)
set @.dynamicsql = ' alter table institution_xref alter column
institution_id varchar(60) not null '
EXEC (@.dynamicsql)
select @.institution_iddefault = object_name(cdefault) from syscolumns
where id = object_id('institution_xref') and name = 'institution_id'
exec(' alter table institution_xref drop constraint ' +
@.institution_iddefault)
set @.dynamicsql = ' alter table institution_xref drop constraint
institution_xref_pk '
EXEC (@.dynamicsql)
set @.dynamicsql = ' alter table institution_xref with nocheck add
constraint institution_xref_pk primary key clustered (originalcode,
institution_id) '
EXEC (@.dynamicsql)

thanks
Jeff
Jeff KishJeff Kish (jeff.kish@.mro.com) writes:

Quote:

Originally Posted by

I need to add a new varchar(8) column that is not null to the primary key.

Ouch! That can be paintful, at least if there are foreign keys
referencing the table.

Quote:

Originally Posted by

abusing it).
Here is the code I'm using now.. it basically adds the column
'institution_id'
as not null along with a default.
Then I jump through a couple of hoops trying to get rid of the default.
Finally I setup the primary key again.
>
I can only feel I'm supposed to be maybe using a constraint column with
a name to do this easier/more properly.

Yes, if you name the constraint it's a little easier:

Quote:

Originally Posted by

set @.dynamicsql = ' alter table institution_xref add institution_id
varchar(60) not null default ''' + @.default_institution_id + ''' '

...not null CONSTRAINT my_temp_default DEFAULT ''' ...

Quote:

Originally Posted by

EXEC (@.dynamicsql)

ALTER TABLE ... DROP CONSTRAINT my_temp_default

--
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|||On Tue, 5 Jun 2007 22:28:21 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Jeff Kish (jeff.kish@.mro.com) writes:

Quote:

Originally Posted by

>I need to add a new varchar(8) column that is not null to the primary key.

>
>Ouch! That can be paintful, at least if there are foreign keys
>referencing the table.
>

Quote:

Originally Posted by

>abusing it).
>Here is the code I'm using now.. it basically adds the column
>'institution_id'
>as not null along with a default.
>Then I jump through a couple of hoops trying to get rid of the default.
>Finally I setup the primary key again.
>>
>I can only feel I'm supposed to be maybe using a constraint column with
>a name to do this easier/more properly.

>
>Yes, if you name the constraint it's a little easier:
>

Quote:

Originally Posted by

> set @.dynamicsql = ' alter table institution_xref add institution_id
>varchar(60) not null default ''' + @.default_institution_id + ''' '

>
>...not null CONSTRAINT my_temp_default DEFAULT ''' ...
>

Quote:

Originally Posted by

> EXEC (@.dynamicsql)

>
ALTER TABLE ... DROP CONSTRAINT my_temp_default

thanks. much!
Jeff Kish