I am using Access2002 and SQL server 2k. I need to store some documents (PDF) in the database as embedded object that can be activated and edited by double-click. How can I implement this in VBA/T-SQL in the same way that "insert embedded object" does?
UPDATETEXT or WRITETEXT will just store the document as a "long binary data". I've heard it's better only store the path in database but I need to open the database for the remote access via Internet. Can I achieve this by just storing the file path?
Any help appreciated!You heard correctly about using the file system to store the documents, and only storing the path in the database. The file system will give you much better performance.
You will want to store the documents in a folder on your web-accessible server (web server?), in a folder with Read security permissions granted for your users (either end-user account for secure server, or web server account for public server). In the DB, the path should point to the URL of the file, such as "http://myserver.mydomain.com/files/myfile.pdf" and not the physical location ("c:/files/myfile.pdf").|||Or you could store the physical path to the PDF in the server, then when you need to send the file to them, you stream the data to them (which is different then pointing them to a URL).|||Thanks bpdWork and Seppuku! I now have a clue how to do it.
Seppuku, do you mean use somthing like ADO stream to send files and then SaveToFile at remote computers?|||Originally posted by whfrank
Thanks bpdWork and Seppuku! I now have a clue how to do it.
Seppuku, do you mean use somthing like ADO stream to send files and then SaveToFile at remote computers?
Exactly... If you stream the file as an attachment it will force the user to download, otherwise some browsers will attempt to associate it with an application (such as Word, Excel, PDF, etc) and open the document in the browser. You can do it either way. I've done both.|||Exactly. If the user opening the document in the browser is acceptable, then a file reference will be a lot less work than the streaming. If not, then streaming os the way to go.|||Originally posted by bpdWork
Exactly. If the user opening the document in the browser is acceptable, then a file reference will be a lot less work than the streaming. If not, then streaming os the way to go.
Unless you want to secure the location of the document. There may be a time when you want to download a doc into the browser window, but it's location is secure (due to username/password or some other criteria). To secure it, you may still want to stream the document to the browser so you can validate the user against any security requirements you may have. When streaming a file, you can force the download by setting the Content Disposition header as an attachement. If you ignore that, it will download into the browser (assuming there's an association), but still gives you the ability to validate the user.
An example would be a document management system. If you just provide URLs to the documents, anyone can get them without authenticating. If you stream the document to them, you can ensure they are logged in and have the proper rights to the document, and then can send that document to them to load in their browser (or download, by forcing the Content Disposition to attachment).|||Actually, you can secure the documents in the file system using NTsecurity as well. Unauthorized uses will not be able to download documents they do not have access to.|||Yes but that requires you to have administrative rights on the server to setup and manage. Besides, the users you want to access the files don't always have accounts on your NT domain, requiring you to set up an account for everyone. If this is on an Intranet, NT auth may work fine, but on the Internet, chances are they won't be on your domain.
If you really want to go that route, a LDAP solution works well. I'm doing that right now on a site using Siteminder and Secureway. It prompts for user access similar to NT auth, but can be updated through a web interface.|||Originally posted by Seppuku
Exactly... If you stream the file as an attachment it will force the user to download, otherwise some browsers will attempt to associate it with an application (such as Word, Excel, PDF, etc) and open the document in the browser. You can do it either way. I've done both.
If I use Access to develop the front-end application, the physical path stored in SQL server is like "C:/myfile.pdf", but this is the location on the server. How to write a procedure in Access on remote computers stream it and send it? I'm a bit confused, please help me out. Thanks!|||Originally posted by whfrank
If I use Access to develop the front-end application, the physical path stored in SQL server is like "C:/myfile.pdf", but this is the location on the server. How to write a procedure in Access on remote computers stream it and send it? I'm a bit confused, please help me out. Thanks!
Are you using Access as your database on the Internet? Or just to test with? Access should never be used as an Internet database backend.
As for how to stream it, what server-side scripting language are you using on your website?|||you guys are all saying that it's slow... Actually, unless you use "text in row" option on the table, - only pointers to text/image pages are stored in the table. Also, if you implement methodology that requires to store the actual documents in the file system, you just expanded your responsibility from being a dba to also being in charge of the directory structure, os access right, etc., etc... And that's not to mention disaster recovery and business continuity... Just a thought :)|||Actually, even without text-in-row turned on, the B-Tree structure used by SQL is much slower than the file system. SQL stores a pointer to the B-tree structure in the table. Depending on the file size, the b-tree breaks the file up into little pieces, with a string of pointers connecting them. Upon retrieval, the pieces are reassembled into a complete file. MS actually recommends using the file system for this type of storage if it is an option.
I belive from his last post the whfrank is using Access as the front end, and connecting to a SQL backend over the internet. I haven't played with Access front-ends in a while (thank god!!), so I have no idea how it would deal with BLOBs, or streaming for that matter, but I am sure by now they have been built in. Maybe MS has something about how to use BLOBs through Access, which I am sure will be the limiting factor.|||From past experience, the best performance I've seen from storing BLOBs in the DB is when they are small (such as images).
I agree that you start getting into a gray area when you start storing on the disk w/ pointers in the DB, but that's why you have system admins and security for in the first place. If you're doing this right, then you have disaster recovery on the disks and in SQL Server. You should have a team of system admins and security personnel. All of that is their responsibility to maintain.
Just as bpd said, everything has to be pulled from the disk whether it's stored in the DB (which is just a pointer to an area on the disk), or a drive path.
With the BLOB stored in the DB, you have to make a request for it, the DB executes the request, hits the disk for the files containing the data, then send all the data (through the DB) to the requesting app to be streamed to the user. This takes up resources that the DB wasn't meant to do (file transfer).
When you store the file path in the DB, you make a request for the for it, the DB executes the request and returns a string, then the file system can be directly accessed for the contents of the file and streamed to the user. Less IO, more efficient, and you remove the DB from the mix, leaving it to handle other requests quickly.
There may be times though when this is not functionally possible. An example would be if your SQL Servers were separate from your Web Servers. In this case, all of your web servers might not have all of the documents locally (unless you're using a content manager). You may need to send the document from the SQL server to the web server in that case. But that's even more time consuming because it involves the network in a DMZ.
What just came to mind though is that if you can pipe the documents from another server, you're set. I've never tried opening a file through named pipes in ASP though. Sounds like I have a project :)
I could be wrong in all that.. but it just seems logical that you separate what the DB is good at doing from what the FS is good at doing..|||Well, that's what this forum is for, - to share, and disagree at times (which is what I am doing now :) )
I have an app that has a custom report writer built into it (it's not Crystal, nowhere near, but better :) ). Certain users are allowed to create new reports and modify existing ones. Others are allowed to only execute existing reports. The app has its own security that is based on database roles. The fact that I am storing report definitions into text fields allows me not to worry about security. I know-I know, yoall gonna tell me that even if I store definitions into FS the security policy can be made to support the app security. And I would agree with you, maybe :)|||Originally posted by bpdWork
I belive from his last post the whfrank is using Access as the front end, and connecting to a SQL backend over the internet. I haven't played with Access front-ends in a while (thank god!!), so I have no idea how it would deal with BLOBs, or streaming for that matter, but I am sure by now they have been built in. Maybe MS has something about how to use BLOBs through Access, which I am sure will be the limiting factor.
Yes, actually I'm not building a website but a Access-front-end, SQL-back-end application. Our company wants to share some info (it's changing all the time on both sides) with several factories in another country. It's quite simple to use Access Date Project to connect to the SQL Server, you get all tables and queries (same as they are in the SQL server, all LOB types there, text, ntext, image). But all forms, reports, modules (can vary from every user) are bulit in Access and on the remote computers. Then the problem comes out: I get a file path (c:/file.pdf) from DB that is actually the physical location on the server, but for the remote users it's a file path on their own computers... Any idea how to deal with it?|||A few questions need to be answered first.
Are the different locations on the same NT domain, intranet, or share a VPN connection? Or are they communicating over the open internet? This will determine what types of methods are available to you for referencing the file locations.
Perhaps the simplest (not best, but simplest) way of accessing the files, assuming you are going over the open internet, would be FTP or HTTP-based file access. You could store an addess in the db such as http://thefileserver.mycompany.com/file.pdf and shell to it (check out the VBA Shell command, though it may have been replaced by now). That should spawn the user's browser, and, if they have Adobe PDF installed, allow them to view the PDF in the browser, or prompt them to save it if they don't. You would need to set up a simple web server (IIS) to serve up the files (simple).
If the locations are on the same domain, you can protect the file folders with NT Authentication, granting read permissions to a new user group, then adding the people who should have access to that group. If on seperate domains, you can use a local security model, and let everyone use the same account (again, not the trickiest or most secure method, but you could have it running in a day).
If this sounds like what you are after, I'm sure we can expond on it. If not, I'm sure we can help you with another way.|||They are communicating over internet. We have a domain within the company and remote computers can be just thought as individual PCs.
If pointing users to an URL, it's ok for the remote users but seems not so good for the internal users in the domain. One method is to store two paths in the database, one URL and one physical location. Do you think it's feasible?|||Or if you want to get really ugly, you could do web a web server, create a simple ASP page, and stream it from the URL you store in the DB (I know.. I know.. I really don't have a vested interest in streaming, I promise). All I'm saying is that by doing it that way, you don't expose the actual documents.
Your DB would store a list of URLs like http://my.domain.com/getfile.asp?file=myfile.pdf. Then when you shell to the URL as bpd suggested, they will be passed to the site, hit the ASP which could then validate any security requirements you may have, log who viewed it, maybe increment a counter on how many times it's been accessed, etc, then stream the file to them...
If all you want to do is get them the file, no bells and whistles, then this may be to much... go with the simple web server method or the direct file share... but if you have some specialty requirements around this project that goes beyond the scope of getting them the file and providing security, you may need to look to other methods of document retrieval.
Just giving options :)|||Originally posted by whfrank
They are communicating over internet. We have a domain within the company and remote computers can be just thought as individual PCs.
If pointing users to an URL, it's ok for the remote users but seems not so good for the internal users in the domain. One method is to store two paths in the database, one URL and one physical location. Do you think it's feasible?
Why would it not be good to use a URL for both of your users? Do your internal users not have Internet access? If you're communicating over the Internet without a VPN, you'll need to do a web server solution (possibly with SSL if the documents are sensitive). Whether it's pointing to the absolute URL of the document, or to an ASP that streams the document, you'll still need a web server. You do not want to put a computer out on the Internet that has an open share to the file system.
Avoid storing a local path and a URL in the DB if you can. It would be duplication of content.sql
Showing posts with label store. Show all posts
Showing posts with label store. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
HELP! Is this a potential locking nightmare??
The following table is used to store the points earned each month by
salesmen. Items
purchased by customers on-line all have points assigned to them that are
used for this
purpose. We used to add to this table as each customers application was
completed.
For some reason we have been asked to make it realtime. The work is mostly
done but
I have an issue. As each item is being added/changed/deleted on customers
orders,
these points are accumulated into the corresponding row in this table. So,
if
100 customers purchasing from the same salesman are on-line at the same time
is
this going to be a potential locking issue? These updates are done within a
transaction. Since the sales structure does have an "upline" type of
structure,
these points have to trickle up to several peoples entries in an
all-or-nothing fashion.
This is supposed to roll out soon(tomorrow). I'm worried that once we
really get traffic hitting this that
our server is going to have issues.
CREATE TABLE [dbo].[tblMonthlyPoints](
[lngSalesPersonID] [int] NOT NULL,
[intMonth] [tinyint] NOT NULL,
[intYear] [int] NOT NULL,
[decPoints] [decimal](19, 4) NOT NULL CONSTRAINT
[DF_tblMonthlyPoints_decPoints] DEFAULT (0),
CONSTRAINT [PK_tblMonthlyPoints] PRIMARY KEY CLUSTERED
(
[lngSalesPersonID] ASC,
[intMonth] ASC,
[intYear] ASC
) ON [PRIMARY]
) ON [PRIMARY]On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>We used to add to this table as each customers application was
>completed.
>For some reason we have been asked to make it realtime.
Hi Tim,
Have you considered storing the raw data (i.e. the individual sales)
instead of the aggregated monthly totals? I usually prefer to work that
way, since it makes recovery after errors so much easier.
The table you posted could then be replaced with a view.
>if
>100 customers purchasing from the same salesman are on-line at the same tim
e
>is
>this going to be a potential locking issue?
Yes. Based on your table structure, registering the purchase would
require an exclusive lock. It will be a row lock, since the primary key
can be used to locate the row to be updated - but if several connections
want to update the same row (i.e. same salesman, same month, same year),
they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
run into deadlocks).
> These updates are done within a
>transaction. Since the sales structure does have an "upline" type of
>structure,
>these points have to trickle up to several peoples entries in an
>all-or-nothing fashion.
I don't really understand this. Do you mean that you update several rows
in the table, or do yoou mean that one update in the table fires a
trigger that does the trickling-up?
In the former case, try to update all rows in a single update statement:
UPDATE MonthlyPoints
SET Point s = Points + 5
WHERE SalesPersonID IN (123, 456, 7890)
AND Year = 2006
AND Month = 5
>CREATE TABLE [dbo].[tblMonthlyPoints](
> [lngSalesPersonID] [int] NOT NULL,
> [intMonth] [tinyint] NOT NULL,
> [intYear] [int] NOT NULL,
(snip)
Yoou should also consider replacing the individual Month and Year
columns with a single datetime (or smalldatetime) column that holds the
start of the month. Storing year and month as individual columns often
causes more grief than using a single datetime column.
Hugo Kornelis, SQL Server MVP|||Thanks for replying!! I'd given up on this thread...
I understand about updating them all in one statement the problem is the
list of ID's to update has to be created recursively. I know I could use
CTE's but we're not on 2005 just yet...(I am holding my breath here).....
We are storing the raw data already....I have already suggested the view
alternative which I preferred as well. It was shot down by others because I
guess they have some bonus scheme that gets applied as their points increase
( I don't know enough about their scheme yet to disagree ) which is too
complicated to do in that manner. I was just more curious about the
locking issue...I warned them it could be an issue so my hands are clean.
At least the raw data is present on each sales transaction so it can be
reconstructed after the fact "when" I have to fix it.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:lmes6291sa5ju81btj1g28oq367c1j052j@.
4ax.com...
> On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
> yahoo D-O-T com> wrote:
>
> Hi Tim,
> Have you considered storing the raw data (i.e. the individual sales)
> instead of the aggregated monthly totals? I usually prefer to work that
> way, since it makes recovery after errors so much easier.
> The table you posted could then be replaced with a view.
>
> Yes. Based on your table structure, registering the purchase would
> require an exclusive lock. It will be a row lock, since the primary key
> can be used to locate the row to be updated - but if several connections
> want to update the same row (i.e. same salesman, same month, same year),
> they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
> run into deadlocks).
>
> I don't really understand this. Do you mean that you update several rows
> in the table, or do yoou mean that one update in the table fires a
> trigger that does the trickling-up?
> In the former case, try to update all rows in a single update statement:
> UPDATE MonthlyPoints
> SET Point s = Points + 5
> WHERE SalesPersonID IN (123, 456, 7890)
> AND Year = 2006
> AND Month = 5
>
> (snip)
> Yoou should also consider replacing the individual Month and Year
> columns with a single datetime (or smalldatetime) column that holds the
> start of the month. Storing year and month as individual columns often
> causes more grief than using a single datetime column.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 19 May 2006 15:58:40 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>Thanks for replying!! I'd given up on this thread...
>I understand about updating them all in one statement the problem is the
>list of ID's to update has to be created recursively. I know I could use
>CTE's but we're not on 2005 just yet...(I am holding my breath here).....
Hi Tim,
There are many alternatives on SQL Server 2000 as well. The best
alternatives might include a redesign of your table. The most commonly
used method for storing hierarchies is not the must suitable for use in
a relational model. Google for "nested sets model" if you want to find
out the most common alternative.
(snip)
> I
>guess they have some bonus scheme that gets applied as their points increas
e
>( I don't know enough about their scheme yet to disagree ) which is too
>complicated to do in that manner.
I'd like to see more info on that. To me, the words "too complicated"
are like a red rag. <g>
Hugo Kornelis, SQL Server MVP
salesmen. Items
purchased by customers on-line all have points assigned to them that are
used for this
purpose. We used to add to this table as each customers application was
completed.
For some reason we have been asked to make it realtime. The work is mostly
done but
I have an issue. As each item is being added/changed/deleted on customers
orders,
these points are accumulated into the corresponding row in this table. So,
if
100 customers purchasing from the same salesman are on-line at the same time
is
this going to be a potential locking issue? These updates are done within a
transaction. Since the sales structure does have an "upline" type of
structure,
these points have to trickle up to several peoples entries in an
all-or-nothing fashion.
This is supposed to roll out soon(tomorrow). I'm worried that once we
really get traffic hitting this that
our server is going to have issues.
CREATE TABLE [dbo].[tblMonthlyPoints](
[lngSalesPersonID] [int] NOT NULL,
[intMonth] [tinyint] NOT NULL,
[intYear] [int] NOT NULL,
[decPoints] [decimal](19, 4) NOT NULL CONSTRAINT
[DF_tblMonthlyPoints_decPoints] DEFAULT (0),
CONSTRAINT [PK_tblMonthlyPoints] PRIMARY KEY CLUSTERED
(
[lngSalesPersonID] ASC,
[intMonth] ASC,
[intYear] ASC
) ON [PRIMARY]
) ON [PRIMARY]On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>We used to add to this table as each customers application was
>completed.
>For some reason we have been asked to make it realtime.
Hi Tim,
Have you considered storing the raw data (i.e. the individual sales)
instead of the aggregated monthly totals? I usually prefer to work that
way, since it makes recovery after errors so much easier.
The table you posted could then be replaced with a view.
>if
>100 customers purchasing from the same salesman are on-line at the same tim
e
>is
>this going to be a potential locking issue?
Yes. Based on your table structure, registering the purchase would
require an exclusive lock. It will be a row lock, since the primary key
can be used to locate the row to be updated - but if several connections
want to update the same row (i.e. same salesman, same month, same year),
they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
run into deadlocks).
> These updates are done within a
>transaction. Since the sales structure does have an "upline" type of
>structure,
>these points have to trickle up to several peoples entries in an
>all-or-nothing fashion.
I don't really understand this. Do you mean that you update several rows
in the table, or do yoou mean that one update in the table fires a
trigger that does the trickling-up?
In the former case, try to update all rows in a single update statement:
UPDATE MonthlyPoints
SET Point s = Points + 5
WHERE SalesPersonID IN (123, 456, 7890)
AND Year = 2006
AND Month = 5
>CREATE TABLE [dbo].[tblMonthlyPoints](
> [lngSalesPersonID] [int] NOT NULL,
> [intMonth] [tinyint] NOT NULL,
> [intYear] [int] NOT NULL,
(snip)
Yoou should also consider replacing the individual Month and Year
columns with a single datetime (or smalldatetime) column that holds the
start of the month. Storing year and month as individual columns often
causes more grief than using a single datetime column.
Hugo Kornelis, SQL Server MVP|||Thanks for replying!! I'd given up on this thread...
I understand about updating them all in one statement the problem is the
list of ID's to update has to be created recursively. I know I could use
CTE's but we're not on 2005 just yet...(I am holding my breath here).....
We are storing the raw data already....I have already suggested the view
alternative which I preferred as well. It was shot down by others because I
guess they have some bonus scheme that gets applied as their points increase
( I don't know enough about their scheme yet to disagree ) which is too
complicated to do in that manner. I was just more curious about the
locking issue...I warned them it could be an issue so my hands are clean.
At least the raw data is present on each sales transaction so it can be
reconstructed after the fact "when" I have to fix it.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:lmes6291sa5ju81btj1g28oq367c1j052j@.
4ax.com...
> On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
> yahoo D-O-T com> wrote:
>
> Hi Tim,
> Have you considered storing the raw data (i.e. the individual sales)
> instead of the aggregated monthly totals? I usually prefer to work that
> way, since it makes recovery after errors so much easier.
> The table you posted could then be replaced with a view.
>
> Yes. Based on your table structure, registering the purchase would
> require an exclusive lock. It will be a row lock, since the primary key
> can be used to locate the row to be updated - but if several connections
> want to update the same row (i.e. same salesman, same month, same year),
> they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
> run into deadlocks).
>
> I don't really understand this. Do you mean that you update several rows
> in the table, or do yoou mean that one update in the table fires a
> trigger that does the trickling-up?
> In the former case, try to update all rows in a single update statement:
> UPDATE MonthlyPoints
> SET Point s = Points + 5
> WHERE SalesPersonID IN (123, 456, 7890)
> AND Year = 2006
> AND Month = 5
>
> (snip)
> Yoou should also consider replacing the individual Month and Year
> columns with a single datetime (or smalldatetime) column that holds the
> start of the month. Storing year and month as individual columns often
> causes more grief than using a single datetime column.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 19 May 2006 15:58:40 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>Thanks for replying!! I'd given up on this thread...
>I understand about updating them all in one statement the problem is the
>list of ID's to update has to be created recursively. I know I could use
>CTE's but we're not on 2005 just yet...(I am holding my breath here).....
Hi Tim,
There are many alternatives on SQL Server 2000 as well. The best
alternatives might include a redesign of your table. The most commonly
used method for storing hierarchies is not the must suitable for use in
a relational model. Google for "nested sets model" if you want to find
out the most common alternative.
(snip)
> I
>guess they have some bonus scheme that gets applied as their points increas
e
>( I don't know enough about their scheme yet to disagree ) which is too
>complicated to do in that manner.
I'd like to see more info on that. To me, the words "too complicated"
are like a red rag. <g>
Hugo Kornelis, SQL Server MVP
HELP! Is this a potential locking nightmare??
The following table is used to store the points earned each month by
salesmen. Items
purchased by customers on-line all have points assigned to them that are
used for this
purpose. We used to add to this table as each customers application was
completed.
For some reason we have been asked to make it realtime. The work is mostly
done but
I have an issue. As each item is being added/changed/deleted on customers
orders,
these points are accumulated into the corresponding row in this table. So,
if
100 customers purchasing from the same salesman are on-line at the same time
is
this going to be a potential locking issue? These updates are done within a
transaction. Since the sales structure does have an "upline" type of
structure,
these points have to trickle up to several peoples entries in an
all-or-nothing fashion.
This is supposed to roll out soon(tomorrow). I'm worried that once we
really get traffic hitting this that
our server is going to have issues.
CREATE TABLE [dbo].[tblMonthlyPoints](
[lngSalesPersonID] [int] NOT NULL,
[intMonth] [tinyint] NOT NULL,
[intYear] [int] NOT NULL,
[decPoints] [decimal](19, 4) NOT NULL CONSTRAINT
[DF_tblMonthlyPoints_decPoints] DEFAULT (0),
CONSTRAINT [PK_tblMonthlyPoints] PRIMARY KEY CLUSTERED
(
[lngSalesPersonID] ASC,
[intMonth] ASC,
[intYear] ASC
) ON [PRIMARY]
) ON [PRIMARY]On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>We used to add to this table as each customers application was
>completed.
>For some reason we have been asked to make it realtime.
Hi Tim,
Have you considered storing the raw data (i.e. the individual sales)
instead of the aggregated monthly totals? I usually prefer to work that
way, since it makes recovery after errors so much easier.
The table you posted could then be replaced with a view.
>if
>100 customers purchasing from the same salesman are on-line at the same time
>is
>this going to be a potential locking issue?
Yes. Based on your table structure, registering the purchase would
require an exclusive lock. It will be a row lock, since the primary key
can be used to locate the row to be updated - but if several connections
want to update the same row (i.e. same salesman, same month, same year),
they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
run into deadlocks).
> These updates are done within a
>transaction. Since the sales structure does have an "upline" type of
>structure,
>these points have to trickle up to several peoples entries in an
>all-or-nothing fashion.
I don't really understand this. Do you mean that you update several rows
in the table, or do yoou mean that one update in the table fires a
trigger that does the trickling-up?
In the former case, try to update all rows in a single update statement:
UPDATE MonthlyPoints
SET Point s = Points + 5
WHERE SalesPersonID IN (123, 456, 7890)
AND Year = 2006
AND Month = 5
>CREATE TABLE [dbo].[tblMonthlyPoints](
> [lngSalesPersonID] [int] NOT NULL,
> [intMonth] [tinyint] NOT NULL,
> [intYear] [int] NOT NULL,
(snip)
Yoou should also consider replacing the individual Month and Year
columns with a single datetime (or smalldatetime) column that holds the
start of the month. Storing year and month as individual columns often
causes more grief than using a single datetime column.
--
Hugo Kornelis, SQL Server MVP|||Thanks for replying!! I'd given up on this thread...
I understand about updating them all in one statement the problem is the
list of ID's to update has to be created recursively. I know I could use
CTE's but we're not on 2005 just yet...(I am holding my breath here).....
We are storing the raw data already....I have already suggested the view
alternative which I preferred as well. It was shot down by others because I
guess they have some bonus scheme that gets applied as their points increase
( I don't know enough about their scheme yet to disagree ) which is too
complicated to do in that manner. I was just more curious about the
locking issue...I warned them it could be an issue so my hands are clean.
At least the raw data is present on each sales transaction so it can be
reconstructed after the fact "when" I have to fix it.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:lmes6291sa5ju81btj1g28oq367c1j052j@.4ax.com...
> On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
> yahoo D-O-T com> wrote:
>>We used to add to this table as each customers application was
>>completed.
>>For some reason we have been asked to make it realtime.
> Hi Tim,
> Have you considered storing the raw data (i.e. the individual sales)
> instead of the aggregated monthly totals? I usually prefer to work that
> way, since it makes recovery after errors so much easier.
> The table you posted could then be replaced with a view.
>>if
>>100 customers purchasing from the same salesman are on-line at the same
>>time
>>is
>>this going to be a potential locking issue?
> Yes. Based on your table structure, registering the purchase would
> require an exclusive lock. It will be a row lock, since the primary key
> can be used to locate the row to be updated - but if several connections
> want to update the same row (i.e. same salesman, same month, same year),
> they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
> run into deadlocks).
>> These updates are done within a
>>transaction. Since the sales structure does have an "upline" type of
>>structure,
>>these points have to trickle up to several peoples entries in an
>>all-or-nothing fashion.
> I don't really understand this. Do you mean that you update several rows
> in the table, or do yoou mean that one update in the table fires a
> trigger that does the trickling-up?
> In the former case, try to update all rows in a single update statement:
> UPDATE MonthlyPoints
> SET Point s = Points + 5
> WHERE SalesPersonID IN (123, 456, 7890)
> AND Year = 2006
> AND Month = 5
>>CREATE TABLE [dbo].[tblMonthlyPoints](
>> [lngSalesPersonID] [int] NOT NULL,
>> [intMonth] [tinyint] NOT NULL,
>> [intYear] [int] NOT NULL,
> (snip)
> Yoou should also consider replacing the individual Month and Year
> columns with a single datetime (or smalldatetime) column that holds the
> start of the month. Storing year and month as individual columns often
> causes more grief than using a single datetime column.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 19 May 2006 15:58:40 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>Thanks for replying!! I'd given up on this thread...
>I understand about updating them all in one statement the problem is the
>list of ID's to update has to be created recursively. I know I could use
>CTE's but we're not on 2005 just yet...(I am holding my breath here).....
Hi Tim,
There are many alternatives on SQL Server 2000 as well. The best
alternatives might include a redesign of your table. The most commonly
used method for storing hierarchies is not the must suitable for use in
a relational model. Google for "nested sets model" if you want to find
out the most common alternative.
(snip)
> I
>guess they have some bonus scheme that gets applied as their points increase
>( I don't know enough about their scheme yet to disagree ) which is too
>complicated to do in that manner.
I'd like to see more info on that. To me, the words "too complicated"
are like a red rag. <g>
--
Hugo Kornelis, SQL Server MVP
salesmen. Items
purchased by customers on-line all have points assigned to them that are
used for this
purpose. We used to add to this table as each customers application was
completed.
For some reason we have been asked to make it realtime. The work is mostly
done but
I have an issue. As each item is being added/changed/deleted on customers
orders,
these points are accumulated into the corresponding row in this table. So,
if
100 customers purchasing from the same salesman are on-line at the same time
is
this going to be a potential locking issue? These updates are done within a
transaction. Since the sales structure does have an "upline" type of
structure,
these points have to trickle up to several peoples entries in an
all-or-nothing fashion.
This is supposed to roll out soon(tomorrow). I'm worried that once we
really get traffic hitting this that
our server is going to have issues.
CREATE TABLE [dbo].[tblMonthlyPoints](
[lngSalesPersonID] [int] NOT NULL,
[intMonth] [tinyint] NOT NULL,
[intYear] [int] NOT NULL,
[decPoints] [decimal](19, 4) NOT NULL CONSTRAINT
[DF_tblMonthlyPoints_decPoints] DEFAULT (0),
CONSTRAINT [PK_tblMonthlyPoints] PRIMARY KEY CLUSTERED
(
[lngSalesPersonID] ASC,
[intMonth] ASC,
[intYear] ASC
) ON [PRIMARY]
) ON [PRIMARY]On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>We used to add to this table as each customers application was
>completed.
>For some reason we have been asked to make it realtime.
Hi Tim,
Have you considered storing the raw data (i.e. the individual sales)
instead of the aggregated monthly totals? I usually prefer to work that
way, since it makes recovery after errors so much easier.
The table you posted could then be replaced with a view.
>if
>100 customers purchasing from the same salesman are on-line at the same time
>is
>this going to be a potential locking issue?
Yes. Based on your table structure, registering the purchase would
require an exclusive lock. It will be a row lock, since the primary key
can be used to locate the row to be updated - but if several connections
want to update the same row (i.e. same salesman, same month, same year),
they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
run into deadlocks).
> These updates are done within a
>transaction. Since the sales structure does have an "upline" type of
>structure,
>these points have to trickle up to several peoples entries in an
>all-or-nothing fashion.
I don't really understand this. Do you mean that you update several rows
in the table, or do yoou mean that one update in the table fires a
trigger that does the trickling-up?
In the former case, try to update all rows in a single update statement:
UPDATE MonthlyPoints
SET Point s = Points + 5
WHERE SalesPersonID IN (123, 456, 7890)
AND Year = 2006
AND Month = 5
>CREATE TABLE [dbo].[tblMonthlyPoints](
> [lngSalesPersonID] [int] NOT NULL,
> [intMonth] [tinyint] NOT NULL,
> [intYear] [int] NOT NULL,
(snip)
Yoou should also consider replacing the individual Month and Year
columns with a single datetime (or smalldatetime) column that holds the
start of the month. Storing year and month as individual columns often
causes more grief than using a single datetime column.
--
Hugo Kornelis, SQL Server MVP|||Thanks for replying!! I'd given up on this thread...
I understand about updating them all in one statement the problem is the
list of ID's to update has to be created recursively. I know I could use
CTE's but we're not on 2005 just yet...(I am holding my breath here).....
We are storing the raw data already....I have already suggested the view
alternative which I preferred as well. It was shot down by others because I
guess they have some bonus scheme that gets applied as their points increase
( I don't know enough about their scheme yet to disagree ) which is too
complicated to do in that manner. I was just more curious about the
locking issue...I warned them it could be an issue so my hands are clean.
At least the raw data is present on each sales transaction so it can be
reconstructed after the fact "when" I have to fix it.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:lmes6291sa5ju81btj1g28oq367c1j052j@.4ax.com...
> On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
> yahoo D-O-T com> wrote:
>>We used to add to this table as each customers application was
>>completed.
>>For some reason we have been asked to make it realtime.
> Hi Tim,
> Have you considered storing the raw data (i.e. the individual sales)
> instead of the aggregated monthly totals? I usually prefer to work that
> way, since it makes recovery after errors so much easier.
> The table you posted could then be replaced with a view.
>>if
>>100 customers purchasing from the same salesman are on-line at the same
>>time
>>is
>>this going to be a potential locking issue?
> Yes. Based on your table structure, registering the purchase would
> require an exclusive lock. It will be a row lock, since the primary key
> can be used to locate the row to be updated - but if several connections
> want to update the same row (i.e. same salesman, same month, same year),
> they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
> run into deadlocks).
>> These updates are done within a
>>transaction. Since the sales structure does have an "upline" type of
>>structure,
>>these points have to trickle up to several peoples entries in an
>>all-or-nothing fashion.
> I don't really understand this. Do you mean that you update several rows
> in the table, or do yoou mean that one update in the table fires a
> trigger that does the trickling-up?
> In the former case, try to update all rows in a single update statement:
> UPDATE MonthlyPoints
> SET Point s = Points + 5
> WHERE SalesPersonID IN (123, 456, 7890)
> AND Year = 2006
> AND Month = 5
>>CREATE TABLE [dbo].[tblMonthlyPoints](
>> [lngSalesPersonID] [int] NOT NULL,
>> [intMonth] [tinyint] NOT NULL,
>> [intYear] [int] NOT NULL,
> (snip)
> Yoou should also consider replacing the individual Month and Year
> columns with a single datetime (or smalldatetime) column that holds the
> start of the month. Storing year and month as individual columns often
> causes more grief than using a single datetime column.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 19 May 2006 15:58:40 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>Thanks for replying!! I'd given up on this thread...
>I understand about updating them all in one statement the problem is the
>list of ID's to update has to be created recursively. I know I could use
>CTE's but we're not on 2005 just yet...(I am holding my breath here).....
Hi Tim,
There are many alternatives on SQL Server 2000 as well. The best
alternatives might include a redesign of your table. The most commonly
used method for storing hierarchies is not the must suitable for use in
a relational model. Google for "nested sets model" if you want to find
out the most common alternative.
(snip)
> I
>guess they have some bonus scheme that gets applied as their points increase
>( I don't know enough about their scheme yet to disagree ) which is too
>complicated to do in that manner.
I'd like to see more info on that. To me, the words "too complicated"
are like a red rag. <g>
--
Hugo Kornelis, SQL Server MVP
Friday, February 24, 2012
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
>
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
>
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
>
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
>
Sunday, February 19, 2012
Help with Update Query command Problem
Hi all,
I have this store procedure as follows:
Create Proc UpdateProblem
@.ProblemID int,
@.CompanyName varchar (50),
@.Firstname varchar (50),
@.Lastname Varchar (50),
@.Address varchar (50),
@.Postcode varchar (50),
@.City varchar (50),
@.Phone varchar (50),
@.Cutype varchar (50),
@.ProDescript varchar (50),
@.Sol varchar (50),
@.Email varchar (50)
as Update Problem
set CompanyName = @.CompanyName,
Firstname = @.Firstname,
Lastname = @.Lastname,
Address = @.Address,
PostCode = @.Postcode,
City = @.City,
Phone = @.Phone,
Cutype = @.Cutype,
ProDescript = @.ProDescript,
Sol = @.Sol,
Email = @.Email
where ProblemID = @.ProblemID
when I test the querry
exec UpdateProblem
10004, 'Toro AS','Mike','Tullas','Togo Street','G34 5TT','New York','06582531','Private','Machine is dead','Replace motherboard','goo@.ht.com'
what happen is that when I ran the querry instead of updating the specifc row of 1004 the querry will just update the whole rows in the table with the same data.
Please help. I have set the ProblemID as the Primary key.
When you do a SELECT * FROM Problem WHERE PRoblemID = 10004 , do you get 1 row or multiple rows?|||Only 1 row when I run : SELECT * FROM Problem WHERE PRoblemID = 10004|||
Find out the error. where ProblemID = @.ProblemID --> wrong
where @.ProblemID = ProblemID -- Right
cheers
|||I dont think so. They are both same. There's something else that happened. ...WHERE ProblemID = @.ProblemID should work just fine. Thats the way most queries are written.|||Hi Justnew, is the ProblemID column defined as int type? Can you post some sample data so that we can repro your issue?
Subscribe to:
Posts (Atom)