Applying aggregate function to whole table

Here is my problem and I suspect this is a simple question for the
experts.
My query attempts to get the names of players that played in a game
during the past x minutes and then the average scores of that player
for ALL games played by that player.
The query I have right now looks something like this (its from an
Access query but you get the point):
SELECT players.name, count(game.gameno) as gamesplayed,
Avg(game_players.score) AS avgscore
FROM players INNER JOIN (game INNER JOIN game_players ON game.gameno =
game_players.gameno) ON players.playerno = game_players.playerno
WHERE (((game.date_played) Between DateAdd('n',-100000,Now()) And
Now()))
As you see the Avg function will only return the average scores in the
games played that meet the "WHERE" condition. How can I get the
average score to be the Average for ALL scores for that player?
Thanks in advance.youretoast@.gmail.com,
This "select" statement is not related to sql server. There is not function
"now" in T-SQL, so I do not know if this can help. The idea is calculating
the avg for all rows and just counting the ones that meet the predicate.
SELECT
players.name,
count(
case when game.date_played Between DateAdd('n',-100000,Now()) And Now()
then game.gameno end
) as gamesplayed,
Avg(game_players.score) AS avgscore
FROM
players
INNER JOIN
(
game
INNER JOIN
game_players
ON game.gameno = game_players.gameno
) ON players.playerno = game_players.playerno
group by
players.name
AMB
"youretoast@.gmail.com" wrote:

> Here is my problem and I suspect this is a simple question for the
> experts.
> My query attempts to get the names of players that played in a game
> during the past x minutes and then the average scores of that player
> for ALL games played by that player.
> The query I have right now looks something like this (its from an
> Access query but you get the point):
> SELECT players.name, count(game.gameno) as gamesplayed,
> Avg(game_players.score) AS avgscore
> FROM players INNER JOIN (game INNER JOIN game_players ON game.gameno =
> game_players.gameno) ON players.playerno = game_players.playerno
> WHERE (((game.date_played) Between DateAdd('n',-100000,Now()) And
> Now()))
> As you see the Avg function will only return the average scores in the
> games played that meet the "WHERE" condition. How can I get the
> average score to be the Average for ALL scores for that player?
> Thanks in advance.
>

Apply top & Group By

I want to show top selling items for each store. Sample SQL Query is "SELECT TOP n ItemNumber FROM Inventory Group By Store" n - is the dynamic value. I am accepting this n parameter from the user. If the user is giving 5 for n then, I want to show top 5 selling items for each store. It will be greatfull, If anybodies giving me an idea of how to do this?.

Thanks

I have done it with the row_number function.

Apply filter on dbcc log query

Hi,
I am reading online transaction log using dbcc log command.
But I don't want to read the whole online transaction log, because it will t
ake too long time. Is there any way through which I can filter the record an
d get lesser no. of records.
Is there any way through which I can use dbcc command directly in a query.
Or is there any undocumennted parameter that might passed to dcc log command
through which we can specify filter.
Thanks in advance
PushkarHi
dbcc log is undocumented, so all of it is unsupported.
AFAIK, the commands listed here are the only ones:
http://www.mssqlcity.com/Articles/U...CC.htm#part_2_8
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pushkar" wrote:

> Hi,
> I am reading online transaction log using dbcc log command.
> But I don't want to read the whole online transaction log, because it will
take too long time. Is there any way through which I can filter the record
and get lesser no. of records.
> Is there any way through which I can use dbcc command directly in a query.
> Or is there any undocumennted parameter that might passed to dcc log comma
nd through which we can specify filter.
> Thanks in advance
> Pushkar
>|||> But I don't want to read the whole online transaction log, because it will
> take too long time.
Once again, I suggest a 3rd party tool. This operation is meant for
disaster recovery and occasional usage, not for every day activities where
performance is a concern. http://www.aspfaq.com/2449
It seems you've already spent enough time on this that you would have paid
for the license, if your opportunity cost is more than minimum wage...

Apply code to parameter before query execution

Hello, is there any way to perform custom calcuations on a parameter before
retrieving the data set? I have a table with an encrypted account number
field. The encryption is done at the application level using the .NET
encryption libraries. I need to prepare a report retrieving the transactions
for a particular account. Obviously, I cannot use the plain text account
number as the query parameter. I need to take the user input, encrypt it, and
then use the encrypted value as the parameter. I could perform the encryption
on one of the asp.net pages and pass the encrypted value as a url parameter,
but I would prefer to allow the users to type in the account number on the
report page itself. Is there a way to do this? (I am using SQL Server 2000
Reporting Services, not 2005) Thanks!No problem. When you tie a report parameter to a query parameter you can
first manipulate it in an expression. The fact that they are two different
things (query parameter versus report parameter) is very important.
In the dataset tab click on the ..., parameters tab. This is where the
mapping occurs. On the right instead of selecting the parameter select
expression.
Your expression would look like this:
= Code.Mycode(Parameters!MyParam.value)
You can do code behind report or you can use custom assemblies. Search books
online using the work Code.
There are hoops to go through with custom assemblies, mostly dealing with
permissions. I haven't done this myself but plenty of people have.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joel Daniels" <JoelDaniels@.discussions.microsoft.com> wrote in message
news:DE91CBA6-67D0-4EBD-A07B-9F6B770DEFF9@.microsoft.com...
> Hello, is there any way to perform custom calcuations on a parameter
> before
> retrieving the data set? I have a table with an encrypted account number
> field. The encryption is done at the application level using the .NET
> encryption libraries. I need to prepare a report retrieving the
> transactions
> for a particular account. Obviously, I cannot use the plain text account
> number as the query parameter. I need to take the user input, encrypt it,
> and
> then use the encrypted value as the parameter. I could perform the
> encryption
> on one of the asp.net pages and pass the encrypted value as a url
> parameter,
> but I would prefer to allow the users to type in the account number on the
> report page itself. Is there a way to do this? (I am using SQL Server 2000
> Reporting Services, not 2005) Thanks!

Appliction role Problem with Stored Procedure

Hi,
I'm new using Application Role. I use SQL Query Analyzer to try some
example.
I have create a Application role call "ABC" and have set permission to all
tables & stored procedures.
In Query Analyzer, after setting the role using sp_setapprole,
i) I try to run the stored procedure but it give me 0 result. (it
should return me some records which it doesn't)
ii) I try to run a select statement for a table, it return me all
records.
Since Application Role allows me to set permission for Stored Procedures &
tables, I thought both should be the same.
Does anyone know why is this so?
Regards
VanessaDo you mean that ii is working and i is not? Do you receive any errors?
Try executing the stored procedure code manually.
Or post both i and ii code here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:

> Hi,
> I'm new using Application Role. I use SQL Query Analyzer to try some
> example.
> I have create a Application role call "ABC" and have set permission to all
> tables & stored procedures.
> In Query Analyzer, after setting the role using sp_setapprole,
> i) I try to run the stored procedure but it give me 0 result. (it
> should return me some records which it doesn't)
> ii) I try to run a select statement for a table, it return me all
> records.
> Since Application Role allows me to set permission for Stored Procedures &
> tables, I thought both should be the same.
> Does anyone know why is this so?
> Regards
> Vanessa
>
>|||For stored procedure, i did not receive any errors but it return me zero
result. If i didn't set application role and run the SP, it return me 55
rows of record.
In the Query Analyzer, the statement i run is
Step 1) EXEC sp_setapprole 'EMERGE', 'test'
Step 2 SP ) Exec aneadm.uspGetAppAllSetting
Step 2 Normal Select Statement) select * from aneadm.tblvisit
The SP code is
CREATE PROCEDURE aneadm.uspGetAppAllSetting
AS
-- version 1.0
SET NOCOUNT ON
DECLARE @.ReturnCode INTEGER
DECLARE @.IsActiveBit BIT
DECLARE @.Site VARCHAR(80)
DECLARE @.SiteID INTEGER
SELECT @.ReturnCode = 1 -- not ok
SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
DECLARE @.tSettingDesc VARCHAR(255)
SELECT @.tSettingDesc = NULL
SELECT SettingKey,
SettingDesc,
NoOfCopies,
IsPreview
FROM tblAppSetting (nolock)
WHERE SiteID = @.SiteID
AND IsActive = 1
GO
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...[vbcol=seagreen]
> Do you mean that ii is working and i is not? Do you receive any errors?
> Try executing the stored procedure code manually.
> Or post both i and ii code here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>|||Once you set the application role, user_name() will return 'EMERGE'. Is this
what you want?
Or maybe suser_sname() for the current user id?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:

> For stored procedure, i did not receive any errors but it return me zero
> result. If i didn't set application role and run the SP, it return me 55
> rows of record.
> In the Query Analyzer, the statement i run is
> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
> Step 2 Normal Select Statement) select * from aneadm.tblvisit
> The SP code is
>
> CREATE PROCEDURE aneadm.uspGetAppAllSetting
> AS
> -- version 1.0
> SET NOCOUNT ON
> DECLARE @.ReturnCode INTEGER
> DECLARE @.IsActiveBit BIT
> DECLARE @.Site VARCHAR(80)
> DECLARE @.SiteID INTEGER
> SELECT @.ReturnCode = 1 -- not ok
> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
> DECLARE @.tSettingDesc VARCHAR(255)
> SELECT @.tSettingDesc = NULL
> SELECT SettingKey,
> SettingDesc,
> NoOfCopies,
> IsPreview
> FROM tblAppSetting (nolock)
> WHERE SiteID = @.SiteID
> AND IsActive = 1
> GO
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
>
>|||Thanks.
I think i know why I can't retrieve the records when using Stored Procedure.
Regards
Vanessa
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2CA7337C-3FCC-4F51-8DCA-8F6F6C70E855@.microsoft.com...[vbcol=seagreen]
> Once you set the application role, user_name() will return 'EMERGE'. Is
> this
> what you want?
> Or maybe suser_sname() for the current user id?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>

Appliction role Problem with Stored Procedure

Hi,
I'm new using Application Role. I use SQL Query Analyzer to try some
example.
I have create a Application role call "ABC" and have set permission to all
tables & stored procedures.
In Query Analyzer, after setting the role using sp_setapprole,
i) I try to run the stored procedure but it give me 0 result. (it
should return me some records which it doesn't)
ii) I try to run a select statement for a table, it return me all
records.
Since Application Role allows me to set permission for Stored Procedures &
tables, I thought both should be the same.
Does anyone know why is this so?
Regards
VanessaDo you mean that ii is working and i is not? Do you receive any errors?
Try executing the stored procedure code manually.
Or post both i and ii code here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:
> Hi,
> I'm new using Application Role. I use SQL Query Analyzer to try some
> example.
> I have create a Application role call "ABC" and have set permission to all
> tables & stored procedures.
> In Query Analyzer, after setting the role using sp_setapprole,
> i) I try to run the stored procedure but it give me 0 result. (it
> should return me some records which it doesn't)
> ii) I try to run a select statement for a table, it return me all
> records.
> Since Application Role allows me to set permission for Stored Procedures &
> tables, I thought both should be the same.
> Does anyone know why is this so?
> Regards
> Vanessa
>
>|||For stored procedure, i did not receive any errors but it return me zero
result. If i didn't set application role and run the SP, it return me 55
rows of record.
In the Query Analyzer, the statement i run is
Step 1) EXEC sp_setapprole 'EMERGE', 'test'
Step 2 SP ) Exec aneadm.uspGetAppAllSetting
Step 2 Normal Select Statement) select * from aneadm.tblvisit
The SP code is
CREATE PROCEDURE aneadm.uspGetAppAllSetting
AS
-- version 1.0
SET NOCOUNT ON
DECLARE @.ReturnCode INTEGER
DECLARE @.IsActiveBit BIT
DECLARE @.Site VARCHAR(80)
DECLARE @.SiteID INTEGER
SELECT @.ReturnCode = 1 -- not ok
SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
DECLARE @.tSettingDesc VARCHAR(255)
SELECT @.tSettingDesc = NULL
SELECT SettingKey,
SettingDesc,
NoOfCopies,
IsPreview
FROM tblAppSetting (nolock)
WHERE SiteID = @.SiteID
AND IsActive = 1
GO
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
> Do you mean that ii is working and i is not? Do you receive any errors?
> Try executing the stored procedure code manually.
> Or post both i and ii code here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>> Hi,
>> I'm new using Application Role. I use SQL Query Analyzer to try some
>> example.
>> I have create a Application role call "ABC" and have set permission to
>> all
>> tables & stored procedures.
>> In Query Analyzer, after setting the role using sp_setapprole,
>> i) I try to run the stored procedure but it give me 0 result. (it
>> should return me some records which it doesn't)
>> ii) I try to run a select statement for a table, it return me all
>> records.
>> Since Application Role allows me to set permission for Stored Procedures
>> &
>> tables, I thought both should be the same.
>> Does anyone know why is this so?
>> Regards
>> Vanessa
>>|||Once you set the application role, user_name() will return 'EMERGE'. Is this
what you want?
Or maybe suser_sname() for the current user id?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:
> For stored procedure, i did not receive any errors but it return me zero
> result. If i didn't set application role and run the SP, it return me 55
> rows of record.
> In the Query Analyzer, the statement i run is
> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
> Step 2 Normal Select Statement) select * from aneadm.tblvisit
> The SP code is
>
> CREATE PROCEDURE aneadm.uspGetAppAllSetting
> AS
> -- version 1.0
> SET NOCOUNT ON
> DECLARE @.ReturnCode INTEGER
> DECLARE @.IsActiveBit BIT
> DECLARE @.Site VARCHAR(80)
> DECLARE @.SiteID INTEGER
> SELECT @.ReturnCode = 1 -- not ok
> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
> DECLARE @.tSettingDesc VARCHAR(255)
> SELECT @.tSettingDesc = NULL
> SELECT SettingKey,
> SettingDesc,
> NoOfCopies,
> IsPreview
> FROM tblAppSetting (nolock)
> WHERE SiteID = @.SiteID
> AND IsActive = 1
> GO
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
> >
> > Do you mean that ii is working and i is not? Do you receive any errors?
> >
> > Try executing the stored procedure code manually.
> >
> > Or post both i and ii code here.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Vanessa" wrote:
> >
> >> Hi,
> >>
> >> I'm new using Application Role. I use SQL Query Analyzer to try some
> >> example.
> >>
> >> I have create a Application role call "ABC" and have set permission to
> >> all
> >> tables & stored procedures.
> >>
> >> In Query Analyzer, after setting the role using sp_setapprole,
> >> i) I try to run the stored procedure but it give me 0 result. (it
> >> should return me some records which it doesn't)
> >> ii) I try to run a select statement for a table, it return me all
> >> records.
> >>
> >> Since Application Role allows me to set permission for Stored Procedures
> >> &
> >> tables, I thought both should be the same.
> >>
> >> Does anyone know why is this so?
> >>
> >> Regards
> >> Vanessa
> >>
> >>
> >>
>
>|||Thanks.
I think i know why I can't retrieve the records when using Stored Procedure.
Regards
Vanessa
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2CA7337C-3FCC-4F51-8DCA-8F6F6C70E855@.microsoft.com...
> Once you set the application role, user_name() will return 'EMERGE'. Is
> this
> what you want?
> Or maybe suser_sname() for the current user id?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>> For stored procedure, i did not receive any errors but it return me zero
>> result. If i didn't set application role and run the SP, it return me 55
>> rows of record.
>> In the Query Analyzer, the statement i run is
>> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
>> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
>> Step 2 Normal Select Statement) select * from aneadm.tblvisit
>> The SP code is
>>
>> CREATE PROCEDURE aneadm.uspGetAppAllSetting
>> AS
>> -- version 1.0
>> SET NOCOUNT ON
>> DECLARE @.ReturnCode INTEGER
>> DECLARE @.IsActiveBit BIT
>> DECLARE @.Site VARCHAR(80)
>> DECLARE @.SiteID INTEGER
>> SELECT @.ReturnCode = 1 -- not ok
>> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
>> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
>> DECLARE @.tSettingDesc VARCHAR(255)
>> SELECT @.tSettingDesc = NULL
>> SELECT SettingKey,
>> SettingDesc,
>> NoOfCopies,
>> IsPreview
>> FROM tblAppSetting (nolock)
>> WHERE SiteID = @.SiteID
>> AND IsActive = 1
>> GO
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
>> >
>> > Do you mean that ii is working and i is not? Do you receive any errors?
>> >
>> > Try executing the stored procedure code manually.
>> >
>> > Or post both i and ii code here.
>> >
>> > Ben Nevarez, MCDBA, OCP
>> > Database Administrator
>> >
>> >
>> > "Vanessa" wrote:
>> >
>> >> Hi,
>> >>
>> >> I'm new using Application Role. I use SQL Query Analyzer to try some
>> >> example.
>> >>
>> >> I have create a Application role call "ABC" and have set permission to
>> >> all
>> >> tables & stored procedures.
>> >>
>> >> In Query Analyzer, after setting the role using sp_setapprole,
>> >> i) I try to run the stored procedure but it give me 0 result.
>> >> (it
>> >> should return me some records which it doesn't)
>> >> ii) I try to run a select statement for a table, it return me
>> >> all
>> >> records.
>> >>
>> >> Since Application Role allows me to set permission for Stored
>> >> Procedures
>> >> &
>> >> tables, I thought both should be the same.
>> >>
>> >> Does anyone know why is this so?
>> >>
>> >> Regards
>> >> Vanessa
>> >>
>> >>
>> >>
>>

Application Role Usage

I am new to the security part of SQL server and I'm having a problem with
using the application role while running a query from Excel. I wrote the sp
I'm using and it works fine but I can't get the application role to work. I
have set it up in EM but I'm not sure how to put it in the code in Microsoft
query. Do I have to put the exec sp_setapprole 'role', 'password' statement
in the sp or before I execute the procedure. I tried putting it before and i
t
did not work, maybe I had the wrong syntax or something. Below is what I
wrote:
Exec sp_setapprole 'role', 'password'
GO
Exec GetDefectReport
GOThis should work I think...do you get an error message?
Did you give the approle execute permission on the SP (maybe underlying
tables?)
Does the sp return data for Excel to display (I take it, that's what it has
to do...)?
Lee-Z
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:4C25B4B8-FEC4-49C4-9276-F5B0AF2A6FD1@.microsoft.com...
>I am new to the security part of SQL server and I'm having a problem with
> using the application role while running a query from Excel. I wrote the
> sp
> I'm using and it works fine but I can't get the application role to work.
> I
> have set it up in EM but I'm not sure how to put it in the code in
> Microsoft
> query. Do I have to put the exec sp_setapprole 'role', 'password'
> statement
> in the sp or before I execute the procedure. I tried putting it before and
> it
> did not work, maybe I had the wrong syntax or something. Below is what I
> wrote:
> Exec sp_setapprole 'role', 'password'
> GO
> Exec GetDefectReport
> GO|||The error message is that the syntax is wrong around GO. I wrote the sp in
the query analyzer and now i am calling it from Excel using Microsoft Query.
"Lee-Z" wrote:

> This should work I think...do you get an error message?
> Did you give the approle execute permission on the SP (maybe underlying
> tables?)
> Does the sp return data for Excel to display (I take it, that's what it ha
s
> to do...)?
> Lee-Z
>
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:4C25B4B8-FEC4-49C4-9276-F5B0AF2A6FD1@.microsoft.com...
>
>|||have never tried approle with MS Query, but try to execute the sp_SetAppRole
statement in menu "File"-> "Execute SQL" from MS-Query (leave out the GO
part). Make sure you select your database in the dropdown box.
After that you should be able to get data from your original query (leave
out the "GO" here as well)...
good luck
Lee-Z
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:1FDE810C-6F0F-4E7C-8537-E5987C88D297@.microsoft.com...[vbcol=seagreen]
> The error message is that the syntax is wrong around GO. I wrote the sp in
> the query analyzer and now i am calling it from Excel using Microsoft
> Query.
> "Lee-Z" wrote:
>|||You are not going to be able to use Microsoft Query and get
application roles to perform reliably. Application roles are
connection-specific, and the tools open additonal connections under
the covers in order to speed up connections. This is an issue even if
you code data access in ADO code (which is the usual way to go about
it). The following article, "SQL application role errors with OLE DB
resource pooling" describes the problem and the workaround:
http://support.microsoft.com/defaul...b;en-us;Q229564
--Mary
On Fri, 12 Aug 2005 06:59:20 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The error message is that the syntax is wrong around GO. I wrote the sp in
>the query analyzer and now i am calling it from Excel using Microsoft Query
.
>"Lee-Z" wrote:
>|||Thanks for the help Lee and Mary
"Mary Chipman [MSFT]" wrote:

> You are not going to be able to use Microsoft Query and get
> application roles to perform reliably. Application roles are
> connection-specific, and the tools open additonal connections under
> the covers in order to speed up connections. This is an issue even if
> you code data access in ADO code (which is the usual way to go about
> it). The following article, "SQL application role errors with OLE DB
> resource pooling" describes the problem and the workaround:
> http://support.microsoft.com/defaul...b;en-us;Q229564
> --Mary
> On Fri, 12 Aug 2005 06:59:20 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
>
>

application role how query a view whose reference table is in another db?

Hi everybody.

I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1

I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).

With the following query

SELECT USER_NAME()

I see that the approle is being used.

Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:

The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.

What should I do to make it work?

The table in DB2 has the same schema of the view in DB1 which refers to it.

I put the DB1 TrustWorthy and both the database have the db_chaining option activated.

Any idea on how to solve the problem would be widely appreciated.

Thank you very much.

Vania

Application roles are not designed to work on cross-database scenarios. The following post has a detailed discussion on the topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1312863&SiteID=1

If your application relies heavily on cross-database access, I would recommend against using application roles. If you don’ have a choice, you can create a new execution context using EXECUTE AS in a SP. I wrote a small example that hopefully will be useful:

-- We will strat creating a low privielged login

--that will be used for the cross-DB context

-- and disable it to prevent any connection with it

--

CREATE LOGIN crossdb_login WITH PASSWORD = '50m3 p4zzwrd#'

ALTER LOGIN crossdb_login DISABLE

go

-- First we will go to our source DB to create the SP

-- and digitally sign it

--

use db_Source

go

CREATE USER crossdb_user FOR LOGIN crossdb_login

go

-- This SP will run under the 'crossdb_user' context

--

CREATE PROC sp_demo

WITH EXECUTE AS 'crossdb_user'

AS

-- display the token for testing purposes

SELECT * FROM db_Target.sys.user_token

-- select from a table

SELECT * FROM db_Target.dbo.data

go

-- Sign the modul and create a backup of the cert

-- in order to create a login for it

--

CREATE CERTIFICATE signing_cert

ENCRYPTION BY PASSWORD = '50m3 p4zzwrd#'

WITH SUBJECT = 'signing cert'

go

ADD SIGNATURE TO sp_demo

BY CERTIFICATE signing_cert

WITH PASSWORD = '50m3 p4zzwrd#'

go

BACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer'

go

-- Create a copy of the cert in master and a login mapped to it

--

USE master

go

CREATE CERTIFICATE signing_cert FROM FILE = 'signing_cert.cer'

go

CREATE LOGIN signing_cert FROM CERTIFICATE signing_cert

go

-- Now in the target DB we will create the user we need

-- and grant the appropriate permissions

--

use db_Target

go

CREATE TABLE data( value int )

go

CREATE USER crossdb_user FOR LOGIN crossdb_login

CREATE USER signing_cert

go

-- The certificate will be used to vouch for the

-- impersonated context

--

GRANT AUTHENTICATE TO signing_cert

go

-- GRANT SELECT on the table to the impersonated context

--

GRANT SELECT ON dbo.data to crossdb_user

go

-- Test the new signed SP

--

use db_Source

go

CREATE APPLICATION ROLE approle_demo WITH PASSWORD = '@.ppr0l3 d3m00'

go

GRANT EXECUTE ON sp_demo to approle_demo

go

EXEC sp_setapprole 'approle_demo', '@.ppr0l3 d3m00'

go

-- Should fail

-- Msg 916, Level 14, State 1, Line 1

-- The server principal "<<login_name>>" is not able to access the database "db_Target" under the current security context.

--

SELECT * FROM db_Target.dbo.data

go

-- Should succeed

EXEC sp_demo

go

Please let us know if you have any questions.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you so much and forgive me for my late but was on holidays.

Your solution works fine, in fact (I am sure you have no doubts about it).

A more question, please forgive me if it is obvious:

how, from inside the sp_demo, can I get the name of the server principal (domain\user if Windows authentication) which set the application role ? I mean the caller of the setapprole.

Thank you very much.

Vania

application role how query a view whose reference table is in another db?

Hi everybody.

I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1

I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).

With the following query

SELECT USER_NAME()

I see that the approle is being used.

Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:

The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.

What should I do to make it work?

The table in DB2 has the same schema of the view in DB1 which refers to it.

I put the DB1 TrustWorthy and both the database have the db_chaining option activated.

Any idea on how to solve the problem would be widely appreciated.

Thank you very much.

Vania

Application roles are not designed to work on cross-database scenarios. The following post has a detailed discussion on the topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1312863&SiteID=1

If your application relies heavily on cross-database access, I would recommend against using application roles. If you don’ have a choice, you can create a new execution context using EXECUTE AS in a SP. I wrote a small example that hopefully will be useful:

-- We will strat creating a low privielged login

--that will be used for the cross-DB context

-- and disable it to prevent any connection with it

--

CREATE LOGIN crossdb_login WITH PASSWORD = '50m3 p4zzwrd#'

ALTER LOGIN crossdb_login DISABLE

go

-- First we will go to our source DB to create the SP

-- and digitally sign it

--

use db_Source

go

CREATE USER crossdb_user FOR LOGIN crossdb_login

go

-- This SP will run under the 'crossdb_user' context

--

CREATE PROC sp_demo

WITH EXECUTE AS 'crossdb_user'

AS

-- display the token for testing purposes

SELECT * FROM db_Target.sys.user_token

-- select from a table

SELECT * FROM db_Target.dbo.data

go

-- Sign the modul and create a backup of the cert

-- in order to create a login for it

--

CREATE CERTIFICATE signing_cert

ENCRYPTION BY PASSWORD = '50m3 p4zzwrd#'

WITH SUBJECT = 'signing cert'

go

ADD SIGNATURE TO sp_demo

BY CERTIFICATE signing_cert

WITH PASSWORD = '50m3 p4zzwrd#'

go

BACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer'

go

-- Create a copy of the cert in master and a login mapped to it

--

USE master

go

CREATE CERTIFICATE signing_cert FROM FILE = 'signing_cert.cer'

go

CREATE LOGIN signing_cert FROM CERTIFICATE signing_cert

go

-- Now in the target DB we will create the user we need

-- and grant the appropriate permissions

--

use db_Target

go

CREATE TABLE data( value int )

go

CREATE USER crossdb_user FOR LOGIN crossdb_login

CREATE USER signing_cert

go

-- The certificate will be used to vouch for the

-- impersonated context

--

GRANT AUTHENTICATE TO signing_cert

go

-- GRANT SELECT on the table to the impersonated context

--

GRANT SELECT ON dbo.data to crossdb_user

go

-- Test the new signed SP

--

use db_Source

go

CREATE APPLICATION ROLE approle_demo WITH PASSWORD = '@.ppr0l3 d3m00'

go

GRANT EXECUTE ON sp_demo to approle_demo

go

EXEC sp_setapprole 'approle_demo', '@.ppr0l3 d3m00'

go

-- Should fail

-- Msg 916, Level 14, State 1, Line 1

-- The server principal "<<login_name>>" is not able to access the database "db_Target" under the current security context.

--

SELECT * FROM db_Target.dbo.data

go

-- Should succeed

EXEC sp_demo

go

Please let us know if you have any questions.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you so much and forgive me for my late but was on holidays.

Your solution works fine, in fact (I am sure you have no doubts about it).

A more question, please forgive me if it is obvious:

how, from inside the sp_demo, can I get the name of the server principal (domain\user if Windows authentication) which set the application role ? I mean the caller of the setapprole.

Thank you very much.

Vania

application of fulltext search

Hello, It is possible to search text in my blog use a query sentence instead
of several key words? Which I mean, if I want to get to know "How rich is
Bill Gates?", I can simply enter this sentence instead of enter
"Bill+Gates+rich+how" in my search textbox. If it is applicable, how?I want
to rewrite the code of my blog
Thanks, indeed
Search is unable to interpret the context of your query. English Query has
rich context sensing abilities but it works against a database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SuperRock" <moyuji@.gmail.com> wrote in message
news:eTU999zbHHA.2188@.TK2MSFTNGP04.phx.gbl...
> Hello, It is possible to search text in my blog use a query sentence
> instead of several key words? Which I mean, if I want to get to know "How
> rich is Bill Gates?", I can simply enter this sentence instead of enter
> "Bill+Gates+rich+how" in my search textbox. If it is applicable, how?I
> want to rewrite the code of my blog
> Thanks, indeed
>
|||Does your blogging solution use SQL Server as the data store? Please
elaborate (give comprehensive examples, post DDL and sample data...).
ML
http://milambda.blogspot.com/

Appending columns - horizantal dimention

Hi,
There are two queries as follow:
Original Query Result
select * from Customers
CustomerId Name OrderId
--
11 Mike 101
22 James 102
select * from OrderDetails
OrderId Product Quantity
---
101 100w speaker 1
101 Headphone 2
102 Blank CDs 1
I know this is an unusual approach to merge two result sets like below, but
what is the best way of getting the following result from the those two
queries above:
CustomerId Name OrderId Product Quantity Product
Quantity
----
--
11 Mike 101 100w speaker 1 Headphone 2
22 James 102 Blank CDs 1
Any query sample or suggestion will be appreciated.
Thanks
MehdiSelect *.C, *.OD
FROM Customers C(NOLOCK)
JOIN OrderDetails OD(NOLOCK) ON C.OrderID = OD.OrderID
Just my twist on it,
Adam Turner
"Mehdi" wrote:

> Hi,
> There are two queries as follow:
> Original Query Result
>
> select * from Customers
> CustomerId Name OrderId
> --
> 11 Mike 101
> 22 James 102
>
> select * from OrderDetails
> OrderId Product Quantity
> ---
> 101 100w speaker 1
> 101 Headphone 2
> 102 Blank CDs 1
>
> I know this is an unusual approach to merge two result sets like below, bu
t
> what is the best way of getting the following result from the those two
> queries above:
>
> CustomerId Name OrderId Product Quantity Product
> Quantity
> ----
--
> 11 Mike 101 100w speaker 1 Headphone
2
> 22 James 102 Blank CDs 1
>
> Any query sample or suggestion will be appreciated.
>
> Thanks
>
> Mehdi
>
>|||SELECT C.*, OD.*
FROM Customers C(NOLOCK)
JOIN OrderDetails OD(NOLOCK) ON C.OrderID = OD.OrderID
Just my twist on it,
Adam Turner
"Mehdi" wrote:

> Hi,
> There are two queries as follow:
> Original Query Result
>
> select * from Customers
> CustomerId Name OrderId
> --
> 11 Mike 101
> 22 James 102
>
> select * from OrderDetails
> OrderId Product Quantity
> ---
> 101 100w speaker 1
> 101 Headphone 2
> 102 Blank CDs 1
>
> I know this is an unusual approach to merge two result sets like below, bu
t
> what is the best way of getting the following result from the those two
> queries above:
>
> CustomerId Name OrderId Product Quantity Product
> Quantity
> ----
--
> 11 Mike 101 100w speaker 1 Headphone
2
> 22 James 102 Blank CDs 1
>
> Any query sample or suggestion will be appreciated.
>
> Thanks
>
> Mehdi
>
>|||Hi Adam,
Thanks for the reply, however you query is a simple join that gets the
results in vertical dimention.
I think I need some sort of a loop to create and append the columns from the
second result. I will post the final query to the group as soon as I find
a solution.
Thanks
Mehdi
"Adam Turner" <AdamTurner@.discussions.microsoft.com> wrote in message
news:DA06C785-1F8A-495A-B6B2-8FAB46D7B33A@.microsoft.com...
> SELECT C.*, OD.*
> FROM Customers C(NOLOCK)
> JOIN OrderDetails OD(NOLOCK) ON C.OrderID = OD.OrderID
> Just my twist on it,
> Adam Turner
> "Mehdi" wrote:
>|||On Tue, 13 Dec 2005 01:14:23 -0000, Mehdi wrote:

>Hi,
>There are two queries as follow:
>Original Query Result
>
>select * from Customers
>CustomerId Name OrderId
>--
>11 Mike 101
>22 James 102
>
>select * from OrderDetails
>OrderId Product Quantity
>---
>101 100w speaker 1
>101 Headphone 2
>102 Blank CDs 1
>
>I know this is an unusual approach to merge two result sets like below, but
>what is the best way of getting the following result from the those two
>queries above:
>
>CustomerId Name OrderId Product Quantity Product
>Quantity
>----
--
>11 Mike 101 100w speaker 1 Headphone 2
>22 James 102 Blank CDs 1
>
>Any query sample or suggestion will be appreciated.
Hi Mehdi,
Try if this works:
SELECT Der1.CustomerID, Der1.Name, Der1.OrderId,
Der1.Product, Der1.Quantity, Der2.Product, Der2.Quantity
FROM (SELECT a.CustomerID, a.Name, a.OrderId,
b.Product, b.Quantity, COUNT(*) AS Rank
FROM Customers AS a
INNER JOIN orderDetails AS b
ON b.OrderId = a.OrderId
INNER JOIN orderDetails AS c
ON c.OrderId = b.OrderId
AND c.Product <= b.Product
GROUP BY a.CustomerID, a.Name, a.OrderId,
b.Product, b.Quantity) AS Der1
LEFT JOIN (SELECT a.CustomerID, a.Name, a.OrderId,
b.Product, b.Quantity COUNT(*) AS Rank
FROM Customers AS a
INNER JOIN orderDetails AS b
ON b.OrderId = a.OrderId
INNER JOIN orderDetails AS c
ON c.OrderId = b.OrderId
AND c.Product <= b.Product
GROUP BY a.CustomerID, a.Name, a.OrderId,
b.Product, b.Quantity) AS Der2
ON Der2.CustomerID = Der1.CustomerID
AND Der2.Rank = Der1.Rank + 1
WHERE Der1.Rank % 2 = 1
(untested - see www.aspfaq.com/5006 if you prefer a testede reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Thanks for the advise, I havn't tried it yet, but I think it will work as
long as my number of columns are fixed. I have decided to manipulate the
dataset outside SQL using C# code to generate a new dataset based on number
of required number of columns as they are dynamic and change.
Thanks again.
Mehdi|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:froup1pla55vvh2tkf11i00cr528d70m65@.
4ax.com...
> On Tue, 13 Dec 2005 01:14:23 -0000, Mehdi wrote:
>
but
>----
--
2
> Hi Mehdi,
> Try if this works:
> SELECT Der1.CustomerID, Der1.Name, Der1.OrderId,
> Der1.Product, Der1.Quantity, Der2.Product, Der2.Quantity
> FROM (SELECT a.CustomerID, a.Name, a.OrderId,
> b.Product, b.Quantity, COUNT(*) AS Rank
> FROM Customers AS a
> INNER JOIN orderDetails AS b
> ON b.OrderId = a.OrderId
> INNER JOIN orderDetails AS c
> ON c.OrderId = b.OrderId
> AND c.Product <= b.Product
> GROUP BY a.CustomerID, a.Name, a.OrderId,
> b.Product, b.Quantity) AS Der1
> LEFT JOIN (SELECT a.CustomerID, a.Name, a.OrderId,
> b.Product, b.Quantity COUNT(*) AS Rank
> FROM Customers AS a
> INNER JOIN orderDetails AS b
> ON b.OrderId = a.OrderId
> INNER JOIN orderDetails AS c
> ON c.OrderId = b.OrderId
> AND c.Product <= b.Product
> GROUP BY a.CustomerID, a.Name, a.OrderId,
> b.Product, b.Quantity) AS Der2
> ON Der2.CustomerID = Der1.CustomerID
> AND Der2.Rank = Der1.Rank + 1
> WHERE Der1.Rank % 2 = 1
Ugggh....
Have you forgotten about RAC :)
www.rac4sql.net

append query with isnull function

i'm trying to run an append query using data from 2 tables. i want to replace nulls with blanks ('') bellow is my statment. when i run this statment with out the iif(isnull)) statmentes the query works fine. is there another way of replacing my nulls with blanks.
Thank you,
Thomas

insert into tblcustomers (cusName, cusNumber, Active, cusContact, cusCrLimit,cusTerms)
SELECT dbo.tblCustomersIOA.CustomerName, dbo.tblCustomersIOA.Cust#,
dbo.tblCustomersIOA.Active,
iif(isnull(dbo.tblCustomersIOA.Contact),'',dbo.tbl CustomersIOA.Contact) ,
dbo.tblCustomersIOA.CreditLimit,
FROM dbo.tblCustomerNotesIOA RIGHT OUTER JOIN
dbo.tblCustomersIOA LEFT OUTER JOIN
WHERE (dbo.tblCustomersIOA.CountryID = 1) AND (dbo.tblCustomersIOA.StateID = 2);Use this:

ISNULL(dbo.tblCustomersIOA.Contact, '')

ISNULL() returns the second parameter if the first param is null...
the second has to be the same type as the first (but you can play around with CAST() and CONVERT() if needed.

ISNULL()'s close cousin is NULLIF(Param1, Param2) which returns NULL
if the two params are equal...Look up BOL for more information on both functions

Append Query Problems

Hi

I am doing an A level database project. I need to build a system to deal with ordering and selling products.

I am trying to make an append query that will append all the products bought in my "invoices" table to my "archive" table. I want invoices that are three months old to be transferred the the "archive" table. I have been trying to put a criteria in the query for the Date field but I haven't managed to find an expression that works.

I'm sorry if this is too simple and you guys think im just being stupid.
I figured it was worth asking on here!! It's really bugging me.Show us what you got so far. Also ... look up dateadd and datediff in BOL.|||Sorry, what is BOL?
"Date"<=#01/01/2006#

Is that what you mean by show? I don't know how to show the whole query :-s|||Can you write a SELECT statement to retrieve the rows that you want to archive?

-PatP|||Thanks but I don't know what a select statement is! Is it the same as a select query? there's something about that in one of my books.|||Is this project in Access? You may need to ask over in the Access forum...|||Yes it is. I realised after I posted here that there is an access forum too. I have posted in there now thanks!

Append Query Problem

I'm testing this query and it does not append any new rows from the
source tables unless the destination table is empty. I want to design
the query just to add new records from the destination tables and leave
existing ones in tact. Help appreciated. Query:

INSERT INTO tMASmembers ( Division, CustomerNumber, CustomerName,
AddressLine1, SalesPersonCode, SIC_Code, SIC_Desc )
SELECT AR1_CustomerMaster.Division, AR1_CustomerMaster.CustomerNumber,
AR1_CustomerMaster.CustomerName, AR1_CustomerMaster.AddressLine1,
AR1_CustomerMaster.SalesPersonCode, AR_90_UDF_AR_Customer.Sic_Code,
AR_90_UDF_AR_Customer.Sic_Desc
FROM AR1_CustomerMaster INNER JOIN AR_90_UDF_AR_Customer ON
(AR1_CustomerMaster.Division = AR_90_UDF_AR_Customer.Division) AND
(AR1_CustomerMaster.CustomerNumber =
AR_90_UDF_AR_Customer.CustomerNumber)
WHERE (((AR1_CustomerMaster.Division) Not In (Select
[tMASmembers].[Division] From [tMASmembers])) AND
((AR1_CustomerMaster.CustomerNumber) Not In (Select
[tMASmembers].[CustomerNumber] From [tMASmembers])) AND
((AR1_CustomerMaster.CustomerName) Not In (Select
[tMASmembers].[CustomerName] From [tMASmembers])) AND
((AR1_CustomerMaster.AddressLine1) Not In (Select
[tMASmembers].[AddressLine1] From [tMASmembers])) AND
((AR1_CustomerMaster.SalesPersonCode) Not In (Select
[tMASmembers].[SalesPersonCode] From [tMASmembers])) AND
((AR_90_UDF_AR_Customer.Sic_Code) Not In (Select
[tMASmembers].[SIC_Code] From [tMASmembers])) AND
((AR_90_UDF_AR_Customer.Sic_Desc) Not In (Select
[tMASmembers].[SIC_Desc] From [tMASmembers])));

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!One more note: In a test I did, it seems I can only append when I delete
the records from the destination table that all belong to an entire
division (01 or 02). So if I delete all the customers from div 01, I can
append them back. The division and customer number fields are the
primary keys and they come in as text from the ODBC import.

Help appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Frank Py" <fpy@.proactnet.com> wrote in message
news:3ffd9cd7$0$70302$75868355@.news.frii.net...
> One more note: In a test I did, it seems I can only append when I delete
> the records from the destination table that all belong to an entire
> division (01 or 02). So if I delete all the customers from div 01, I can
> append them back. The division and customer number fields are the
> primary keys and they come in as text from the ODBC import.
> Help appreciated.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You probably want something like this:

insert into tMASmembers (...)
select (...)
from
AR1_CustomerMaster CM inner join AR_90_UDF_AR_Customer C
on CM.Division = C.Division and CM.CustomerNumber = C.CustomerNumber
where not exists
(select * from tMASmembers tm
where tm.Division = CM.Division and
tm.CustomerNumber = CM.CustomerNumber)

Simon|||Thanks for the example.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Append query from Access Table to Linked SQL Server Table Failing

Strange one here - I am posting this in both SQL Server and Access forums

Access is telling me it can't append any of the records due to a key violation.

The query:

INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;

Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

I can manually append the records using cut and paste with no problems.

I have tried re-linking the tables.

Any ideas?
Thanks,
BradI'd guess that the SQL Server db you think you are linking to in dbo_Colors isn't really the one you think. Perhaps the login/password in your datasource is connecting to a different database than the one you expect?

To check, get the name of the constraint being violated and check in the sql server table to see if that constraint exists

Also, try running the sql server profiler to see what sql server db ms access is trying to insert data into|||mattrevs,

It does appear that I am linking the right table. There is only one Colors table and only one database that has a colors table.

Could you tell me how to check which constraint is being violated? After I manually pasted the data into dbo_Colors, I ran checkconstraints() and no error were reported.

I also tried running the query with implicit_transaction OFF and still no joy.

I ran the trace and here are the last two lines. The first one was simply duplicated for each record:

RPC:Completed exec sp_executesql N'INSERT INTO "dbo"."Colors" ("NameColorID","Application","Red","Green","Blue") VALUES (@.P1,@.P2,@.P3,@.P4,@.P5)', N'@.P1 nvarchar(38),@.P2 nvarchar(30),@.P3 int,@.P4 int,@.P5 int', N'{FFC28EAD-1134-40BB-9723-7D88A0B0AC7A}', N'Tile1', 197, 183, 156 Microsoft Access sa 0 11 0 0 2564 54 2004-06-21 10:35:14.170

SQL:BatchCompleted IF @.@.TRANCOUNT > 0 ROLLBACK TRAN Microsoft Access sa 0 0 0 0 2564 54 2004-06-21 10:35:19.403

- Brad|||You say that pasting the data one row at a time from within ms access works ok?

If so, maybe you could also perform a sql trace on this and see what is different?|||I got it figured out. (BTW, I did the trace with the pasting but the results were ... confusing)

Apparently a bit field in SQL server can be Null?

There are two additional fields in the SQL version of the table. The NVarChar field I had set to allow nulls. I never bothered with the Bit field.

When I checked Allow Nulls on the bit field and re-linked, my query worked.

I guess my lesson her is never assume anything. I have learned that lesson many times and will probably learn it again in the (near) future.

Thanks for your help,
Brad

Append information to data already in a column

Just recently I was thrown into the DBA roll... needless to say I'm not
a DBA. The customer has requested a query to take a column full of
numerical data (a 6 digit number) and run a query to append a .00
behind that number for every record in that column. Is this possible?
I've checked the online books and have done countless searches for a
hint of a query to no avail.
Any and all help would be greatly appreciated.
Thank you,
JIf you just want to represent the (integer?) number as decimal with '.00'
SELECT CAST([YourNumber] AS decimal(8,2)) AS "number"
FROM [YourTable]
If you actually want to change how the number is stored in the database:
ALTER TABLE [YourTable] ALTER COLUMN [YourNumber] decimal(8,2)
"JMiller" wrote:

> Just recently I was thrown into the DBA roll... needless to say I'm not
> a DBA. The customer has requested a query to take a column full of
> numerical data (a 6 digit number) and run a query to append a .00
> behind that number for every record in that column. Is this possible?
> I've checked the online books and have done countless searches for a
> hint of a query to no avail.
> Any and all help would be greatly appreciated.
> Thank you,
> J
>|||You have not told us the current data type of the column, and that
makes all the difference.
If it is an integer (bigint, int, smallint, tinyint) then the data
type would need to change to DECIMAL (or NUMERIC) with two decimal
places.
If it is already a decimal (or numeric) then the type has to change to
add two decimal places, being careful to keep sufficient length.
If the column is character, it needs to be lengthened by three
characters, and then updated to append the '.00' string after the data
that is already there.
If the column is varchar, it MIGHT need to be lengthened, and then it
would be updated as for char.
So, what is the column's data type?
Roy
On 15 Feb 2006 15:16:36 -0800, "JMiller" <Gloranard@.gmail.com> wrote:

>Just recently I was thrown into the DBA roll... needless to say I'm not
>a DBA. The customer has requested a query to take a column full of
>numerical data (a 6 digit number) and run a query to append a .00
>behind that number for every record in that column. Is this possible?
>I've checked the online books and have done countless searches for a
>hint of a query to no avail.
>Any and all help would be greatly appreciated.
>Thank you,
>J|||I apologize for forgetting to add that important piece of information.
The table is currently configured varchar (25).|||>I apologize for forgetting to add that important piece of information.
> The table is currently configured varchar (25).
update mytable set mycolumn = rtrim(mycolumn) + '.00'
where mycolumn is not null
Things to consider.
* The maximum size of any value in mycolumn is less than 23 (since you are
adding three additional characters). How to do this: select
max(len(mycolumn)) from mytable
* Verify that this is actually appropriate for the data found in the column
for all rows. In other words, are all values "properly" numeric.
* Perhaps the better approach is to change the datatype of the column.
Alternatively, create a computed column (or view) that does this for you.
Why? Well, the values that are currently in the table got there somehow.
It is likely that this functionality will continue to use the same format
when inserting or updating rows.
* You might need additional criteria in your where clause. You might not
want to change an empty string to '.00' (which is what the above query will
do).
* For testing purposes, you can wrap the above statement in a transaction.
If you rollback the transaction, you can issue the statement multiple times
and view the results. E.g.,
begin tran
update mytable set ...
select mycolumn from mytable
rollback tran|||Thanks you both for the help, it worked like a champ. Thank you, thank
you, thank you!

Append Data

Hi,
Appreciated it if someone could help me with regards to
the abovesaid problem.
I don't know how to append data using SQL Query Analyzer
from table a to table b because it will create duplicate
table b.recordnumber(auto number) in table b. I tried to
use DTS but failed due to the duplicate record number(auto
number).
tq.
Hi,
Use "SET IDENTITY_INSERT" to allow explicit values in acolumn with identity.
See SET IDENTITY_INSERT in books online. There is good example as well which
shows the usage.
Sample:-
create table id1(i int identity, k char(10))
create table id2(i int identity, k char(10))
go
insert into id1(K) values('hari')
insert into id2(K) values('raj')
go
select * from id1
select * from id2
go
set identity_insert id1 on
insert into id2(k) select k from id1
go
select * from id2
Thanks
Hari
MCDBA
"GW" <anonymous@.discussions.microsoft.com> wrote in message
news:657801c4753d$a64178e0$a501280a@.phx.gbl...
> Hi,
> Appreciated it if someone could help me with regards to
> the abovesaid problem.
> I don't know how to append data using SQL Query Analyzer
> from table a to table b because it will create duplicate
> table b.recordnumber(auto number) in table b. I tried to
> use DTS but failed due to the duplicate record number(auto
> number).
> tq.

append a string to all the values in a column SQL

Sub: Append a string to all the values in a column SQL
Hi all,
This might be a very simple query. But I am new to SQL programming.
Hope someone can help me.
I have a table --
1634 Fred los angeles
123 Sam
1245 abc
1231 ...
1278 ....
578 ...
I want to append TD before the numbers in the first column, I know
there should be a simple statement for this. But I am not getting it as
I have done SQL.
I would like to get
TD1634 Fred los angeles
TD123 Sam
TD1245 abc
TD1231 ...
TD1278 ....
TD578 ...
Hope someone can help me.
Thanks.Hopefully you are doing this in a query, not changing the data in the table,
right? Assuming the column is numeric/integer:
SELECT 'TD' + CONVERT(VARCHAR(12), unnamed_column) FROM unnamed_table;
If the column is already char/varchar then you can say:
SELECT 'TD'+unnamed_column FROM unnamed_table;
<gtg974p@.gmail.com> wrote in message
news:1137170547.476684.150570@.g44g2000cwa.googlegroups.com...
> Sub: Append a string to all the values in a column SQL
> Hi all,
> This might be a very simple query. But I am new to SQL programming.
> Hope someone can help me.
> I have a table --
> 1634 Fred los angeles
> 123 Sam
> 1245 abc
> 1231 ...
> 1278 ....
> 578 ...
> I want to append TD before the numbers in the first column, I know
> there should be a simple statement for this. But I am not getting it as
> I have done SQL.
> I would like to get
> TD1634 Fred los angeles
> TD123 Sam
> TD1245 abc
> TD1231 ...
> TD1278 ....
> TD578 ...
> Hope someone can help me.
> Thanks.
>|||Thanks a lot for your quick reply. As a matter of fact, I would like to
update those values with the new appended values. How should I go about
it.
Thanks again.
Aaron Bertrand [SQL Server MVP] wrote:
> Hopefully you are doing this in a query, not changing the data in the tabl
e,
> right? Assuming the column is numeric/integer:
> SELECT 'TD' + CONVERT(VARCHAR(12), unnamed_column) FROM unnamed_table;
> If the column is already char/varchar then you can say:
> SELECT 'TD'+unnamed_column FROM unnamed_table;
>
>|||We're going to need more information (see http://www.aspfaq.com/5006) such
as what is the actual table name, column names, table structure, etc. Is
that an INT column, does it have the IDENTITY property, etc.
However, I fail to see the value of appending the exact same string to every
single row in a table. Use a view.
CREATE VIEW dbo.Whatever
AS
SELECT unnamed_column = 'TD'+unnamed_column
FROM unnamed_table
GO
Now, when you SELECT unnamed_column FROM dbo.Whatever instead of
unnamed_table, it looks like TD1234, TD15, etc. But you don't have to go
back and update the table every time you add a row.
Another alternative is to use a computed column, like
CREATE TABLE dbo.Foo
(
Column1 VARCHAR(32),
Column2 AS 'TD'+Column1
);
SET NOCOUNT ON;
INSERT dbo.Foo(Column1) SELECT '1234'
INSERT dbo.Foo(Column1) SELECT '15'
SELECT column1, column2 FROM foo;
GO
DROP TABLE dbo.foo;
GO
Again, no updating or perpetual maintenance required.
<gtg974p@.gmail.com> wrote in message
news:1137171379.310459.146110@.z14g2000cwz.googlegroups.com...
> Thanks a lot for your quick reply. As a matter of fact, I would like to
> update those values with the new appended values. How should I go about
> it.
> Thanks again.
>
> Aaron Bertrand [SQL Server MVP] wrote:
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O1Gc1MGGGHA.1676@.TK2MSFTNGP09.phx.gbl...
> We're going to need more information (see http://www.aspfaq.com/5006) such
> as what is the actual table name, column names, table structure, etc. Is
> that an INT column, does it have the IDENTITY property, etc.
> However, I fail to see the value of appending the exact same string to
> every single row in a table. Use a view.
Aaron,
To give this poster the benefit of the doubt, this requirement can arise
when one business assumes another's accounts/customers, and ID numbers
are no longer distinct. I might want to avoid modifying the column, but a
view is not
the answer if there will also need to be prefixes for IDs other than 'TD'.
One option is to put the 'TD' alone in a new column--call it [origin]--and
use
a computed column [origin]+ID for the prefixed IDs. But ultimately that's
not
likely to be a great solution, because a one-column primary key because a
two-column one. Better might be to modify the original column and keep
a separate table matching former IDs with current ones.
Of course, you are right, if the TD is just because management
wants all ID numbers to begin with TD, which is more likely than
my scenario.
Steve Kass
Drew University

> CREATE VIEW dbo.Whatever
> AS
> SELECT unnamed_column = 'TD'+unnamed_column
> FROM unnamed_table
> GO
> Now, when you SELECT unnamed_column FROM dbo.Whatever instead of
> unnamed_table, it looks like TD1234, TD15, etc. But you don't have to go
> back and update the table every time you add a row.
> Another alternative is to use a computed column, like
> CREATE TABLE dbo.Foo
> (
> Column1 VARCHAR(32),
> Column2 AS 'TD'+Column1
> );
> SET NOCOUNT ON;
> INSERT dbo.Foo(Column1) SELECT '1234'
> INSERT dbo.Foo(Column1) SELECT '15'
> SELECT column1, column2 FROM foo;
> GO
> DROP TABLE dbo.foo;
> GO
> Again, no updating or perpetual maintenance required.
>
>
> <gtg974p@.gmail.com> wrote in message
> news:1137171379.310459.146110@.z14g2000cwz.googlegroups.com...
>|||Thanks again.
Let me explain the whole situation.
The table has 5 columns (table name: testchemicals). These are made up
date but in essence this is what it looks like.
No Name Description Value
Date
1. ASDF used in dyeing TD123 Jan
11,2000
2. VBHG used in soda 786 Nov
23, 1983
3. LKJF used in food 1235
Sep 4, 2001
4. POUY used in XYZ TD236 Mar
12, 1999
None of them are linked to anything, so there is no integrity or
referential isues. As you can see the values have two types of naming
scheme. Chemicals tested in one site has TD before them, whereas the
other site didn't use them. Now we want to check, if the data in the
value column starts with TD, if not add TD to it and write it back to
the table. Hope I am making myself clear.
Thanks a lot for helping.|||(gtg974p@.gmail.com) writes:
> The table has 5 columns (table name: testchemicals). These are made up
> date but in essence this is what it looks like.
> No Name Description Value
> Date
> 1. ASDF used in dyeing TD123 Jan
> 11,2000
> 2. VBHG used in soda 786 Nov
> 23, 1983
> 3. LKJF used in food 1235
> Sep 4, 2001
> 4. POUY used in XYZ TD236 Mar
> 12, 1999
> None of them are linked to anything, so there is no integrity or
> referential isues. As you can see the values have two types of naming
> scheme. Chemicals tested in one site has TD before them, whereas the
> other site didn't use them. Now we want to check, if the data in the
> value column starts with TD, if not add TD to it and write it back to
> the table. Hope I am making myself clear.
UPDATE tbl
SET Value = 'TD' + Value
WHERE Value NOT LIKE 'TD%'
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|||Try this:
Create Table Test
(
Col1 varchar(20)
)
Insert Into Test (Col1)
Values('123')
Insert Into Test (Col1)
Values('158')
Insert Into Test (Col1)
Values('TD243')
Insert Into Test (Col1)
Values('8476')
Insert Into Test (Col1)
Values('1829')
Insert Into Test (Col1)
Values('0938')
Insert Into Test (Col1)
Values('TD8933')
Insert Into Test (Col1)
Values('TD343')
Insert Into Test (Col1)
Values('908')
Update a
Set Col1 = (Case
When Left(a.Col1, 2) = 'TD' Then b.Col1
Else 'TD' + b.Col1
End)
>From Test a
Join Test b on a.Col1 = b.Col1
Select * From Test
Drop Table Test
HTH
Barry|||> To give this poster the benefit of the doubt, this requirement can arise
> when one business assumes another's accounts/customers, and ID numbers
> are no longer distinct. I might want to avoid modifying the column, but a
> view is not
> the answer if there will also need to be prefixes for IDs other than 'TD'.
Well, the poster did say "all the values." :-)|||Based on this last post from you this is clearly a case where the new
requirement should be handled by adding a new attribute to the set - e.g.
adding a new column. I'd suggest strongly against changing the values in the
existing column, since that would simply break first normal form.
ML
http://milambda.blogspot.com/

Append (?) query

I'm trying to join 8 views (ViewA, ViewB, etc...) together
into one new table. All the views have 3 columns (ID,
result1, result2), as does the destination table. I'm
currently trying this method -
SELECT * INTO [dbo].[ANewTable] FROM [dbo].[ViewA]
SELECT * INTO [dbo].[ANewTable] FROM [dbo].[ViewB]
...etc...
but only ViewA is added to ANewTable and I get the error
message -
'There is already an object named 'ANewTable' in the
database.'
Any advice on how to resolve this would be greatly
appreciated.
Thanks.
1: Do a UNION between the SELECT statements:
SELECT col1, col2, ... FROM ViewA
UNION ALL
SELECT col1, col2, ... FROM ViewB
UNION ALL
SELECT col1, col2, ... FROM ViewC
2. Once that is sorted out and gives correct result, use it as a derived table:
SELECT col1, col2, ...
INTO aNewTable
FROM
(
<the above code goes here>
) AS d
Each SELECT INTO creates a new table, you cannot have several using the same table name. Also, don't
use SELECT *, that is begging for trouble in the future...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hagglingchad" <anonymous@.discussions.microsoft.com> wrote in message
news:088b01c4a608$d2b2f830$a401280a@.phx.gbl...
> I'm trying to join 8 views (ViewA, ViewB, etc...) together
> into one new table. All the views have 3 columns (ID,
> result1, result2), as does the destination table. I'm
> currently trying this method -
> SELECT * INTO [dbo].[ANewTable] FROM [dbo].[ViewA]
> SELECT * INTO [dbo].[ANewTable] FROM [dbo].[ViewB]
> ...etc...
> but only ViewA is added to ANewTable and I get the error
> message -
> 'There is already an object named 'ANewTable' in the
> database.'
> Any advice on how to resolve this would be greatly
> appreciated.
> Thanks.
|||Tibor,
This is great. Thanks very much. However, I'm not quite
sure of the relevance of 'AS d'.
Chad
>--Original Message--
>1: Do a UNION between the SELECT statements:
>SELECT col1, col2, ... FROM ViewA
>UNION ALL
>SELECT col1, col2, ... FROM ViewB
>UNION ALL
>SELECT col1, col2, ... FROM ViewC
>2. Once that is sorted out and gives correct result, use
it as a derived table:
>SELECT col1, col2, ...
>INTO aNewTable
>FROM
>(
><the above code goes here>
>) AS d
>
>Each SELECT INTO creates a new table, you cannot have
several using the same table name. Also, don't
>use SELECT *, that is begging for trouble in the future...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"hagglingchad" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:088b01c4a608$d2b2f830$a401280a@.phx.gbl...
together
>
>.
>
|||That is just an alias name for the derived table. Same as in below:
FROM tbla AS a JOIN tblb AS b
For a derived table, you *need* to define such a "correlation" name. Syntax requires it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hagglingchad" <anonymous@.discussions.microsoft.com> wrote in message
news:14f401c4a615$b83324a0$a601280a@.phx.gbl...[vbcol=seagreen]
> Tibor,
> This is great. Thanks very much. However, I'm not quite
> sure of the relevance of 'AS d'.
> Chad
> it as a derived table:
> several using the same table name. Also, don't
> wrote in message
> together

Apostrophes and Dynamic SQL

I am trying to Ad-hoc query from a user input(internal app), where the user
enters in a name just as "Al's Game Crazy". Each time I do this, the
Apostrophe gets in the way. I have tried using the "char(39)" setup for wit
h
the like statement. But I keep getting and error.
Error returned:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Code:
set nocount on
Declare @.SQL_Where varchar(1000)
Declare @.Brand varchar(50)
Declare @.pos int
Declare @.SQL varchar(2000)
--Set @.Brand = 'Al' + char(39) +'s Game Crazy'
Set @.Brand = 'Al''s Game Crazy'
SET @.SQL_Where = ''
-- Check Brand
if Len(rtrim(ltrim(@.Brand))) > 0
BEGIN
SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
if @.pos > 0
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
Substring(@.Brand, @.pos + 1, len(@.Brand))
SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand +
'%' + char(39) + ' and'
END
-- Remove the last part of the where clause filter " and"
SET @.SQL_Where = rtrim(@.SQL_Where)
SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
select @.Brand
select @.SQL_Where
SET @.SQL = 'select top 3000 * from view_ContactLocation where' + @.SQL_Where
select @.SQL
exec (@.SQL)
set nocount off
Any help or direction would be appreciated.
Thanks,
ScottTry replacing occurrances of the single apostrophe ' with double apostrophe
''
"Scott Heffron" <ScottHeffron@.discussions.microsoft.com> wrote in message
news:C79CAF92-5466-4B06-B136-7C7970B93BBA@.microsoft.com...
>I am trying to Ad-hoc query from a user input(internal app), where the user
> enters in a name just as "Al's Game Crazy". Each time I do this, the
> Apostrophe gets in the way. I have tried using the "char(39)" setup for
> with
> the like statement. But I keep getting and error.
> Error returned:
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ''.
> Code:
> set nocount on
> Declare @.SQL_Where varchar(1000)
> Declare @.Brand varchar(50)
> Declare @.pos int
> Declare @.SQL varchar(2000)
> --Set @.Brand = 'Al' + char(39) +'s Game Crazy'
> Set @.Brand = 'Al''s Game Crazy'
> SET @.SQL_Where = ''
> -- Check Brand
> if Len(rtrim(ltrim(@.Brand))) > 0
> BEGIN
> SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
> if @.pos > 0
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand
> +
> '%' + char(39) + ' and'
> END
> -- Remove the last part of the where clause filter " and"
> SET @.SQL_Where = rtrim(@.SQL_Where)
> SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
> select @.Brand
> select @.SQL_Where
> SET @.SQL = 'select top 3000 * from view_ContactLocation where' +
> @.SQL_Where
> select @.SQL
> exec (@.SQL)
> set nocount off
>
> Any help or direction would be appreciated.
> Thanks,
> Scott
>
>|||JT, I tried the modifiying the following:
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) + Substring(@.Brand,
@.pos + 1, len(@.Brand))
to
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + '''' + Substring(@.Brand, @.pos
+ 1, len(@.Brand))
There appears to be no difference. I get the same errors. I believe that
is where you were thinking about doing the double "'".
Thanks,
Scott|||Scott Heffron wrote:
> JT, I tried the modifiying the following:
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> to
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + '''' +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> There appears to be no difference. I get the same errors. I believe
> that is where you were thinking about doing the double "'".
> Thanks,
> Scott
No. He's talking about using the REPLACE function to replace the apostrophe
with two apostrophes:
SET @.Brand=REPLACE(@.Brand,'''',''')
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You need to double-up the apostrophe to pass it into the stored procedure.
So from the app, the call need to look like this:
EXEC dbo.MyProc @.Brand = 'Al''s Game Crazy'
This doubling-up allows the stored procedure to be called successfully, as
the double apostrophe acts as an escape mechanism instead of ending the
string early. Now when you get the string into the stored procedure, it's
back to a single apostrophe only. So, if you are trying to put this string
into a string inside the stored procedure, you need to double them up again.
Of course, inside of SQL, you need to escape all instances of a quote. So,
try:
SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' +
REPLACE(@.Brand, '''',''') +
'%' + char(39) + ' and' -- shudder... why add an AND unless you know there
is more WHERE?
Please see the following.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
I think with some work you could streamline this process quite a bit and
make it far less hairy-looking. I'm not going to address the @.sql and
@.sql_where, because I have no idea if and why end users are able to write
your sql statements for you. But for the wildcard search alone, you could
do this:
CREATE PROCEDURE dbo.SearchContacts
@.Brand VARCHAR(50) = ''
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 3000 <column list! don't use *>
FROM View_ContactLocation
WHERE Brand LIKE '%'+@.Brand+'%';
END
GO
"Scott Heffron" <ScottHeffron@.discussions.microsoft.com> wrote in message
news:C79CAF92-5466-4B06-B136-7C7970B93BBA@.microsoft.com...
>I am trying to Ad-hoc query from a user input(internal app), where the user
> enters in a name just as "Al's Game Crazy". Each time I do this, the
> Apostrophe gets in the way. I have tried using the "char(39)" setup for
> with
> the like statement. But I keep getting and error.
> Error returned:
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ''.
> Code:
> set nocount on
> Declare @.SQL_Where varchar(1000)
> Declare @.Brand varchar(50)
> Declare @.pos int
> Declare @.SQL varchar(2000)
> --Set @.Brand = 'Al' + char(39) +'s Game Crazy'
> Set @.Brand = 'Al''s Game Crazy'
> SET @.SQL_Where = ''
> -- Check Brand
> if Len(rtrim(ltrim(@.Brand))) > 0
> BEGIN
> SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
> if @.pos > 0
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand
> +
> '%' + char(39) + ' and'
> END
> -- Remove the last part of the where clause filter " and"
> SET @.SQL_Where = rtrim(@.SQL_Where)
> SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
> select @.Brand
> select @.SQL_Where
> SET @.SQL = 'select top 3000 * from view_ContactLocation where' +
> @.SQL_Where
> select @.SQL
> exec (@.SQL)
> set nocount off
>
> Any help or direction would be appreciated.
> Thanks,
> Scott
>
>|||Thanks, it worked.|||There are several columns that can be used in the where clause that is the
reason for the "and" at the end. Not all the columns are used. I did not
want to do a search on column1 = '%%' if nothing was in the @.column1
variable. I am expecting that is wasting query time and not needed. Sorry,
I used the "*" to save space. You are absolutely right on using the column
names.
What the variable will look like from the users application is "Al's Game
Crazy"
There are 9 different variables. Is it better to allow for a search on '%%'
or leave it out and create the where clause dynamically?
Thanks,
Scott|||> There are 9 different variables. Is it better to allow for a search on
> '%%'
> or leave it out and create the where clause dynamically?
Have you read the following article? I think it applies quite nicely to
your situation. It looks like a long read, but I think you'll be glad you
did it.
http://www.sommarskog.se/dyn-search.html|||Scott Heffron (ScottHeffron@.discussions.microsoft.com) writes:
> I am trying to Ad-hoc query from a user input(internal app), where the
> user enters in a name just as "Al's Game Crazy". Each time I do this,
> the Apostrophe gets in the way. I have tried using the "char(39)" setup
> for with the like statement. But I keep getting and error.
Rather than building the entire SQL string, use sp_executesql instead.
Look at http://www.sommarskog.se/dyn-search.html#sp_executesql for
an example. (Further below there is also an example that uses EXEC()
and deals with strings in a structured way.)
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973B3B229ACAYazorman@.127.0.0.1...

> Rather than building the entire SQL string, use sp_executesql instead.
> Look at http://www.sommarskog.se/dyn-search.html#sp_executesql for
> an example. (Further below there is also an example that uses EXEC()
> and deals with strings in a structured way.)
Other way is to use some tool that will do all job for you (provide your use
r
with friendly interface to create queries and generate SQL statement in resu
lt).
We prefer EasyQuery (http://devtools.korzh.com/eq/) but I think there are so
me
similar products even free ones.
With the best regards, Nik.