How do I make xp_cmdshell transactions run on the client instead of the server

Post title says it all. Any ideas? I asked this earlier but it seems to
have gotten lost in the shuffle.
Randall Arnoldxp_cmdshell runs OS level commands on the server -not the client. Using
xp_cmdshell to run executables on the client is not, in my experience,
something that you even want to attempt. (I'm not saying it can't be
done -just that it is too risky and very troublesome.)
However, if you are trying to have xp_cmdshell read the client computer's
file system, you could take the following steps.
Put xp_cmdshell into a Stored Procedure, use host_name to determine the
client computer and build the unc filepath.
You will be able to read the directory, and read files and write files to
the client computer. (Assuming you have appropriate permissions on the
client computer. Of course, you would NEVER be executing xp_cmdshell with
network admin priviledges, would you?)
While this can be done, the better question is why would you want to do it,
and what are the security ramifications?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:GGaog.32766$Nb2.601914@.news1.nokia.com...
> Post title says it all. Any ideas? I asked this earlier but it seems to
> have gotten lost in the shuffle.
> Randall Arnold
>|||I want to be able to periodically launch a VB script on the client PC.
Security is not an issue at all in this environment.
Randall
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:Ohd5kvfmGHA.4700@.TK2MSFTNGP02.phx.gbl...
> xp_cmdshell runs OS level commands on the server -not the client. Using
> xp_cmdshell to run executables on the client is not, in my experience,
> something that you even want to attempt. (I'm not saying it can't be
> done -just that it is too risky and very troublesome.)
> However, if you are trying to have xp_cmdshell read the client computer's
> file system, you could take the following steps.
> Put xp_cmdshell into a Stored Procedure, use host_name to determine the
> client computer and build the unc filepath.
> You will be able to read the directory, and read files and write files to
> the client computer. (Assuming you have appropriate permissions on the
> client computer. Of course, you would NEVER be executing xp_cmdshell with
> network admin priviledges, would you?)
> While this can be done, the better question is why would you want to do
> it, and what are the security ramifications?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:GGaog.32766$Nb2.601914@.news1.nokia.com...
>|||Security is always an issue :) My question would be do you absolutely have
to do this from within SQL Server? If not, I would create a Windows Service
to launch your script on a timer, or something to that effect.
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:fObog.32769$Nb2.601990@.news1.nokia.com...
>I want to be able to periodically launch a VB script on the client PC.
>Security is not an issue at all in this environment.
> Randall
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:Ohd5kvfmGHA.4700@.TK2MSFTNGP02.phx.gbl...
>|||I'm looking at any and all reasonable options to solving this need, working
several threads in parallel. One idea similar to this one but it requires
the symin role to have write/execute privileges on another server in a
certain folder but the IT guys here can't figure out how to give symin
that ability.
*sigh*
Randall
"Mike C#" <xyz@.xyz.com> wrote in message
news:OzgeLjgmGHA.3600@.TK2MSFTNGP02.phx.gbl...
> Security is always an issue :) My question would be do you absolutely
> have to do this from within SQL Server? If not, I would create a Windows
> Service to launch your script on a timer, or something to that effect.
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:fObog.32769$Nb2.601990@.news1.nokia.com...
>|||You are using SQL 2000 right? Honestly this task doesn't belong on SQL
Server. You can probably force the issue, but you'd be better off overall
if you made this a separate application that operated independently of SQL
Server. Is there some particular reason you feel you need to have it kick
off from inside SQL Server?
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:MsAog.33256$_k2.585855@.news2.nokia.com...
> I'm looking at any and all reasonable options to solving this need,
> working several threads in parallel. One idea similar to this one but it
> requires the symin role to have write/execute privileges on another
> server in a certain folder but the IT guys here can't figure out how to
> give symin that ability.
> *sigh*
>|||Well there's always VBScript, .BAT files and the dos command shell "at"
command :) I think someone else already mentioned sharing a directory on
the client and mapping a drive to it from the server. Another possibility
(note that I haven't actually tried this...) might be to install MSDE on the
client and run an SP via linked server that runs xp_cmdshell on the client.
Note again that I'm not even sure this would work, as I haven't tried it,
but it might be worth a try... I'd still recommend using a scripting
language of some sort to do the job, but if xp_cmdshell is what you want,
then you might give this a try.
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:ldDog.33266$_k2.585753@.news2.nokia.com...
> I'm trying to do as much as possible via SQL server because that's what we
> have and I lack the tools to "do it right" otherwise. I'd much rather be
> doing this in ASP.NET and deploying everything on the intranet the way it
> SHOULD be done. This facility will be closed by this time next year so
> I'm not exactly seeing people jump all over my resource requests. ; )
> But in the meantime, I still have this demand to deal with...
> Randall|||I've tried scripting, but can't get our IT guys to figure out how to give
symin write/execute privileges on a protected share (where the work needs
to take place and results stored).
Randall
"Mike C#" <xxx@.yyy.com> wrote in message news:jNEog.63$Ur7.47@.fe09.lga...
> Well there's always VBScript, .BAT files and the dos command shell "at"
> command :) I think someone else already mentioned sharing a directory on
> the client and mapping a drive to it from the server. Another possibility
> (note that I haven't actually tried this...) might be to install MSDE on
> the client and run an SP via linked server that runs xp_cmdshell on the
> client. Note again that I'm not even sure this would work, as I haven't
> tried it, but it might be worth a try... I'd still recommend using a
> scripting language of some sort to do the job, but if xp_cmdshell is what
> you want, then you might give this a try.
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:ldDog.33266$_k2.585753@.news2.nokia.com...
>|||If you don't mind, can I ask specifically what you're trying to accomplish?
Someone might be able to give you a better solution if we knew exactly what
you were trying to do. So far it sounds like you might be trying to read
some file(s) in, do some processing on them and write them back out? Or are
you trying to import or export data from SQL Server? Or maybe some
combination?
As for periodically executing a VBScript on a timer, the "at" command could
probably do the trick for you. As for setting share permissions, it all
depends on your network -- you might want to try one of the .networking, .vb
or .vbscript newsgroups (if you haven't already)
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:nyUog.32924$Nb2.605940@.news1.nokia.com...
> I've tried scripting, but can't get our IT guys to figure out how to give
> symin write/execute privileges on a protected share (where the work
> needs to take place and results stored).
> Randall|||I'm trying to take daily SQL queries and automatically build Powerpoint
presentations containing charts and tables representing data from those
queries (production performance/yield metrics). It's too much work for
people do be constantly burdened with, but I lack the proper tools and have
had trouble getting expenditures approved. This facility will be shuttered
by this time next year... but meanwhile work has to get out and we're told
we have to solve everything for the folks in Mexico who will be taking our
jobs.
Anyway, one poster here showed me how to get SQL Server 2000 Reporting
Services for free, so I've ordered that. I'll try to put management off
until it comes in.
As for the folder access, I have found that simply granting the
Adminstrators group on server A write/execute privileges on a folder on
server B solves my scripting problem. Just having a hard time getting IT
guys to do it.
Thanks for your interest.
Randall
"Mike C#" <xyz@.xyz.com> wrote in message
news:utHt9nEnGHA.3656@.TK2MSFTNGP03.phx.gbl...
> If you don't mind, can I ask specifically what you're trying to
> accomplish? Someone might be able to give you a better solution if we knew
> exactly what you were trying to do. So far it sounds like you might be
> trying to read some file(s) in, do some processing on them and write them
> back out? Or are you trying to import or export data from SQL Server? Or
> maybe some combination?
> As for periodically executing a VBScript on a timer, the "at" command
> could probably do the trick for you. As for setting share permissions, it
> all depends on your network -- you might want to try one of the
> .networking, .vb or .vbscript newsgroups (if you haven't already)
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:nyUog.32924$Nb2.605940@.news1.nokia.com...
>sql

How do I find out whether a PK is referenced by any FK?

I need to perform regular maintenance on a table who is referenced through F
K from many
other tables.
Instead of attempting to bluntly delete the rows and let SQL report RI error
s , I would
like to first check whether the row is currently being referenced without ha
ving to check
each referencing table.
How can I accomplish that?
thanks.Do you mean whether an FK exists at all, or whether any relevant data exists
in the FK tables? Note that if the FK is defined as nullable then the mere
presence of rows in the FK table does not mean that those rows depend on any
rows in the PK table.
"Gaetan" <someone@.somewhere.com> wrote in message
news:ten1n1h9al41tjats6o3lheo1i91rl40kg@.
4ax.com...
>I need to perform regular maintenance on a table who is referenced through
>FK from many
> other tables.
> Instead of attempting to bluntly delete the rows and let SQL report RI
> errors , I would
> like to first check whether the row is currently being referenced without
> having to check
> each referencing table.
> How can I accomplish that?
> thanks.|||The FK constraints are defined in the other tables. I want to determine whet
her any
relevant data exists in the referencing tables. NULL values are not allowed
in the FK
values.
On Tue, 8 Nov 2005 12:31:39 -0500, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Do you mean whether an FK exists at all, or whether any relevant data exist
s
>in the FK tables? Note that if the FK is defined as nullable then the mere
>presence of rows in the FK table does not mean that those rows depend on an
y
>rows in the PK table.
>
>
>"Gaetan" <someone@.somewhere.com> wrote in message
> news:ten1n1h9al41tjats6o3lheo1i91rl40kg@.
4ax.com...
>|||Hello, Gaetan
If your primary key has is composed of a single column and the number
of referencing tables is not too big (less than 20...30 tables), you
can try to dynamically construct an SQL statement to check that tables,
based on the informations from the system tables (or the
information_schema views, if you preffer).
The code might be something like this:
DECLARE @.TableName sysname
DECLARE @.PKColumn sysname
DECLARE @.Condition nvarchar(100)
DECLARE @.SQL nvarchar(4000)
SET @.TableName = 'SomeTable'
SET @.Condition = 'SomeColumn=123'
SELECT @.SQL=ISNULL(@.SQL+CHAR(13)+'UNION ALL'+CHAR(13),'')
+'SELECT '''+ft.name+''' WHERE EXISTS ('
+'SELECT * FROM '+QUOTENAME(ft.name)
+' WHERE '+QUOTENAME(fc.name)+' IN ('
+'SELECT '+QUOTENAME(pc.name)
+' FROM '+QUOTENAME(@.TableName)
+ISNULL('WHERE '+@.Condition,'')+'))'
FROM sysreferences r
INNER JOIN sysobjects fk ON r.constid=fk.id
INNER JOIN sysobjects ft ON r.fkeyid=ft.id
INNER JOIN sysobjects pt ON r.rkeyid=pt.id
INNER JOIN syscolumns fc ON r.fkeyid=fc.id AND r.fkey1=fc.colid
INNER JOIN syscolumns pc ON r.rkeyid=pc.id AND r.rkey1=pc.colid
WHERE fk.xtype='F' AND pt.name=@.TableName
PRINT @.SQL
EXEC(@.SQL)
IF @.@.ROWCOUNT>0 BEGIN
PRINT 'There are some rows in other tables referencing our row(s)'
END
Be carefull what you put in the @.Condition variable, because this code
is vulnerable to "SQL Injection" attacks. Also, the maximum length of a
nvarchar string (4000 characters) may be easily exceeded if you have
many foreign keys for referencing your table.
Razvan|||On Tue, 08 Nov 2005 17:23:04 GMT, Gaetan wrote:

>I need to perform regular maintenance on a table who is referenced through
FK from many
>other tables.
>Instead of attempting to bluntly delete the rows and let SQL report RI erro
rs , I would
>like to first check whether the row is currently being referenced without h
aving to check
>each referencing table.
>How can I accomplish that?
Hi Gaetan,
You can't. The only way to know if a row is referenced or not is to
check all referencing tables.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Wonderful,
This is exactly what I was looking for. I will have less than 15 tables refe
rencing a main
table so there is no problem with the string size. Injection will not proble
m since there
will be no user input.
Thanks again Razvan.
On 8 Nov 2005 12:57:16 -0800, "Razvan Socol" <rsocol@.gmail.com> wrote:

>Hello, Gaetan
>If your primary key has is composed of a single column and the number
>of referencing tables is not too big (less than 20...30 tables), you
>can try to dynamically construct an SQL statement to check that tables,
>based on the informations from the system tables (or the
>information_schema views, if you preffer).
>The code might be something like this:
>DECLARE @.TableName sysname
>DECLARE @.PKColumn sysname
>DECLARE @.Condition nvarchar(100)
>DECLARE @.SQL nvarchar(4000)
>SET @.TableName = 'SomeTable'
>SET @.Condition = 'SomeColumn=123'
>SELECT @.SQL=ISNULL(@.SQL+CHAR(13)+'UNION ALL'+CHAR(13),'')
> +'SELECT '''+ft.name+''' WHERE EXISTS ('
> +'SELECT * FROM '+QUOTENAME(ft.name)
> +' WHERE '+QUOTENAME(fc.name)+' IN ('
> +'SELECT '+QUOTENAME(pc.name)
> +' FROM '+QUOTENAME(@.TableName)
> +ISNULL('WHERE '+@.Condition,'')+'))'
>FROM sysreferences r
>INNER JOIN sysobjects fk ON r.constid=fk.id
>INNER JOIN sysobjects ft ON r.fkeyid=ft.id
>INNER JOIN sysobjects pt ON r.rkeyid=pt.id
>INNER JOIN syscolumns fc ON r.fkeyid=fc.id AND r.fkey1=fc.colid
>INNER JOIN syscolumns pc ON r.rkeyid=pc.id AND r.rkey1=pc.colid
>WHERE fk.xtype='F' AND pt.name=@.TableName
>PRINT @.SQL
>EXEC(@.SQL)
>IF @.@.ROWCOUNT>0 BEGIN
> PRINT 'There are some rows in other tables referencing our row(s)'
>END
>Be carefull what you put in the @.Condition variable, because this code
>is vulnerable to "SQL Injection" attacks. Also, the maximum length of a
>nvarchar string (4000 characters) may be easily exceeded if you have
>many foreign keys for referencing your table.
>Razvan|||Understood.
Performance wise, I wonder whether if it is preferable to attempt to delete
a referenced
row and let SQL report an error and handle it in the code or run SQL stateme
nt(s) to scan
each referencing tables!
Do you have an idea which approach is faster? The majority of the cases I wi
ll have less
than 5 referencing tables.
Regards, Gaetan.
On Tue, 08 Nov 2005 22:16:20 +0100, Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_
fo> wrote:

>On Tue, 08 Nov 2005 17:23:04 GMT, Gaetan wrote:
>
>Hi Gaetan,
>You can't. The only way to know if a row is referenced or not is to
>check all referencing tables.
>Best, Hugo|||> Performance wise, I wonder whether if it is preferable to attempt to
> delete a referenced
> row and let SQL report an error and handle it in the code or run SQL
> statement(s) to scan
> each referencing tables!
> Do you have an idea which approach is faster? The majority of the cases I
> will have less
> than 5 referencing tables.
Well, what is the end game here?
I'm not sure I understand the purpose. If there are no referencing rows,
allow them to just delete everything from the PK table. If there are
referencing rows, then what?
A|||On Tue, 08 Nov 2005 21:48:01 GMT, Gaetan wrote:

>Understood.
>Performance wise, I wonder whether if it is preferable to attempt to delete
a referenced
>row and let SQL report an error and handle it in the code or run SQL statem
ent(s) to scan
>each referencing tables!
>Do you have an idea which approach is faster? The majority of the cases I w
ill have less
>than 5 referencing tables.
>Regards, Gaetan.
Hi Gaetan,
That depends.
If most cases will not be referenced, deleting without checking will be
fastest. If you do check, you're duplicating the work (since SQL Server
will have to re-run the check as part of the DELETE command).
On the other hand, if many cases will fail, checking first might be
faster. First, because you can control the order in which tables are
checked (making sure the table most likely to be the offender is checked
first). And second, because you eliminate the need for a ROLLBACK
operation (which is often slower than the original data manipulation).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Nov 2005 16:57:41 -0500, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Well, what is the end game here?
>I'm not sure I understand the purpose. If there are no referencing rows,
>allow them to just delete everything from the PK table. If there are
>referencing rows, then what?
>A
>
The goal is simple; I have a core table that need to be refreshed at some in
tervals. That
core table is refreshed (insert / update / delete) from data elements locate
d somewhere
else.
Inserts and updates of the core table are easy ... just do it. However, for
the deletes,
other tables may still be referencing rows of the core table. Performance is
not
imperative but since I have yet to write the code, I would rather do it the
way that makes
most sense without going heels over head, hence the original question; Shoul
d I just
delete the core table row and catch the RI errors or scan the referencing ta
bles and skip
the delete operation when a core table row is still being referenced. I must
not delete
the row when it is still referenced ... I have to wait until yet another pro
cess deletes
the rows from the referencing tables.
Gaetan.

How do I do this in crystal report?

Hi, I am currently using crystal report 8.0. I need to create a report that instead of print downwards i need it go sideway. For eg normal report

00512789
1

00512567
2

but i need to do this

00512789 00512567
1 2

I tried using the cross tab report but it does not suit my needs. And also the report is actually taking data from 3 queries but the input for all 3 queries is the same. How do i let users to enter the parameter once for all 3 queries. Any help is appreciated. Thank youHi,

As per my understanding u want to show ur data Horizontally insted of vertically.
If m i right then, u try with "Format Page With Multiple Columns" functionality

I sure u get the solution which u r looking for

-Yags

How do I display the day name instead of the day #

Hi,

I created a Time Dimension and ended up with the following (as shown in the attached image). The problem is that when I go to the Day level, I see numbers e.g. 1, 2, ..., 31. Instead I want to display the Weekday Name e.g. Monday, Tueday and so on (i.e. what we get with the datename(dw,<date>) function. Right?)

However I am not sure what to change and where in order to get the desired results. Can someone kindly guide me.

Thanks.use:
datename(dw,"dbo"."TimeDimension"."TheDate")

ie

select datename(dw,getdate())|||use:
datename(dw,"dbo"."TimeDimension"."TheDate")


Hi hirshal.

I tried entering the above in the Member Name Column and the Member Key column but did not get the desired effect :(

Can you kindly guide? Do I need to enter the above quoted formula in both fields (Member Name Column and Member Key column) or in only one of these?

Thanks.