How to make proper foreign keys in EM? And how to create tables using SQL statem

Hi!

I wonder how to make right foreign keys in Enterprise Manager. I use diagram but in this case I get 'rigid' keys, so I can't delete records. But I want it to set NULL on removing records.

And is it possible to create tables by writing SQl statements in EM? I tried but the new table didn't appear :(.Use Query Analyzer and you will know what are you doing (GUI does not show what it does).sql

How to make JOB without xp_regread procedure

Hi!!
I removed 'xp_regread' procedure for security
after that I can't make JOB in Enterprise Manager
How can I make JOB without 'xp_regread' procedure
thanks~Do not remove extended stored procedures without first checking dependencies and fully researching their functionality. Add it back and just remove the execute permission from public.

How to make correct join

Hi
I have to tables one called GROUPS and one called ACCOUNT

In table Group I have the follwing fields
Groupid, AccountFrom, AccountTo

In table Account I have
AccountNo, Name etc.

Records in Groups:
P1, 1001, 1002
P1, 1005, 1007
P1, 1010, 1010
P1, 1007, 1012

Now I want to have the corresponding AccountNo from ACCOUNT (from range
AccountFrom..AccountTo), that is the following result:
1001
1002
1005
1006
1007
1008
1009
1010
1011
1012

If f.x. 1008 doesn't exist in ACCOUNT this should not be listed. The
result will be used in another view.
My problem is that I also get every other record from table ACCOUNT.

Do anyone out there have a solution on my problem ?
BR/JanPost your current SELECT statement.
Usually , you would use an INNER JOIN for your requiremnts , i.e An inner
join returns all rows that result in a match .

Are you currently using a LEFT JOIN?

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<jannoergaard@.hotmail.com> wrote in message
news:1144159750.922225.272910@.v46g2000cwv.googlegr oups.com...
> Hi
> I have to tables one called GROUPS and one called ACCOUNT
> In table Group I have the follwing fields
> Groupid, AccountFrom, AccountTo
> In table Account I have
> AccountNo, Name etc.
> Records in Groups:
> P1, 1001, 1002
> P1, 1005, 1007
> P1, 1010, 1010
> P1, 1007, 1012
> Now I want to have the corresponding AccountNo from ACCOUNT (from range
> AccountFrom..AccountTo), that is the following result:
> 1001
> 1002
> 1005
> 1006
> 1007
> 1008
> 1009
> 1010
> 1011
> 1012
> If f.x. 1008 doesn't exist in ACCOUNT this should not be listed. The
> result will be used in another view.
> My problem is that I also get every other record from table ACCOUNT.
> Do anyone out there have a solution on my problem ?
> BR/Jan|||Hi there

As I recall the statement it's something like this

Select distinct dbo.Account.AccountNo as AccountNo
>From dbo.Account inner join dbo.Groups
On dbo.Groups.AccountFrom <= dbo.Account.AccountNo AND
dbo.Groups.AccountTo >= dbo.Account.AccountNo

Hope you have a solution for me. I have tried different ways but don't
seme to get it right.
BR /Jan|||(jannoergaard@.hotmail.com) writes:
> As I recall the statement it's something like this
> Select distinct dbo.Account.AccountNo as AccountNo
>>From dbo.Account inner join dbo.Groups
> On dbo.Groups.AccountFrom <= dbo.Account.AccountNo AND
> dbo.Groups.AccountTo >= dbo.Account.AccountNo
> Hope you have a solution for me. I have tried different ways but don't
> seme to get it right.

This is certainly better, since it relieves you from the DISTINCT:

SELECT a.AccountNp
FROM dbo.Account a
WHERE EXISTS (SELECT *
FROM dbo.Groups g
WHERE a.AccountNo BETWEEN g.AccountFrom AND g.AccountTo)

But whether that really addresses your issue, I cannot tell, because
your posting was very clear.

If this query does not work out, I suggest that you post the following:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result from the sample.

This permits anyone who wants to answer to copy and paste into
a query tool and develop a tested query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
Thanks very much. This worked out just as I wanted it to. Next time I
will have in mind what you suggested.
BR/Jan

How to make 2 columns in datagrid with random records?

Hi
I want to make random record from both columns
Not just like this
"SELECT Firstname,Lasename FROM rndnames ORDER BY NewID()"
but more like this but this code dont work becouse i dont know how to put it into the code:
"SELECT Firstname FROM rndnames ORDER BY NewID()"
"SELECT Lasename FROM rndnames ORDER BY NewID()"
As you see i want both columns to be random placed..
Please help me...
Well i found one way that was 2 tables the code ended like this
SqlConnection1.Open()
Dim sqlcon As NewSqlCommand("Select Firstname,spillernavn From rndnames CROSS JOINspillere ORDER BY NewID()", SqlConnection1)
Dim sqlrd As SqlDataReader
sqlrd = sqlcon.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataSource = sqlrd
DataGrid1.DataBind()
Well Well at least it works;)

How to maintain the history of information in a table?

Hi!
I have a question regarding what is the best way to maintain history
information in a table. I'm quite new to this, so please excuse me if
this is a common or a silly question.
Assume there's a table for storing the tax information, called TaxInfo.
Its got a PK column, TaxID, and other tables have an FK to TaxID.
There's a "Percentage" column in this table, whose value might change
over a period of time. Of course, there is a Name column and a
Description column.
Now, assume there is some existing data in other tables which refer to
this Percentage value. If this value changes, all existing data in
other tables should continue to use the older Percentage value, whereas
any new and subsequent data in the other tables should use the newer
value.
I'm thinking two date columns, ValidFrom and ValidUpto in the TaxInfo
table. ValidFrom is the date on which the row is inserted. ValidUpto
will normally be NULL, but if there's a change in the Percentage value,
then the ValidUpto of that row becomes today's date, and there comes
another row with the new Percentage value and ValidUpto = NULL. So
whenever I want the active Percentage value, I look for the row with
ValidUpto = NULL. But, since the TaxID is a PK, there cannot be a
second row with the same TaxID, and I'm stuck. And there cannot be
another TaxID representing that tax, since it is the same tax but with
a different value. If I make TaxID a non-PK column, then how do I
create a relationship from another table to this table? I think this
problem should be so common that there should be some kind of a
readymade design pattern that solves this problem?
Generally, is this what is called historization? I've heard of this
term, but I did a Google search for historization and nothing came up!
What exactly is the technical term, if there's any, for this kind of a
feature? And if this is not historization, what else is called
historization?
- RameshRead this article
http://vyaskn.tripod.com/sql_archive_data.htm
"Ramesh" <dramesh@.rushmorent.com> wrote in message
news:1141046170.179430.102730@.i40g2000cwc.googlegroups.com...
> Hi!
> I have a question regarding what is the best way to maintain history
> information in a table. I'm quite new to this, so please excuse me if
> this is a common or a silly question.
> Assume there's a table for storing the tax information, called TaxInfo.
> Its got a PK column, TaxID, and other tables have an FK to TaxID.
> There's a "Percentage" column in this table, whose value might change
> over a period of time. Of course, there is a Name column and a
> Description column.
> Now, assume there is some existing data in other tables which refer to
> this Percentage value. If this value changes, all existing data in
> other tables should continue to use the older Percentage value, whereas
> any new and subsequent data in the other tables should use the newer
> value.
> I'm thinking two date columns, ValidFrom and ValidUpto in the TaxInfo
> table. ValidFrom is the date on which the row is inserted. ValidUpto
> will normally be NULL, but if there's a change in the Percentage value,
> then the ValidUpto of that row becomes today's date, and there comes
> another row with the new Percentage value and ValidUpto = NULL. So
> whenever I want the active Percentage value, I look for the row with
> ValidUpto = NULL. But, since the TaxID is a PK, there cannot be a
> second row with the same TaxID, and I'm stuck. And there cannot be
> another TaxID representing that tax, since it is the same tax but with
> a different value. If I make TaxID a non-PK column, then how do I
> create a relationship from another table to this table? I think this
> problem should be so common that there should be some kind of a
> readymade design pattern that solves this problem?
> Generally, is this what is called historization? I've heard of this
> term, but I did a Google search for historization and nothing came up!
> What exactly is the technical term, if there's any, for this kind of a
> feature? And if this is not historization, what else is called
> historization?
> - Ramesh
>|||Hi Uri,
Thanks for mentioning that article. It talks about archiving, but what
I need is not archiving: both the new and the old rows ought to be
there in the table, so I guess that article doesn't apply to my current
problem. Thanks a lot anyway.
- Ramesh|||Your idea of versioning the tax information using ValidFrom and ValidTo is
the right idea and a standard solution. In this specific case, perhaps you
should have a compound primary key consisting of TaxID + ValidFrom. This
concept is often referred to as "data versioning". It is one common
component of (but does not entirely define) data warehousing.
http://www.dmreview.com/article_sub...ticleID=1025568
http://www.dmreview.com/article_sub.cfm?articleID=7202
"Ramesh" <dramesh@.rushmorent.com> wrote in message
news:1141046170.179430.102730@.i40g2000cwc.googlegroups.com...
> Hi!
> I have a question regarding what is the best way to maintain history
> information in a table. I'm quite new to this, so please excuse me if
> this is a common or a silly question.
> Assume there's a table for storing the tax information, called TaxInfo.
> Its got a PK column, TaxID, and other tables have an FK to TaxID.
> There's a "Percentage" column in this table, whose value might change
> over a period of time. Of course, there is a Name column and a
> Description column.
> Now, assume there is some existing data in other tables which refer to
> this Percentage value. If this value changes, all existing data in
> other tables should continue to use the older Percentage value, whereas
> any new and subsequent data in the other tables should use the newer
> value.
> I'm thinking two date columns, ValidFrom and ValidUpto in the TaxInfo
> table. ValidFrom is the date on which the row is inserted. ValidUpto
> will normally be NULL, but if there's a change in the Percentage value,
> then the ValidUpto of that row becomes today's date, and there comes
> another row with the new Percentage value and ValidUpto = NULL. So
> whenever I want the active Percentage value, I look for the row with
> ValidUpto = NULL. But, since the TaxID is a PK, there cannot be a
> second row with the same TaxID, and I'm stuck. And there cannot be
> another TaxID representing that tax, since it is the same tax but with
> a different value. If I make TaxID a non-PK column, then how do I
> create a relationship from another table to this table? I think this
> problem should be so common that there should be some kind of a
> readymade design pattern that solves this problem?
> Generally, is this what is called historization? I've heard of this
> term, but I did a Google search for historization and nothing came up!
> What exactly is the technical term, if there's any, for this kind of a
> feature? And if this is not historization, what else is called
> historization?
> - Ramesh
>|||I know CELKO would say the way you are going about it is the best (at least
in the fact that you have two columns for the start and end dates).
Personally, I have only seen this done once, and the programmers that did it
were not very good, and there were all types of problems with their
implementation. One the other hand, I have seen it done with one date quite
well, where you use only a start date and always use a subquery to select
the maximum date <= [today] or <= [join date]. The sub query generally
preforms well and once you write the code once you can cut and paste it
everywhere else. You don't care about maintaining the end date, which is
basically dependent on the esistence of the begin date in the next row.
Keep in mind that this is just my opinion, but I have seen the exact tax
tables you mention handled in this way, along with employee data, department
lists, and lookup tables of all sorts. Unfortunately, I do not have the
benefit of having seen the begin/end date approach done correctly, so I
can't give a fair comparison of the two methods.
Hopefully someone else can chime in with their experiences and speak to the
pros and cons of each.
Joe, if you could address the issue personally, I think it would make for an
excellent discussion.
"Ramesh" <dramesh@.rushmorent.com> wrote in message
news:1141046170.179430.102730@.i40g2000cwc.googlegroups.com...
> Hi!
> I have a question regarding what is the best way to maintain history
> information in a table. I'm quite new to this, so please excuse me if
> this is a common or a silly question.
> Assume there's a table for storing the tax information, called TaxInfo.
> Its got a PK column, TaxID, and other tables have an FK to TaxID.
> There's a "Percentage" column in this table, whose value might change
> over a period of time. Of course, there is a Name column and a
> Description column.
> Now, assume there is some existing data in other tables which refer to
> this Percentage value. If this value changes, all existing data in
> other tables should continue to use the older Percentage value, whereas
> any new and subsequent data in the other tables should use the newer
> value.
> I'm thinking two date columns, ValidFrom and ValidUpto in the TaxInfo
> table. ValidFrom is the date on which the row is inserted. ValidUpto
> will normally be NULL, but if there's a change in the Percentage value,
> then the ValidUpto of that row becomes today's date, and there comes
> another row with the new Percentage value and ValidUpto = NULL. So
> whenever I want the active Percentage value, I look for the row with
> ValidUpto = NULL. But, since the TaxID is a PK, there cannot be a
> second row with the same TaxID, and I'm stuck. And there cannot be
> another TaxID representing that tax, since it is the same tax but with
> a different value. If I make TaxID a non-PK column, then how do I
> create a relationship from another table to this table? I think this
> problem should be so common that there should be some kind of a
> readymade design pattern that solves this problem?
> Generally, is this what is called historization? I've heard of this
> term, but I did a Google search for historization and nothing came up!
> What exactly is the technical term, if there's any, for this kind of a
> feature? And if this is not historization, what else is called
> historization?
> - Ramesh
>|||I think your problem is that you are trying to use TaxID for two purposes -
to identify a particular class of tax, and to identify a particular
time-restricted value of that class. Your taxinfo table could have a
separate column called 'TaxClass'. Now when you are searching for the tax
rate to be added to a new row in another table, you look through the TaxIDs
within the correct TaxClass and find the row with the null 'ValidTo' column.
You now use the TaxID of this row in your foreign key column.
When the tax rate changes for that class, you add a new row (with a new
TaxID) for that Tax Class, modify the previous 'current' row by writing the
'ValidTo' date, and subsequent entries will use the new value.
Sorry if I've misunderstood the problem in any way - I just skipped through
in an idle moment.
"Ramesh" <dramesh@.rushmorent.com> wrote in message
news:1141046170.179430.102730@.i40g2000cwc.googlegroups.com...
> Hi!
> I have a question regarding what is the best way to maintain history
> information in a table. I'm quite new to this, so please excuse me if
> this is a common or a silly question.
> Assume there's a table for storing the tax information, called TaxInfo.
> Its got a PK column, TaxID, and other tables have an FK to TaxID.
> There's a "Percentage" column in this table, whose value might change
> over a period of time. Of course, there is a Name column and a
> Description column.
> Now, assume there is some existing data in other tables which refer to
> this Percentage value. If this value changes, all existing data in
> other tables should continue to use the older Percentage value, whereas
> any new and subsequent data in the other tables should use the newer
> value.
> I'm thinking two date columns, ValidFrom and ValidUpto in the TaxInfo
> table. ValidFrom is the date on which the row is inserted. ValidUpto
> will normally be NULL, but if there's a change in the Percentage value,
> then the ValidUpto of that row becomes today's date, and there comes
> another row with the new Percentage value and ValidUpto = NULL. So
> whenever I want the active Percentage value, I look for the row with
> ValidUpto = NULL. But, since the TaxID is a PK, there cannot be a
> second row with the same TaxID, and I'm stuck. And there cannot be
> another TaxID representing that tax, since it is the same tax but with
> a different value. If I make TaxID a non-PK column, then how do I
> create a relationship from another table to this table? I think this
> problem should be so common that there should be some kind of a
> readymade design pattern that solves this problem?
> Generally, is this what is called historization? I've heard of this
> term, but I did a Google search for historization and nothing came up!
> What exactly is the technical term, if there's any, for this kind of a
> feature? And if this is not historization, what else is called
> historization?
> - Ramesh
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:587521
On 27 Feb 2006 05:16:10 -0800, Ramesh wrote:
(snip)
>I'm thinking two date columns, ValidFrom and ValidUpto in the TaxInfo
>table. ValidFrom is the date on which the row is inserted. ValidUpto
>will normally be NULL, but if there's a change in the Percentage value,
>then the ValidUpto of that row becomes today's date, and there comes
>another row with the new Percentage value and ValidUpto = NULL. So
>whenever I want the active Percentage value, I look for the row with
>ValidUpto = NULL.
Hi Ramesh,
Good thinking. However, you might find it easier to handle things if you
explicitly store the maximum possible date value (9999-12-31) in the
ValidUpto column if it's still valid. You can then easily find a row
that is "current" for a given date by looking for a row with
ValidFrom <= @.TheDate AND ValidUpto < @.TheDate
With the NULL, you'd require all kinds of extra null handling.
Beware that you'll have to make sure to develop some foolproof triggers
to guarantee that you can never have anomalies like two percentages that
are valid on the same day, or a day with no valid percentage. (You can
prevent these issues by having just the ValidFrom date, as suggested by
Jim, but you'll find the pperformance of that version to be slower).

> But, since the TaxID is a PK, there cannot be a
>second row with the same TaxID, and I'm stuck. And there cannot be
>another TaxID representing that tax, since it is the same tax but with
>a different value.
Primary key is either (TaxID, ValidFrom) or (TaxID, ValidUpto). And the
combination that you don't choose as PK should be declared UNIQUE.

> If I make TaxID a non-PK column, then how do I
>create a relationship from another table to this table? I think this
>problem should be so common that there should be some kind of a
>readymade design pattern that solves this problem?
You can't have a strict relationship, since SQL Server won't understand
that the values (TaxID = 7; TaxDate = '20060218') should refer to the
row with key values (TaxID = 7; ValidFrom = '20060101'; ValidUpto =
'20061231').
You could consider adding a TaxPeriodID to this table and making that
the primary key, but I feel that this is a somewhat kludgy solution. I'd
leave it as is and accept that SQL Server won't understand the exact
relation between the tables. Downside is that you'll have to provide
your own referential integrity handling (in triggers, presumably).
Hugo Kornelis, SQL Server MVP|||I think I will go with a PK made up of both TaxID and ValidFrom
columns. Too bad SQL Server won't let me "see" the relationships on the
diagrams...
Thanks so very much to all of you for your inputs and suggestions.
- Ramesh

how to list perissions

Hi
I use C# cod and a have a problem with listing all premisions on some
user
please help
ciaoBoko
Some times ago Aaron ( If I remember well) answered the same question
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"Boko" <borcecif@.yahoo.com> wrote in message
news:1141124678.068438.60580@.e56g2000cwe.googlegroups.com...
> Hi
> I use C# cod and a have a problem with listing all premisions on some
> user
> please help
> ciao
>

How to Limit the number of rows returned with MSSQL, like the MySQL Limit clause.

hi

I use to work with mysql but now I need mssql. Can somebody tell my how I canwork around LIMIT to select a number of records so I can display 10 records at a time in stead of all?

ThanksI think there is nothing exactly the same as LIMIT. But you can use TOP to get limited records. e.g. TOP 10 will return 10 records but this can not be used like TOP 20,10.

Moreover, you can loop the results to reach to your required result number and start displaying them. But this is not a good approach

I don't know the current implementation of TOP. I read this somewhere.|||I've edited the title of the thread, and moved it to the Sql Server / MSSQL Forums.

Moderator

How to let user use xp_cmdshell?

Hi
I wonder to know Hot to let SQL Server user (not Admin role) use
xp_cmdshell?
Cause the execution response access denied.
What should I do? Thanks!
AngiOnly members of the 'Admin' role can execute xp_cmdshell. And there's really
not any options.
What are you attempting to accomplish?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"angi" <angi@.news.microsoft.com> wrote in message
news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
> Hi
> I wonder to know Hot to let SQL Server user (not Admin role) use
> xp_cmdshell?
> Cause the execution response access denied.
> What should I do? Thanks!
> Angi
>|||Thanks!
It's too bad that only Admin role can execute it.
I write a store procedure and use xp_cmdshell to execute .exe file on
localhost.
Casue the user include non-Admin role, so I want to know is any way that
user can execute xp_cmdshell.
Any good idea? Thanks!
Angi
"Arnie Rowland" <arnie@.1568.com> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Only members of the 'Admin' role can execute xp_cmdshell. And there's
> really not any options.
> What are you attempting to accomplish?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
>[/vbcol]|||Oh, I'm use SQL Server 2005!
Any way can let user execute " xp_cmdshell" ?
Thanks!
Angi
"Arnie Rowland" <arnie@.1568.com> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Only members of the 'Admin' role can execute xp_cmdshell. And there's
> really not any options.
> What are you attempting to accomplish?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
>[/vbcol]|||On Thu, 13 Jul 2006 10:06:15 +0800, "angi" <angi@.news.microsoft.com>
wrote:

>Thanks!
>It's too bad that only Admin role can execute it.
>I write a store procedure and use xp_cmdshell to execute .exe file on
>localhost.
>Casue the user include non-Admin role, so I want to know is any way that
>user can execute xp_cmdshell.
>Any good idea? Thanks!
>Angi
I worked around this on SQL Server 7.0, not sure I ever tried it on
2000. I can't say that I played with it any time recently.
I created a proc in master. That proc executed xp_cmdshell. I then
granted exec on that proc to public.
create proc dbo.TestThis
as
exec master..xp_cmdshell 'dir'
GO
GRANT EXECUTE ON [dbo].[TestThis] TO [public]
GO
Of course in production I would rather not grant anything to public.
Roy Harvey
Beacon Falls, CT|||Perhaps if you gave us more information about what you are attempting to
accomplish we might be of better help.
Personally, I can't imagine why you would want to do something so
'dangerous' and in contravention to 'Best Practices'. More information might
help us understand. (Remember, if a user and execute xp_cmdshell, the user
could do the following:
EXECUTE xp_cmdshell "net stop MSSQLServer"
EXECUTE xp_cmdshell "Format C:"
Why would you ever give anyone other than an administrator that much power
over your server?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"angi" <angi@.news.microsoft.com> wrote in message
news:%23bPijJipGHA.4268@.TK2MSFTNGP04.phx.gbl...
> Oh, I'm use SQL Server 2005!
> Any way can let user execute " xp_cmdshell" ?
> Thanks!
> Angi
> "Arnie Rowland" <arnie@.1568.com>
> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...
>|||Thanks Roy and Arnie so concern about this issue.
Why am I attempting to accomplish this dangerous issue?
Because I want to show a image on Report Server and Where is the image
source?
The image source comes from store procedure to execute a .exe file and the
.exe file gets some kinds of user parameter to produce their own image.
After produce the image file then bluk insert the image file as binary data
type into database.
Last, user from Report Server to see their own image information.
During these processes, the most critical is use xp_cmdshell to execute the
.exe file.
I post procedure code as fellow, hope will help.
And thanks for help me 'Discovery' the Best Practices! ^^
Appreciate!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE dbo.spImportGauge
(
@.GaugeType NVARCHAR(10),
@.Orgn NVARCHAR(20),
@.KpiParent NVARCHAR(10),
@.KpiUserMode NVARCHAR (5),
@.GaugeApPath NVARCHAR(200), --Gauge AP's location
@.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and same
as fmt\bcp.fmt's location
)
AS
DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
DECLARE @.FileSize NVARCHAR( 20) --save image file size
DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
DECLARE @.Header NVARCHAR(200) --Bcp format's Header
DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
DECLARE @.FullText NVARCHAR(500)
DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
folder string
-- generate GUID as a unique image file name
SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
SET @.GaugeFileName = @.GaugeGUID + '.jpg'
--Import Image bcp.fmt file location
SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
--Check Gauge type
IF @.GaugeType = 'Gauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear2'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'miniGauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
--use XP_CMDSHELL to execute AP
EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
--use temp table to store @.ExeCmdString result
SET DATEFORMAT MDY
IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #DirList (FName NVARCHAR(2000))
--setting Import file's path and delete non-image information
SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
DELETE #DirList WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
--Import picture's FormatAreference Online Book's bcp
--ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/c0af54f5-ca4a-4995-a3a
4-0ce39c30ec38.htm --ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f566db3
e-0a3b-4a61-9c84-49f8d42f5760.htm SET @.Header = '1 SQLIMAGE 0
' SET @.Tailer = ' ""
2 data ""' --replace FileSize format SELECT @.FileSize = REPLACE((
SELECT SUBSTRING(FName,30,10) AS FileSize FROM #DirList),',','') --rebuild b
cp.fmt Format SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath EXEC Master..XP
_CMDS
HELL @.FullText , NO_OUTPUT SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath EXE
C Master..XP_CMDSHELL @.FullText , NO_OUTPUT SET @.FullText = '@.echo ' + @.Hea
der + @.FileSize + @.Tailer + ' >> ' +@.BcpFilePath EXEC Master..XP_CMDSHELL @.F
ullText , NO_OUTPUT --Files
is a physical table used to save Binary information TRUNCATE TABLE dbo.Files
--image INSERT to Files SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.
GaugeFilePath +@.GaugeFileName + '''' + ' WITH ( FORMATFILE = ''' + @.BcpF
ilePath + ''' ) ' EXEC SP_
EXECUTESQL @.FullText --delete physical image SET @.DelCmdString = 'del ' + @.
GaugeFilePath + @.GaugeFileName EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT --
clear all the files SELECT * FROM Files TRUNCATE TABLE Files DROP TABLE #Dir
ListGO"Arnie Rowland" <arni
e@.1568.com> glsD:%23m$1c4jpGHA.2464@.TK2MSFTNGP03.phx.gbl...> Perha
ps if you gave us more information about what you are attempting toaccomplis
h we might be of better help.>> Personally, I can't imagine why you would wa
nt to do something so'dange
rous' and in contravention to 'Best Practices'. More information mighthelp u
s understand. (Remember, if a user and execute xp_cmdshell, the usercould do
the following:>> EXECUTE xp_cmdshell "net stop MSSQLServer"> EXECUTE xp
_cmdshell "Format C:">> Why
would you ever give anyone other than an administrator that much powerover y
our server?>> --> Arnie Rowland*> "To be successful, your heart must accompa
ny your knowledge.">>>> "angi" <angi@.news.microsoft.com> wrote in messagenew
s:%23bPijJipGHA.4268@.TK2MS
FTNGP04.phx.gbl...>> Oh, I'm use SQL Server 2005!>> Any way can let user exe
cute " xp_cmdshell" ?>> Thanks!>>>> Angi>>>> "Arnie Rowland" <arnie@.1568.com
> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...>>> Only members of
the 'Admin' role can execu
te xp_cmdshell. And there'sreally not any options.>>>>>> What are you attemp
ting to accomplish?>>>>>> -->>> Arnie Rowland*>>> "To be successful, your he
art must accompany your knowledge.">>>>>>>>>>>> "angi" <angi@.news.microsoft.
com> wrote in messagenews:u
NhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...>>>> Hi>>>>>>>> I wonder to know Hot
to let SQL Server user (not Admin role) usexp_cmdshell?>>>> Cause the execut
ion response access denied.>>>> What should I do? Thanks!>>>>>>>> Angi>>>>>>
>>>>>>>>>>|||I repost the SP Code
CREATE PROCEDURE dbo.spImportGauge
(
@.GaugeType NVARCHAR(10),
@.Orgn NVARCHAR(20),
@.KpiParent NVARCHAR(10),
@.KpiUserMode NVARCHAR (5),
@.GaugeApPath NVARCHAR(200), --Gauge AP's location
@.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and same
as fmt\bcp.fmt's location
)
AS
DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
DECLARE @.FileSize NVARCHAR( 20) --save image file size
DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
DECLARE @.Header NVARCHAR(200) --Bcp format's Header
DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
DECLARE @.FullText NVARCHAR(500)
DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
folder string
-- generate GUID as a unique image file name
SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
SET @.GaugeFileName = @.GaugeGUID + '.jpg'
--Import Image bcp.fmt file location
SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
--Check Gauge type
IF @.GaugeType = 'Gauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear2'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'miniGauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
--use XP_CMDSHELL to execute AP
EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
--use temp table to store @.ExeCmdString result
SET DATEFORMAT MDY
IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #DirList (FName NVARCHAR(2000))
--setting Import file's path and delete non-image information
SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
DELETE #DirList WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
--Import picture's FormatAreference Online Book's bcp
SET @.Header = '1 SQLIMAGE 0 '
SET @.Tailer = ' "" 2 data ""'
--replace FileSize format
SELECT @.FileSize = REPLACE((SELECT SUBSTRING(FName,30,10) AS FileSize
FROM #DirList),',','')
--rebuild bcp.fmt Format
SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
SET @.FullText = '@.echo ' + @.Header + @.FileSize + @.Tailer + ' >> ' +
@.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
--Files is a physical table used to save Binary information
TRUNCATE TABLE dbo.Files
--image INSERT to Files
SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.GaugeFilePath +
@.GaugeFileName + '''' +
' WITH ( FORMATFILE = ''' + @.BcpFilePath + ''' ) '
EXEC SP_EXECUTESQL @.FullText
--delete physical image
SET @.DelCmdString = 'del ' + @.GaugeFilePath + @.GaugeFileName
EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT
--clear all the files
SELECT * FROM Files
TRUNCATE TABLE Files
DROP TABLE #DirList
GO|||The xp_cmdshell page in BOL
(http://msdn2.microsoft.com/en-us/library/ms175046.aspx) indicates that you
need CONTROL SERVER permissions to execute xp_cmdshell.
SQL Server Reporting Services may be the wrong tool to use to create custom
interactive images to display to the user. Most likely, that would be better
handled in a different client application.
If you have boxed yourself in and have no choice but to use xp_cmdshell,
then you will have to provide the users admin privileges over the entire
server -and I think that would be a major security issue and mistake.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"angi" <angi@.news.microsoft.com> wrote in message
news:OOjhca7rGHA.2240@.TK2MSFTNGP04.phx.gbl...
>I repost the SP Code
>
> CREATE PROCEDURE dbo.spImportGauge
> (
> @.GaugeType NVARCHAR(10),
> @.Orgn NVARCHAR(20),
> @.KpiParent NVARCHAR(10),
> @.KpiUserMode NVARCHAR (5),
> @.GaugeApPath NVARCHAR(200), --Gauge AP's location
> @.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and
> same as fmt\bcp.fmt's location
> )
> AS
> DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
> DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
> DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
> DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
> DECLARE @.FileSize NVARCHAR( 20) --save image file size
> DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
> DECLARE @.Header NVARCHAR(200) --Bcp format's Header
> DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
> DECLARE @.FullText NVARCHAR(500)
> DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
> folder string
>
> -- generate GUID as a unique image file name
> SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
> SET @.GaugeFileName = @.GaugeGUID + '.jpg'
> --Import Image bcp.fmt file location
> SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
> --Check Gauge type
> IF @.GaugeType = 'Gauge'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'Linear'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'Linear2'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'miniGauge'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
>
> --use XP_CMDSHELL to execute AP
> EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
> --use temp table to store @.ExeCmdString result
> SET DATEFORMAT MDY
> IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
> DROP TABLE #DirList
> CREATE TABLE #DirList (FName NVARCHAR(2000))
> --setting Import file's path and delete non-image information
> SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
> INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
> DELETE #DirList WHERE
> SUBSTRING(FName,1,2) < '00' OR
> SUBSTRING(FName,1,2) > '99' OR
> FName IS NULL OR
> FName LIKE '%<DIR>%'
>
> --Import picture's FormatAreference Online Book's bcp
> SET @.Header = '1 SQLIMAGE 0 '
> SET @.Tailer = ' "" 2 data ""'
> --replace FileSize format
> SELECT @.FileSize = REPLACE((SELECT SUBSTRING(FName,30,10) AS FileSize
> FROM #DirList),',','')
> --rebuild bcp.fmt Format
> SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> SET @.FullText = '@.echo ' + @.Header + @.FileSize + @.Tailer + ' >> ' +
> @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> --Files is a physical table used to save Binary information
> TRUNCATE TABLE dbo.Files
> --image INSERT to Files
> SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.GaugeFilePath +
> @.GaugeFileName + '''' +
> ' WITH ( FORMATFILE = ''' + @.BcpFilePath + ''' ) '
> EXEC SP_EXECUTESQL @.FullText
> --delete physical image
> SET @.DelCmdString = 'del ' + @.GaugeFilePath + @.GaugeFileName
> EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT
> --clear all the files
> SELECT * FROM Files
> TRUNCATE TABLE Files
> DROP TABLE #DirList
> GO
>|||Have you explored the following (source is BooksOnline):
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in whic
h
the SQL Server service is running. When the user is not a member of the
sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server.
"angi" wrote:

> Hi
> I wonder to know Hot to let SQL Server user (not Admin role) use
> xp_cmdshell?
> Cause the execution response access denied.
> What should I do? Thanks!
> Angi
>
>

How to know a Server IP address?

Hi
I read that with the next instruction in the SQL Query Analyzer I can know
the IP address of my actual server:
exec master..xp_cmdshell 'ipconfig'
However, it shows 9 rows with additional information I don't want.
I just want the IP address.
How can I do this?
Thanks
Gonzalo,
PING? but will return 3 rows as well. Possibly xp_regread (undoc in Master
db) to read key containing IP address?
HTH
Jerry
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:u1eJ0p10FHA.3924@.TK2MSFTNGP14.phx.gbl...
> Hi
> I read that with the next instruction in the SQL Query Analyzer I can know
> the IP address of my actual server:
> exec master..xp_cmdshell 'ipconfig'
> However, it shows 9 rows with additional information I don't want.
> I just want the IP address.
> How can I do this?
> Thanks
>
|||Gonzalo Torres wrote:
> Hi
> I read that with the next instruction in the SQL Query Analyzer I can
> know the IP address of my actual server:
> exec master..xp_cmdshell 'ipconfig'
> However, it shows 9 rows with additional information I don't want.
> I just want the IP address.
> How can I do this?
> Thanks
Insert the output from xp_cmdshell into a temp table and parse it as
needed. Run IPCONFIG from the command prompt with the "/?" parameter for
a list of options that may help you reduce its output.
David Gugick
Quest Software
www.imceda.com
www.quest.com

How to know a Server IP address?

Hi
I read that with the next instruction in the SQL Query Analyzer I can know
the IP address of my actual server:
exec master..xp_cmdshell 'ipconfig'
However, it shows 9 rows with additional information I don't want.
I just want the IP address.
How can I do this?
ThanksGonzalo,
PING? but will return 3 rows as well. Possibly xp_regread (undoc in Master
db) to read key containing IP address?
HTH
Jerry
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:u1eJ0p10FHA.3924@.TK2MSFTNGP14.phx.gbl...
> Hi
> I read that with the next instruction in the SQL Query Analyzer I can know
> the IP address of my actual server:
> exec master..xp_cmdshell 'ipconfig'
> However, it shows 9 rows with additional information I don't want.
> I just want the IP address.
> How can I do this?
> Thanks
>|||Gonzalo Torres wrote:
> Hi
> I read that with the next instruction in the SQL Query Analyzer I can
> know the IP address of my actual server:
> exec master..xp_cmdshell 'ipconfig'
> However, it shows 9 rows with additional information I don't want.
> I just want the IP address.
> How can I do this?
> Thanks
Insert the output from xp_cmdshell into a temp table and parse it as
needed. Run IPCONFIG from the command prompt with the "/?" parameter for
a list of options that may help you reduce its output.
David Gugick
Quest Software
www.imceda.com
www.quest.com