September 25, 2011 at 4:55 am
Hi,
There's an option called CONTEXT_INFO() for using it as @@MYGLOBALVARIABLE.
I can use it like:
DECLARE @USERID BINARY(128)
SET @USERID = CAST(132456789 AS BINARY(128))
SET CONTEXT_INFO @USERID
Then:
SELECT CONVERT(BIGINT, CONTEXT_INFO())
gives me the 123456789 as bigint.
Everything works perfect here, I can use it whereever I want in the connection scope (this is the beauty of the CONTEXT_INFO(), assigned per connection).
But problem starts if I want to use it in a bigint field's default value. The default value has no problems, I can directly write:
CONVERT(BIGINT, CONTEXT_INFO())
but the value always be null, I try to put it in a function and use function in field's default value but nothing changes.
Thanks for your helps...
September 25, 2011 at 5:47 am
wshmstr (9/25/2011)
Hi,There's an option called CONTEXT_INFO() for using it as @@MYGLOBALVARIABLE.
I can use it like:
DECLARE @USERID BINARY(128)
SET @USERID = CAST(132456789 AS BINARY(128))
SET CONTEXT_INFO @USERID
Then:
SELECT CONVERT(BIGINT, CONTEXT_INFO())
gives me the 123456789 as bigint.
Everything works perfect here, I can use it whereever I want in the connection scope (this is the beauty of the CONTEXT_INFO(), assigned per connection).
But problem starts if I want to use it in a bigint field's default value. The default value has no problems, I can directly write:
CONVERT(BIGINT, CONTEXT_INFO())
but the value always be null, I try to put it in a function and use function in field's default value but nothing changes.
Thanks for your helps...
I think you may have a design issue here. I am not sure why you want to use context_info(). Sure it is sort of cool you can do that, but I am sure there are a lot better ways to store and save userid, which would work with a function so that the default would work on insert.
Honestly, I would always pass known values like userid into my stored proc to do the insert. I would try to not use defaults on columns even if they exist. If you know what the value should be I would set it.
You really need to be careful with sql connections. For instance if you have a web site, the sql connections are reused. In fact sql server is setup to reuse connections when the connection string is the same.
September 25, 2011 at 6:14 am
i think the issue is contextinfo is varbinary(128); you have to convert to nvarchar, and then to biginto to get the value you want.
exec
sp_help 'master.dbo.sysprocesses'
DECLARE @var VARBINARY(128)
SET @var = CAST(N'1234567890123456' AS VARBINARY(128))
SET CONTEXT_INFO @var
GO
-- Select the context information
DECLARE @sess VARBINARY(128), @var NVARCHAR(64)
SET @sess = (SELECT context_info FROM master.dbo.sysprocesses
WHERE spid = @@spid)
SET @var = CAST(@sess AS NVARCHAR(64))
print @var
SELECT CAST(context_info AS NVARCHAR(64)) AS RESULTS FROM master.dbo.sysprocesses
WHERE spid = @@spid
Lowell
September 25, 2011 at 8:49 am
The issue here is the conversion. Try the sample below to see a working version.
You need to explicitly grab the correct number of bytes from the context_info() result before converting to bigint, otherwise you get an incorrect conversion.
DECLARE @cv BIGINT
SET @cv=1234567890123456
SET CONTEXT_INFO @cv;
GO
IF OBJECT_ID('tempdb..#test1') IS NOT NULL
DROP TABLE #test1;
GO
CREATE TABLE #test1(id INT IDENTITY(1,1) PRIMARY KEY,USER_ID BIGINT DEFAULT(CONVERT(BIGINT,CONVERT(VARBINARY(8),CONTEXT_INFO()))));
GO
INSERT
#test1
DEFAULT VALUES;
GO
SELECT *
FROM
#test1;
GO
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
September 25, 2011 at 10:58 am
Thanks all for the answers, especially mister.magoo.
It worked like a charm, thanks again. Now I just run a small script when my application first start and assign userid to context than. Hundreds of tables now know the user that inserting the datas
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy