HELP! Dynamic binding of schema element names

Hi all,
Somehow I lost the thread where I originally posted this query. Thanks
Erland and GregO for your answers. Very helpful indeed.
Although I think I can solve my problem with what you guys suggested, I'd
really like to know your opinion and maybe offer solutions
of achieving my goal. Let me put my problem forth in more detail:
My application has all it's logic in a COM component and all SQL queries are
issued from there.
The application itself was never designed with security and access-control
in mind
(which I'm cursing it for and have to incorporate now :-(( ). So, now I have
a
few thousand queries that I don't want to affect drastically.
I have a bunch of users in a [USER] table and a bunch of resources in a
[RESOURCE] table. What ultimately should happen is that
every user should be able to see resources only entitled to her based on
some security policy.
Here is how I think it can be done.
A new entity [SEC_GROUP] can be introduced where each user is part of one or
more security groups. For each security group,
I will create a view on the [RESOURCE] table: [admingrp_resource],
[generaluser_resource] and so on.
So, when a user issues a query like,
SELECT * FROM [RESOURCE]
I will actually substitute [RESOURCE] with a function like
get_resource_view(userid)
EXEC( 'SELECT * FROM ' + get_resource_view(userid))
The get_resource_view( ) function would get the appropriate resource view
for the user based on her security group.
The above would be rather easy if the user is part of only one security
group. If there are more, I might have to do
some unions. This would impact the queries quite a bit, but I can't think of
another way to implement this.
Once again, thanks for your responses, I appreciate your help and looking
forward to more suggestions.
Best regards,
--Abhi
Abhijith Das (adas@.expeditevcs.com) writes:
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
>
> Here's a simple select statement
>
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
>
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
>
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition = > 1.
>
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however. Is there a way to
> accomplish this?
Yes. But it is unlikely that it is the right thing to do. Since I don't
know your underlying problem, I cannot suggest a solution here and now.
But this article on my web site, both describes on how you can achieve
this - and why you most probably should not do it anyway.
http://www.sommarskog.se/dynamic_sql.html.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.aspAbhijith Das (adas@.expeditevcs.com) writes:
> My application has all it's logic in a COM component and all SQL queries
> are issued from there. The application itself was never designed with
> security and access-control in mind (which I'm cursing it for and have
> to incorporate now :-(( ). So, now I have a few thousand queries that I
> don't want to affect drastically.
> I have a bunch of users in a [USER] table and a bunch of resources in a
> [RESOURCE] table. What ultimately should happen is that every user
> should be able to see resources only entitled to her based on some
> security policy. Here is how I think it can be done. A new entity
> [SEC_GROUP] can be introduced where each user is part of one or more
> security groups. For each security group, I will create a view on the
> [RESOURCE] table: [admingrp_resource], [generaluser_resource] and so
> on.
First of all, for this to be meaningful, you need to revoke access to
the tables from the users. Keep in mind that there are other means to
connecting to SQL Server, and a skilled user could for instance use
Query Analyzer to access the data.
> So, when a user issues a query like,
> SELECT * FROM [RESOURCE]
> I will actually substitute [RESOURCE] with a function like
> get_resource_view(userid)
> EXEC( 'SELECT * FROM ' + get_resource_view(userid))
But why do you want to have this function in SQL? Since you apparently
have all your logic client-side, why not stick to that? Depending on
the size of the data stored for these security groups, you could read
this data once, and keep it in memory. (May need some refresh mechanism
in case the security configuration is changed.)
From this follows that the user will need to have SELECT access to
the table what defines the security groups and the resources. (Unless
you use an application role.)
> The get_resource_view( ) function would get the appropriate resource
> view for the user based on her security group. The above would be rather
> easy if the user is part of only one security group. If there are more,
> I might have to do some unions. This would impact the queries quite a
> bit, but I can't think of another way to implement this.
A common approach to row-level security is to have views that includes
conditions like:
AND userid = SYSTEM_USER
Although one should be aware of that a skilled user with a query tool can
still be able to carve out glimpses of data he is not intended to see.
For a more complex security scheme, you could have table-valued
functions, but you would have to one for each base table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

HELP! Dynamic binding of schema element names

Hi all,
I'm trying to achieve the following using SQL and SQLServer2000 is the db
I'm using.
Here's a simple select statement
SELECT column1, column2 FROM my_table WHERE some_condition = 1
What I want to be able to do is bind the name my_table to an actual
tablename during runtime, i.e. when the query executes.
The equivalent effect of what I'd like can be represented as below:
SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition = 1.
Here get_my_table_name( ) is a function that evaluates and returns the table
name. Things don't work this way however.
Is there a way to accomplish this?
Any replies are greatly appreciated.
Thanks in advance,
--Abhi[posted and mailed, please reply in news]
Abhijith Das (adas@.expeditevcs.com) writes:
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition => 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however. Is there a way to
> accomplish this?
Yes. But it is unlikely that it is the right thing to do. Since I don't
know your underlying problem, I cannot suggest a solution here and now.
But this article on my web site, both describes on how you can achieve
this - and why you most probably should not do it anyway.
http://www.sommarskog.se/dynamic_sql.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi ,
No you can't do that that way. You must use dynamic SQL something like this
Declare @.SQL varchar(1000)
set @.SQL = 'SELECT column1, column2 FROM ' + 'my_table' + ' WHERE
some_condition = 1'
exec @.SQL
--
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Abhijith Das" <adas@.expeditevcs.com> wrote in message
news:u400jnbqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition => 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however.
> Is there a way to accomplish this?
> Any replies are greatly appreciated.
> Thanks in advance,
> --Abhi
>

HELP! Dynamic binding of schema element names

Hi all,
I'm trying to achieve the following using SQL and SQLServer2000 is the db
I'm using.
Here's a simple select statement
SELECT column1, column2 FROM my_table WHERE some_condition = 1
What I want to be able to do is bind the name my_table to an actual
tablename during runtime, i.e. when the query executes.
The equivalent effect of what I'd like can be represented as below:
SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition = 1.
Here get_my_table_name( ) is a function that evaluates and returns the table
name. Things don't work this way however.
Is there a way to accomplish this?
Any replies are greatly appreciated.
Thanks in advance,
--Abhi
Hi ,
No you can't do that that way. You must use dynamic SQL something like this
Declare @.SQL varchar(1000)
set @.SQL = 'SELECT column1, column2 FROM ' + 'my_table' + ' WHERE
some_condition = 1'
exec @.SQL
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Abhijith Das" <adas@.expeditevcs.com> wrote in message
news:u400jnbqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition =
> 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however.
> Is there a way to accomplish this?
> Any replies are greatly appreciated.
> Thanks in advance,
> --Abhi
>
|||[posted and mailed, please reply in news]
Abhijith Das (adas@.expeditevcs.com) writes:
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition =
> 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however. Is there a way to
> accomplish this?
Yes. But it is unlikely that it is the right thing to do. Since I don't
know your underlying problem, I cannot suggest a solution here and now.
But this article on my web site, both describes on how you can achieve
this - and why you most probably should not do it anyway.
http://www.sommarskog.se/dynamic_sql.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Hi all,
Somehow I lost the thread where I originally posted this query. Thanks
Erland and GregO for your answers. Very helpful indeed.
Although I think I can solve my problem with what you guys suggested, I'd
really like to know your opinion and maybe offer solutions
of achieving my goal. Let me put my problem forth in more detail:
My application has all it's logic in a COM component and all SQL queries are
issued from there.
The application itself was never designed with security and access-control
in mind
(which I'm cursing it for and have to incorporate now :-(( ). So, now I have
a
few thousand queries that I don't want to affect drastically.
I have a bunch of users in a [USER] table and a bunch of resources in a
[RESOURCE] table. What ultimately should happen is that
every user should be able to see resources only entitled to her based on
some security policy.
Here is how I think it can be done.
A new entity [SEC_GROUP] can be introduced where each user is part of one or
more security groups. For each security group,
I will create a view on the [RESOURCE] table: [admingrp_resource],
[generaluser_resource] and so on.
So, when a user issues a query like,
SELECT * FROM [RESOURCE]
I will actually substitute [RESOURCE] with a function like
get_resource_view(userid)
EXEC( 'SELECT * FROM ' + get_resource_view(userid))
The get_resource_view( ) function would get the appropriate resource view
for the user based on her security group.
The above would be rather easy if the user is part of only one security
group. If there are more, I might have to do
some unions. This would impact the queries quite a bit, but I can't think of
another way to implement this.
Once again, thanks for your responses, I appreciate your help and looking
forward to more suggestions.
Best regards,
--Abhi
Abhijith Das (adas@.expeditevcs.com) writes:
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition =
> 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however. Is there a way to
> accomplish this?
Yes. But it is unlikely that it is the right thing to do. Since I don't
know your underlying problem, I cannot suggest a solution here and now.
But this article on my web site, both describes on how you can achieve
this - and why you most probably should not do it anyway.
http://www.sommarskog.se/dynamic_sql.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Abhijith Das (adas@.expeditevcs.com) writes:
> My application has all it's logic in a COM component and all SQL queries
> are issued from there. The application itself was never designed with
> security and access-control in mind (which I'm cursing it for and have
> to incorporate now :-(( ). So, now I have a few thousand queries that I
> don't want to affect drastically.
> I have a bunch of users in a [USER] table and a bunch of resources in a
> [RESOURCE] table. What ultimately should happen is that every user
> should be able to see resources only entitled to her based on some
> security policy. Here is how I think it can be done. A new entity
> [SEC_GROUP] can be introduced where each user is part of one or more
> security groups. For each security group, I will create a view on the
> [RESOURCE] table: [admingrp_resource], [generaluser_resource] and so
> on.
First of all, for this to be meaningful, you need to revoke access to
the tables from the users. Keep in mind that there are other means to
connecting to SQL Server, and a skilled user could for instance use
Query Analyzer to access the data.

> So, when a user issues a query like,
> SELECT * FROM [RESOURCE]
> I will actually substitute [RESOURCE] with a function like
> get_resource_view(userid)
> EXEC( 'SELECT * FROM ' + get_resource_view(userid))
But why do you want to have this function in SQL? Since you apparently
have all your logic client-side, why not stick to that? Depending on
the size of the data stored for these security groups, you could read
this data once, and keep it in memory. (May need some refresh mechanism
in case the security configuration is changed.)
From this follows that the user will need to have SELECT access to
the table what defines the security groups and the resources. (Unless
you use an application role.)

> The get_resource_view( ) function would get the appropriate resource
> view for the user based on her security group. The above would be rather
> easy if the user is part of only one security group. If there are more,
> I might have to do some unions. This would impact the queries quite a
> bit, but I can't think of another way to implement this.
A common approach to row-level security is to have views that includes
conditions like:
AND userid = SYSTEM_USER
Although one should be aware of that a skilled user with a query tool can
still be able to carve out glimpses of data he is not intended to see.
For a more complex security scheme, you could have table-valued
functions, but you would have to one for each base table.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Help! Can't pass names with apostrophe from ASP to SQL7 proc

I have a proc that does wildcard searches on names which works fine
expect with names that contain an apostrophe like O'Neil.
When O'Neil is sent to the proc, I get "Incorrect syntax near 'Neil'."
This tells me I've got a delimiter issue.
I've tried replacing the single apostrophe with a double or even using
quotes as the delimiter and still get an error. What should I try; can
anyone help?
BTW, here's an example of the proc.
========================================
=============
CREATE proc getName
@.CUSTNAME varchar(15)
as
declare @.SQL varchar(4000)
set @.SQL = 'select PARENT_NAME, CITY, STATE, ZIP from
[CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like ''%' + @.CUSTNAME + '%'''
exec (@.SQL)
========================================
==============
I've passed the name as below and still get errors.
getName O'Neil
getName 'O'Neil'
getName ''O'Neil'' - All Single quotes chr (39)
getName "O'Neil" - Quotes on the outside chr(34)
getName 'O''Neil' - All Single quotes chr (39)
getName "O''Neil" - Quotes on the outside chr(34), and double
singles in the name chr(39)
Nothing works due to the syntex. What can I do, please HELP!!
Thank you,
DaeI don't understand why you are using dynamic sql for this..
I guess you simplified the actual query for the post :)
But anyways.. try this.
getName 'O''''Neil' -- 4 single quotes|||this would work
getname 'o''''niel'
Best Regards
Vadivel
http://vadivel.blogspot.com
"Omnibuzz" wrote:

> I don't understand why you are using dynamic sql for this..
> I guess you simplified the actual query for the post :)
> But anyways.. try this.
> getName 'O''''Neil' -- 4 single quotes|||Or this should work:
SET QUOTED_IDENTIFIER OFF
EXEC getName "O'Anders"
"Vadivel" wrote:
> this would work
> getname 'o''''niel'
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "Omnibuzz" wrote:
>

Help with xp_fileexist

Hello,
I am wanting to test to see if we have photos that exist on our photo
server. We store the photo names in a database and want to run a cursor to
check whether the physical photo is there. When I run the following:
select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
exec master..xp_fileexist @.File, @.i out
if @.i = 1
print ' exists'
else
print @.File + ' does not exists'
I get the does not exist msg. If I change the @.File to a file locally on the
dbserver box I get the exists msg. I seem to have a permission issue. The
instance on the dbserver is running on a local admin account that is not
part of the domain. Is there a way to run the script and impersonate someone
within the domain? Any ideas would be greatly appreciated.
Jake
Depedning on how this is run, either your NT login, or the NT login SQL
Server uses, or the login SQL Agent uses ( if a scheduled TSQL task) must
have the appropriate permissions...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
the
> dbserver box I get the exists msg. I seem to have a permission issue. The
> instance on the dbserver is running on a local admin account that is not
> part of the domain. Is there a way to run the script and impersonate
someone
> within the domain? Any ideas would be greatly appreciated.
> Jake
>
|||Wayne,
I login query analyzer using windows authentication. When I run the code
I still get the "does not exist" msg. However I can goto windows explorer
and open the file. So I have appropriate permissions. Any ideas on why else
it would tell me the file doesn't exist?
Jake
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OTxbuWF7EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Depedning on how this is run, either your NT login, or the NT login SQL
> Server uses, or the login SQL Agent uses ( if a scheduled TSQL task) must
> have the appropriate permissions...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jake" <rondican@.hotmail.com> wrote in message
> news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> the
> someone
>
|||The problem is that SQL is running under a local account. This doesn't have
access to the remote fileshare. You would need to run under a domain user
account.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
> the dbserver box I get the exists msg. I seem to have a permission issue.
> The instance on the dbserver is running on a local admin account that is
> not part of the domain. Is there a way to run the script and impersonate
> someone within the domain? Any ideas would be greatly appreciated.
> Jake
>
|||"Jake" <rondican@.hotmail.com> wrote in message
news:u8kiNjF7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Wayne,
> I login query analyzer using windows authentication. When I run the
code
> I still get the "does not exist" msg. However I can goto windows explorer
> and open the file. So I have appropriate permissions. Any ideas on why
else
> it would tell me the file doesn't exist?
I'm pretty sure the SERVER needs to have permissions. What account is SQL
Server running under?
[vbcol=seagreen]
> Jake
>
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:OTxbuWF7EHA.3616@.TK2MSFTNGP11.phx.gbl...
must[vbcol=seagreen]
photo[vbcol=seagreen]
on[vbcol=seagreen]
The[vbcol=seagreen]
not
>
|||Greg,
SqlServer is running on an admin account of the local box only. The box
has been joined to the domain but the user that sql runs under doesn't exist
in the domain. I would prefer not to have sql run under a domain account if
there is a way to impersonate a user of the domain during the script but if
SQL needs to run under a domain user then I guess that's what needs to be
done. Do you have any further suggestions?
Jake
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:QN%zd.77824$Uf.41443@.twister.nyroc.rr.com...
> "Jake" <rondican@.hotmail.com> wrote in message
> news:u8kiNjF7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> code
> else
> I'm pretty sure the SERVER needs to have permissions. What account is SQL
> Server running under?
>
> must
> photo
> on
> The
> not
>
|||Yup, Jasper is right... If your SQL Server is running under localsystem, it
does, and can not have access to any network resources..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
the
> dbserver box I get the exists msg. I seem to have a permission issue. The
> instance on the dbserver is running on a local admin account that is not
> part of the domain. Is there a way to run the script and impersonate
someone
> within the domain? Any ideas would be greatly appreciated.
> Jake
>

Help with xp_fileexist

Hello,
I am wanting to test to see if we have photos that exist on our photo
server. We store the photo names in a database and want to run a cursor to
check whether the physical photo is there. When I run the following:
select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
exec master..xp_fileexist @.File, @.i out
if @.i = 1
print ' exists'
else
print @.File + ' does not exists'
I get the does not exist msg. If I change the @.File to a file locally on the
dbserver box I get the exists msg. I seem to have a permission issue. The
instance on the dbserver is running on a local admin account that is not
part of the domain. Is there a way to run the script and impersonate someone
within the domain? Any ideas would be greatly appreciated.
JakeDepedning on how this is run, either your NT login, or the NT login SQL
Server uses, or the login SQL Agent uses ( if a scheduled TSQL task) must
have the appropriate permissions...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
the
> dbserver box I get the exists msg. I seem to have a permission issue. The
> instance on the dbserver is running on a local admin account that is not
> part of the domain. Is there a way to run the script and impersonate
someone
> within the domain? Any ideas would be greatly appreciated.
> Jake
>|||Wayne,
I login query analyzer using windows authentication. When I run the code
I still get the "does not exist" msg. However I can goto windows explorer
and open the file. So I have appropriate permissions. Any ideas on why else
it would tell me the file doesn't exist?
Jake
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OTxbuWF7EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Depedning on how this is run, either your NT login, or the NT login SQL
> Server uses, or the login SQL Agent uses ( if a scheduled TSQL task) must
> have the appropriate permissions...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jake" <rondican@.hotmail.com> wrote in message
> news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> I am wanting to test to see if we have photos that exist on our photo
>> server. We store the photo names in a database and want to run a cursor
>> to
>> check whether the physical photo is there. When I run the following:
>> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
>> exec master..xp_fileexist @.File, @.i out
>> if @.i = 1
>> print ' exists'
>> else
>> print @.File + ' does not exists'
>>
>> I get the does not exist msg. If I change the @.File to a file locally on
> the
>> dbserver box I get the exists msg. I seem to have a permission issue. The
>> instance on the dbserver is running on a local admin account that is not
>> part of the domain. Is there a way to run the script and impersonate
> someone
>> within the domain? Any ideas would be greatly appreciated.
>> Jake
>>
>|||The problem is that SQL is running under a local account. This doesn't have
access to the remote fileshare. You would need to run under a domain user
account.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
> the dbserver box I get the exists msg. I seem to have a permission issue.
> The instance on the dbserver is running on a local admin account that is
> not part of the domain. Is there a way to run the script and impersonate
> someone within the domain? Any ideas would be greatly appreciated.
> Jake
>|||"Jake" <rondican@.hotmail.com> wrote in message
news:u8kiNjF7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Wayne,
> I login query analyzer using windows authentication. When I run the
code
> I still get the "does not exist" msg. However I can goto windows explorer
> and open the file. So I have appropriate permissions. Any ideas on why
else
> it would tell me the file doesn't exist?
I'm pretty sure the SERVER needs to have permissions. What account is SQL
Server running under?
> Jake
>
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:OTxbuWF7EHA.3616@.TK2MSFTNGP11.phx.gbl...
> > Depedning on how this is run, either your NT login, or the NT login SQL
> > Server uses, or the login SQL Agent uses ( if a scheduled TSQL task)
must
> > have the appropriate permissions...
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Jake" <rondican@.hotmail.com> wrote in message
> > news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> >> Hello,
> >>
> >> I am wanting to test to see if we have photos that exist on our
photo
> >> server. We store the photo names in a database and want to run a cursor
> >> to
> >> check whether the physical photo is there. When I run the following:
> >>
> >> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> >> exec master..xp_fileexist @.File, @.i out
> >> if @.i = 1
> >> print ' exists'
> >> else
> >> print @.File + ' does not exists'
> >>
> >>
> >> I get the does not exist msg. If I change the @.File to a file locally
on
> > the
> >> dbserver box I get the exists msg. I seem to have a permission issue.
The
> >> instance on the dbserver is running on a local admin account that is
not
> >> part of the domain. Is there a way to run the script and impersonate
> > someone
> >> within the domain? Any ideas would be greatly appreciated.
> >>
> >> Jake
> >>
> >>
> >
> >
>|||Greg,
SqlServer is running on an admin account of the local box only. The box
has been joined to the domain but the user that sql runs under doesn't exist
in the domain. I would prefer not to have sql run under a domain account if
there is a way to impersonate a user of the domain during the script but if
SQL needs to run under a domain user then I guess that's what needs to be
done. Do you have any further suggestions?
Jake
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:QN%zd.77824$Uf.41443@.twister.nyroc.rr.com...
> "Jake" <rondican@.hotmail.com> wrote in message
> news:u8kiNjF7EHA.1408@.TK2MSFTNGP10.phx.gbl...
>> Wayne,
>> I login query analyzer using windows authentication. When I run the
> code
>> I still get the "does not exist" msg. However I can goto windows explorer
>> and open the file. So I have appropriate permissions. Any ideas on why
> else
>> it would tell me the file doesn't exist?
> I'm pretty sure the SERVER needs to have permissions. What account is SQL
> Server running under?
>
>> Jake
>>
>>
>> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> news:OTxbuWF7EHA.3616@.TK2MSFTNGP11.phx.gbl...
>> > Depedning on how this is run, either your NT login, or the NT login SQL
>> > Server uses, or the login SQL Agent uses ( if a scheduled TSQL task)
> must
>> > have the appropriate permissions...
>> >
>> > --
>> > Wayne Snyder, MCDBA, SQL Server MVP
>> > Mariner, Charlotte, NC
>> > www.mariner-usa.com
>> > (Please respond only to the newsgroups.)
>> >
>> > I support the Professional Association of SQL Server (PASS) and it's
>> > community of SQL Server professionals.
>> > www.sqlpass.org
>> >
>> > "Jake" <rondican@.hotmail.com> wrote in message
>> > news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
>> >> Hello,
>> >>
>> >> I am wanting to test to see if we have photos that exist on our
> photo
>> >> server. We store the photo names in a database and want to run a
>> >> cursor
>> >> to
>> >> check whether the physical photo is there. When I run the following:
>> >>
>> >> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
>> >> exec master..xp_fileexist @.File, @.i out
>> >> if @.i = 1
>> >> print ' exists'
>> >> else
>> >> print @.File + ' does not exists'
>> >>
>> >>
>> >> I get the does not exist msg. If I change the @.File to a file locally
> on
>> > the
>> >> dbserver box I get the exists msg. I seem to have a permission issue.
> The
>> >> instance on the dbserver is running on a local admin account that is
> not
>> >> part of the domain. Is there a way to run the script and impersonate
>> > someone
>> >> within the domain? Any ideas would be greatly appreciated.
>> >>
>> >> Jake
>> >>
>> >>
>> >
>> >
>>
>|||Yup, Jasper is right... If your SQL Server is running under localsystem, it
does, and can not have access to any network resources..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
the
> dbserver box I get the exists msg. I seem to have a permission issue. The
> instance on the dbserver is running on a local admin account that is not
> part of the domain. Is there a way to run the script and impersonate
someone
> within the domain? Any ideas would be greatly appreciated.
> Jake
>

Help with xp_fileexist

Hello,
I am wanting to test to see if we have photos that exist on our photo
server. We store the photo names in a database and want to run a cursor to
check whether the physical photo is there. When I run the following:
select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
exec master..xp_fileexist @.File, @.i out
if @.i = 1
print ' exists'
else
print @.File + ' does not exists'
I get the does not exist msg. If I change the @.File to a file locally on the
dbserver box I get the exists msg. I seem to have a permission issue. The
instance on the dbserver is running on a local admin account that is not
part of the domain. Is there a way to run the script and impersonate someone
within the domain? Any ideas would be greatly appreciated.
JakeDepedning on how this is run, either your NT login, or the NT login SQL
Server uses, or the login SQL Agent uses ( if a scheduled TSQL task) must
have the appropriate permissions...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
the
> dbserver box I get the exists msg. I seem to have a permission issue. The
> instance on the dbserver is running on a local admin account that is not
> part of the domain. Is there a way to run the script and impersonate
someone
> within the domain? Any ideas would be greatly appreciated.
> Jake
>|||Wayne,
I login query analyzer using windows authentication. When I run the code
I still get the "does not exist" msg. However I can goto windows explorer
and open the file. So I have appropriate permissions. Any ideas on why else
it would tell me the file doesn't exist?
Jake
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OTxbuWF7EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Depedning on how this is run, either your NT login, or the NT login SQL
> Server uses, or the login SQL Agent uses ( if a scheduled TSQL task) must
> have the appropriate permissions...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jake" <rondican@.hotmail.com> wrote in message
> news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> the
> someone
>|||The problem is that SQL is running under a local account. This doesn't have
access to the remote fileshare. You would need to run under a domain user
account.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
> the dbserver box I get the exists msg. I seem to have a permission issue.
> The instance on the dbserver is running on a local admin account that is
> not part of the domain. Is there a way to run the script and impersonate
> someone within the domain? Any ideas would be greatly appreciated.
> Jake
>|||"Jake" <rondican@.hotmail.com> wrote in message
news:u8kiNjF7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Wayne,
> I login query analyzer using windows authentication. When I run the
code
> I still get the "does not exist" msg. However I can goto windows explorer
> and open the file. So I have appropriate permissions. Any ideas on why
else
> it would tell me the file doesn't exist?
I'm pretty sure the SERVER needs to have permissions. What account is SQL
Server running under?

> Jake
>
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:OTxbuWF7EHA.3616@.TK2MSFTNGP11.phx.gbl...
must[vbcol=seagreen]
photo[vbcol=seagreen]
on[vbcol=seagreen]
The[vbcol=seagreen]
not[vbcol=seagreen]
>|||Greg,
SqlServer is running on an admin account of the local box only. The box
has been joined to the domain but the user that sql runs under doesn't exist
in the domain. I would prefer not to have sql run under a domain account if
there is a way to impersonate a user of the domain during the script but if
SQL needs to run under a domain user then I guess that's what needs to be
done. Do you have any further suggestions?
Jake
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:QN%zd.77824$Uf.41443@.twister.nyroc.rr.com...
> "Jake" <rondican@.hotmail.com> wrote in message
> news:u8kiNjF7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> code
> else
> I'm pretty sure the SERVER needs to have permissions. What account is SQL
> Server running under?
>
> must
> photo
> on
> The
> not
>|||Yup, Jasper is right... If your SQL Server is running under localsystem, it
does, and can not have access to any network resources..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jake" <rondican@.hotmail.com> wrote in message
news:%23tqjBTF7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am wanting to test to see if we have photos that exist on our photo
> server. We store the photo names in a database and want to run a cursor to
> check whether the physical photo is there. When I run the following:
> select @.File = '\\photoserver-l-01\iphotos\hoco\ali\dcp_2965.jpg'
> exec master..xp_fileexist @.File, @.i out
> if @.i = 1
> print ' exists'
> else
> print @.File + ' does not exists'
>
> I get the does not exist msg. If I change the @.File to a file locally on
the
> dbserver box I get the exists msg. I seem to have a permission issue. The
> instance on the dbserver is running on a local admin account that is not
> part of the domain. Is there a way to run the script and impersonate
someone
> within the domain? Any ideas would be greatly appreciated.
> Jake
>

Help With Where Clause

I am creating a food database application where I allow the user to search
for food names. For this example, lets supposed that my database has the
following food names on the table.
Salt with water
Chips and salsa
Fried Chicken
Guacamole
If the user types 'sa' on the search textbox I would like to retrieve the
following records:
Salt with water
Chips and salsa
This is because Salt starts with 'sa' and salsa also starts with 'sa'. If
the user typed 'sa ch' (this is the string 'sa' then a space and then the
string 'ch'). I would like to retrieve the records
Salt with water
Chips and salsa
Fried Chicken
I get the same records as before because I included the string 'sa' and I
get the record 'Fried Chicken' because Chicken starts with 'ch'. The problem
is that the user can have as many words as he or she pleases. It even gets
more complicated because I would like to use a store procedure on Microsoft
SQL server.
Could someone help me creating this store procedure, PLEASE! Thank you.Rene
See if this helps
CREATE TABLE #Test
(
col VARCHAR(50)
)
INSERT INTO #Test VALUES ('Salt with water')
INSERT INTO #Test VALUES ('Chips and salsa')
INSERT INTO #Test VALUES ('Fried Chicken')
INSERT INTO #Test VALUES ('Guacamole')
SELECT * FROM #Test WHERE col LIKE '%sa%'
"Rene" <nospam@.nospam.com> wrote in message
news:uI8eG$kaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I am creating a food database application where I allow the user to search
> for food names. For this example, lets supposed that my database has the
> following food names on the table.
> Salt with water
> Chips and salsa
> Fried Chicken
> Guacamole
> If the user types 'sa' on the search textbox I would like to retrieve the
> following records:
> Salt with water
> Chips and salsa
> This is because Salt starts with 'sa' and salsa also starts with 'sa'. If
> the user typed 'sa ch' (this is the string 'sa' then a space and then the
> string 'ch'). I would like to retrieve the records
> Salt with water
> Chips and salsa
> Fried Chicken
> I get the same records as before because I included the string 'sa' and I
> get the record 'Fried Chicken' because Chicken starts with 'ch'. The
problem
> is that the user can have as many words as he or she pleases. It even gets
> more complicated because I would like to use a store procedure on
Microsoft
> SQL server.
> Could someone help me creating this store procedure, PLEASE! Thank you.
>|||> SELECT * FROM #Test WHERE col LIKE '%sa%'
This won't work. Aquery like this will also return food name such as 'Carne
Aa' because 'Aa' has 'sa' inside its text, I am only interested on
food that has any of its words *start* with the string 'sa'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23OXdQmlaFHA.3364@.TK2MSFTNGP09.phx.gbl...
> Rene
> See if this helps
> CREATE TABLE #Test
> (
> col VARCHAR(50)
> )
> INSERT INTO #Test VALUES ('Salt with water')
> INSERT INTO #Test VALUES ('Chips and salsa')
> INSERT INTO #Test VALUES ('Fried Chicken')
> INSERT INTO #Test VALUES ('Guacamole')
> SELECT * FROM #Test WHERE col LIKE '%sa%'
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:uI8eG$kaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> problem
> Microsoft
>|||WHERE col LIKE '% sa%' or col like LIKE '%,sa%' or col like LIKE '%.sa%' or
left(col, 2) = 'sa'
You know where I'm going with this, right.
I have seen a script (stored procedure or function) that extracts words from
a string and converts it to a table, one word per column
This may help you with this problem. Maybe someone here could point you to
the script. I can't seem to be able to find it right now.
"Rene" <nospam@.nospam.com> wrote in message
news:ONj7qTqaFHA.2736@.TK2MSFTNGP12.phx.gbl...
> This won't work. Aquery like this will also return food name such as
> 'Carne Aa' because 'Aa' has 'sa' inside its text, I am only
> interested on food that has any of its words *start* with the string 'sa'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23OXdQmlaFHA.3364@.TK2MSFTNGP09.phx.gbl...
>