how to get result of a dynamic query in a proc

Say the query is 'select count(*) cnt from xyz' where 'xyz' need to be
dynamically determined. What are natural ways to receive the value of
cnt and continue to process accordingly within a proc?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Charles,
See if this helps..
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/?id=262499
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Charles Yu" <anonymous@.devdex.com> wrote in message
news:uEDA8V%23SDHA.1572@.TK2MSFTNGP12.phx.gbl...
> Say the query is 'select count(*) cnt from xyz' where 'xyz' need to be
> dynamically determined. What are natural ways to receive the value of
> cnt and continue to process accordingly within a proc?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

How to get mobile subscription to sync with dynamic snapshot

Hello Everyone,

I have a publication on a Sql 2000 (SP4) server. This publication has dynamic filtering enabled. What I want to do is create an interface which will generate a new dynamic snapshot based on filtering input from users. So far I can create the dynamic snapshot easy enough and I can see the filtered results on a Sql 2000 subscriber. However, there seems to be no way to configure a mobile database to point to the dynamic snapshot. This is easy to configure in a Sql 2000 subscription - the options are right there are on the properties page. Yet, it seems like the mobile database only points to the required unfiltered snapshot through the IIS proxy.

Is there any way to force the mobile database to use the dynamic snapshot instead?

Thanks,

Mike

You can set the HOST_NAME() function in code to anything you want on the mobile app (using the SqlCeReplication object's HostName property), and use this is your publication filter.

Hope this assists.

|||

Thanks, Eric.

I thought of that as an option. However, I would rather use a Forms or a Web app to set HOSTNAME. If I set it in the Mobile App itself, then the users are going to be forced to try to enter the filtering criteria while the device is cradled, which would be clumsy at best.

Right now, I can set the HOSTNAME using the ActiveX replciation objects in a VB app, but it only seems to work on a dynamic snapshot.

|||

Hi Mike,

I am not sure I understand - the users should be able to enter the criteria any time on the device (and save it in registry or a file). I assume that it is the users that select the hostname, not you. In the Device app you can add code to validate the entered hostname....

|||

Thanks, Erik.

That's actually a good idea. The app I am writing replaces a commercial app in which the users enter their filtering criteria in the Windows component of the system. The resulting data is then downloaded to the mobile device. I was stuck in that mindset. Yes, I could have them enter the information anytime with the understanding that the filter will not apply until the next time they synchronize with the host.

Thanks for the help!

Mike

|||If that answers your post, please remember to mark as answered, thanks.

how to get host_name in .net programming for subscribers

Hi all...
While setting up my merge replication through the replication wizard in I
read that the use of HOST_NAME() in the dynamic filter returns the
Subscriber's Machine Name. How to get host_name() value in .net code can
anyone give or suggest the code
how to write to get host_name and where i have to use it . I already defined
the host_name in sql server under dynamic filter for merge replication.
I have 50 subscribers and 1 publisher. I use merge replication. The
distributor runs on publisher. The publisher and distributer server name is
creativesoft.How do I set the dynamic filter that every subscriber would get
only his data?
yours truly,
Pramod Renikindi,
creativesoft,
australia
system.dns.gethostname()
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pramod" <pramod@.discussions.microsoft.com> wrote in message
news:AE2A49CF-B76F-4719-9D60-984E9FB6D085@.microsoft.com...
> Hi all...
> While setting up my merge replication through the replication wizard in I
> read that the use of HOST_NAME() in the dynamic filter returns the
> Subscriber's Machine Name. How to get host_name() value in .net code can
> anyone give or suggest the code
> how to write to get host_name and where i have to use it . I already
defined
> the host_name in sql server under dynamic filter for merge replication.
> I have 50 subscribers and 1 publisher. I use merge replication. The
> distributor runs on publisher. The publisher and distributer server name
is
> creativesoft.How do I set the dynamic filter that every subscriber would
get
> only his data?
> --
> yours truly,
> Pramod Renikindi,
> creativesoft,
> australia

How to get generated identity value?

Hi,
How can I retrieve generated identity value after insert a row into table in case I can identify inserted row?
(the INSERT statement is dynamic constructed and queried from data access layer, not in stored procedure)
Thanks,In the same batch, you can use @.@.identity (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp) or scope_identity() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp). Once you allow the batch to end, it gets a lot trickier to get the identity value back.

-PatP

How to get field value from a dynamic table in stored procedure

In my stored procedure,I want get some fields' value from a dynamic
table,something look like below:
PROCEDURE GetFileds
(
@.Field1 INT,
@.Field2 VARCHAR(255),
@.Table VARCHAR(255)
)
AS
--I want to get two fields from table which's name was passed by
paramerter.
SELECT
@.Field1 = FIELD1,
@.Field2 = FIELD2
FROM
@.Table
--Show @.Filed1 and @.Field2's Value
PRINT CONVERT(VARCHAR(255), @.Field1)
PRINT @.Field2
How to do that?I use EXECUTE() function,but the table name can't be
dynamic.debussy@.gmail.com wrote:
> In my stored procedure,I want get some fields' value from a dynamic
> table,something look like below:
> PROCEDURE GetFileds
> (
> @.Field1 INT,
> @.Field2 VARCHAR(255),
> @.Table VARCHAR(255)
> )
> AS
> --I want to get two fields from table which's name was passed by
> paramerter.
> SELECT
> @.Field1 = FIELD1,
> @.Field2 = FIELD2
> FROM
> @.Table
> --Show @.Filed1 and @.Field2's Value
> PRINT CONVERT(VARCHAR(255), @.Field1)
> PRINT @.Field2
> How to do that?I use EXECUTE() function,but the table name can't be
> dynamic.
My suggestion wold be not to do this. But read this article:
http://www.sommarskog.se/dynamic_sql.html
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||(debussy@.gmail.com) writes:
> In my stored procedure,I want get some fields' value from a dynamic
> table,something look like below:
> PROCEDURE GetFileds
> (
> @.Field1 INT,
> @.Field2 VARCHAR(255),
> @.Table VARCHAR(255)
> )
> AS
> --I want to get two fields from table which's name was passed by
> paramerter.
> SELECT
> @.Field1 = FIELD1,
> @.Field2 = FIELD2
> FROM
> @.Table
> --Show @.Filed1 and @.Field2's Value
> PRINT CONVERT(VARCHAR(255), @.Field1)
> PRINT @.Field2
> How to do that?I use EXECUTE() function,but the table name can't be
> dynamic.
A counter-question, why? If this is how you want to program SQL, forget
about stored procedures and send the queries from the client. That will
make your life easier.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp