Answers from Microsoft

When I set up a linked-server connection between two servers, I get the error message Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. I set ANSI_NULLS and ANSI_WARNINGS on in Query Analyzer. I'm running a script that uses the xp_cmdshell extended stored procedure with the argument

'ISQL-U -P -S{ServerB} -Q{stored procedure}'

This stored procedure performs a linked-server query from Server B to Server A. Why do I receive the error message?

The problem is that you're using isql.exe instead of the ODBC-based osql.exe. Using osql.exe will correct the problem.

The following query works fine:

SELECT
	*
	FROM
	OPENROWSET
	(

'SQLOLEDB'
	, 'DRIVER={SQL 
	Server};SERVER=(local);Trusted_Connection=yes'
	, 'SET FMTONLY OFF EXEC sp_who2'
	)

However, the following query doesn't work:

DECLARE
@STR varchar (2000)
SET
@STR = 'DRIVER={SQL
	Server};SERVER=(local);Trusted_Connection=yes'
SELECT
	*
	FROM
	OPENROWSET
	(

'SQLOLEDB'
	, @STR
	, 'SET FMTONLY OFF EXEC sp_who2'
)

Why doesn't the second query, which uses a string, work?

Usually, SQL Server doesn't allow passing parameters to OpenRowset (or OpenQuery) because SQL Server needs all the information you supply inside OpenRowset to get the meta data of the rowset and compile and optimize the query. If you use variables, SQL Server can get the values only at execution time.

You could use variables and achieve this dynamic behavior by constructing the entire query string in a variable and doing an EXEC (@str) statement. Listing 1 shows the code for the EXEC statement.

I tried to back up my databases to a file on a network share and restore the databases on the secondary server. I tried to use the Net Use command to create the share on the target server, as in

NET USE * \\PS5\C /USER:ONEDEV{domain id}
	{password} /PERSISTENT:NO

But I keep getting the message There are no available drive letters left. Enterprise Manager shows many available drives. How can I fix this problem?

Just back up your databases directly to the Uniform Naming Convention (UNC) path. SQL Server doesn't see mapped drive letters the same way a user does. You need to grant the SQL Server service account full control permissions on the share \\PS5\C, make a new share, or make the SQL Server service account a member of the administrators group on machine PS5.