how to make the contents of sqlserver tables, case sensitive?

hi

the contents of sql server tables, aren't case sensitive.
and datas in them , are the same , whether they contains normal or caps letter.
for example Test = test=teST , and sql can't recogize the difference between them.

how can i make it sensitive?

thank u for your attention

Hi,
you may create a database with a case sesitive Collation and this will make the whole database case sestive including table names i.e.
select * from SYSOBJECTS will give you an error

How to load a Legacy DTS Package from .NET?

If I have an object of type "Microsoft.SqlServer.DTS.Runtime.Application,"

can I use one of the following functions
- ExistsOnDTSServer
- ExistsOnSQLServer
- LoadFromDTSServer
- LoadFromSQLServer
- LoadFromSQLServer2

to interact/load with a Legacy (SQL 2000) DTS package, stored on a SQL 2005 machine?

If so, can someone post an example first-argument (the Package Path) ?
Thanks.

I suspect that this is not possible.

Why would you not use the DTS object model to load and run a DTS package? Although it's a COM library, you can certainly use it from .NET...

-Doug|||You're probably right. It was worth someone verifying before assuming correctly/incorrectly.

The "Microsoft DTSPackage Object" COM object is an adequate alternative.
But this would require SQL 2000 Client Tools to be installed on the machine, in addition to SQL 2005 Client Tools, to gain use of both objects.

how to link Access with a .mdf file?

Hi,
i want to import data from a stand-alone .mdf file.
I use sqlserver express 2005 (windows xp prof).
In sqlserver, i attachted the .mdf file.
Then I created an odbc link, but when i try to import data from Access, i
only see the master database, not the tables of the .mdf file.
How can i import data from a .mdf file into Access?
Thanks for help
BenSQL Server Express (and in common 2005) has secured metadata, meaning
that you will only see metadata you are priviledged to. Seems that the
account you are using to connect to the datbase does not have the
appropiate permissions to access the database.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

How to limit amount of results in a search?

I'm busy writing a local site search engine that searches through a sqlserver database and I want to know how or what is the correct sqlsyntax to use in order to limit the amount of results a page loads at atime? The idea is obviously similar to something like google where youonly see a certain amount of results first and then click at the botomfor the next eg. 10 results.
The second question is how do I, after the first page with the firstset of results that were shown, "clear" the second page ofthe previous html in order to show the next set of results? To give you an idea what my code looks like at the moment. Please don'tkill me if the code is done a bit a lame, because I'm still learning.
<%@. Page Language="C#" Debug="true" EnableSessionState="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SqlClient" %>
<%@. import Namespace="System.IO" %>
<script runat="server">
void Page_Load(Object sender , EventArgs e)
{
//strings used to get values from basicSearch.aspx
string strProvince;
string strGender;
string strHeight;
string strBodyType;
string strLooks;
string strHairColor;
string strEyeColor;
string strEthnicity;
string strHomeLanguage;
string strRelStatus;
string strRelInterest;
string strHaveChildren;
string strWantChildren;
//strings used for storing results from search
string resUserName;
string resFirstName;
string resLastName;
string resUserPhoto;
string resProvince;
string resGender;
string resAge;
string resHeight;
string resBodyType;
string resLooks;
string resHairColor;
string resEyeColor;
string resEthnicity;
string resHomeLanguage;
string resRelStatus;
string resRelInterest;
string resHaveChildren;
string resWantChildren;
string resProfileHeading;
string resTextDescription;
string resTextDescription2 = "";// used for the actual display of the value
string strQuery; // to store concattenated search strings
string strInput; // to store textfile input while being read
StreamReader objStreamReader;

strProvince = Session["sessionProvince"].ToString();
strGender = Session["sessionGender"].ToString();
strHeight = Session["sessionHeight"].ToString();
strBodyType = Session["sessionBodyType"].ToString();
strLooks = Session["sessionLooks"].ToString();
strHairColor = Session["sessionHairColor"].ToString();
strEyeColor = Session["sessionEyeColor"].ToString();
strEthnicity = Session["sessionEthnicity"].ToString();
strHomeLanguage = Session["sessionHomeLanguage"].ToString();
strRelStatus = Session["sessionRelStatus"].ToString();
strRelInterest = Session["sessionRelInterest"].ToString();
strHaveChildren = Session["sessionHaveChildren"].ToString();
strWantChildren = Session["sessionWantChildren"].ToString();

strQuery = strProvince + " " +strGender + " " + strHeight + " " + strBodyType + " " + strLooks + " " +
strHairColor + " " + strEyeColor + " " + strEthnicity + " " +strHomeLanguage + " " +
strRelStatus + " " + strRelInterest + " " + strHaveChildren + " "+ strWantChildren;

SqlConnection conPubs;
string strSearch;
SqlCommand cmdSearch;
SqlDataReader dtrSearch;
conPubs = new SqlConnection(@."Server=THALION\THALION;Integrated Security=SSPI;Database=DateGame" );
//retrieve the results from the db
strSearch = "SELECT * FROMclient," + "FREETEXTTABLE( client, * , @.searchphrase ) searchTable "+ "WHERE [KEY] = client.userName " + "ORDER BY RANK DESC ";
cmdSearch = new SqlCommand( strSearch, conPubs );
cmdSearch.Parameters.Add( "@.searchphrase", strQuery );
conPubs.Open();
dtrSearch = cmdSearch.ExecuteReader();

//start display of results
lblResults.Text = "<tablewidth='100%' style='border-style:solid; border-width:thin;border-color:#E1E2DC;' cellpadding='0' cellspacing='0'>";
while ( dtrSearch.Read())
{
//values read from the returned result set
resUserName = dtrSearch[ "userName" ].ToString();
resFirstName = dtrSearch[ "firstName" ].ToString();
resLastName = dtrSearch[ "lastName" ].ToString();
resUserPhoto = dtrSearch[ "userPhoto" ].ToString();
resProvince = dtrSearch[ "province" ].ToString();
resGender = dtrSearch[ "gender" ].ToString();
resAge = dtrSearch[ "age"].ToString();
resHeight = dtrSearch[ "height" ].ToString();
resBodyType = dtrSearch[ "bodyType" ].ToString();
resLooks = dtrSearch[ "looks" ].ToString();
resHairColor = dtrSearch[ "hairColour" ].ToString();
resEyeColor = dtrSearch[ "eyeColour" ].ToString();
resEthnicity = dtrSearch[ "ethnicity" ].ToString();
resHomeLanguage = dtrSearch[ "homeLang" ].ToString();
resRelStatus = dtrSearch[ "relationshipStatus"].ToString();
resRelInterest = dtrSearch[ "relationPreference" ].ToString();
resHaveChildren = dtrSearch[ "haveChildren" ].ToString();
resWantChildren = dtrSearch[ "wantChildren" ].ToString();
resProfileHeading = dtrSearch[ "profileHeading" ].ToString();
resTextDescription = dtrSearch[ "textDescription" ].ToString();

// read the text file's info into a variable for display
if (File.Exists( MapPath( "text files" +"\\" +resTextDescription ) ) )
{

objStreamReader = File.OpenText( MapPath( "text files" +"\\"+resTextDescription ) );
strInput = objStreamReader.ReadLine();
while ( strInput != null)
{

resTextDescription2 += strInput;
strInput = objStreamReader.ReadLine();
}
objStreamReader.Close();
}
else
{
resTextDescription2 = "myFile.txt does not exist!";
}
//determine whether male or female in order to display correct sign
if ( resGender == "Male")
resGender = "Male_sign_1.jpg";
else
resGender = "Female_sign_1.jpg";
//determine whether 'want' and 'have' children and convert to correctwords for display
if ( resHaveChildren == "have kids" )
resHaveChildren = "Yes";
else
resHaveChildren = "No";
if ( resWantChildren == "want kids" )
resWantChildren = "Yes";
else
resWantChildren = "No";
// The writing of html to display the values calculated
lblResults.Text += "<tr><td width='16%' bgcolor='#C7C9BE' class='text_bold'><div align='center'>" +resUserName +
"</div><hr noshade class='hr_line'></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'class='text_bold'><div align='center'>" + resProfileHeading +
"</div><hr noshade class='hr_line'></td><tdcolspan='2'><div align='center'class='page_headings'>%</div></td></tr>";
lblResults.Text += "<tr><td rowspan='15' valign='top'bgcolor='#C7C9BE'><p align='center'><img src='images/" +resGender + "' width='20' height='22'></p>" +
"<img src='photos/" + resUserPhoto + "' width='80'height='88'><div align='center'></div></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'><pclass='text'><br></p><p class='text'>" +resTextDescription2 + "</p><pclass='text_bold'> </p></td>";
lblResults.Text += "<td width='7%' rowspan='15'valign='top'><img src='images/hotlist_1.jpg' alt='Add ToFavorites' width='34' height='32'></td>" +
"<td width='14%' rowspan='15' valign='top'><imgsrc='images/email_1.jpg' alt='Email this profile' width='42'height='36'></td></tr>";
lblResults.Text += "<tr><td width='26%' bgcolor='#E1E2DC'class='text_bold'>Location :</td><td width='37%'bgcolor='#E1E2DC' class='text'>" + resProvince +"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Age :</td><td bgcolor='#E1E2DC'class='text'>" + resAge + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Height : </td><td bgcolor='#E1E2DC'class='text'>" + resHeight + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Body Type : </td><td bgcolor='#E1E2DC'class='text'>" + resBodyType + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Looks : </td><td bgcolor='#E1E2DC'class='text'>"+ resLooks+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Hair Colour : </td><td bgcolor='#E1E2DC'class='text'>"+resHairColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Eye Colour : </td><td bgcolor='#E1E2DC'class='text'>"+resEyeColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Ethnicity : </td><td bgcolor='#E1E2DC'class='text'>"+resEthnicity+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Home Language : </td><tdbgcolor='#E1E2DC'class='text'>"+resHomeLanguage+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Relationship Status : </td><tdbgcolor='#E1E2DC'class='text'>"+resRelStatus+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Realtionship Interest : </td><tdbgcolor='#E1E2DC'class='text'>"+resRelInterest+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Have Children : </td><tdbgcolor='#E1E2DC'class='text'>"+resHaveChildren+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Want Children : </td><tdbgcolor='#E1E2DC'class='text'>"+resWantChildren+"</td></tr>";
lblResults.Text += "<tr><td colspan='2'bgcolor='#E1E2DC'> </td></tr>";
lblResults.Text += "<tr><tdbgcolor='#497792'> </td><td colspan='2'bgcolor='#678BA1'> </td><td colspan='2'bgcolor='#678BA1'> </td></tr>";
lblResults.Text += "<tr><td> </td><tdcolspan='2'> </td><tdcolspan='2'> </td></tr>";
resTextDescription2 = "";
}
lblResults.Text += "</table>";
conPubs.Close();
}
void Button_Login(Object sender , ImageClickEventArgs e)
{
SqlConnection conClient;
string strSelect;
string strclientName;
SqlCommand cmdSelect;

//create a connection
conClient = newSqlConnection( @."Server=THALION\THALION;IntegratedSecurity=SSPI;database=DateGame" );
//select statement
strSelect = "Select userNameFrom [client] Where userName=@.username and userPassword=@.userpassword";
cmdSelect = new SqlCommand( strSelect, conClient );
cmdSelect.Parameters.Add( "@.username", txtNickName.Text );
cmdSelect.Parameters.Add( "@.userpassword", txtPword.Text );
//open a connection to db
conClient.Open();
//check to see if it already exists
strclientName = System.Convert.ToString(cmdSelect.ExecuteScalar());
if (strclientName.ToLower() == txtNickName.Text.ToString().ToLower())
{
//Store user name as session variable
Session["sessionUserName"] = txtNickName.Text.ToString();
txtNickName.Text = "";
txtPword.Text = "";
}
else
lbl_invalid_login.Text = "Invalid login!";
conClient.Close();
}
</script>

Actually, you code is quite nice! quick suggestion, use the ? operator for stuff like:

code wrote:

if ( resGender == "Male")
resGender = "Male_sign_1.jpg";
else
resGender = "Female_sign_1.jpg"

w/ the question mark operator it becomes

code wrote:

resGender = (resGender=="Male"?"Male_sign_1.jpg":"Female_sign_1.jpg");

as for how to impelment pagination, here's a linkHow to Implement a Repeater Control with Pagination

How to know the Reporting services version at runtime

HI, can anybody help me?
I have a problem. With the sqlserver 2005 sp2 some behavieur are differt from previous versions.
For example with service pack 1 there isn't the "select all" option for multi value parameters, so i add MY select all, but with service pack 2 there is the "select all" option for multivalue paramteres, so when my reports are running under a server with service pack 2 in the list of multivalue parameter I'm going to find "Select all" and "MY select all"
It is possible to know at runtime under which version my repors will be running? so I can use or not MY select all. (i cannot force client with a particular version)

Thank in advance

If you can call any webservice method (e.g. ListSecureMethods), you will get back a serverinfoheader, where you can look into reportserverversion property to determine the server version.

how to know if sqlserver is clustered

hi,

can u tell me how to find out sql server is clustered or not using sql dmo (c++)

Is there any method or property that i can use to find out if sql server is failover cluster

Use the Cluster Server APIs for this:

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

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Check out the IsClustered property under Server.Information - here's a code sample:

Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

If srvMgmtServer.Information.IsClustered = True Then
' Do something here
End If

Hope that helps.

|||yes we have it in server properties i got the required information..

how to know if sqlserver is clustered

hi,

can u tell me how to find out sql server is clustered or not using sql dmo (c++)

Is there any method or property that i can use to find out if sql server is failover cluster

Use the Cluster Server APIs for this:

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

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Check out the IsClustered property under Server.Information - here's a code sample:

Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

If srvMgmtServer.Information.IsClustered = True Then
' Do something here
End If

Hope that helps.

|||yes we have it in server properties i got the required information..

How to know if INSERTing or UPDATing?

Hi all.
I am quite new to SQLServer 2005 and I cannot seem to solve it.
If we have a INSERT, UPDATE trigger, how do we know if the action that
fired it is an INSERT or an UPDATE?
Looking at 'deleted' virtual values perhaps?
COLUMNS_UPDATED() yields > 0 even for INSERTS.
TIA
Cheers.
Carlos.Carlos,
Rows in inserted and deleted = UPDATE
Rows in inserted only = INSERT
Rows in deleted only = DELETE
BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
because the columns have either explicit values or implicit (NULL) values
inserted.
RLF
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
>|||> Looking at 'deleted' virtual values perhaps?
Yes,
if exists (select 1 from deleted) and exists (select 1 from inserted)
' update|||On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Carlos,
> Rows in inserted and deleted = UPDATE
> Rows in inserted only = INSERT
> Rows in deleted only = DELETE
> BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> because the columns have either explicit values or implicit (NULL) values
> inserted.
> RLF
> "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> > Hi all.
> > I am quite new to SQLServer 2005 and I cannot seem to solve it.
> > If we have a INSERT, UPDATE trigger, how do we know if the action that
> > fired it is an INSERT or an UPDATE?
> > Looking at 'deleted' virtual values perhaps?
> > COLUMNS_UPDATED() yields > 0 even for INSERTS.
> > TIA
> > Cheers.
> > Carlos.
Yep!
I was suspecting someting like that...
Thank you so much Russell.
Its a long long way to go from Oracle to SQL Server...
Cheers.
Carlos.|||On 7 jun, 17:23, CarlosAL <miotromailcar...@.netscape.net> wrote:
> On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
>
> > Carlos,
> > Rows in inserted and deleted = UPDATE
> > Rows in inserted only = INSERT
> > Rows in deleted only = DELETE
> > BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> > because the columns have either explicit values or implicit (NULL) values
> > inserted.
> > RLF
> > "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> >news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> > > Hi all.
> > > I am quite new to SQLServer 2005 and I cannot seem to solve it.
> > > If we have a INSERT, UPDATE trigger, how do we know if the action that
> > > fired it is an INSERT or an UPDATE?
> > > Looking at 'deleted' virtual values perhaps?
> > > COLUMNS_UPDATED() yields > 0 even for INSERTS.
> > > TIA
> > > Cheers.
> > > Carlos.
> Yep!
> I was suspecting someting like that...
> Thank you so much Russell.
> Its a long long way to go from Oracle to SQL Server...
> Cheers.
> Carlos.
...and thank you Aaron too! (You posted while I was writing)
Cheers.
Carlos|||On Jun 7, 8:02 pm, CarlosAL <miotromailcar...@.netscape.net> wrote:
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
I think checking rows in inserted and deleted is a crude way . If
Trigger does not insert or update rows in the table due to some
where condition, these system tables will not have any rows. Some
times it may be required to capture the event .
MS should provide a function to capture which one of the INSERT/UPDATE/
DELETE events prompted trigger to execute|||> I think checking rows in inserted and deleted is a crude way . If
> Trigger does not insert or update rows in the table due to some
> where condition, these system tables will not have any rows.
And so any IF EXISTS check will return false. Anyway, your trigger should
have IF @.@.ROWCOUNT > 0 before executing anything anyway. That way if no
rows are affected, the trigger exits cleanly.|||On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>Hi all.
>I am quite new to SQLServer 2005 and I cannot seem to solve it.
>If we have a INSERT, UPDATE trigger, how do we know if the action that
>fired it is an INSERT or an UPDATE?
Hi Carlos,
If the trigger has to do different things on insert and update, why not
create two triggers? Saves you the need to find out what the action was,
and might result in better performance to boot!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:
> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
> >Hi all.
> >I am quite new to SQLServer 2005 and I cannot seem to solve it.
> >If we have a INSERT, UPDATE trigger, how do we know if the action that
> >fired it is an INSERT or an UPDATE?
> Hi Carlos,
> If the trigger has to do different things on insert and update, why not
> create two triggers? Saves you the need to find out what the action was,
> and might result in better performance to boot!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Thanks to all.
Hugo: The problem here is the developer (not me) decided to implement
TWO triggers in order to provide values for audit columns. If we have
two triggers (basically they do the same thing), when we UPDATE the
(auditory) values in an insert, we fire the second trigger, which is
not desiseable: two triggers to do one (same) thing.
Cheers and thanks again.
Carlos.|||You can easily add logic to your update stored procedure that ignores
updates that affect the auditing columns. Have a look at IF
UPDATE(column_name) ...
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181544855.466045.265580@.q75g2000hsh.googlegroups.com...
> On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
> wrote:
>> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>> >Hi all.
>> >I am quite new to SQLServer 2005 and I cannot seem to solve it.
>> >If we have a INSERT, UPDATE trigger, how do we know if the action that
>> >fired it is an INSERT or an UPDATE?
>> Hi Carlos,
>> If the trigger has to do different things on insert and update, why not
>> create two triggers? Saves you the need to find out what the action was,
>> and might result in better performance to boot!
>> --
>> Hugo Kornelis, SQL Server MVP
>> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
> Thanks to all.
> Hugo: The problem here is the developer (not me) decided to implement
> TWO triggers in order to provide values for audit columns. If we have
> two triggers (basically they do the same thing), when we UPDATE the
> (auditory) values in an insert, we fire the second trigger, which is
> not desiseable: two triggers to do one (same) thing.
> Cheers and thanks again.
> Carlos.
>

How to know if INSERTing or UPDATing?

Hi all.
I am quite new to SQLServer 2005 and I cannot seem to solve it.
If we have a INSERT, UPDATE trigger, how do we know if the action that
fired it is an INSERT or an UPDATE?
Looking at 'deleted' virtual values perhaps?
COLUMNS_UPDATED() yields > 0 even for INSERTS.
TIA
Cheers.
Carlos.Carlos,
Rows in inserted and deleted = UPDATE
Rows in inserted only = INSERT
Rows in deleted only = DELETE
BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
because the columns have either explicit values or implicit (NULL) values
inserted.
RLF
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
>|||> Looking at 'deleted' virtual values perhaps?
Yes,
if exists (select 1 from deleted) and exists (select 1 from inserted)
' update|||On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> Carlos,
> Rows in inserted and deleted = UPDATE
> Rows in inserted only = INSERT
> Rows in deleted only = DELETE
> BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> because the columns have either explicit values or implicit (NULL) values
> inserted.
> RLF
> "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
>
>
>
>
>
>
>
>
Yep!
I was suspecting someting like that...
Thank you so much Russell.
Its a long long way to go from Oracle to SQL Server...
Cheers.
Carlos.|||On 7 jun, 17:23, CarlosAL <miotromailcar...@.netscape.net> wrote:
> On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Yep!
> I was suspecting someting like that...
> Thank you so much Russell.
> Its a long long way to go from Oracle to SQL Server...
> Cheers.
> Carlos.
...and thank you Aaron too! (You posted while I was writing)
Cheers.
Carlos|||On Jun 7, 8:02 pm, CarlosAL <miotromailcar...@.netscape.net> wrote:
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
I think checking rows in inserted and deleted is a crude way . If
Trigger does not insert or update rows in the table due to some
where condition, these system tables will not have any rows. Some
times it may be required to capture the event .
MS should provide a function to capture which one of the INSERT/UPDATE/
DELETE events prompted trigger to execute|||> I think checking rows in inserted and deleted is a crude way . If
> Trigger does not insert or update rows in the table due to some
> where condition, these system tables will not have any rows.
And so any IF EXISTS check will return false. Anyway, your trigger should
have IF @.@.ROWCOUNT > 0 before executing anything anyway. That way if no
rows are affected, the trigger exits cleanly.|||On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:

>Hi all.
>I am quite new to SQLServer 2005 and I cannot seem to solve it.
>If we have a INSERT, UPDATE trigger, how do we know if the action that
>fired it is an INSERT or an UPDATE?
Hi Carlos,
If the trigger has to do different things on insert and update, why not
create two triggers? Saves you the need to find out what the action was,
and might result in better performance to boot!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:
> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>
>
> Hi Carlos,
> If the trigger has to do different things on insert and update, why not
> create two triggers? Saves you the need to find out what the action was,
> and might result in better performance to boot!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Thanks to all.
Hugo: The problem here is the developer (not me) decided to implement
TWO triggers in order to provide values for audit columns. If we have
two triggers (basically they do the same thing), when we UPDATE the
(auditory) values in an insert, we fire the second trigger, which is
not desiseable: two triggers to do one (same) thing.
Cheers and thanks again.
Carlos.|||You can easily add logic to your update stored procedure that ignores
updates that affect the auditing columns. Have a look at IF
UPDATE(column_name) ...
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181544855.466045.265580@.q75g2000hsh.googlegroups.com...
> On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
> wrote:
> Thanks to all.
> Hugo: The problem here is the developer (not me) decided to implement
> TWO triggers in order to provide values for audit columns. If we have
> two triggers (basically they do the same thing), when we UPDATE the
> (auditory) values in an insert, we fire the second trigger, which is
> not desiseable: two triggers to do one (same) thing.
> Cheers and thanks again.
> Carlos.
>

How to know if INSERTing or UPDATing?

Hi all.
I am quite new to SQLServer 2005 and I cannot seem to solve it.
If we have a INSERT, UPDATE trigger, how do we know if the action that
fired it is an INSERT or an UPDATE?
Looking at 'deleted' virtual values perhaps?
COLUMNS_UPDATED() yields > 0 even for INSERTS.
TIA
Cheers.
Carlos.
Carlos,
Rows in inserted and deleted = UPDATE
Rows in inserted only = INSERT
Rows in deleted only = DELETE
BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
because the columns have either explicit values or implicit (NULL) values
inserted.
RLF
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181228538.517937.317920@.o5g2000hsb.googlegro ups.com...
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
>
|||> Looking at 'deleted' virtual values perhaps?
Yes,
if exists (select 1 from deleted) and exists (select 1 from inserted)
' update
|||On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> Carlos,
> Rows in inserted and deleted = UPDATE
> Rows in inserted only = INSERT
> Rows in deleted only = DELETE
> BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> because the columns have either explicit values or implicit (NULL) values
> inserted.
> RLF
> "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> news:1181228538.517937.317920@.o5g2000hsb.googlegro ups.com...
>
>
>
>
Yep!
I was suspecting someting like that...
Thank you so much Russell.
Its a long long way to go from Oracle to SQL Server...
Cheers.
Carlos.
|||On 7 jun, 17:23, CarlosAL <miotromailcar...@.netscape.net> wrote:
> On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
>
>
>
>
>
>
>
>
> Yep!
> I was suspecting someting like that...
> Thank you so much Russell.
> Its a long long way to go from Oracle to SQL Server...
> Cheers.
> Carlos.
...and thank you Aaron too! (You posted while I was writing)
Cheers.
Carlos
|||On Jun 7, 8:02 pm, CarlosAL <miotromailcar...@.netscape.net> wrote:
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
I think checking rows in inserted and deleted is a crude way . If
Trigger does not insert or update rows in the table due to some
where condition, these system tables will not have any rows. Some
times it may be required to capture the event .
MS should provide a function to capture which one of the INSERT/UPDATE/
DELETE events prompted trigger to execute
|||> I think checking rows in inserted and deleted is a crude way . If
> Trigger does not insert or update rows in the table due to some
> where condition, these system tables will not have any rows.
And so any IF EXISTS check will return false. Anyway, your trigger should
have IF @.@.ROWCOUNT > 0 before executing anything anyway. That way if no
rows are affected, the trigger exits cleanly.
|||On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:

>Hi all.
>I am quite new to SQLServer 2005 and I cannot seem to solve it.
>If we have a INSERT, UPDATE trigger, how do we know if the action that
>fired it is an INSERT or an UPDATE?
Hi Carlos,
If the trigger has to do different things on insert and update, why not
create two triggers? Saves you the need to find out what the action was,
and might result in better performance to boot!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:
> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>
> Hi Carlos,
> If the trigger has to do different things on insert and update, why not
> create two triggers? Saves you the need to find out what the action was,
> and might result in better performance to boot!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Thanks to all.
Hugo: The problem here is the developer (not me) decided to implement
TWO triggers in order to provide values for audit columns. If we have
two triggers (basically they do the same thing), when we UPDATE the
(auditory) values in an insert, we fire the second trigger, which is
not desiseable: two triggers to do one (same) thing.
Cheers and thanks again.
Carlos.
|||You can easily add logic to your update stored procedure that ignores
updates that affect the auditing columns. Have a look at IF
UPDATE(column_name) ...
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181544855.466045.265580@.q75g2000hsh.googlegr oups.com...
> On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
> wrote:
> Thanks to all.
> Hugo: The problem here is the developer (not me) decided to implement
> TWO triggers in order to provide values for audit columns. If we have
> two triggers (basically they do the same thing), when we UPDATE the
> (auditory) values in an insert, we fire the second trigger, which is
> not desiseable: two triggers to do one (same) thing.
> Cheers and thanks again.
> Carlos.
>