DATATYPE PROBLEM(cross)

I have a column in my table BizdekiFiyat . The datatype = float length =8
(to save money values).. It is impossible to change these attributes for
some reasons.
It has records like This
BizdekiFiyat
110
24
29.5
31.35
I use Vb.Net . I use ExecuteReader To select values from my db..
After first attemp
Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
IT returns
110
24
295
3135
Dim BizdekiFiyat As String
It returns right results.
110
24
29.5
31.35
There is a problem with decimal records when i want to evaluate this
values..
For example
Dim BizdekiFiyat As String
BizdekiFiyat = BizdekiFiyat * 1.05
It is supposed to be
29.5 * 1.05 =30.975
31.35*1.05=32.9175
but it returns
309,75
3291,75
How can i solve this problem ?"Savas Ates" <in da club> wrote in message
news:OEhAfpwLGHA.648@.TK2MSFTNGP14.phx.gbl...
>I have a column in my table BizdekiFiyat . The datatype = float length =8
>(to save money values).. It is impossible to change these attributes for
>some reasons.
>
> Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
> IT returns
> 110
> 24
> 295
> 3135
Integer datatype will always truncate your decimal fraction values.
I've had data dimension problems trying to use the Decimal datatype for
holding (SQL) decimal data returned through parameters using MS's EntLib
DAAB. I resolved this by using .NET's Double datatype (though I'd prefer to
know why .NET's decimal gave me the problem in the first place).

> Dim BizdekiFiyat As String
> It returns right results.
Because the value is being represented and stored as a string (just like
typing into a textbox), not a numeric type, so...

> There is a problem with decimal records when i want to evaluate this
> values..
> For example
> Dim BizdekiFiyat As String
> BizdekiFiyat = BizdekiFiyat * 1.05
> How can i solve this problem ?
You're expecting .NET to intelligently convert your datatypes for you, which
it is valiantly trying to do. You should consider setting Option Strict on
(Tools | Options | Projects | VB Defaults) to prevent loose data typing and
late binding. You should strongly type your datatypes as a matter of god
practice. When you need to convert datatypes, dothis explicitely using
CType(sourceObj, targetType), or the shorthand versions such as Cint(value),
CDbl(value), etc.
As for your calculations: e.g. using Double to store your values, create a
function which you'll call when necessary to do your calculations:
private function MultiplyBizdekiFiyat(Byval origValue as double, Byval
MultiplyBy as double) As Double
'Perform the calculation
MultiplyBizdekiFiyat = origValue * MultiplyBy
'Return the value to the calling method.
return MultiplyBizdekiFiyat
end function
Hope that helps
Al

DataType converting, char to float

I need to convert Data Types in a table where all fields are char, so that the ones containing numeric values is decimal or float.
I've tried this query:
SELECT Ansilumens, CAST(RTRIM(Ansilumens) AS FLOAT) FROM dbo.projector
And I get the error: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Error converting data type varchar to float.

Does anyone how I can convert chars to decimals or floats? When the tabel already contains lots of data, without loosing it..

Thanks.--Find bad values
select YourCol
from YourTable
where isnumeric(YourCol)=0

/*
Then choose, what numeric type do you want.
Float is usually used in science for storing inaccurate very high range values. Stored as single/double real binary.
Int-like datatype for integers (without decimal places). Stored as sign fixed binary.
Numeric for precise calculations, large nums (10^36), fixed decimals. Stored as sign nibble (2 decimal digits in one byte)
rounded up to 1+4n bytes.
Money is fast predefined numeric with special rounding and 4 decimal places. Stored as sign fixed binary.
*/

--if your nums are really large, try FLOAT(53) or NUMERIC(38) or ballanced NUMERIC(32,16)

--Sending test values would be your benefit.|||If you plan on using numeric or decimal data types you really have to know what is the largest numeric value you will use - not just to the left of the decimal but to the right as well (your precision and scale) otherwise you will receive an arithmetic overflow error. Float has the same issues with precision.

The following post discusses this problem:

post (http://dbforums.com/showthread.php?threadid=554550)|||Well, my problem (after some more testing) seems that everything I try to insert from VB6 is impossible to get into the db unless the datatype in the db is char, varchar, timestamp or text that is.
This is the code:

Dim InsertQuery As String
Set oConn = New Connection

Set oRec = New Recordset
oRec.Open InsertQuery, oConn

is my problem related to that everything in InsertQuery, is of course, a string when the SQL query executes?

-jr|||You need to post an actual insert statement and the data types as defined by the table.|||InsertQuery = "INSERT INTO black (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', '" & Now & "', '432.95');"

Is a test-query I use

And the table is defined as follows:
Part - char - size 11 - Do not allow nulls
Serial - char - size 8 - Do not allow nulls
[Time] - datetime - Do not allow nulls
S0 - decimal - Precision: 2 - Do not allow nulls|||Your post has 2 main problems
1. You do not specify scale for decimal. Minimum numeric(5,2) for this insert.
2. You use VB Now() function, which is setting-specific. Use getdate() on server.

create table testNum(
Part char (11) not null
,Serial char(8) not null
,[Time] datetime not null
,S0 decimal(15,2) not null
)
GO
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), '432.95')
--faster
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), 432.95)|||What is the most numbers to the left of the decimal and to the right for the column S0 ? Once you know this, create that field with a precision of the 2 maximums combined and the scale of the maximum of the length to the right of the decimal.|||Thanks rnealejr and ispaleny... it was clearly my SQL knowledge (or the lack of it) that was the problem. Works great now, though. Thanks again.|||Just one more thing..
When the value is (e.g) 2300.00 the .00 is not showing in the db, how to make the decimals show even though they are only zeroes (0)?|||This will happen in enterprise manager - run a query in query analyzer and you will see them.

Datatype conversion strangeness

Hi all,

After more headbanging and cursing than an entire Metallica audience, I have finally deduced how to store and calculate the values of a stack storing RPN calculations using a recursive stored procedure. But there is a new conundrum. Originally, to test the above, I was using fairly simple values -- 10, 20, +, 3, /, etc. But the real data is likely to include values with at least a couple of decimal places -- these have been configured using the money data type. Nevertheless, in my actual stack table the value needs to be specified as a varchar. However, as soon as I start sending in 0.68 as a varchar to my stored procedure, it gives an error.

Can anyone offer any light on this?

(The error message number is 245, data type conversion error. But this is inexplicable as I have commented out any conversion code in order to get to the root of the problem. As far as I am aware this value "0.68" is always being passed around as a varchar.)

Thanks.

But you might be doing some operation that involves say concatenation of numeric value with varchar or vice versa. This will result in implicit conversion of the value to the data type with highest precedence and depending on the value you can get run-time errors. It is hard to tell what is wrong without looking at some repro. You should however avoid doing these type of operations since the potential for misinterpretation or run-time error is large. If you are dealing with numeric values then specify the data type of the variables accordingly.|||Umachandar,

Many thanks for your reply. The reason I'm doing it this way is that I'm modelling a stack. Also some values are actually the tablename.objectname id of values in a read-only section of the database, so the <value> part of this stack has to be flexible -- hence varchar. Surely the potential for runtime error is not that great, as I only want to be able to handle values such as the following:-

table1.id1 (this gets parsed by the stored procedure into values such as follow...)
0.68
0.43
10
20
+
*

|||Again, it is hard to tell what is wrong without looking at some piece of code that reproes the error. Alternatively, you could run profiler trace with statement level events to see which statement is failing (you can even get this from the error message header which will point to the line that generated the error).|||OK let me try to explain as fully as possible.

I have a table NEO_FORMULA_STACK (f_id(int), position(int), val(varchar(100)), valname(varchar(100)), units (nvarchar(12).

I have a stored proc, which accepts f_id (as above) as a parameter. Based on this f_id it then loads the 'stack' of values into table TEMP_STACK (pos(int),val(varchar(100).

What I mean is that there are multiple formulas in the original table each with their own 'stack' of values (arranged in Reverse Polish Notation format, i.e. 10,20,+,3,/ [a way of writing ((10+20)/3).

Now, in the course of the load from NEO_FORMULA_STACK TO TEMP_STACK conversions such as tablename.objectname being evaluated to e.g. 0.098 might very well occur. But my impression is that if NEO_FORMULA_STACK has a val column of VARCHAR(100) datatype, and so does TEMP_STACK, then there should be absolutely no problem going from 'tablename.objectname' to '0.000918'. These are BOTH completely valid strings. Bear in mind that NO calculation has taken place at this point, I have simply loaded NEO_FORMULA_STACK for a particular f_id into TEMP_STACK. Attempting to pop off a value like '0.0098' into a temporary variable of varchar(100) is throwing error: 245, converting from varchar to int at line 77 of the stored proc:-

I can't give you the exact line, because one sp is calling another so the line it cites as being in the wrong is a comment.

Please let me know if you need more detail, or if you have any clues based on this information. Many thanks!
|||"But you might be doing some operation that involves say concatenation of numeric value with varchar or vice versa. This will result in implicit conversion of the value to the data type with highest precedence and depending on the value you can get run-time errors."

I assure you I am doing no concatenation or anything like that. I removed all such code in order to try and attack the problem. I am loading one varchar into another varchar, then popping that varchar off into a temporary variable of type varchar -- and it is that pop which is throwing the error, saying illegal conversion, when as far as I can see they are ALL varchar(100).
|||Your explanation doesn't really help to clarify the exact operations being done in the SP. As I said, this error happens due to implicit conversions of values of different types or initialization of variables/parameters using value of different data type. Add additional debug statements to see the results before the input so you can identify the issue. Or create a simpler repro that will help you identify the problem due to implicit conversion.

Datatype Conversion during insert

I am performing an insert inside a stored procedure. In the values list , I am doing some data converision. I am getting an compile error like:

Server: Msg 170, Level 15, State 1, Procedure premiumstage_to_fact, Line 303
Line 303: Incorrect syntax near '='.
The code is:

INSERT INTO table-name( c1,c2,c3,c4)

VALUES
(@.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3)

Where am I going wrong? Where do I do the conversion? The comma after END AS @.variable, Is that syntax right?

Please advise.

ThanksUse SELECT instead of VALUES.

INSERT INTO table-name( c1,c2,c3,c4)

SELECT @.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3|||snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.|||Originally posted by ms_sql_dba
snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.

to ms_sql_dba:

You are right - it works for 2000. I am not sure about 7. May somebody test it and reply.

create table test13(id int,code varchar(10))
go
insert test13 values(1,case when 1=1 then 1 else 0 end)
insert test13 values(1,'4'+'5')

datasource question

im using this datasource to extract values from a database.

i simply want to extract the values for ratingsum and ratingcount to populate variables so as to be checked within a code loop.

how do i do this? in classis asp would be something like <%=rsRecords(RatingSum)%
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
ProviderName="<%$ ConnectionStrings:MyConnectionString.ProviderName %>" SelectCommand="SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE Itemid=100">
</asp:SqlDataSource>

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim dv As System.Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

If dv.Count > 0 Then
Dim myvalue = dv(0).Item("RatingSum")
Dim myvalue2 = dv(0).Item(1) 'orItem("RatingCount")
Label2.Text = CType(myvalue, Int16) & "*************Count=" & CType(myvalue2, Int16)

Else
Label2.Text = "not data!"

End If

End Sub

This way you can send the value back from your code. In this case from the label.text.

You can also use this section within your select parameter to bind your where clause parameter to a textbox control instead of the hard-coded value.

<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="itemId" PropertyName="Text" Type="int16" />

</SelectParameters>

dataset to sql server

How to transfer data in a table in dataset to a table in sql server ?

i.e. I want to transfer the table values in excel to a table in sql server. I have populated the excel table in a dataset. I just want to know how to transfer from dataset to sql server.

Thanks

You can use the INTO clause to move the data into SQL Server because it creates a new table on destination or use the code below to load the data into SQL Server then load that table into a dataset. The reason is Excel since XP/2003 can use sql statements. Hope this helps.

/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

http://msdn2.microsoft.com/en-us/library/ms188029.aspx

|||

I cant understand ur code. Kindly explain it.

I want to export the data in excel to sql server table. I have fetched the data from excel and stored in dataset. Then i want to export it to sql server. How to do that?

|||That code is to insert Excel into SQL Server table before you put the table into a dataset, if you don't want that then use the link and create a SELECT INTO statement it create a table for you and now that I think about it you can create a SQL Server Table with ExecuteNonQuery in ADO.NET run a search in the forums you should find some links with code. Hope this helps.

dataset query based on a global parameter

I need to create a report that displays data based on the userid. all the
records have a userid field filled with many userid values. how can I use the
global paramerer userid in the criteria for my dataset. so when a user run
the report olny records with his or her userid are showncreate a parameter with default value the global parameter. then use that
parameter in the query. ofcourse you can't use caching for those reports
"DJJIII" wrote:
> I need to create a report that displays data based on the userid. all the
> records have a userid field filled with many userid values. how can I use the
> global paramerer userid in the criteria for my dataset. so when a user run
> the report olny records with his or her userid are shown|||You do not have to have a 1:1 mapping between query parameters and report
parameters. Doing it the way suggested here means you have to muck around
with hiding it (since you don't want people to change it).
Have your query parameter, let's call it @.UserID. RS will automatically
recreate a report parameter called UserID but we won't use it. Click on the
..., go to parameters. Map the query parameter @.UserID to the global . The
parameter dialog box has a name and value columns. On the value side switch
it to expression. This brings you to the expression builder where you can
pick the User!UserID global variable. One thing to note, this variable has
the domain as well as the user id, so if you don't want this you will have
to strip it off.
Now, go you your layout, Report->Parameters and delete the now unneeded
UserID report parameter.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:04C477F1-17E5-47C5-BB11-9EF31278F1CE@.microsoft.com...
> create a parameter with default value the global parameter. then use that
> parameter in the query. ofcourse you can't use caching for those reports
> "DJJIII" wrote:
>> I need to create a report that displays data based on the userid. all
>> the
>> records have a userid field filled with many userid values. how can I use
>> the
>> global paramerer userid in the criteria for my dataset. so when a user
>> run
>> the report olny records with his or her userid are shown|||great tip, that is indeed a lot more practical
"Bruce L-C [MVP]" wrote:
> You do not have to have a 1:1 mapping between query parameters and report
> parameters. Doing it the way suggested here means you have to muck around
> with hiding it (since you don't want people to change it).
> Have your query parameter, let's call it @.UserID. RS will automatically
> recreate a report parameter called UserID but we won't use it. Click on the
> ..., go to parameters. Map the query parameter @.UserID to the global . The
> parameter dialog box has a name and value columns. On the value side switch
> it to expression. This brings you to the expression builder where you can
> pick the User!UserID global variable. One thing to note, this variable has
> the domain as well as the user id, so if you don't want this you will have
> to strip it off.
> Now, go you your layout, Report->Parameters and delete the now unneeded
> UserID report parameter.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Antoon" <Antoon@.discussions.microsoft.com> wrote in message
> news:04C477F1-17E5-47C5-BB11-9EF31278F1CE@.microsoft.com...
> > create a parameter with default value the global parameter. then use that
> > parameter in the query. ofcourse you can't use caching for those reports
> >
> > "DJJIII" wrote:
> >
> >> I need to create a report that displays data based on the userid. all
> >> the
> >> records have a userid field filled with many userid values. how can I use
> >> the
> >> global paramerer userid in the criteria for my dataset. so when a user
> >> run
> >> the report olny records with his or her userid are shown
>
>

Dataset manipulation questions

I have a dataset that is created by a Stored Procedure that is
cumulative of all values that I need to report against. The problem is
that I need DISTINCT results of certain columns. To do it within the
sproc would take a prohibitively long time and it would be ideal if I
could do it within the table object on the layout tab. I have played
around with the filtering capabilities in the table properties, but
they are very limited. Is there any way I can produce a DISTINCT list
within the results of a dataset?
Thanks in advance for any help or guidance.
...ChrisMy suggestion is to have your result in the stored procedure to be put into
a temp table. Have an extra field to store the distinct results and then
update the field with the distinct values. I guarantee you this will be
faster than anything RS can do (plus I don't know how to do what you want in
RS).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<craniumgroup@.gmail.com> wrote in message
news:1116866722.102195.305620@.g14g2000cwa.googlegroups.com...
>I have a dataset that is created by a Stored Procedure that is
> cumulative of all values that I need to report against. The problem is
> that I need DISTINCT results of certain columns. To do it within the
> sproc would take a prohibitively long time and it would be ideal if I
> could do it within the table object on the layout tab. I have played
> around with the filtering capabilities in the table properties, but
> they are very limited. Is there any way I can produce a DISTINCT list
> within the results of a dataset?
> Thanks in advance for any help or guidance.
> ...Chris
>

Dataset

When I make a dataset as parameter how can I make an ALL value which
doesn't
select the single values but all values instead.
Like
Month: all, jan, feb, etc.
when I choose all it's supposed to ignore it in the where clause, I tried
with case but it didn't work out, any clues on this one ?
Jack
--
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 45077 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dkgive it a null value and adapt your query so it says : "SELECT ... WHERE
month LIKE '@.parameterMonth'"
"Jack Nielsen" wrote:
> When I make a dataset as parameter how can I make an ALL value which
> doesn't
> select the single values but all values instead.
> Like
> Month: all, jan, feb, etc.
> when I choose all it's supposed to ignore it in the where clause, I tried
> with case but it didn't work out, any clues on this one ?
> Jack
>
> --
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at fÃ?Â¥ 45077 spam-mails.
> Betalende brugere fÃ?Â¥r ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>|||Similarly...
"Select * from mytable where (col1 = @.parm or @.parm is null)"
This can also be used w/ multiple parms
"Select * from mytable where (col1 = @.parm or @.parm is null) and (col2 =@.parm2 or @.parm2 is null)"
I've used this method in many queries/procs to avoid using dynamic sql...
Cheers!
--
Ben Sullins
"Koen" wrote:
> give it a null value and adapt your query so it says : "SELECT ... WHERE
> month LIKE '@.parameterMonth'"
> "Jack Nielsen" wrote:
> > When I make a dataset as parameter how can I make an ALL value which
> > doesn't
> > select the single values but all values instead.
> >
> > Like
> >
> > Month: all, jan, feb, etc.
> >
> > when I choose all it's supposed to ignore it in the where clause, I tried
> > with case but it didn't work out, any clues on this one ?
> >
> > Jack
> >
> >
> > --
> > Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> > Den har indtil videre sparet mig for at fÃ?Â¥ 45077 spam-mails.
> > Betalende brugere fÃ?Â¥r ikke denne besked i deres e-mails.
> > Hent gratis SPAMfighter her: www.spamfighter.dk
> >
> >
> >

datalength doubling values

Am I missing something? I'm trying to return the size of the data contained in a varbinary(max) column, however it appears that the value being returned is double what it should be. Is this normal, or is there something else I need to do?

Thanks,

Devin

Edit: I'm not discounting that my data may be weird, but I wanted to cast my net as wide as possible.

Perhaps the field contains trailing blanks...|||

Why do you think this is double? Can you post a snippet of code that doesn't seem to make sense? Like:

set nocount on

declare @.test varbinary(max)

set @.test = 0x12

select datalength(@.test)

set @.test = 0x1234

select datalength(@.test)

Returns:

--

1

--

2

|||

Ignore this. I was using compression on the streams as I was putting them into the column. Apparently the framework's GZipStream class has a bug that causes it to mishandle files that already have compression in them (video, jpg, pdf) so that they end up larger.

DATALENGTH was reporting the correct size for the contents of the column.

Thanks,

Devin

Data-driven subscription with multi-select parameter

I'm trying to create a data-driven subscription with the ability to pass
multiple values using a multi-select parameter. Is this possible, and if so
how does one store the data in the database?
Thanks,
JoshI need this ability also, and according to this post, it is really not
possible. The solution proposed here is not feasable for my needs as I have
potentially from 1 to many many values for each parameter. Also, have no idea
how to use the SOAP API.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=518858&SiteID=1
"JSI" wrote:
> I'm trying to create a data-driven subscription with the ability to pass
> multiple values using a multi-select parameter. Is this possible, and if so
> how does one store the data in the database?
> Thanks,
> Josh

DataBindings

Hello

I need to link two tables,

one has a primary key (Membership) and one doesn't (results),

results doesn't have any primary key values, I have added a membership number column can I bind the results table column membership number to the values in the membership table?

If so how?

C

You need a FOREIGN KEY constraint between the 2 tables. Just set a FOREIGN KEY constraint on the membership number column of 'results' talbe which references the Primary Key column of Membership table. You can use such T-SQL statement:

ALTER TABLE Membership ADD CONSTRAINT
FK_MemShip_Num FOREIGN KEY
(
[membership number]
) REFERENCES dbo.titlex
(
[membership number]
)

For more information, please refers to following article:

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_04_8ypg.asp?frame=true

Database Users and Triggers

I created a trigger on a table T1 for insert and update. The trigger
selects values from table T2, which happens to be in a different
database. It seems I have to create users in that other database for
every user who updates or inserts records into T1. Is there any way
around this, using views or stored procedures, in SQL Server 2000?
Thanks
TimoHi Timo,
I don't think you'll be able to do what you want.
Trigger is running in security context of a user who fired it. As so, select
statement on T2 table is run as that user and user must have proper
permissions.
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Timo" <timo@.org.org> wrote in message
news:eBj$lRp9FHA.1032@.TK2MSFTNGP11.phx.gbl...
>I created a trigger on a table T1 for insert and update. The trigger
>selects values from table T2, which happens to be in a different database.
>It seems I have to create users in that other database for every user who
>updates or inserts records into T1. Is there any way around this, using
>views or stored procedures, in SQL Server 2000?
> Thanks
> Timo|||Hi,
please refer article FYI :
http://www.netdscure.co.in/Articles/AuditDML.htm
--
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Timo" wrote:

> I created a trigger on a table T1 for insert and update. The trigger
> selects values from table T2, which happens to be in a different
> database. It seems I have to create users in that other database for
> every user who updates or inserts records into T1. Is there any way
> around this, using views or stored procedures, in SQL Server 2000?
> Thanks
> Timo
>

Database trigger to run managed C# code

Hi there,

Values in my database need to updated periodically. The code, upon starting the application, queries the database and stores the values in the Application collection. This is to avoid making a database call everytime the values are needed (increases performance). The drawback is that changes to the database values are not updated in the code.

How can I create a database trigger that will update the C# Application colllection whenever a table value is updated?

The following article gave me some good insight on how to integrate the .NET CLR with SQL Server 2005.

http://msdn2.microsoft.com/en-us/library/ms345136.aspx

I can now create triggers to run managed C# code. However, the CLR is limited because I cannot reference the System.Web.HttpApplicationState object or anything to do with HTTP requests; so I can't update my Application collection.

However, the CLR does let me access System.Web.Services. So I should be able to make a web service call to update the variables. More to come on this....

|||

I solved my issue. Seems like I was going down the wrong path. The SqlCacheDependency class is what I needed. I can set up a cache dependency on a SQL table so that when values in that table change the associated cache value will be invalid.

Here's a good article on SqlCacheDependency ->http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html?page=last

This increases performance significantly! Awesome!