Able to connect via local host but not server

I have a page that connects to SQL server. It works fine when running in local host but when I publish to the server, I get the following:

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)

Any ideas on where to start?

Thanks,

Mark

Try these two blog posts:

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

Ability to update multiple tables simultaneously via stored proc

Anyone have any ideas on how to use a stored procedure to update multiple tables simultaneously? I am updating a parent record and zero or more child records. I would like to make one stored procedure call if possible to do so. Any ideas on doing this would be appreciated. Thanks!

EverettYou can update more than one table within a stored procedure, just make sure to use BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN

Example:

CREATE PROCEDURE sp_ModifyMasterDetail
(
.
.
.
@.msg varchar(255) output
)
AS
SET NOCOUNT ON

DECLARE @.error int
, @.tfTran tinyint

--
-- Start Transaction
--
IF (@.@.TRANCOUNT = 0) BEGIN
SELECT @.tfTran = 1
BEGIN TRAN
END
ELSE
SELECT @.tfTran = 0

UPDATE tblMaster
.
WHERE ID = @.ID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

UPDATE tblDetail
.
WHERE ID = @.ID
AND SubID = @.SubID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

--
-- Check to see if an error occured during processing. If so then
-- ROLLBACK else COMMIT transactions
--
Error_Exit:

IF (@.error <> 0) BEGIN
IF (@.tfTran = 1)
ROLLBACK TRAN

SELECT @.msg = 'ERROR: Transaction failed with error ' + CONVERT(varchar(20),@.error),
END
ELSE BEGIN
IF (@.tfTran = 1)
COMMIT TRAN

SELECT @.msg = 'Transaction successful'
END

RETURN @.error
GO|||begin tran
update parent set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
update child set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
commit tran

Or you could put a trigger on the parent (or child) table or on a view of the combination - depends on the updates you want to do.|||Thanks guys! Either one of these will do the trick, except that I'm not sure how to get the data into the stored procedure! I guess I could munge it into varchar(8000), but I'm not sure that it would always be long enough. Any way of passing either an array or a recordset/cursor into a stored procedure?

Everett|||You can create a temp table on the spid, populate it then access it in the SP.

Call the SP repeated times with the values and the SP can populate a table keyed on spid.

Call the sp with comma delimitted strings with the values.

Have lots of parameters - up to the max you think you will need.

A transaction that was started in a MARS batch is still active at the end of the batch. The tran

I'm using SQL Server 2005 via ODBC, MARS enabled. I get an error when i try to start explicit transaction// Enable MARSSQLSetConnectAttr(cDbc, SQL_COPT_SS_MARS_ENABLED, (PTR)SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER)..// Connect to Datasource.SQL DriverConnect(.......// Executing begin transactionSQLExecDirect(cStmt, "BEGIN TRANSACTION",lstrlen("BEGIN TRANSACTION") );SQLState 37000

[Microsoft][SQL Native Client][SQL Server]A transaction that was

started in a MARS batch is still active at the end of the batch. The

transaction is rolled back.

Native Error 3997So what is wrong with my codethanks in advance grs

With MARS enabled, transaction that span a single request need to started using API call -- this is required in order to synchronize the "explicit transaction" state change at the connection level with request activity in the connection. See the following blog entry from Chihan for more details.

http://blogs.msdn.com/cbiyikoglu/archive/2006/11/21/mars-transactions-and-sql-error-3997-3988-or-3983.aspx

Regards

G2

A transaction that was started in a MARS batch is still active at the end of the batch. The

I'm using SQL Server 2005 via ODBC, MARS enabled. I get an error when i try to start explicit transaction// Enable MARSSQLSetConnectAttr(cDbc, SQL_COPT_SS_MARS_ENABLED, (PTR)SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER)..// Connect to Datasource.SQL DriverConnect(.......// Executing begin transactionSQLExecDirect(cStmt, "BEGIN TRANSACTION",lstrlen("BEGIN TRANSACTION") );SQLState 37000

[Microsoft][SQL Native Client][SQL Server]A transaction that was

started in a MARS batch is still active at the end of the batch. The

transaction is rolled back.

Native Error 3997So what is wrong with my codethanks in advance grs

With MARS enabled, transaction that span a single request need to started using API call -- this is required in order to synchronize the "explicit transaction" state change at the connection level with request activity in the connection. See the following blog entry from Chihan for more details.

http://blogs.msdn.com/cbiyikoglu/archive/2006/11/21/mars-transactions-and-sql-error-3997-3988-or-3983.aspx

Regards

G2

sql

A third dynamic assembly loading problem ;)

Hi!

From the dll I've installed in SQL Server I load a dll via reflection.assembly.load from the GAC . I know the dll can load the dll when I test it outside SQL Server but when I run it via a store procedure from within the SQL Server I keep getting a System.IO.FileNotFoundException.

How does this work? Isn't all dll in the GAC loaded by SQL Server? Is this impossible to achieve?

Thanks in advance

Richard Hallgren

There are only a sub-set (13 or something like that, all of them system assemblies) of assemblies that are allowed to leaded from the GAC inside of SQL Server. No user assemblies are allowed to be loaded from the GAC.

So what you need to do is to catalog the assemblies you want to load dynamically in the database, and it should work.

Niels
|||Thanks Niels!

But the dependency tree for the assemblies I like to reference is kind of big and guess I have to catalog all of those as well (that is the assemblies that the assembly I like to reference depend on). Some of them contains unmanaged code as well but I guess as long as I set the unsafe permission mode on those I should be able to catalog those as well. Right?|||

Richard Hallgren wrote:

But the dependency tree for the assemblies I like to reference is kind of big and guess I have to catalog all of those as well (that is the assemblies that the assembly I like to reference depend on). Some of them contains unmanaged code as well but I guess as long as I set the unsafe permission mode on those I should be able to catalog those as well. Right?

You basically need to catalog all assemblies that will be loaded, either directly or indirectly.

Niels

a minus b, how?

Hi, I know that under oracle you can do simple minus calculations of set
data extracted via select statements i.e.
select client from tableA
minus
select client from tableB
But how do ytou do this in sql?
I tried two methods:
method 1:
select a.client from tableA a where a.client not in (select b.client from
tableB b)
... this doesnt return any results
method 2:
select a.client, b.client from tableA a left join tableB b on a.client =
b.client
.. those results with b.client= null are the ones im interested in
I've noticed that method 1 and method 2 dont give the same results. method 1
gives no results, method 2 gives > 0 results. Whats the difference between
method 1 and 2, and my objective of setA - setB ?
any help most appreciated!
cheers, johnjohn r wrote:
> Hi, I know that under oracle you can do simple minus calculations of set
> data extracted via select statements i.e.
> select client from tableA
> minus
> select client from tableB
> But how do ytou do this in sql?
> I tried two methods:
> method 1:
> select a.client from tableA a where a.client not in (select b.client from
> tableB b)
> ... this doesnt return any results
> method 2:
> select a.client, b.client from tableA a left join tableB b on a.client =
> b.client
> .. those results with b.client= null are the ones im interested in
> I've noticed that method 1 and method 2 dont give the same results. method
1
> gives no results, method 2 gives > 0 results. Whats the difference between
> method 1 and 2, and my objective of setA - setB ?
> any help most appreciated!
> cheers, john
In SQL Server 2005 use EXCEPT instead of Oracle's MINUS.
In SQL Server 2000 use either the NOT IN method or your LEFT JOIN
method (add WHERE b.client IS NULL) or use NOT EXISTS.
The difference between your NOT IN query and your LEFT JOIN query is
almost certainly to do with NULLs in TableB. If a client in the
subquery is NULL then the result of NOT IN is always false or unknown
so no rows will be returned. Change your first query to:
SELECT A.client
FROM tableA A
WHERE A.client NOT IN
(SELECT B.client
FROM tableB AS B
WHERE B.client IS NOT NULL) ;
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132222310.964234.264250@.o13g2000cwo.googlegroups.com...
> john r wrote:
> In SQL Server 2005 use EXCEPT instead of Oracle's MINUS.
> In SQL Server 2000 use either the NOT IN method or your LEFT JOIN
> method (add WHERE b.client IS NULL) or use NOT EXISTS.
> The difference between your NOT IN query and your LEFT JOIN query is
> almost certainly to do with NULLs in TableB. If a client in the
> subquery is NULL then the result of NOT IN is always false or unknown
> so no rows will be returned. Change your first query to:
> SELECT A.client
> FROM tableA A
> WHERE A.client NOT IN
> (SELECT B.client
> FROM tableB AS B
> WHERE B.client IS NOT NULL) ;
> --
> David Portas
> SQL Server MVP
> --
>
thanks for the reply, all the info is much appreciated!
cheers, john