How To Assign OPENQUERY Results from Linked Servers to local variables..

Hi All,

I have a problem about assigning the results of an OPENQUERY. Please check the code below:

DECLARE @.sqlString nvarchar(4000)
DECLARE @.sqlString1 nvarchar(4000)
DECLARE @.custName nvarchar(100)

SET @.custID = 2
SET @.sqlString1 = 'SELECT * FROM myTable WHERE CustID = ' + CAST( @.custID as varchar(6))
SELECT @.sqlString = 'SELECT CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
EXECUTE(@.sqlString)

--
The above code works fine but i need something like
SELECT @.sqlString = 'SELECT @.custname=CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
EXECUTE(@.sqlString)

By doing this i want to assign the CustName to the local variable @.custName.
I tried to use a temp table but in that case i was not able to specify a where clause in @.sqlString1. (i'd rather use linked_server.databasename.owner.tablename , but i read that this does not provide good performance )

Any suggestions about the problem are welcomed!
Thanks in advance,

Bahtiyar KARANLIKdeclare @.sqlString nvarchar(1000)
SELECT @.sqlString = 'SELECT @.custname=CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
EXEC sp_executesql @.sqlString, N'@.custname varchar(20) out', @.custname out

or

SELECT @.sqlString = 'SELECT CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
create table #a (s varchar(20))
insert #a
exec (@.sqlstring)

select @.custname = s from #a
drop table #a

Using the 4 part name in the query should be just s good. If you are joining with strings then make sure the servers are collation compatible so that the filter is performed on the remote server and check the query plan.
This sort of thing can give bad performance if used as a join to a local table but if you are just selecting using constants for a filter it should be OK.

How to add tabls to published article without recreating snapshot?

Hi experts:

We have 4 SQL SERVER 2000 servers linked via replication, and due to business changes we have to add one table to the published databases. And the existed database has more than 20g, recreating the snapshot and re-init replication is not allowed as the business cannot be stopped more than 1 hour. So my question is how to add tables to the published article without recreating the snapshot?

By that is not possilbe, can we publish one new article on the same database?

Thanks in advance!

Ron

Hi Ron,

If you are using merge replication, you have to regenerate the snapshot for the entire publication after you added the new article. For snapshot\transactional replication, the snapshot agent will only generate the snapshot for the new article if the immediate_sync property of your publication is set to 0. In any case, you can always put the new article in a separate publication instead.

Hope that helps,

-Raymond

How to add record thru command button in adp.

Hi,

In access database project where my tables are linked from MSSQL server, how can I add record from the FORM itself using command button. Bcz it is not like mdb files in access that you can just drag a command button onto the form and take an action like add, delete, print or find rec. I just came to know it from immediate window using following SQL line;

Docmd.RunSql "Insert......

This is ok but how it could be done thru a command button on FORM.

Thanks in advance.

With kind regards,
AshfaqueYou can use RunSQL with an ADP file. Or you can call an INSERT procedure on your SQL Server database.|||Hi blindman,

Bcz I am new to adp, can you please extend your help in more detail. I never used adp and its prodedure. How can I do it. Do I need to create a function / procedure? If so. How..Need to know badly. . Help in this regards will be higly appriciated.

Thanks a lot.
Ashfaque|||In an ADP application, no data is stored in Microsoft Access. It is all stored on the back-end server.

You can put code in the modules or forms in the ADP file, but it should be limited to affecting the behavior of the user interface. Your business logic should be implemented in stored procedures, views, and user-defined functions on your database server.

For an explanation of how to write TSQL code for SQL Server, look up VIEWS and STORED PROCEDURES in Books Online.|||Thanks again blindman,

I will check it.

With regards,
Ashfaque