Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Take a look at CONTEXT_INFO in the Books Online. This allows you to store
up to 128 bytes of binary info that can be used anywhere in the session.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:uI04I9duHHA.4440@.TK2MSFTNGP06.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>

Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Take a look at CONTEXT_INFO in the Books Online. This allows you to store
up to 128 bytes of binary info that can be used anywhere in the session.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:uI04I9duHHA.4440@.TK2MSFTNGP06.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>sql

Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Asked and answered in .programming. Please refrain from multiposting.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:%23cNc2ceuHHA.3376@.TK2MSFTNGP04.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>|||Take a look at SET CONTEXT_INFO in the Books Online. That allows you to set
a value for the connection that is accessible anywhere in the session with
the CONTEXT_INFO function. Since the value is binary, you'll need to
convert as needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:%23cNc2ceuHHA.3376@.TK2MSFTNGP04.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>

is it possibl to avoid cursor (returned rows)

Normal set up

cursor
Select ( a bunch of crap..)

Usually I have to use the cursor to go through each record and do more crap with each record, for example I may email each user returned in the select. Is it possible to somehow use a stored procedure instead of a cursor or any other way to make this more efficient?

It's a batch job (run at night) and it'll return about 7,000 rows roughly each time so it's not terrible to the point where i need it to be super efficient, i'd just like to keep as little load on the sql server as much as possible. I assume it's one of those situations where I just can't avoid using the cursor.If your select is simple, you can define a variable to hold the table key(s) and use a while loop to walk through the table.

If your select is complex you could dump the result set of the select into a table and then use a while loop/variable to walk the table.

declare @.au_id varchar(11)
select @.au_id = min(au_id) from pubs.dbo.authors
while @.au_id is not null begin
select * from authors where au_id = @.Au_id
select @.au_id = min(au_id) from pubs.dbo.authors where au_id > @.au_id
end

granted this is simple but it ilistrattes the point

Is IIS always required for replication?

I would like to take advantage of either Merge or RDA replication over HTTP, but would like to avoid setting up IIS.

Is IIS always required to support connectivity for SQL Server Mobile replication back to a desktop SQL Server database?

Thanks, Tim

Yes IIS is very much required for Merge or RDA with SQL Server. However, setting up IIS is not a big thing as we have a "Configure Web Synchronization" wizard that is very helpful for you in this regard.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||Hi

And how setting up a replication bettween a Windows Mobile 5 Pocket PC and a Windows XP Home Edition PC when IIS can't be install on Home Edition ?

?

Thanks for help
Jo?l

|||

1) What is your backend database?

2) Can I know what is the problem in moving to Win XP Professional Edition?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||1) To replace synchronisation between Access/PocketAccess (not more supported with WM5), we use know a MSDE database (on PC) with a SQL Server CE 2.0 database (on PocketPC). The replication work fine on Windows XP Pro, but on Windows XP Home there is no IIS to do the replication.

2) Our customer have Windows XP Home Edition. I'm ok with you that's not very "professional", but we can't tell to our customer : "Hey guys, you must move your forty PC to Windows XP Pro, because our software don't run on XP Home". They will say us "bye bye".

And they have already some old PocketPC (before WM5) with this application installed and it works with XP Home ... that's a big problem for us.

Thanks for helping us

|||I have heard that we can synchronize using ActiveSync is it correct or not, because on Online Book I read we can synchronize using IIS. I need to synchronize my database on Pocket PC to my Desktop PC without using IIS (if we have the cradle why we need access to internet from Pocket PC).

And if synchronized only can work with IIS than use the old way try to send each data by client server application. Its a silly things, but thats the only way if we can only synchronize using IIS.

Is IIS always required for replication?

I would like to take advantage of either Merge or RDA replication over HTTP, but would like to avoid setting up IIS.

Is IIS always required to support connectivity for SQL Server Mobile replication back to a desktop SQL Server database?

Thanks, Tim

Yes IIS is very much required for Merge or RDA with SQL Server. However, setting up IIS is not a big thing as we have a "Configure Web Synchronization" wizard that is very helpful for you in this regard.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||Hi

And how setting up a replication bettween a Windows Mobile 5 Pocket PC and a Windows XP Home Edition PC when IIS can't be install on Home Edition ?

?

Thanks for help
Jo?l|||

1) What is your backend database?

2) Can I know what is the problem in moving to Win XP Professional Edition?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||1) To replace synchronisation between Access/PocketAccess (not more supported with WM5), we use know a MSDE database (on PC) with a SQL Server CE 2.0 database (on PocketPC). The replication work fine on Windows XP Pro, but on Windows XP Home there is no IIS to do the replication.

2) Our customer have Windows XP Home Edition. I'm ok with you that's not very "professional", but we can't tell to our customer : "Hey guys, you must move your forty PC to Windows XP Pro, because our software don't run on XP Home". They will say us "bye bye".

And they have already some old PocketPC (before WM5) with this application installed and it works with XP Home ... that's a big problem for us.

Thanks for helping us|||I have heard that we can synchronize using ActiveSync is it correct or not, because on Online Book I read we can synchronize using IIS. I need to synchronize my database on Pocket PC to my Desktop PC without using IIS (if we have the cradle why we need access to internet from Pocket PC).

And if synchronized only can work with IIS than use the old way try to send each data by client server application. Its a silly things, but thats the only way if we can only synchronize using IIS.