Granting Permissions on Multiple Tables

I created a new Role called GRPSELECT. I want to give this group the abilit
y to only run the SELECT statement on all the tables in my database. Right
now I can run "GRANT SELECT ON table TO GRPSELECT" in Query Analyzer, but it
only allows me to do this
to one table at a time.
How can I accomplish this to all 600 tables in the database without manually
typing in every single table?
Thanks in advance!Instead of creating a new role you can add these users/group to
db_datareader fixed db role.
Members of db_datareader fixed db role have select permissions on any
objects in the db.
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclaimer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Jon Jones" <Jon Jones@.discussions.microsoft.com> wrote in message
news:96F7208A-EA64-467A-8AD2-5477782ED32E@.microsoft.com...
> I created a new Role called GRPSELECT. I want to give this group the
ability to only run the SELECT statement on all the tables in my database.
Right now I can run "GRANT SELECT ON table TO GRPSELECT" in Query Analyzer,
but it only allows me to do this to one table at a time.
> How can I accomplish this to all 600 tables in the database without
manually typing in every single table?
> Thanks in advance!|||Jon,
You can use Transact-SQL to generate the script for you:
Ex:
SELECT 'GRANT SELECT ON ' + so.name + ' TO GRPSELECT'
FROM dbo.sysobjects so
WHERE so.type = 'u'
This output can then be copied.
Randy Dyess
"Jon Jones" wrote:

> I created a new Role called GRPSELECT. I want to give this group the ability to o
nly run the SELECT statement on all the tables in my database. Right now I can run
"GRANT SELECT ON table TO GRPSELECT" in Query Analyzer, but it only allows me to do
thi
s to one table at a time.
> How can I accomplish this to all 600 tables in the database without manual
ly typing in every single table?
> Thanks in advance!sql

Granting permission on multiple stored procs

Is there a better way of granting permission to a stored proc for a selected user other than (enterprise manager) select sp then accessing propertys then permissions, then user?GRANT EXEC ON <proc> TO <MyUser>

just about anything you can do in the EM can be scripted saved as script and used again. which makes you more efficient.|||but the rest of the story is this...
the project is a work in progress thing. we are added new stored procs as needed (up to 83 as of this morning) and now we wanted a selected user to test the new project. I was looking for a way to not have to type out all 83 stored proc names in order to grant access to them for this user. I was hoping for a solution/feature that i was not aware of in EM. so it looks like i will have to do this process manually either way (EM or Script)...|||select 'grant execute on ' + name + ' to user'
from sysobjects
where type = 'P'
and crdate > getdate() -1|||You are using Database Roles, I hope.

granting permission

Want to grant permission for multiple tables to a user or role. is there a w
ay to do this with transact-SQL.
Please help!!!!!
Thanks.
A.SYes, look up GRANT in Books Online.
"J C" <anonymous@.discussions.microsoft.com> wrote in message
news:96B8B085-4A59-45B5-B2F6-1FF3336628FC@.microsoft.com...
> Want to grant permission for multiple tables to a user or role. is there a
way to do this with transact-SQL.
> Please help!!!!!
> Thanks.
> A.Ssql

Good or Bad Idea? Multiple Tables for Data Import

Hello all,
We have an application which runs on a campaign basis. Data is loaded
throughout the day into our application. We typically load 20K - 30K rows
of data per campaign per day (total of about 150K - 200K row of data per
day).
We typically run about 30 - 35 campaigns simultaneously.
In order to increase the performance of our application, I was thinking of
loading each campaign into it's own table. The campaign tables would all be
identical. This would allow us to index each of these tables before each
campaign run to ensure the best possible performance.
What do you guys think?
We're finding that our application is being bogged down - we need to query
the tables continously as the application run... and return record sets
with minimal time.
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
Lucas
Have you read "Partitioned View" article in the BOL? If I understood you
correctly , that what you need .
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D12EDD5C9ECnntprogerscom@.127.0.0.1...
> Hello all,
> We have an application which runs on a campaign basis. Data is loaded
> throughout the day into our application. We typically load 20K - 30K rows
> of data per campaign per day (total of about 150K - 200K row of data per
> day).
> We typically run about 30 - 35 campaigns simultaneously.
> In order to increase the performance of our application, I was thinking of
> loading each campaign into it's own table. The campaign tables would all
> be
> identical. This would allow us to index each of these tables before each
> campaign run to ensure the best possible performance.
> What do you guys think?
> We're finding that our application is being bogged down - we need to query
> the tables continously as the application run... and return record sets
> with minimal time.
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:

> Lucas
> Have you read "Partitioned View" article in the BOL? If I understood
> you correctly , that what you need .
Thanks URI, that seems to be what we need.
From your experience, would we gain a lot of performance by segmenting data
into it's own table?
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
|||Hi
Yes, if you have appropriate indexes defined on the table you will be
benefit from performance.
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D180999FD22nntprogerscom@.127.0.0.1...
> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:
>
> Thanks URI, that seems to be what we need.
> From your experience, would we gain a lot of performance by segmenting
> data
> into it's own table?
>
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com

Good or Bad Idea? Multiple Tables for Data Import

Hello all,
We have an application which runs on a campaign basis. Data is loaded
throughout the day into our application. We typically load 20K - 30K rows
of data per campaign per day (total of about 150K - 200K row of data per
day).
We typically run about 30 - 35 campaigns simultaneously.
In order to increase the performance of our application, I was thinking of
loading each campaign into it's own table. The campaign tables would all be
identical. This would allow us to index each of these tables before each
campaign run to ensure the best possible performance.
What do you guys think?
We're finding that our application is being bogged down - we need to query
the tables continously as the application run... and return record sets
with minimal time.
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.comLucas
Have you read "Partitioned View" article in the BOL? If I understood you
correctly , that what you need .
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D12EDD5C9ECnntprogerscom@.127.0.0.1...
> Hello all,
> We have an application which runs on a campaign basis. Data is loaded
> throughout the day into our application. We typically load 20K - 30K rows
> of data per campaign per day (total of about 150K - 200K row of data per
> day).
> We typically run about 30 - 35 campaigns simultaneously.
> In order to increase the performance of our application, I was thinking of
> loading each campaign into it's own table. The campaign tables would all
> be
> identical. This would allow us to index each of these tables before each
> campaign run to ensure the best possible performance.
> What do you guys think?
> We're finding that our application is being bogged down - we need to query
> the tables continously as the application run... and return record sets
> with minimal time.
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:

> Lucas
> Have you read "Partitioned View" article in the BOL? If I understood
> you correctly , that what you need .
Thanks URI, that seems to be what we need.
From your experience, would we gain a lot of performance by segmenting data
into it's own table?
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Hi
Yes, if you have appropriate indexes defined on the table you will be
benefit from performance.
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D180999FD22nntprogerscom@.127.0.0.1...
> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:
>
>
> Thanks URI, that seems to be what we need.
> From your experience, would we gain a lot of performance by segmenting
> data
> into it's own table?
>
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com

Good or Bad Idea? Multiple Tables for Data Import

Hello all,
We have an application which runs on a campaign basis. Data is loaded
throughout the day into our application. We typically load 20K - 30K rows
of data per campaign per day (total of about 150K - 200K row of data per
day).
We typically run about 30 - 35 campaigns simultaneously.
In order to increase the performance of our application, I was thinking of
loading each campaign into it's own table. The campaign tables would all be
identical. This would allow us to index each of these tables before each
campaign run to ensure the best possible performance.
What do you guys think?
We're finding that our application is being bogged down - we need to query
the tables continously as the application run... and return record sets
with minimal time.
Thanks.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.comLucas
Have you read "Partitioned View" article in the BOL? If I understood you
correctly , that what you need .
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D12EDD5C9ECnntprogerscom@.127.0.0.1...
> Hello all,
> We have an application which runs on a campaign basis. Data is loaded
> throughout the day into our application. We typically load 20K - 30K rows
> of data per campaign per day (total of about 150K - 200K row of data per
> day).
> We typically run about 30 - 35 campaigns simultaneously.
> In order to increase the performance of our application, I was thinking of
> loading each campaign into it's own table. The campaign tables would all
> be
> identical. This would allow us to index each of these tables before each
> campaign run to ensure the best possible performance.
> What do you guys think?
> We're finding that our application is being bogged down - we need to query
> the tables continously as the application run... and return record sets
> with minimal time.
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:
> Lucas
> Have you read "Partitioned View" article in the BOL? If I understood
> you correctly , that what you need .
Thanks URI, that seems to be what we need.
From your experience, would we gain a lot of performance by segmenting data
into it's own table?
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Hi
Yes, if you have appropriate indexes defined on the table you will be
benefit from performance.
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D180999FD22nntprogerscom@.127.0.0.1...
> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:
>> Lucas
>> Have you read "Partitioned View" article in the BOL? If I understood
>> you correctly , that what you need .
>
> Thanks URI, that seems to be what we need.
> From your experience, would we gain a lot of performance by segmenting
> data
> into it's own table?
>
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com

Going after repeat offenders in SP

Issue is a batch processing SP that does multiple inserts (6 tables)
This statement contains a "repeat offending" NOT IN ( ) clause.
-- start code
SELECT
@.BatchID, MA.Merchant_Account_ID, 0, 0, @.Batch_Date, 0, 0
FROM Merchant_Account MA
WHERE Merchant_Account_ID NOT IN (SELECT El_Account_ID FROM
Operating_Account_Summary WHERE Transaction_Batch_ID=@.BatchID)
ORDER BY Merchant_Account_ID
Would it be better off to do that selection one time into a #T1 and
reference that all the needed times, finally dropping it at the end? Of
course I'd create it outside of the transaction and drop it likewise.
Table has 1/2 million rows now and growing @. 10,000 per w.
TIAIs it that the rows generated by this SELECT are to be inserted into
multiple tables? If so, it would make sense to populate a temp table and
then use it. I'm curious as to why these same rows have to be inserted into
multiple tables. I sit possible to use one table and reference it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"_Stephen" <srussell@.electracash.com> wrote in message
news:uIH3csTmGHA.492@.TK2MSFTNGP05.phx.gbl...
Issue is a batch processing SP that does multiple inserts (6 tables)
This statement contains a "repeat offending" NOT IN ( ) clause.
-- start code
SELECT
@.BatchID, MA.Merchant_Account_ID, 0, 0, @.Batch_Date, 0, 0
FROM Merchant_Account MA
WHERE Merchant_Account_ID NOT IN (SELECT El_Account_ID FROM
Operating_Account_Summary WHERE Transaction_Batch_ID=@.BatchID)
ORDER BY Merchant_Account_ID
Would it be better off to do that selection one time into a #T1 and
reference that all the needed times, finally dropping it at the end? Of
course I'd create it outside of the transaction and drop it likewise.
Table has 1/2 million rows now and growing @. 10,000 per w.
TIA|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4con5TmGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Is it that the rows generated by this SELECT are to be inserted into
> multiple tables? If so, it would make sense to populate a temp table and
> then use it. I'm curious as to why these same rows have to be inserted
> into
> multiple tables. I sit possible to use one table and reference it?
Thanks for the reply.
It will insert different data table depending, but it's initial where is the
key not in (select key from other table)
So I will refactor this and other sp's that follow the same sense of
nonsense. Actually it's a refactor of an app that only needed to do this
once, but the new and better data layout needs the same call a few more
times.

Globally Change SQL Svc Account passwords

Hello. Is there any batch method to do a mass change of
multiple server's SQL Server Service account and/or
passwords? Just looking for any tips on changing MANY SQL
boxes' Service startup accounts. Is there any batch
commands to set a service account logon? THanks, BruceBelow is a VBScript to change service passwords. It will change the
password for all of the services on the specified servers running under
the account.
'begin script
Option Explicit
Dim oWin32_Services, oWin32_Service
Dim ServerName, StartName, NewPassword, Messages
' *** specify Windows account and new password here ***
StartName = "Domain\Account" 'Windows service account name
NewPassword = "NewAccountPassword"
' **************************
' *** specify server list here ***
Call ChangeServerServicePasswords("ServerName1", _
StartName, _
NewPassword, _
Messages)
Call ChangeServerServicePasswords("ServerName2", _
StartName, _
NewPassword, _
Messages)
' **************************
WScript.Echo Messages
Sub ChangeServerServicePasswords(ServerName, _
StartName, _
NewPassword, _
Messages)
Dim SQL
'select all services running under this account
SQL = "SELECT * FROM Win32_Service WHERE StartName = '" & _
Replace(StartName, "\", "\\") & "'"
Set oWin32_Services =GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
ServerName & _
"/root/cimv2").ExecQuery(SQL, , 48)
For Each oWin32_Service In oWin32_Services
Call ChangeServicePassword(oWin32_Service, _
NewPassword, _
Messages)
Next
End Sub
Sub ChangeServicePassword(oWin32_Service, _
NewPassword, _
Messages)
Dim intResult
intResult = oWin32_Service.Change(,,,,,,,NewPassword)
If intResult = 0 Then
Messages = Messages & _
oWin32_Service.SystemName & " " & _
oWin32_Service.Caption & _
" service account password changed for account " & _
oWin32_Service.StartName & vbcrlf
Else
Messages = Messages & _
oWin32_Service.SystemName & " " & _
oWin32_Service.Caption & _
" service account password change failed for account " & _
oWin32_Service.StartName & _
". Win32_Service.Change result is " & _
CStr(intResult) & vbcrlf
End If
End Sub
'end scrpt
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:056a01c3addf$98198f30$a001280a@.phx.gbl...
> Hello. Is there any batch method to do a mass change of
> multiple server's SQL Server Service account and/or
> passwords? Just looking for any tips on changing MANY SQL
> boxes' Service startup accounts. Is there any batch
> commands to set a service account logon? THanks, Bruce|||Maybe if you code something through WMI or something
similar, but there is no way I know of.
And if you're on a cluster, you have to do it through
Enterprise Manager anyway, so it wouldn't take care of
failover clustering installations of SQL Server.
>--Original Message--
>Hello. Is there any batch method to do a mass change of
>multiple server's SQL Server Service account and/or
>passwords? Just looking for any tips on changing MANY
SQL
>boxes' Service startup accounts. Is there any batch
>commands to set a service account logon? THanks, Bruce
>.
>|||wow... Great stuff Dan, thanks! Bruce
>--Original Message--
>Below is a VBScript to change service passwords. It will
change the
>password for all of the services on the specified servers
running under
>the account.
>
>'begin script
>Option Explicit
>Dim oWin32_Services, oWin32_Service
>Dim ServerName, StartName, NewPassword, Messages
>' *** specify Windows account and new password here ***
>StartName = "Domain\Account" 'Windows service account
name
>NewPassword = "NewAccountPassword"
>' **************************
>' *** specify server list here ***
>Call ChangeServerServicePasswords("ServerName1", _
> StartName, _
> NewPassword, _
> Messages)
>Call ChangeServerServicePasswords("ServerName2", _
> StartName, _
> NewPassword, _
> Messages)
>' **************************
>WScript.Echo Messages
>Sub ChangeServerServicePasswords(ServerName, _
> StartName, _
> NewPassword, _
> Messages)
> Dim SQL
> 'select all services running under this account
> SQL = "SELECT * FROM Win32_Service WHERE StartName
= '" & _
> Replace(StartName, "\", "\\") & "'"
> Set oWin32_Services =>GetObject("winmgmts:{impersonationLevel=impersonate}!//"
& _
> ServerName & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_Service In oWin32_Services
> Call ChangeServicePassword(oWin32_Service, _
> NewPassword, _
> Messages)
> Next
>End Sub
>Sub ChangeServicePassword(oWin32_Service, _
> NewPassword, _
> Messages)
> Dim intResult
> intResult = oWin32_Service.Change(,,,,,,,NewPassword)
> If intResult = 0 Then
> Messages = Messages & _
> oWin32_Service.SystemName & " " & _
> oWin32_Service.Caption & _
> " service account password changed for
account " & _
> oWin32_Service.StartName & vbcrlf
> Else
> Messages = Messages & _
> oWin32_Service.SystemName & " " & _
> oWin32_Service.Caption & _
> " service account password change failed for
account " & _
> oWin32_Service.StartName & _
> ". Win32_Service.Change result is " & _
> CStr(intResult) & vbcrlf
> End If
>End Sub
>'end scrpt
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:056a01c3addf$98198f30$a001280a@.phx.gbl...
>> Hello. Is there any batch method to do a mass change of
>> multiple server's SQL Server Service account and/or
>> passwords? Just looking for any tips on changing MANY
SQL
>> boxes' Service startup accounts. Is there any batch
>> commands to set a service account logon? THanks, Bruce
>
>.
>

Global Variable

Dear All!
I want to know that how can i declare a global variable in database, assign some value to it, then using it in multiple triggers and procedure then deallocating that.
Please provide a smal example.
Regards,
Shabber.Create a User-Defined Function that returns the value?|||And deallocate it?

What the heck are you doing?

UDF is the only way to simulate a global variable, but if you then wipe out your UDF it will break your sprocs.|||I'd assume Shabber has no big need for deallocating. It's probably just that if there would have been such a thing as a global variable, then it would have been a good habit to deallocate once it wasn't needed anymore.|||Thanks for replying.

But how function will provide the functionality of Global Variables. A bit confusing.

Regards,
Shabber.|||You call the function, which will return the value you're after.|||Thanks for replying.

But how function will provide the functionality of Global Variables. A bit confusing.

Regards,
Shabber.

CREATE TABLE MyParameter (
SiteID int IDENTITY (1,1) NOT NULL,
SiteName varchar(255) NOT NULL
)

CREATE FUNCTION udfMyFunction
(@.p1 int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.sTemp varchar(255)

SELECT @.sTemp = SiteName FROM MyParameter WHERE SiteID = @.p1
RETURN @.sTemp
END

Example Data and Usage:

insert into MyParameter(SiteName) Values ('Foo')
insert into MyParameter(SiteName) Values ('Bar')

SELECT dbo.udfMyFunction (2)

I don't know that this example is all that useful, but maybe it will give you some ideas.

Regards,

hmscott

Global update of multiple tables

Hi
I need to globally update certain tables over multiple databases. I have
about 300 tables in each database and only a handfull will be the same.
Can I do this with replication? Can you setup replication for only certain
tables?
Jaco,
replication is seldom all the tables in a database - mostly a publication
contains a small subset of the tables. I'd recommend having a good look a
BOL (books on line) and the book in my signature below.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul
"Paul Ibison" wrote:

> Jaco,
> replication is seldom all the tables in a database - mostly a publication
> contains a small subset of the tables. I'd recommend having a good look a
> BOL (books on line) and the book in my signature below.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Global temp tables in SQL Server

Hi!
I have a stored proc that creates a global temp table. How can I have multiple users select records from and insert records in that table without overwriting and/or deleting data?
Thanks so much for your help!
-ParulA global temp table is like any other table.
How would you prevent users from overwriting data in a permanent table?
A better question is why are you using a global temporary table, and would local temporary tables suffice?|||I am using a global temp table because I have the following:

Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)

Is it possible to somehow lock these tables so the records inserted by one are not affected by another. Is there a way to use some kind of a userID?|||I am using a global temp table because I have the following:

Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)This does note require the use of "GLOBAL" temporary tables (prefixed with "##"). It only requires "LOCAL" temporary tables (prefixed with "#").|||Won't the variables lose scope if local temp tables are used?|||Not if one procedure calls the next. Then they are nested, and any temporary table declared in the outermost procedure exists until that procedure completes, making it available to the nested procedures.|||The downside to any temp table being used by nested procs is that each and every nested proc will recompile each time it is called. This could cause severe overhead in the execution time.|||ehhhhh...I've yet to see compile time become a factor outside of processes that are run hundreds of times per hour. And you'd have the same problem with global temporary tables.|||In any case....

Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction

Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls|||That should work, thanks!|||You should make a permanent table though...what for example, do you think would happen if you had a global table in existance and then tried to create it again?

Maybe you can explain to use what you are trying to do instead of trying to sledge hammer a solution with technology

Simple is usually the best answer|||while i am using a global table, if the stored proc is running in multiple sessions, it errors out saying "##temp already exists" or "it cannot be dropped."|||In any case....

Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction

Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls
We gotta agree to disagree on this one. I doubt that a permanent table is an appropriate solution tohis problem, and adding the SPID to a global temporary table is just a hack for treating it like a local temporary table.|||so what else do you recommend i should try?|||Local.

Temporary.

Tables.|||Who's we?

And you mean respectfully diagree...you wouldn't want to get banned

In any case, I'd rather know what you are really trying to accomplish in the first place.

Also, it would be interesting to know how much data we are talking about.|||You lookin' fer a fight, Joisey-boy?

You are free to "diagree" with me, while'st I shall "disagree" with you (respectfully, as always).

Global Temp Table & Multiple Users

I have a stored procedure that *must* create a global
temporary table (##myTable), but the problem is that
there are multiple users which could be using this stored
procedure.
What best practices are there for dealing with this
problem?
Ideally, I want to queue up the execution if multiple
users all run the proc at the same time, rather than
return an IsRunning status of sorts and then waiting in
the application.
Any suggestions?
NOTE: I cannot change the name of the table dynamically
(i.e. '##myTable' + @.UserID) because of how I have to
deal with the data in the procedure (and nested
procedures).
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> I have a stored procedure that *must* create a global
> temporary table (##myTable), but the problem is that
Why must it create a global temporary table? Is there truly no other
way for you to achieve your goal? Please post some more about your
situation. To date I've never seen any absolute requirement for a global
temporary table.
|||Force each connection to do all of the processing related to the table
within a transaction, and at the start of the transaction get an exclusive
lock on the table. That will prevent mutliple connections from interfering
with the population and use of the table - but it will serialize them. Your
ideal design will require a pessimistic locking approach where the
application does some sort of checkout / reservation logic - nothing in sql
server that I know of that will do this for you.
The best approach is to avoid the global temp table, as Adam indicated.
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> I have a stored procedure that *must* create a global
> temporary table (##myTable), but the problem is that
> there are multiple users which could be using this stored
> procedure.
> What best practices are there for dealing with this
> problem?
> Ideally, I want to queue up the execution if multiple
> users all run the proc at the same time, rather than
> return an IsRunning status of sorts and then waiting in
> the application.
> Any suggestions?
> NOTE: I cannot change the name of the table dynamically
> (i.e. '##myTable' + @.UserID) because of how I have to
> deal with the data in the procedure (and nested
> procedures).
>
|||This is kind of where I ended up going.
The reason that I have to use Global Temporary tables is
because one nested stored proc controls the creation of
the table and then the outer procedure uses the results.
Essentially, what is happening is this a dynamic SQL
query builder which uses the results of another query to
construct the SQL.
The inner proc generates a portion of the SQL, the outer
proc executes it, then when the execution is complete,
the outer proc uses it to complete the SQL.
After more careful review, I can actually create a
dynamically named global temporary table, but I would
have to build several more nested stored procedures to
pull it off. It is turning into an awful mess.
What would really be nice is
SELECT *
INTO #@.TableName
FROM Employees
(You listening MS?)
All my problems would be solved.
I am going to try the transaction locking and see if
deadlocks result. Otherwise, I am going to have to go
deeper into nesting stored procs.

>--Original Message--
>Force each connection to do all of the processing
related to the table
>within a transaction, and at the start of the
transaction get an exclusive
>lock on the table. That will prevent mutliple
connections from interfering
>with the population and use of the table - but it will
serialize them. Your
>ideal design will require a pessimistic locking approach
where the
>application does some sort of checkout / reservation
logic - nothing in sql
>server that I know of that will do this for you.
>The best approach is to avoid the global temp table, as
Adam indicated.
>"Steven" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
stored
>
>.
>
|||One more thing....
Anyone see any problems with this method of creating a
temporary table?
DECLARE @.TempTable varchar(200)
SET @.TempTable = '##BaseName_' + REPLACE( CAST( NewID
() as varchar(80) ), '-', '' )
Seems to work even when two tables are created
milliseconds apart from one another.
Am I going to be in trouble later?
Thanks for all your help!

>--Original Message--
>Force each connection to do all of the processing
related to the table
>within a transaction, and at the start of the
transaction get an exclusive
>lock on the table. That will prevent mutliple
connections from interfering
>with the population and use of the table - but it will
serialize them. Your
>ideal design will require a pessimistic locking approach
where the
>application does some sort of checkout / reservation
logic - nothing in sql
>server that I know of that will do this for you.
>The best approach is to avoid the global temp table, as
Adam indicated.
>"Steven" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
stored
>
>.
>
|||Why don't you create a permanent table that uses uniqueidentifiers to track
calls? Then the inner proc can just return the correct uniqueidentifier to
the outer proc.
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:278901c49c26$90228c10$a601280a@.phx.gbl...[vbcol=seagreen]
> This is kind of where I ended up going.
> The reason that I have to use Global Temporary tables is
> because one nested stored proc controls the creation of
> the table and then the outer procedure uses the results.
> Essentially, what is happening is this a dynamic SQL
> query builder which uses the results of another query to
> construct the SQL.
> The inner proc generates a portion of the SQL, the outer
> proc executes it, then when the execution is complete,
> the outer proc uses it to complete the SQL.
> After more careful review, I can actually create a
> dynamically named global temporary table, but I would
> have to build several more nested stored procedures to
> pull it off. It is turning into an awful mess.
> What would really be nice is
> SELECT *
> INTO #@.TableName
> FROM Employees
> (You listening MS?)
> All my problems would be solved.
> I am going to try the transaction locking and see if
> deadlocks result. Otherwise, I am going to have to go
> deeper into nesting stored procs.
> related to the table
> transaction get an exclusive
> connections from interfering
> serialize them. Your
> where the
> logic - nothing in sql
> Adam indicated.
> message
> stored
|||Also:
http://www.sommarskog.se/share_data.html
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:278901c49c26$90228c10$a601280a@.phx.gbl...
> This is kind of where I ended up going.
> The reason that I have to use Global Temporary tables is
> because one nested stored proc controls the creation of
> the table and then the outer procedure uses the results.
|||dynamic table names will require the use of dynamic sql. be certain that
you need this before you start down this path. It is much easier to design
/ code / debug when the table name is static. You might also need to
"delimit" the table name if non-standard characters are included in the
name.
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:279001c49c27$1c736a40$a601280a@.phx.gbl...[vbcol=seagreen]
> One more thing....
> Anyone see any problems with this method of creating a
> temporary table?
> DECLARE @.TempTable varchar(200)
> SET @.TempTable = '##BaseName_' + REPLACE( CAST( NewID
> () as varchar(80) ), '-', '' )
> Seems to work even when two tables are created
> milliseconds apart from one another.
> Am I going to be in trouble later?
> Thanks for all your help!
> related to the table
> transaction get an exclusive
> connections from interfering
> serialize them. Your
> where the
> logic - nothing in sql
> Adam indicated.
> message
> stored
|||I've done the something very similar for reporting purposes. Serialization
was acceptable since it was more important to use a consistent methodology
for the generation of the data (also, it was designed in v7 without a
complete set of requirements nor a good understanding of the data and how
ubiqitous it would become). In hindsight, it might have been better to use
regular temp tables, although that would have required a much different
design and I'm not sure if performance would be any better. The good part
about serialization is that it limits the dbms pounding that these reports
involve. Actually, the real problem is that the system is designed to
function as both an OLTP and a data warehouse - if only the requirements had
been known before coding started!
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:278901c49c26$90228c10$a601280a@.phx.gbl...[vbcol=seagreen]
> This is kind of where I ended up going.
> The reason that I have to use Global Temporary tables is
> because one nested stored proc controls the creation of
> the table and then the outer procedure uses the results.
> Essentially, what is happening is this a dynamic SQL
> query builder which uses the results of another query to
> construct the SQL.
> The inner proc generates a portion of the SQL, the outer
> proc executes it, then when the execution is complete,
> the outer proc uses it to complete the SQL.
> After more careful review, I can actually create a
> dynamically named global temporary table, but I would
> have to build several more nested stored procedures to
> pull it off. It is turning into an awful mess.
> What would really be nice is
> SELECT *
> INTO #@.TableName
> FROM Employees
> (You listening MS?)
> All my problems would be solved.
> I am going to try the transaction locking and see if
> deadlocks result. Otherwise, I am going to have to go
> deeper into nesting stored procs.
> related to the table
> transaction get an exclusive
> connections from interfering
> serialize them. Your
> where the
> logic - nothing in sql
> Adam indicated.
> message
> stored

Global Temp Table & Multiple Users

I have a stored procedure that *must* create a global
temporary table (##myTable), but the problem is that
there are multiple users which could be using this stored
procedure.
What best practices are there for dealing with this
problem?
Ideally, I want to queue up the execution if multiple
users all run the proc at the same time, rather than
return an IsRunning status of sorts and then waiting in
the application.
Any suggestions?
NOTE: I cannot change the name of the table dynamically
(i.e. '##myTable' + @.UserID) because of how I have to
deal with the data in the procedure (and nested
procedures)."Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> I have a stored procedure that *must* create a global
> temporary table (##myTable), but the problem is that
Why must it create a global temporary table? Is there truly no other
way for you to achieve your goal? Please post some more about your
situation. To date I've never seen any absolute requirement for a global
temporary table.|||Force each connection to do all of the processing related to the table
within a transaction, and at the start of the transaction get an exclusive
lock on the table. That will prevent mutliple connections from interfering
with the population and use of the table - but it will serialize them. Your
ideal design will require a pessimistic locking approach where the
application does some sort of checkout / reservation logic - nothing in sql
server that I know of that will do this for you.
The best approach is to avoid the global temp table, as Adam indicated.
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> I have a stored procedure that *must* create a global
> temporary table (##myTable), but the problem is that
> there are multiple users which could be using this stored
> procedure.
> What best practices are there for dealing with this
> problem?
> Ideally, I want to queue up the execution if multiple
> users all run the proc at the same time, rather than
> return an IsRunning status of sorts and then waiting in
> the application.
> Any suggestions?
> NOTE: I cannot change the name of the table dynamically
> (i.e. '##myTable' + @.UserID) because of how I have to
> deal with the data in the procedure (and nested
> procedures).
>|||This is kind of where I ended up going.
The reason that I have to use Global Temporary tables is
because one nested stored proc controls the creation of
the table and then the outer procedure uses the results.
Essentially, what is happening is this a dynamic SQL
query builder which uses the results of another query to
construct the SQL.
The inner proc generates a portion of the SQL, the outer
proc executes it, then when the execution is complete,
the outer proc uses it to complete the SQL.
After more careful review, I can actually create a
dynamically named global temporary table, but I would
have to build several more nested stored procedures to
pull it off. It is turning into an awful mess.
What would really be nice is
SELECT *
INTO #@.TableName
FROM Employees
(You listening MS')
All my problems would be solved.
I am going to try the transaction locking and see if
deadlocks result. Otherwise, I am going to have to go
deeper into nesting stored procs.
>--Original Message--
>Force each connection to do all of the processing
related to the table
>within a transaction, and at the start of the
transaction get an exclusive
>lock on the table. That will prevent mutliple
connections from interfering
>with the population and use of the table - but it will
serialize them. Your
>ideal design will require a pessimistic locking approach
where the
>application does some sort of checkout / reservation
logic - nothing in sql
>server that I know of that will do this for you.
>The best approach is to avoid the global temp table, as
Adam indicated.
>"Steven" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
>> I have a stored procedure that *must* create a global
>> temporary table (##myTable), but the problem is that
>> there are multiple users which could be using this
stored
>> procedure.
>> What best practices are there for dealing with this
>> problem?
>> Ideally, I want to queue up the execution if multiple
>> users all run the proc at the same time, rather than
>> return an IsRunning status of sorts and then waiting in
>> the application.
>> Any suggestions?
>> NOTE: I cannot change the name of the table dynamically
>> (i.e. '##myTable' + @.UserID) because of how I have to
>> deal with the data in the procedure (and nested
>> procedures).
>>
>
>.
>|||One more thing....
Anyone see any problems with this method of creating a
temporary table?
DECLARE @.TempTable varchar(200)
SET @.TempTable = '##BaseName_' + REPLACE( CAST( NewID
() as varchar(80) ), '-', '' )
Seems to work even when two tables are created
milliseconds apart from one another.
Am I going to be in trouble later?
Thanks for all your help!
>--Original Message--
>Force each connection to do all of the processing
related to the table
>within a transaction, and at the start of the
transaction get an exclusive
>lock on the table. That will prevent mutliple
connections from interfering
>with the population and use of the table - but it will
serialize them. Your
>ideal design will require a pessimistic locking approach
where the
>application does some sort of checkout / reservation
logic - nothing in sql
>server that I know of that will do this for you.
>The best approach is to avoid the global temp table, as
Adam indicated.
>"Steven" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
>> I have a stored procedure that *must* create a global
>> temporary table (##myTable), but the problem is that
>> there are multiple users which could be using this
stored
>> procedure.
>> What best practices are there for dealing with this
>> problem?
>> Ideally, I want to queue up the execution if multiple
>> users all run the proc at the same time, rather than
>> return an IsRunning status of sorts and then waiting in
>> the application.
>> Any suggestions?
>> NOTE: I cannot change the name of the table dynamically
>> (i.e. '##myTable' + @.UserID) because of how I have to
>> deal with the data in the procedure (and nested
>> procedures).
>>
>
>.
>|||Why don't you create a permanent table that uses uniqueidentifiers to track
calls? Then the inner proc can just return the correct uniqueidentifier to
the outer proc.
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:278901c49c26$90228c10$a601280a@.phx.gbl...
> This is kind of where I ended up going.
> The reason that I have to use Global Temporary tables is
> because one nested stored proc controls the creation of
> the table and then the outer procedure uses the results.
> Essentially, what is happening is this a dynamic SQL
> query builder which uses the results of another query to
> construct the SQL.
> The inner proc generates a portion of the SQL, the outer
> proc executes it, then when the execution is complete,
> the outer proc uses it to complete the SQL.
> After more careful review, I can actually create a
> dynamically named global temporary table, but I would
> have to build several more nested stored procedures to
> pull it off. It is turning into an awful mess.
> What would really be nice is
> SELECT *
> INTO #@.TableName
> FROM Employees
> (You listening MS')
> All my problems would be solved.
> I am going to try the transaction locking and see if
> deadlocks result. Otherwise, I am going to have to go
> deeper into nesting stored procs.
> >--Original Message--
> >Force each connection to do all of the processing
> related to the table
> >within a transaction, and at the start of the
> transaction get an exclusive
> >lock on the table. That will prevent mutliple
> connections from interfering
> >with the population and use of the table - but it will
> serialize them. Your
> >ideal design will require a pessimistic locking approach
> where the
> >application does some sort of checkout / reservation
> logic - nothing in sql
> >server that I know of that will do this for you.
> >
> >The best approach is to avoid the global temp table, as
> Adam indicated.
> >
> >"Steven" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> >> I have a stored procedure that *must* create a global
> >> temporary table (##myTable), but the problem is that
> >> there are multiple users which could be using this
> stored
> >> procedure.
> >>
> >> What best practices are there for dealing with this
> >> problem?
> >>
> >> Ideally, I want to queue up the execution if multiple
> >> users all run the proc at the same time, rather than
> >> return an IsRunning status of sorts and then waiting in
> >> the application.
> >>
> >> Any suggestions?
> >>
> >> NOTE: I cannot change the name of the table dynamically
> >> (i.e. '##myTable' + @.UserID) because of how I have to
> >> deal with the data in the procedure (and nested
> >> procedures).
> >>
> >>
> >
> >
> >.
> >|||Also:
http://www.sommarskog.se/share_data.html
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:278901c49c26$90228c10$a601280a@.phx.gbl...
> This is kind of where I ended up going.
> The reason that I have to use Global Temporary tables is
> because one nested stored proc controls the creation of
> the table and then the outer procedure uses the results.|||dynamic table names will require the use of dynamic sql. be certain that
you need this before you start down this path. It is much easier to design
/ code / debug when the table name is static. You might also need to
"delimit" the table name if non-standard characters are included in the
name.
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:279001c49c27$1c736a40$a601280a@.phx.gbl...
> One more thing....
> Anyone see any problems with this method of creating a
> temporary table?
> DECLARE @.TempTable varchar(200)
> SET @.TempTable = '##BaseName_' + REPLACE( CAST( NewID
> () as varchar(80) ), '-', '' )
> Seems to work even when two tables are created
> milliseconds apart from one another.
> Am I going to be in trouble later?
> Thanks for all your help!
> >--Original Message--
> >Force each connection to do all of the processing
> related to the table
> >within a transaction, and at the start of the
> transaction get an exclusive
> >lock on the table. That will prevent mutliple
> connections from interfering
> >with the population and use of the table - but it will
> serialize them. Your
> >ideal design will require a pessimistic locking approach
> where the
> >application does some sort of checkout / reservation
> logic - nothing in sql
> >server that I know of that will do this for you.
> >
> >The best approach is to avoid the global temp table, as
> Adam indicated.
> >
> >"Steven" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> >> I have a stored procedure that *must* create a global
> >> temporary table (##myTable), but the problem is that
> >> there are multiple users which could be using this
> stored
> >> procedure.
> >>
> >> What best practices are there for dealing with this
> >> problem?
> >>
> >> Ideally, I want to queue up the execution if multiple
> >> users all run the proc at the same time, rather than
> >> return an IsRunning status of sorts and then waiting in
> >> the application.
> >>
> >> Any suggestions?
> >>
> >> NOTE: I cannot change the name of the table dynamically
> >> (i.e. '##myTable' + @.UserID) because of how I have to
> >> deal with the data in the procedure (and nested
> >> procedures).
> >>
> >>
> >
> >
> >.
> >|||I've done the something very similar for reporting purposes. Serialization
was acceptable since it was more important to use a consistent methodology
for the generation of the data (also, it was designed in v7 without a
complete set of requirements nor a good understanding of the data and how
ubiqitous it would become). In hindsight, it might have been better to use
regular temp tables, although that would have required a much different
design and I'm not sure if performance would be any better. The good part
about serialization is that it limits the dbms pounding that these reports
involve. Actually, the real problem is that the system is designed to
function as both an OLTP and a data warehouse - if only the requirements had
been known before coding started!
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:278901c49c26$90228c10$a601280a@.phx.gbl...
> This is kind of where I ended up going.
> The reason that I have to use Global Temporary tables is
> because one nested stored proc controls the creation of
> the table and then the outer procedure uses the results.
> Essentially, what is happening is this a dynamic SQL
> query builder which uses the results of another query to
> construct the SQL.
> The inner proc generates a portion of the SQL, the outer
> proc executes it, then when the execution is complete,
> the outer proc uses it to complete the SQL.
> After more careful review, I can actually create a
> dynamically named global temporary table, but I would
> have to build several more nested stored procedures to
> pull it off. It is turning into an awful mess.
> What would really be nice is
> SELECT *
> INTO #@.TableName
> FROM Employees
> (You listening MS')
> All my problems would be solved.
> I am going to try the transaction locking and see if
> deadlocks result. Otherwise, I am going to have to go
> deeper into nesting stored procs.
> >--Original Message--
> >Force each connection to do all of the processing
> related to the table
> >within a transaction, and at the start of the
> transaction get an exclusive
> >lock on the table. That will prevent mutliple
> connections from interfering
> >with the population and use of the table - but it will
> serialize them. Your
> >ideal design will require a pessimistic locking approach
> where the
> >application does some sort of checkout / reservation
> logic - nothing in sql
> >server that I know of that will do this for you.
> >
> >The best approach is to avoid the global temp table, as
> Adam indicated.
> >
> >"Steven" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0d9001c49c10$bc124420$a301280a@.phx.gbl...
> >> I have a stored procedure that *must* create a global
> >> temporary table (##myTable), but the problem is that
> >> there are multiple users which could be using this
> stored
> >> procedure.
> >>
> >> What best practices are there for dealing with this
> >> problem?
> >>
> >> Ideally, I want to queue up the execution if multiple
> >> users all run the proc at the same time, rather than
> >> return an IsRunning status of sorts and then waiting in
> >> the application.
> >>
> >> Any suggestions?
> >>
> >> NOTE: I cannot change the name of the table dynamically
> >> (i.e. '##myTable' + @.UserID) because of how I have to
> >> deal with the data in the procedure (and nested
> >> procedures).
> >>
> >>
> >
> >
> >.
> >

Global Search Criteria - 1 criteria for multiple columns

I am curious of other's opinions on this. It may be more a UI issue than a
database one, but there is a database side to it, and I have seen that folks
here have a broader knowledge base than just proper table structures, so...
I have an application where we need to search for an employee. You can
search on name, employee ID, or department. The determination has been made
to allow the user to enter one value, and then search for the value in any
of the above columns.
i.e.
employee_id = @.SearchString
or
employee_name like '%' + @.SearchString + '%'
or
department_name like '%' + @.SearchString + '%'
The question is, what has your experience been with this approach, from both
a database perspective, and an end user / UI perspective.One technique in such situations is to use a computed column like:
CREATE TABLE tbl (
...,
employee_id ...,
employee_name...,
department_name...
comp_col AS ( employee_id + employee_name + department_name ) );
and create an index on this comp_col column.
Now change your search pattern in your query to use comp_col LIKE '%' + @.p +
'%'. Depending on your usage you can create a covering index on all the
three columns & avoid the computed column altogether as well.
Anith|||Thanks, I'll give that approach a try to help performance.
Does anyone have any non-database related feedback on this approach?
Usability, clarity to the end user, etc?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ugqOHufdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> One technique in such situations is to use a computed column like:
> CREATE TABLE tbl (
> ...,
> employee_id ...,
> employee_name...,
> department_name...
> comp_col AS ( employee_id + employee_name + department_name ) );
> and create an index on this comp_col column.
> Now change your search pattern in your query to use comp_col LIKE '%' + @.p
+
> '%'. Depending on your usage you can create a covering index on all the
> three columns & avoid the computed column altogether as well.
> --
> Anith
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ugqOHufdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> One technique in such situations is to use a computed column like:
> CREATE TABLE tbl (
> ...,
> employee_id ...,
> employee_name...,
> department_name...
> comp_col AS ( employee_id + employee_name + department_name ) );
> and create an index on this comp_col column.
> Now change your search pattern in your query to use comp_col LIKE '%' + @.p
> + '%'. Depending on your usage you can create a covering index on all the
> three columns & avoid the computed column altogether as well.
> --
> Anith
>
Anith,
Could you explain the performance advantage to using either the computed
column or the covering index compared to just a straight where clause (e.g.
WHERE employee_id + employee_name + department_name LIKE '%' + @.p + '%' ) ?
I'm not knocking the approach, I would just like to understand. Also, it
seems as though once you use the LIKE clause, any advantage to an index
disappears.
Thanks,
Kevin|||I think I messed this one up :-( You are right, the wild card in the
beginning of the parameter will nullify the index usage.
Anith

global constant for multiple reports

I have a set of 25 reports that contain the links to the other application
(URL). Is there a way to define a single "global" string for all the reports
to quickly replace that url with the right one? Let's say it's
http://testingserver/app for testing, and http://production/app for
production. I hate to change the link value on every report. Using custom
assemblies to keep such common references looks like too much overhead. Are
there any other global report configuration options I'm missing?
--
Regards,
Dmitry DuginovHello Dmitry,
You could do this in the VS.NET 2005 IDE.
Click the Edit -> Find and Replace -> Replace in Files.
Type the http://testingserver/app in the Find what section, and type
http://production/app in the Replace with section.
Then, click the Find options and type *.rdl in the Look at these file types.
And then, you could click replace all button and all the files in your
solutionn will be modified.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Sorry, but this "approach" just doesn't sound right. It's
"hardcode-change-redeploy". How it's better than "hardcode-change-recomplie"
for regular applications? It's not; that's why we are always trying to keep
some variables in config files: to change them, not the source code...
D.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:zH9Hlb%239GHA.4432@.TK2MSFTNGXA01.phx.gbl...
> You could do this in the VS.NET 2005 IDE.
> Click the Edit -> Find and Replace -> Replace in Files.
> Type the http://testingserver/app in the Find what section, and type
> http://production/app in the Replace with section.
> Then, click the Find options and type *.rdl in the Look at these file
types.
> And then, you could click replace all button and all the files in your
> solutionn will be modified.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
>
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Dmitry,
You could not do this in the Reporting Services Designer.
Since the RDL file is a XML format file, you could modify the xml file
before you deploy to the report server.
This is the only approach you could do in the reporting services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.