kill running query: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

How to manage security with Access FE and SQL 2005 Express BE

I have upsized my Access 2003 database to SQL Server 2005 Express so that al
l
the tables are now reside in SQL Server; forms, queries and report are still
in Access, but how do I manage users now?
Do I setup users on the SQL Server or maintain those which I have set up in
Access using the security wizard.
It is important that users have no direct access to the tables but
unfortunately they can still do so in Access. I do not wish that they amend
or edit them directly. I am hoping that I could prevent this with the SQL
server, but I don’t know how to and then how do I connect the security of
the
SQL server with that of the Access database.
Please help.Hi,
yeah you can manage users the same as you done in MS ACCESS
1). Create SQL Users
2). Create Roles
3). Assign proper privileges to Roles
4). Map the users to that Roles respectively
and refer http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=24
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Lisa Tanenbaum" wrote:

> I have upsized my Access 2003 database to SQL Server 2005 Express so that
all
> the tables are now reside in SQL Server; forms, queries and report are sti
ll
> in Access, but how do I manage users now?
> Do I setup users on the SQL Server or maintain those which I have set up i
n
> Access using the security wizard.
> It is important that users have no direct access to the tables but
> unfortunately they can still do so in Access. I do not wish that they amen
d
> or edit them directly. I am hoping that I could prevent this with the SQL
> server, but I don’t know how to and then how do I connect the security o
f the
> SQL server with that of the Access database.
> Please help.
>|||Andy
Thanks for your reply. I am completely new to SQL Server so it wasn't so
obvious to me. I think I understand it a little better now.
"Andy Davis" wrote:
[vbcol=seagreen]
> Hi,
> yeah you can manage users the same as you done in MS ACCESS
> 1). Create SQL Users
> 2). Create Roles
> 3). Assign proper privileges to Roles
> 4). Map the users to that Roles respectively
> and refer http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=24
> :-)
> Regards
> --
> Andy Davis
> Activecrypt Team
> ---
> SQL Server Encryption Software
> http://www.activecrypt.com
>
> "Lisa Tanenbaum" wrote:
>

how to manage concurrency between multiple, disconnected clients

I have a system use MS SQL 2005 & .NET 2.0, my tables don't have rowversion, but I heard SQL 2005 manage a rowversion by itself, can I use this to do a "ConflictDetection".

All I try to do is I want to get a error when I try to update a row which been modified by someone else after I read row.

Thanks.

I have never used SQL Server 2005 timestamp field which is what you are referring to as the row version. This is an autoincrementing field when a change is detected and is useful in preventing dirty writes.

I have used this practice in managing phantom data back with SQL Server 2000 just by adding a column of type INT and incrementing it each time the record is changed. Basically this is how it works, when I retrieve the record, I get a version of 1, at the same time another person gets the same record with version 1 as well. This person then updates the record causing it to increment to 2. In the meantime, I decide to update the record as well, your application or database should compare the version number you have which is 1 with the current version number at the database, if it is the same then allow update as this mean no changes were detected. In this case the version is at 2 which means the copy of the data at version 1 when i retrieved it is stale and you would alert the user that either they cannot save until they do a refresh or inform them if the choose to proceed, data will be lost.

Using row versions is optimistic locking as many people can read the same record.

You can also use pessimistic looking by locking the record once you have it. You can do this by using Transactions in .NET (System.Data.SqlClient.Transaction) and specify the Isolation levels.

How to make Update relation?

I have two tables: TableX & TableY

there is two similar fields (Size) with the same datatype, I want to make a relation in a way if I change the value in (TableX.Size) the same value will be applied to (TableY.Size).

How to?

Lewe:

The are a couple of things that you need to know first:

How does TableX relate to TableY? TableX SHOULD have a key to TableY so that corresponding X and Y members can be joined|||

I'm not sure that you are really talking about needing a 'relation'. It sounds as though you only want a method that will change TableY values when a similar value in TableX is changed.

That 'could' be done with a TRIGGER. But first, it will be necessary for you to identify which row in TableY is to be changed as a result of a particular row in TableX getting changed. In other words, "How do the two tables 'relate' to each other?"

How to make Update relation?

I have two tables: TableX & TableY

there is two similar fields (Size) with the same datatype, I want to make a relation in a way if I change the value in (TableX.Size) the same value will be applied to (TableY.Size).

How to?

Lewe:

The are a couple of things that you need to know first:

How does TableX relate to TableY? TableX SHOULD have a key to TableY so that corresponding X and Y members can be joined|||

I'm not sure that you are really talking about needing a 'relation'. It sounds as though you only want a method that will change TableY values when a similar value in TableX is changed.

how to make the contets of tables, case sensitive?

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

Case-sensitivity is controlled by the collation that's being used by the server in the context of the query you're executing. I believe you can set the collation at the server, database or column level in SQL Server 2005. For more information, see the "Working with Collations" topic in Books Online.

Also, please post relational database engine questions to the SQL Database Engine forum (http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=93) where you're likely to get more prompt and accurate replies.

Raman Iyer
SQL Server Data Mining

sql

how to make the contents of tables, case sensitive?

how can i make it sensitive?

thank u for your attention

Raman Iyer
SQL Server Data Mining

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

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

Wednesday, March 28, 2012

How to make tables & stored procedures in MSDE?

Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.

The problem is I don't know where to input data to set up tables or stored procedures after configured the MSDE. The MS Desktop Engine pops me "configuration sucessfully", then I can just open a small window with the title of "SQL Server Service Manager", telling me SQL Server is running. But I can't find any SQL application in the Start->All Programs-> for me to run.

What's the human interface of MSDE? Can anybody tell me how you deal with MSDE?

And in my VB.NET's Server Explorer, I find there is only "Data connections" icon which allows me to connect to existing database, and no "Servers" icon as showed in many Visual Studio instruction books, which make me no chance to create new database. Sad

My Windows system is XP professional (SP2) with IIS, .NET framework 1.1 and Server Extensions Adminstrator. What's my problem? Did I configure wrong somewhere? I want to make my computer as a local server. And does the combination of VB.NET + MSDE work?

Thanks a lot for your help! Have a good weekend!

Henry Li

There is no program that comes with msde for managing and creating databases. You can use visual studio 2003's server explorer for that. Here is an example of how to create a database with vb.net

http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994

|||Hello Ken,
Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response. Smile

Do you mean I can only use codes to generate database tables and stored procedures? Are all these tables located under the MYPC.master.dbo icon in the Server Explorer? Is there any simpler way that I just right-click something then it gives me an option to "New tables" or "New Database"? (But I don't have the Servers icon in the Server Explorer, did I configure wrong for my VB.NET package?)

And does the MSDE have interface window like Access that can give you a straight-forward and nice looking window to input table data? Can MSDE work independantly without VS.NET's help? I really have no idea how MSDE itself look like. By the way, does SQL Server Express have windows and menus to manipulate data?

I hope my questions are not too many. Thanks a lot in advance for your help!

Best wishes,
Henry Li|||Hi,
You can create databases, tables, etc in visual studio 2003 server explorer also. Right click on databases and select new database. The Sql Server Express is the free 2005 version of msde. You can download a management console for the express edition.
Ken|||

Dear Ken,
You don't really understand my problems yet.
I am using VB.NET package, not VS.NET. The interface and menu are similar, but when I did a help search of "Server Explorer" within it, it told me VB.NET doesn't have the Server node within the Server Explorer, only VS.NET package has it. I just have the first node, which is "Data Connection".

I ran your code in a new project "test1". I pasted it on the WebForms1.aspx.vb, then built it. It generated errors as below:

Build started: Project: test1, Configuration: Debug .NET

Preparing resources...
Updating references...
Performing main compilation...
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(24) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(26) : error BC30182: Type expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30691: 'EventArgs' is a type in 'System' and cannot be used as an expression.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30800: Method arguments must be enclosed in parentheses.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30205: End of statement expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(33) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(48) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(88) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(92) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(93) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(113) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(123) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(124) : error BC30451: Name 'MessageBox' is not declared.
Building satellite assemblies...
Satellite assemblies could not be built because the main project output is missing.

- Done -

Build: 0 succeeded, 1 failed, 0 skipped
Sad I am so dissapointed with VB.NET standard edition, as it seems it can't generate new database, it can just connect to existing database that are already built up.
So I am curious whether MSDE can work independently to let me set up new database, but you ignored my question. I really feel depressed about this combination of VB.NET + MSDE.
You may search on your VS.NET help function for "Server Explorer", it will tell you VB.NET package doesn't have server node.

Thanks & Best regards,
Henry Li

|||Hello Ken,
Visual Basic.NET Standard Edition package is cheaper than VS.NET package. It has less functions than VS.NET package, and cost me $114.
On its package box it mentions it can't build device-based applications and solutions, can't build/debug/deploy powerful server software while VS.NET Professional can.

You said SQL Server Express 2005 is a new version of MSDE, I am just wondering whether it can work like Oracle Plus or Access which can let you have some windows to key in some data?

Thanks a lot!
Henry|||You need a imports system.data.sqlclient at the top of the code file.|||Sorry I thought vb.net standard had the server explorer. Here is a link to download the free sql server 2005 express.

http://msdn.microsoft.com/vstudio/express/sql/

Here is a link to the free management studio for sql server 2005 express

http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en|||Hello Ken,
Thanks a lot! This "imports system.data.sqlclient" really helps!
Now it reduces to only 2 errors left, which are:
--
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared. (under the sub createDataBase()->Try/Catch)
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'. (under the sub CreateClientsTable() ->me.Text)
--

I think I might need another line of "imports something"?
Sorry I am a beginner of programming in sql. And want to try your code to see if it can pass through to connect to my MSDE.

By the way, I've downloaded the SQL Server Express and the management console but haven't installed them, and still downloading the 440MB Studio Web Develop Express under the Visual Studio 2005 Express which was issued for free download just 2 weeks ago. I hope this can take place of my VB.NET package.

Thanks a lot again!

Best regards,
Henry|||I wrote that as windows forms application. Messagebox.show is not supported in web application's neither is me.text. Try changing messagebox.show to response.write and me.text = to response.write("The string" & "<br>")|||Hi, I am new one in this forum. About question: "How to make tables & stored procedures in MSDE?", try with command line tool - osql. If you need more help, I am here. Or search MS site about it.

Stevan
|||Hello Ken,
I've downloaded the Visual Web Developer 2005 Express which includes the SQL Server Express, so I installed them both.
The SQL Server 2005 Express is similar to the MSDE, although it has a configuration console, it still can't handle database.
Then I downloaded the SQL Management Studio Express, it's really good! Big Smile It gives me free choices to deal with security, log-in, creation of new databases!

Then I created a database in the SQLEXPRESS via the management studio console, then in the Visual Web Dev Express I created connection with that new database, it connects!

Now I can creat tables and stored procedures either directly in the SQLEXPRESS's database (via the mgt studio), or indirectly in the Visual Web Dev Express within the database explorer window. Smile

Now I don't use the VB.NET package anymore except if I need to create some windows exe files.

Thank you for your kindness!

Best wishes,
Henry Li|||Thank you Stevan,
I found my problem was some basic things that maybe many people took it for granted while I didn't know.
Actually I need to make a database in the MSDE first, and MSDE has no interface to let me set it up. I need the management software such as the SQL Management Studio 2005 Express.
Now I found this Mgt Studio Express is very useful, it can handle MSDE and SQLEXPRESS. I created some databases with the help of Mgt Studio Express, then I can create tables or procedures!

Best wishes,
Henry|||

Hi,

You can generate tables with osql command, i just need to indicate the command as following:

osql -U user -S server\instancename

then

1> create database test

2> GO

1>use test

2>GO

then you're inside of test database and you are ready to generate your table according you're needed

How to make tables & stored procedures in MSDE?

Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.

What's the human interface of MSDE? Can anybody tell me how you deal with MSDE?

Thanks a lot for your help! Have a good weekend!

Henry Li

There is no program that comes with msde for managing and creating databases. You can use visual studio 2003's server explorer for that. Here is an example of how to create a database with vb.net

http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994

|||Hello Ken,
Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response. Smile

I hope my questions are not too many. Thanks a lot in advance for your help!

I ran your code in a new project "test1". I pasted it on the WebForms1.aspx.vb, then built it. It generated errors as below:

Build started: Project: test1, Configuration: Debug .NET

- Done -

Thanks & Best regards,
Henry Li

You said SQL Server Express 2005 is a new version of MSDE, I am just wondering whether it can work like Oracle Plus or Access which can let you have some windows to key in some data?

http://msdn.microsoft.com/vstudio/express/sql/

Here is a link to the free management studio for sql server 2005 express

I think I might need another line of "imports something"?
Sorry I am a beginner of programming in sql. And want to try your code to see if it can pass through to connect to my MSDE.

Thanks a lot again!

Stevan|||Hello Ken,
I've downloaded the Visual Web Developer 2005 Express which includes the SQL Server Express, so I installed them both.
The SQL Server 2005 Express is similar to the MSDE, although it has a configuration console, it still can't handle database.
Then I downloaded the SQL Management Studio Express, it's really good! Big Smile It gives me free choices to deal with security, log-in, creation of new databases!

Then I created a database in the SQLEXPRESS via the management studio console, then in the Visual Web Dev Express I created connection with that new database, it connects!

Now I can creat tables and stored procedures either directly in the SQLEXPRESS's database (via the mgt studio), or indirectly in the Visual Web Dev Express within the database explorer window. Smile

Now I don't use the VB.NET package anymore except if I need to create some windows exe files.

Thank you for your kindness!

Best wishes,
Henry|||

Hi,

You can generate tables with osql command, i just need to indicate the command as following:
osql -U user -S server\instancename
then
1> create database test
2> GO
1>use test
2>GO
then you're inside of test database and you are ready to generate your table according you're needed

How to make table relations using query.

Hi,

How can I make relations between two tables using query? Tables are already there with data.

RegardsDo you mean you want to add a Foreign Key constraint?

Or do you just want to select the data?

To select the data, you would use a JOIN:


SELECT
 Table1.name,
 Table2.address
FROM
 Table1
INNER JOIN
 Table2 ON Table1.ID = Table2.ID

If you give us some more information we should be able to provide better help.

Terri|||Hi,

Thanks. Actually I uploaded my SQl Server DB to a free server for testing purpose. Tables were uploaded successfully but the relationship between the tables were erased, I mean Primary key and Foriegn key. The problem is that they have only a web based panel working to update database and I have to use Query Commands to manage my DB.

Tables are already there, I just need to make relations (Primary & Foreign Kies) between these tables.

I hope I could make it a little bit clear.

Regards,|||You would need something along these lines (assuming column names for myPKColumn and myFKColumn):

ALTER TABLE
myTable
ADD CONSTRAINT
myPKConstraintName
PRIMARY KEY (myPKColumn)

ALTER TABLE
myTable
ADD CONSTRAINT
myFKConstraintName
FOREIGN KEY (myFKColumn)

See BOL orConstrain your SQL Server data with T-SQL for more info.

Terri

how to make sensitive data encrypted in tables

Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.Encryption is available in SQL server 2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.|||See if this helps:
http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
Which version of SQL Server are you using?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.|||We are using SQL Server 2000.
Thanks for the responses.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23B%23XkILiGHA.1276@.TK2MSFTNGP03.phx.gbl...
> See if this helps:
> http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
> Which version of SQL Server are you using?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Any idea how to make sensitive data encrypted and make it readable for
> applications which uses it. I mean like password in syslogins table. Is
> there a way to do it in sql server or do i need to use any third party
> tools. Can you please list out if there are any tools.
> Thanks,
> Subbu.
>
>|||Then you'll have to encrypt the data before storing it in SQL Server. Check
out:
http://msdn.microsoft.com/library/d...ction-dpapi.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:O0y$STLiGHA.2220@.TK2MSFTNGP05.phx.gbl...
We are using SQL Server 2000.
Thanks for the responses.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23B%23XkILiGHA.1276@.TK2MSFTNGP03.phx.gbl...
> See if this helps:
> http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
> Which version of SQL Server are you using?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Any idea how to make sensitive data encrypted and make it readable for
> applications which uses it. I mean like password in syslogins table. Is
> there a way to do it in sql server or do i need to use any third party
> tools. Can you please list out if there are any tools.
> Thanks,
> Subbu.
>
>

how to make sensitive data encrypted in tables

Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.Encryption is available in SQL server 2005.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.|||See if this helps:
http://www.sqlsecurity.com/FAQs/SQLServerFAQ/tabid/55/Default.aspx
Which version of SQL Server are you using?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
Any idea how to make sensitive data encrypted and make it readable for
applications which uses it. I mean like password in syslogins table. Is
there a way to do it in sql server or do i need to use any third party
tools. Can you please list out if there are any tools.
Thanks,
Subbu.|||We are using SQL Server 2000.
Thanks for the responses.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23B%23XkILiGHA.1276@.TK2MSFTNGP03.phx.gbl...
> See if this helps:
> http://www.sqlsecurity.com/FAQs/SQLServerFAQ/tabid/55/Default.aspx
> Which version of SQL Server are you using?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Any idea how to make sensitive data encrypted and make it readable for
> applications which uses it. I mean like password in syslogins table. Is
> there a way to do it in sql server or do i need to use any third party
> tools. Can you please list out if there are any tools.
> Thanks,
> Subbu.
>
>|||Then you'll have to encrypt the data before storing it in SQL Server. Check
out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsecure/html/windataprotection-dpapi.asp
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:O0y$STLiGHA.2220@.TK2MSFTNGP05.phx.gbl...
We are using SQL Server 2000.
Thanks for the responses.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23B%23XkILiGHA.1276@.TK2MSFTNGP03.phx.gbl...
> See if this helps:
> http://www.sqlsecurity.com/FAQs/SQLServerFAQ/tabid/55/Default.aspx
> Which version of SQL Server are you using?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:OcHijBLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Any idea how to make sensitive data encrypted and make it readable for
> applications which uses it. I mean like password in syslogins table. Is
> there a way to do it in sql server or do i need to use any third party
> tools. Can you please list out if there are any tools.
> Thanks,
> Subbu.
>
>

How to make proper foreign keys in EM? And how to create tables using SQL statem

Hi!

I wonder how to make right foreign keys in Enterprise Manager. I use diagram but in this case I get 'rigid' keys, so I can't delete records. But I want it to set NULL on removing records.

And is it possible to create tables by writing SQl statements in EM? I tried but the new table didn't appear :(.Use Query Analyzer and you will know what are you doing (GUI does not show what it does).sql

How to make multiple-valued in one parameter in Query Builder?

Dear all,

I have a problem over here, hope to get some good advices from you guys.

I use Query Builder in VS2005 where you could see some tables on top, queries on the middle and resultset on the bottom windows.

I have a statement like below:

SELECT...WHERE Booking.BookingType IN (@.Type1, @.Type2, @.Type3, @.Type4, @.Type5)...

However, I found out that there are too many parameters, I wish to have only ONE parameters in the list of IN() operator. Is there any way to do it? Thanks!

I would suggest giving a look to Jens Suessmeyer's SPLIT function; the definition of this function can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

You ought to be able to code up your lists as comma separated strings and create a query that

looks something like this:

Code Snippet

SELECT...

from Booking

join split (@.yourParm, ',')

on Booking.BookingType = splitValue

Another alternative is to pass your argument as XML and use SQL Servers XML ability to interpret the XML argument.

Another question: Are you trying to make a parameter similar to a multi-value parameter that is available in Reporting Services? If the answer to that question is "YES" then you need to understand that feature is only available in Reporting Services and is not in general available to Transact SQL.

|||

If you are trying to reduce the number of parameters, just remove them from inside the paretheses.

In fact, if you are ONLY testing for a single parameter, use equals instead of IN (...) -it may be a bit more efficient.

|||

Actually, what I wish to have is putting a list of values in the @.Param programmatically in my code, thus, I wish to have one single @.Param instead of multiple @.Param(s) in SQL. I used Query Builder GUI to build my SQL query in VS2005, however, I couldn't find way to just put one @.Param and set multiple values in it.

For e.g.

SELECT * FROM Booking WHERE Type IN ( @.Param );

C# Code:

....DataTable1.GetData("Lab, Meeting, Course");

Is this possible to do this?

Thanks!

|||

There is no direct way to do something like

Code Snippet

SELECT * FROM Booking WHERE Type IN ( @.Param );

in which @.param contains data such as 'Lab, Meeting, Course'. Again, I would suggest using the SPLIT function to accomplish your objective.

Monday, March 26, 2012

How to make distributed query by SQL Express

I am going to make a distributed query, but in the query design view I cannot add the tables from another server, why?

hi,

AFAIK, the designers only show objects local to the connected server (and database)... but you can easely insert the required code pointing to remote linked servers...

regards

|||Thanks, i will try.

How to make correct join

Hi
I have to tables one called GROUPS and one called ACCOUNT

In table Group I have the follwing fields
Groupid, AccountFrom, AccountTo

In table Account I have
AccountNo, Name etc.

Records in Groups:
P1, 1001, 1002
P1, 1005, 1007
P1, 1010, 1010
P1, 1007, 1012

Now I want to have the corresponding AccountNo from ACCOUNT (from range
AccountFrom..AccountTo), that is the following result:
1001
1002
1005
1006
1007
1008
1009
1010
1011
1012

If f.x. 1008 doesn't exist in ACCOUNT this should not be listed. The
result will be used in another view.
My problem is that I also get every other record from table ACCOUNT.

Do anyone out there have a solution on my problem ?
BR/JanPost your current SELECT statement.
Usually , you would use an INNER JOIN for your requiremnts , i.e An inner
join returns all rows that result in a match .

Are you currently using a LEFT JOIN?

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<jannoergaard@.hotmail.com> wrote in message
news:1144159750.922225.272910@.v46g2000cwv.googlegr oups.com...
> Hi
> I have to tables one called GROUPS and one called ACCOUNT
> In table Group I have the follwing fields
> Groupid, AccountFrom, AccountTo
> In table Account I have
> AccountNo, Name etc.
> Records in Groups:
> P1, 1001, 1002
> P1, 1005, 1007
> P1, 1010, 1010
> P1, 1007, 1012
> Now I want to have the corresponding AccountNo from ACCOUNT (from range
> AccountFrom..AccountTo), that is the following result:
> 1001
> 1002
> 1005
> 1006
> 1007
> 1008
> 1009
> 1010
> 1011
> 1012
> If f.x. 1008 doesn't exist in ACCOUNT this should not be listed. The
> result will be used in another view.
> My problem is that I also get every other record from table ACCOUNT.
> Do anyone out there have a solution on my problem ?
> BR/Jan|||Hi there

As I recall the statement it's something like this

Select distinct dbo.Account.AccountNo as AccountNo
>From dbo.Account inner join dbo.Groups
On dbo.Groups.AccountFrom <= dbo.Account.AccountNo AND
dbo.Groups.AccountTo >= dbo.Account.AccountNo

Hope you have a solution for me. I have tried different ways but don't
seme to get it right.
BR /Jan|||(jannoergaard@.hotmail.com) writes:
> As I recall the statement it's something like this
> Select distinct dbo.Account.AccountNo as AccountNo
>>From dbo.Account inner join dbo.Groups
> On dbo.Groups.AccountFrom <= dbo.Account.AccountNo AND
> dbo.Groups.AccountTo >= dbo.Account.AccountNo
> Hope you have a solution for me. I have tried different ways but don't
> seme to get it right.

This is certainly better, since it relieves you from the DISTINCT:

SELECT a.AccountNp
FROM dbo.Account a
WHERE EXISTS (SELECT *
FROM dbo.Groups g
WHERE a.AccountNo BETWEEN g.AccountFrom AND g.AccountTo)

But whether that really addresses your issue, I cannot tell, because
your posting was very clear.

If this query does not work out, I suggest that you post the following:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result from the sample.

This permits anyone who wants to answer to copy and paste into
a query tool and develop a tested query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
Thanks very much. This worked out just as I wanted it to. Next time I
will have in mind what you suggested.
BR/Jan

How to make aspnet DB use local time zone?

I created aspnet DB on SQL Server 2K using aspnet_regsql utility. Everything works fine except the DateTime fields in all tables are using a wrong time zone. How do I set it to use my local time zone?

Any help will be greatly appreciated.

All stored procedures in aspnetdb use UTC-based datetime fields. That's pretty normal practice for any database that might need to be accessed from multiple time zones.

Friday, March 23, 2012

How to make a Trigger for more tan 1 table?

Hi,
I have to put a Trigger on +-100 tables, and the Trigger will perform the
same action. Is there away to Create jsut 1 trigger that will work for all
the tables?
This for:
- don't having to make 100 times the same trigger
- just having to do maintenance on only 1 trigger and not 100
Any help would be really aprpeciated,
Thansk a lot,
PieterWhat about sending the action inside the trigger to a common stored
procedure which could be maintained easily ?
(Therefore is must be coded very good to keep up SQL Server db health ;-) )
HTH, Jens Suessmeyer.
"DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
news:escuv1WSFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> I have to put a Trigger on +-100 tables, and the Trigger will perform the
> same action. Is there away to Create jsut 1 trigger that will work for all
> the tables?
> This for:
> - don't having to make 100 times the same trigger
> - just having to do maintenance on only 1 trigger and not 100
> Any help would be really aprpeciated,
> Thansk a lot,
> Pieter
>|||Hi
No, a trigger can only be bound to a single table.
A Trigger can call a stored procedure, but the Inserted and Deleted tables
are not available to the SP. You could put all your logic in the SP. But you
still have to put 100 triggers in place.
What does your trigger do?
Regards
Mike
"DraguVaso" wrote:
> Hi,
> I have to put a Trigger on +-100 tables, and the Trigger will perform the
> same action. Is there away to Create jsut 1 trigger that will work for all
> the tables?
> This for:
> - don't having to make 100 times the same trigger
> - just having to do maintenance on only 1 trigger and not 100
> Any help would be really aprpeciated,
> Thansk a lot,
> Pieter
>
>|||Thanks.
My Triggrer has to insert the SQL-Query that fired the trigger in another
table, and add if it was an update/insert/delete trigger.
So I guess I won't be able to put it in a seperate SP...
this is my trigger:
CREATE TRIGGER TriggerName
ON categories
FOR INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON
DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
@.QueryType as nvarchar(10)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
INSERT INTO #inputbuffer
EXEC (@.ExecStr)
SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
IF EXISTS (SELECT * FROM inserted)
BEGIN
SET @.QueryType = 'INSERT'
IF EXISTS (SELECT * FROM deleted)
BEGIN
SET @.QueryType = 'UPDATE'
END
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM deleted)
BEGIN
SET @.QueryType = 'DELETE'
END
END
INSERT INTO tblTransactions
SELECT @.Qry AS Query,
SYSTEM_USER as LoginName,
USER AS UserName,
CURRENT_TIMESTAMP AS QueryDate,
@.QueryType AS QueryType
END
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> Hi
> No, a trigger can only be bound to a single table.
> A Trigger can call a stored procedure, but the Inserted and Deleted tables
> are not available to the SP. You could put all your logic in the SP. But
you
> still have to put 100 triggers in place.
> What does your trigger do?
> Regards
> Mike
> "DraguVaso" wrote:
> > Hi,
> >
> > I have to put a Trigger on +-100 tables, and the Trigger will perform
the
> > same action. Is there away to Create jsut 1 trigger that will work for
all
> > the tables?
> > This for:
> > - don't having to make 100 times the same trigger
> > - just having to do maintenance on only 1 trigger and not 100
> >
> > Any help would be really aprpeciated,
> >
> > Thansk a lot,
> >
> > Pieter
> >
> >
> >|||Did you consider third party tools for this, tools that are using the transaction log and probably
have less overhead? I've listed some of those tools on my links page (the log reader tools).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Thanks.
> My Triggrer has to insert the SQL-Query that fired the trigger in another
> table, and add if it was an update/insert/delete trigger.
> So I guess I won't be able to put it in a seperate SP...
> this is my trigger:
> CREATE TRIGGER TriggerName
> ON categories
> FOR INSERT, UPDATE, DELETE AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
> @.QueryType as nvarchar(10)
> CREATE TABLE #inputbuffer
> (
> EventType nvarchar(30),
> Parameters int,
> EventInfo nvarchar(255)
> )
> SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
> INSERT INTO #inputbuffer
> EXEC (@.ExecStr)
> SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> IF EXISTS (SELECT * FROM inserted)
> BEGIN
> SET @.QueryType = 'INSERT'
> IF EXISTS (SELECT * FROM deleted)
> BEGIN
> SET @.QueryType = 'UPDATE'
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT * FROM deleted)
> BEGIN
> SET @.QueryType = 'DELETE'
> END
> END
> INSERT INTO tblTransactions
> SELECT @.Qry AS Query,
> SYSTEM_USER as LoginName,
> USER AS UserName,
> CURRENT_TIMESTAMP AS QueryDate,
> @.QueryType AS QueryType
> END
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
>> Hi
>> No, a trigger can only be bound to a single table.
>> A Trigger can call a stored procedure, but the Inserted and Deleted tables
>> are not available to the SP. You could put all your logic in the SP. But
> you
>> still have to put 100 triggers in place.
>> What does your trigger do?
>> Regards
>> Mike
>> "DraguVaso" wrote:
>> > Hi,
>> >
>> > I have to put a Trigger on +-100 tables, and the Trigger will perform
> the
>> > same action. Is there away to Create jsut 1 trigger that will work for
> all
>> > the tables?
>> > This for:
>> > - don't having to make 100 times the same trigger
>> > - just having to do maintenance on only 1 trigger and not 100
>> >
>> > Any help would be really aprpeciated,
>> >
>> > Thansk a lot,
>> >
>> > Pieter
>> >
>> >
>> >
>|||I did consider them, but they aren't free... :-(
do you know some opensource/free tools?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Did you consider third party tools for this, tools that are using the
transaction log and probably
> have less overhead? I've listed some of those tools on my links page (the
log reader tools).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
> > Thanks.
> > My Triggrer has to insert the SQL-Query that fired the trigger in
another
> > table, and add if it was an update/insert/delete trigger.
> >
> > So I guess I won't be able to put it in a seperate SP...
> >
> > this is my trigger:
> >
> > CREATE TRIGGER TriggerName
> > ON categories
> > FOR INSERT, UPDATE, DELETE AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
> > @.QueryType as nvarchar(10)
> >
> > CREATE TABLE #inputbuffer
> > (
> > EventType nvarchar(30),
> > Parameters int,
> > EventInfo nvarchar(255)
> > )
> >
> > SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
> >
> > INSERT INTO #inputbuffer
> > EXEC (@.ExecStr)
> >
> > SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> >
> > IF EXISTS (SELECT * FROM inserted)
> > BEGIN
> > SET @.QueryType = 'INSERT'
> > IF EXISTS (SELECT * FROM deleted)
> > BEGIN
> > SET @.QueryType = 'UPDATE'
> > END
> > END
> > ELSE
> > BEGIN
> > IF EXISTS (SELECT * FROM deleted)
> > BEGIN
> > SET @.QueryType = 'DELETE'
> > END
> > END
> >
> > INSERT INTO tblTransactions
> > SELECT @.Qry AS Query,
> > SYSTEM_USER as LoginName,
> > USER AS UserName,
> > CURRENT_TIMESTAMP AS QueryDate,
> > @.QueryType AS QueryType
> > END
> >
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> >> Hi
> >>
> >> No, a trigger can only be bound to a single table.
> >>
> >> A Trigger can call a stored procedure, but the Inserted and Deleted
tables
> >> are not available to the SP. You could put all your logic in the SP.
But
> > you
> >> still have to put 100 triggers in place.
> >>
> >> What does your trigger do?
> >>
> >> Regards
> >> Mike
> >>
> >> "DraguVaso" wrote:
> >>
> >> > Hi,
> >> >
> >> > I have to put a Trigger on +-100 tables, and the Trigger will perform
> > the
> >> > same action. Is there away to Create jsut 1 trigger that will work
for
> > all
> >> > the tables?
> >> > This for:
> >> > - don't having to make 100 times the same trigger
> >> > - just having to do maintenance on only 1 trigger and not 100
> >> >
> >> > Any help would be really aprpeciated,
> >> >
> >> > Thansk a lot,
> >> >
> >> > Pieter
> >> >
> >> >
> >> >
> >
> >
>|||> do you know some opensource/free tools?
I don't think that there are any free log reader tools out there. I recall some free tool that
generates triggers for you, but I didn't find it now, looking though my links. Might be worth
googling for, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:upWCEmXSFHA.2784@.TK2MSFTNGP12.phx.gbl...
>I did consider them, but they aren't free... :-(
> do you know some opensource/free tools?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> Did you consider third party tools for this, tools that are using the
> transaction log and probably
>> have less overhead? I've listed some of those tools on my links page (the
> log reader tools).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
>> > Thanks.
>> > My Triggrer has to insert the SQL-Query that fired the trigger in
> another
>> > table, and add if it was an update/insert/delete trigger.
>> >
>> > So I guess I won't be able to put it in a seperate SP...
>> >
>> > this is my trigger:
>> >
>> > CREATE TRIGGER TriggerName
>> > ON categories
>> > FOR INSERT, UPDATE, DELETE AS
>> > BEGIN
>> > SET NOCOUNT ON
>> >
>> > DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
>> > @.QueryType as nvarchar(10)
>> >
>> > CREATE TABLE #inputbuffer
>> > (
>> > EventType nvarchar(30),
>> > Parameters int,
>> > EventInfo nvarchar(255)
>> > )
>> >
>> > SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
>> >
>> > INSERT INTO #inputbuffer
>> > EXEC (@.ExecStr)
>> >
>> > SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
>> >
>> > IF EXISTS (SELECT * FROM inserted)
>> > BEGIN
>> > SET @.QueryType = 'INSERT'
>> > IF EXISTS (SELECT * FROM deleted)
>> > BEGIN
>> > SET @.QueryType = 'UPDATE'
>> > END
>> > END
>> > ELSE
>> > BEGIN
>> > IF EXISTS (SELECT * FROM deleted)
>> > BEGIN
>> > SET @.QueryType = 'DELETE'
>> > END
>> > END
>> >
>> > INSERT INTO tblTransactions
>> > SELECT @.Qry AS Query,
>> > SYSTEM_USER as LoginName,
>> > USER AS UserName,
>> > CURRENT_TIMESTAMP AS QueryDate,
>> > @.QueryType AS QueryType
>> > END
>> >
>> >
>> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> > news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
>> >> Hi
>> >>
>> >> No, a trigger can only be bound to a single table.
>> >>
>> >> A Trigger can call a stored procedure, but the Inserted and Deleted
> tables
>> >> are not available to the SP. You could put all your logic in the SP.
> But
>> > you
>> >> still have to put 100 triggers in place.
>> >>
>> >> What does your trigger do?
>> >>
>> >> Regards
>> >> Mike
>> >>
>> >> "DraguVaso" wrote:
>> >>
>> >> > Hi,
>> >> >
>> >> > I have to put a Trigger on +-100 tables, and the Trigger will perform
>> > the
>> >> > same action. Is there away to Create jsut 1 trigger that will work
> for
>> > all
>> >> > the tables?
>> >> > This for:
>> >> > - don't having to make 100 times the same trigger
>> >> > - just having to do maintenance on only 1 trigger and not 100
>> >> >
>> >> > Any help would be really aprpeciated,
>> >> >
>> >> > Thansk a lot,
>> >> >
>> >> > Pieter
>> >> >
>> >> >
>> >> >
>> >
>> >
>>
>|||ok thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OP9wqrXSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > do you know some opensource/free tools?
> I don't think that there are any free log reader tools out there. I recall
some free tool that
> generates triggers for you, but I didn't find it now, looking though my
links. Might be worth
> googling for, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:upWCEmXSFHA.2784@.TK2MSFTNGP12.phx.gbl...
> >I did consider them, but they aren't free... :-(
> > do you know some opensource/free tools?
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OU2C9VXSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> >> Did you consider third party tools for this, tools that are using the
> > transaction log and probably
> >> have less overhead? I've listed some of those tools on my links page
(the
> > log reader tools).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> news:OOLuVFXSFHA.2964@.TK2MSFTNGP15.phx.gbl...
> >> > Thanks.
> >> > My Triggrer has to insert the SQL-Query that fired the trigger in
> > another
> >> > table, and add if it was an update/insert/delete trigger.
> >> >
> >> > So I guess I won't be able to put it in a seperate SP...
> >> >
> >> > this is my trigger:
> >> >
> >> > CREATE TRIGGER TriggerName
> >> > ON categories
> >> > FOR INSERT, UPDATE, DELETE AS
> >> > BEGIN
> >> > SET NOCOUNT ON
> >> >
> >> > DECLARE @.ExecStr varchar(50), @.Qry nvarchar(1000),
> >> > @.QueryType as nvarchar(10)
> >> >
> >> > CREATE TABLE #inputbuffer
> >> > (
> >> > EventType nvarchar(30),
> >> > Parameters int,
> >> > EventInfo nvarchar(255)
> >> > )
> >> >
> >> > SET @.ExecStr = 'DBCC INPUTBUFFER(' + STR(@.@.SPID) + ')'
> >> >
> >> > INSERT INTO #inputbuffer
> >> > EXEC (@.ExecStr)
> >> >
> >> > SET @.Qry = (SELECT EventInfo FROM #inputbuffer)
> >> >
> >> > IF EXISTS (SELECT * FROM inserted)
> >> > BEGIN
> >> > SET @.QueryType = 'INSERT'
> >> > IF EXISTS (SELECT * FROM deleted)
> >> > BEGIN
> >> > SET @.QueryType = 'UPDATE'
> >> > END
> >> > END
> >> > ELSE
> >> > BEGIN
> >> > IF EXISTS (SELECT * FROM deleted)
> >> > BEGIN
> >> > SET @.QueryType = 'DELETE'
> >> > END
> >> > END
> >> >
> >> > INSERT INTO tblTransactions
> >> > SELECT @.Qry AS Query,
> >> > SYSTEM_USER as LoginName,
> >> > USER AS UserName,
> >> > CURRENT_TIMESTAMP AS QueryDate,
> >> > @.QueryType AS QueryType
> >> > END
> >> >
> >> >
> >> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> >> > news:0B9DFD0C-BF8A-4B46-B0D9-C74115A706F6@.microsoft.com...
> >> >> Hi
> >> >>
> >> >> No, a trigger can only be bound to a single table.
> >> >>
> >> >> A Trigger can call a stored procedure, but the Inserted and Deleted
> > tables
> >> >> are not available to the SP. You could put all your logic in the SP.
> > But
> >> > you
> >> >> still have to put 100 triggers in place.
> >> >>
> >> >> What does your trigger do?
> >> >>
> >> >> Regards
> >> >> Mike
> >> >>
> >> >> "DraguVaso" wrote:
> >> >>
> >> >> > Hi,
> >> >> >
> >> >> > I have to put a Trigger on +-100 tables, and the Trigger will
perform
> >> > the
> >> >> > same action. Is there away to Create jsut 1 trigger that will work
> > for
> >> > all
> >> >> > the tables?
> >> >> > This for:
> >> >> > - don't having to make 100 times the same trigger
> >> >> > - just having to do maintenance on only 1 trigger and not 100
> >> >> >
> >> >> > Any help would be really aprpeciated,
> >> >> >
> >> >> > Thansk a lot,
> >> >> >
> >> >> > Pieter
> >> >> >
> >> >> >
> >> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||Open source tools are not free. Like any other software (including
anything you develop) they have a cost of implementation and ownership.
Sorry to state the obvious but the fallacy that you implied is too
commonly repeated. The cost of acquiring software is typically only a
small fraction of the cost of owning and running it.
If you want to log SQL statements without any capital outlay then you
could use SQL Profiler. You pay for this in performance on your server
though, so you may well find the third-party tools are cheaper :-).
--
David Portas
SQL Server MVP
--

How to make a load test a database

Common question i thing. We finished a project and about to deploy but we need tto test it. Is there a way to load random value into our tables(we got 54 tables in it). Or we need to write an application to do this. Thanks for suggestionsVSDB has a data generator which can generate various based data, either on real data, pulled fromyour produtional system or random data, based on the possible range for the data types.

Jens K. Suessmeyer.

http://www.sqlserver2008.de
|||

When I had such a requirement used DTM data generator that has worked perfectly.

FYi

Friday, March 30, 2012

How to manage security with Access FE and SQL 2005 Express BE

how to manage concurrency between multiple, disconnected clients

How to make Update relation?

How to make Update relation?

how to make the contets of tables, case sensitive?

how to make the contents of tables, case sensitive?

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

Wednesday, March 28, 2012

How to make tables & stored procedures in MSDE?

How to make tables & stored procedures in MSDE?

How to make table relations using query.

how to make sensitive data encrypted in tables

how to make sensitive data encrypted in tables

How to make proper foreign keys in EM? And how to create tables using SQL statem

How to make multiple-valued in one parameter in Query Builder?

Monday, March 26, 2012

How to make distributed query by SQL Express

How to make correct join

How to make aspnet DB use local time zone?

Friday, March 23, 2012

How to make a Trigger for more tan 1 table?

How to make a Trigger for more tan 1 table?

How to make a load test a database

kill running query

Blog Archive

About Me