Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL que

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Up

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

Just because you have 6GB of free disk space does not mean that disk space is available to the log file. What are the growth options? I strongly believe is very bad practice to rely on auto-growth of data or log files. It can seriously impact performance of a system, so should be actively managed rather than forgotten.

|||

Thanks all.

Tomorrow

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL que

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Up

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

Just because you have 6GB of free disk space does not mean that disk space is available to the log file. What are the growth options? I strongly believe is very bad practice to rely on auto-growth of data or log files. It can seriously impact performance of a system, so should be actively managed rather than forgotten.

|||

Thanks all.

Tomorrow

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Up

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

Just because you have 6GB of free disk space does not mean that disk space is available to the log file. What are the growth options? I strongly believe is very bad practice to rely on auto-growth of data or log files. It can seriously impact performance of a system, so should be actively managed rather than forgotten.

|||

Thanks all.

Tomorrow

Help! The IIF Statement in a query...

Part of the where clause in my SQL Statement is conditional. The query clause is as follows:

SELECT...

FROM...

WHERE PROJECT.COMPLETED<>-1 AND IIF(PROJECT.COST > RANGE.MINRANGE AND PROJECT.COST<RANGE.MAXRANGE , PROJECTRANGE.PROJECTRANGEID <>0 ,NULL)

I guess I didn't translate the if satement correctly because I always got errors when I tried to preview my report.

Need help analyzing the if statement for me. Thanks in advance.

What exactly are you trying to do?

Besides, the IIF you have is not syntactically correctn. IIF(<condition>, Expression if the condition is TRUE, Expression if the condition is FALSE). What you have is IIF( <condition>, <Condition>, <Value>) which is incorrect.

|||

Thanks for reply ndinakar. Here is what I am trying to do:

In the if statement, if ProjectCompleted is true(-1 means false), and if project.cost is greater than minimum range and less than max range, then the where clause should be like the following:

WHERE PROJECT.COMPLETED<>-1 ANDRANGE.PROJECTRANGEID <>0

If the Project.Cost is out of the range of minimum and max range (greater than max range or less than minimum range), then the if statement should not return anything, and the where clause will be like this:

WHERE PROJECT.COMPLETED<>-1

|||

I think I understand your question only partially. So what do you mean when you say return nothing if cost is out of the range? Do you still want to see those records or they should not be in the result set? You can probabbly put a filter on the record set accodringly.

|||Not sure if this will help but it looks to me like you are mixing your languages. IIF is for use in expressions in reporting services table cells etc. In SQL you have to use IF with BEGIN and END for your conditional statements. Have a look at this link which I found very usefulhttp://www.databasejournal.com/features/mssql/article.php/3361651sql

HELP! Stored Procedure Problem

I need to pull distinct records in my SP, but if there are different values in some of the columns it grabs those records too. I tried a nested query but I get an error that it is returning more than one value and not allowed.

I can grab the 1st record for each cust I need like this in a view:

SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad
FROM dbo.PropReportData
GROUP BY vcCustId, vcAdsource
ORDER BY vcCustId

and then Inner Join it in my SP like this:

CREATE PROCEDURE Reports_GetReportData
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT *
FROM dbo.vPrePull INNER JOIN
 dbo.PropReportData ON dbo.vPrePull.MinRecNo = dbo.PropReportData.siEntry
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GO

Now, the problem is this, the parameters are in my stored procedure which is parsed second. So I am not getting the unique data to pull from.

Ultimately what I need to use is below with something inside it that will do what the view above did:

CREATE PROCEDURE Reports_GetReportData2
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad,sientry,vcProspectName,
vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,vcComments
FROM dbo.PropReportData
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GROUP BY vcCustId, vcAdsource,sientry,vcProspectName,vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,
vcComments
ORDER BY vcCustId
GO

Got to have this done by C.O.B. Monday or I may not have a job.
Thanks.What you want seems doable, but some more info (structure of the tables, some sample data) would help.

Not that I feel pressured or anything here...|||I'm using it to create reports for my CRM application and I used the Reports Starter Kit as my base. I am displaying Each Ad Source the Prospect called in on and the Prospects Record data in a tabular report. In the footer of each Ad Source it gives a count of the total leads that came in on that Ad Source. The problem is this, when a change is made it creates another record for that prospect with a unique record number; this has to be for history purposes, because I have another report that display all record activity, (that one works). So when I display the Prospect records it has a duplicate, which I can cull out by using vcCustID, MIN(siEntry) this gives me the first record entry for that Prospect, BUT, if one of the fields that I am trying to display was changed (like Visit Date in the example below), another instance of the Prospect is displayed. So for example I have a count of 2 unique leads by Ad Source and they call back and change their Visit Date, it will display a count of 2 leads (which is correct) and display 3 records (wrong), showing that Prospect twice.

Example:
ApartmentGuide.com
Prospect Name.Telephone.Email.DesiredHome.Move-In Date.Status.Visit Date
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/23/2003
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/26/2003
Walker, Johnny 555-645-7895 drunk@.booze.com 1 x 1 10/23/2003 Visit Set 10/23/2003

Total Leads this Ad Source: 2

I am pulling the data from a single table called PropReportData that has just the info I need for reporting. This was necessary because the information necessary to create a report is in 9 different tables and the amount of executes necessary to do the inner joins caused major performance issues and after running an execution plan it just didn't seem feasible to continue in that direction.

The table has vcCustID(Unique), cApartmentSite(used to associate Client to Cust to), siEntry(Unique Record Number), Ad Source, etc.

The last Stored Procedure in my first post is what I need to work, it has the parameters in it I need to display client specific data, which uses cApartmentSite. The value is picked up from the UserLogin and put in session to be used with my parameters and a few other things.

Each Prospect record created by my Marketing Associates for that client has this value inserted in a field in the PropReportData table creating the Client to Prospect relationship. So when the client logs it only pulls their information based on the cApartmentSite value in the tables.

Let me know if you need more info.

Thanks.|||This seems to work:

CREATE PROCEDURE Reports_PainInTheButt
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT DISTINCT
 TOP 100 PERCENT dbo.PropReportData.vcCustId, MIN(DISTINCT dbo.PropReportData.siEntry) AS siEntry,
 MIN(DISTINCT dbo.PropReportData.vcAdsource) AS vcAdsource,
 MIN(DISTINCT dbo.PropReportData.vcProspectName) AS vcProspectName, MIN(DISTINCT dbo.PropReportData.vcPhone) AS vcPhone,
 MIN(DISTINCT dbo.PropReportData.vcEmail) AS vcEmail, MIN(DISTINCT dbo.PropReportData.vcDesiredHome) AS vcDesiredHome,
 MIN(DISTINCT dbo.PropReportData.vcMoveInDate) AS vcMoveInDate, MIN(DISTINCT dbo.PropReportData.vcStatus) AS vcStatus,
 MIN(DISTINCT dbo.PropReportData.vcVisitDate) AS vcVisitDate, MIN(DISTINCT dbo.PropReportData.vcComments) AS vcComments
FROM dbo.PropReportData
WHERE dbo.PropReportData.cApartmentSite = @.cApartmentSite AND dbo.PropReportData.vcAdsource = @.vcAdSource
GROUP BY dbo.PropReportData.vcCustId
ORDER BY dbo.PropReportData.vcCustId
GO

If you see any potential problems with this let me know, it's all I can come up with.

Thanks.

Help! SQL Server 2000 extended stored procedure hangs in Windows 98

I am trying to run xp_cmdshell from the Query Analyzer using SQL
Server 2000 running on Windows 98.

It seems like it should be simple - I'm typing

xp_cmdshell 'dir *.exe'

in the Query Analyzer in the Master db. I'm logged in as sa.

The timer starts running and never stops. No error message.

Can anyone PLEASE help me with this? Any suggestions would be
appreciated. Are SQL Server 2000 extended stored procedures not
supported in Windows 98? I've tried searching the Knowledge Base but
can't find anything.

Thanks!sylmart7 (sylmart7@.aol.com) writes:
> I am trying to run xp_cmdshell from the Query Analyzer using SQL
> Server 2000 running on Windows 98.
> It seems like it should be simple - I'm typing
> xp_cmdshell 'dir *.exe'
> in the Query Analyzer in the Master db. I'm logged in as sa.
> The timer starts running and never stops. No error message.
> Can anyone PLEASE help me with this? Any suggestions would be
> appreciated. Are SQL Server 2000 extended stored procedures not
> supported in Windows 98? I've tried searching the Knowledge Base but
> can't find anything.

As I have no experience at all of Windows 98, I cannot really help. What
I can say, though, after having read the topic on xp_cmdshell in Books
Online is: yes, xp_cmdshell is supported on Win98. The article mentions
several restrictions with regards to security context and return value
on Win 98, so obviously you should be able to use it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help! sp_dboption

I was trying to set a database to single user only but it
was taking some time (about a minute) so I cancelled the
query.
It is still attempting to cancel 6 mintes later...
any ideas?Hi ,
If it is sql 2000, execute:-
Alter database <dbname> set single_user with rollback immediate
If it is SQL 7 or older version:-
Kill all the users connected to the database first then use sp_dboption
sp_dboption 'dbname','single user',true
--
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2810701c46417$0328d6d0$a401280a@.phx.gbl...
> I was trying to set a database to single user only but it
> was taking some time (about a minute) so I cancelled the
> query.
> It is still attempting to cancel 6 mintes later...
> any ideas?|||i eventually got a connection broken error message.
problem is I can't access the database now either through
query analyser or enterprise manager.
>--Original Message--
>Hi ,
>If it is sql 2000, execute:-
>Alter database <dbname> set single_user with rollback
immediate
>If it is SQL 7 or older version:-
>Kill all the users connected to the database first then
use sp_dboption
>sp_dboption 'dbname','single user',true
>--
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:2810701c46417$0328d6d0$a401280a@.phx.gbl...
>> I was trying to set a database to single user only but
it
>> was taking some time (about a minute) so I cancelled the
>> query.
>> It is still attempting to cancel 6 mintes later...
>> any ideas?
>
>.
>|||There may be someone else, who is the single user... Do an sp_who and see
who that is and perhaps kill them
--
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
<anonymous@.discussions.microsoft.com> wrote in message
news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
> i eventually got a connection broken error message.
> problem is I can't access the database now either through
> query analyser or enterprise manager.
> >--Original Message--
> >Hi ,
> >
> >If it is sql 2000, execute:-
> >
> >Alter database <dbname> set single_user with rollback
> immediate
> >
> >If it is SQL 7 or older version:-
> >
> >Kill all the users connected to the database first then
> use sp_dboption
> >
> >sp_dboption 'dbname','single user',true
> >
> >--
> >Thanks
> >Hari
> >MCDBA
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:2810701c46417$0328d6d0$a401280a@.phx.gbl...
> >> I was trying to set a database to single user only but
> it
> >> was taking some time (about a minute) so I cancelled the
> >> query.
> >>
> >> It is still attempting to cancel 6 mintes later...
> >>
> >> any ideas?
> >
> >
> >.
> >|||no other users except me...i can;t access the database at
all now.
>--Original Message--
>There may be someone else, who is the single user... Do
an sp_who and see
>who that is and perhaps kill them
>--
>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
><anonymous@.discussions.microsoft.com> wrote in message
>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
>> i eventually got a connection broken error message.
>> problem is I can't access the database now either
through
>> query analyser or enterprise manager.
>> >--Original Message--
>> >Hi ,
>> >
>> >If it is sql 2000, execute:-
>> >
>> >Alter database <dbname> set single_user with rollback
>> immediate
>> >
>> >If it is SQL 7 or older version:-
>> >
>> >Kill all the users connected to the database first then
>> use sp_dboption
>> >
>> >sp_dboption 'dbname','single user',true
>> >
>> >--
>> >Thanks
>> >Hari
>> >MCDBA
>> >
>> >
>> ><anonymous@.discussions.microsoft.com> wrote in message
>> >news:2810701c46417$0328d6d0$a401280a@.phx.gbl...
>> >> I was trying to set a database to single user only
but
>> it
>> >> was taking some time (about a minute) so I cancelled
the
>> >> query.
>> >>
>> >> It is still attempting to cancel 6 mintes later...
>> >>
>> >> any ideas?
>> >
>> >
>> >.
>> >
>
>.
>|||anon,
What error do you get? What status is the database in? Run this in master:
select status from sysdatabases where name = <yourdbname>
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
anonymous@.discussions.microsoft.com wrote:
> no other users except me...i can;t access the database at
> all now.
>>--Original Message--
>>There may be someone else, who is the single user... Do
> an sp_who and see
>>who that is and perhaps kill them
>>--
>>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
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
>>i eventually got a connection broken error message.
>>problem is I can't access the database now either
> through
>>query analyser or enterprise manager.
>>
>>--Original Message--
>>Hi ,
>>If it is sql 2000, execute:-
>>Alter database <dbname> set single_user with rollback
>>immediate
>>If it is SQL 7 or older version:-
>>Kill all the users connected to the database first then
>>use sp_dboption
>>sp_dboption 'dbname','single user',true
>>--
>>Thanks
>>Hari
>>MCDBA
>>
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:2810701c46417$0328d6d0$a401280a@.phx.gbl...
>>I was trying to set a database to single user only
> but
>>it
>>was taking some time (about a minute) so I cancelled
> the
>>query.
>>It is still attempting to cancel 6 mintes later...
>>any ideas?
>>
>>.
>>
>>.|||status is 12
BTW I am using SQL 7.0
>--Original Message--
>anon,
>What error do you get? What status is the database in?
Run this in master:
>select status from sysdatabases where name = <yourdbname>
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>anonymous@.discussions.microsoft.com wrote:
>> no other users except me...i can;t access the database
at
>> all now.
>>--Original Message--
>>There may be someone else, who is the single user... Do
>> an sp_who and see
>>who that is and perhaps kill them
>>--
>>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
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
>>i eventually got a connection broken error message.
>>problem is I can't access the database now either
>> through
>>query analyser or enterprise manager.
>>
>>--Original Message--
>>Hi ,
>>If it is sql 2000, execute:-
>>Alter database <dbname> set single_user with rollback
>>immediate
>>If it is SQL 7 or older version:-
>>Kill all the users connected to the database first
then
>>use sp_dboption
>>sp_dboption 'dbname','single user',true
>>--
>>Thanks
>>Hari
>>MCDBA
>>
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:2810701c46417$0328d6d0$a401280a@.phx.gbl...
>>I was trying to set a database to single user only
>> but
>>it
>>was taking some time (about a minute) so I cancelled
>> the
>>query.
>>It is still attempting to cancel 6 mintes later...
>>any ideas?
>>
>>.
>>
>>.
>.
>|||OK, now what error do you get when you type in
use <mydb>
in Query Analyzer?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
anonymous@.discussions.microsoft.com wrote:
> status is 12
> BTW I am using SQL 7.0
>>--Original Message--
>>anon,
>>What error do you get? What status is the database in?
> Run this in master:
>>select status from sysdatabases where name = <yourdbname>
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602.html
>>
>>anonymous@.discussions.microsoft.com wrote:
>>no other users except me...i can;t access the database
> at
>>all now.
>>
>>--Original Message--
>>There may be someone else, who is the single user... Do
>>an sp_who and see
>>
>>who that is and perhaps kill them
>>--
>>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
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
>>
>>i eventually got a connection broken error message.
>>problem is I can't access the database now either
>>through
>>
>>query analyser or enterprise manager.
>>
>>--Original Message--
>>Hi ,
>>If it is sql 2000, execute:-
>>Alter database <dbname> set single_user with rollback
>>immediate
>>
>>If it is SQL 7 or older version:-
>>Kill all the users connected to the database first
> then
>>use sp_dboption
>>
>>sp_dboption 'dbname','single user',true
>>--
>>Thanks
>>Hari
>>MCDBA
>>
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:2810701c46417$0328d6d0$a401280a@.phx.gbl...
>>
>>>I was trying to set a database to single user only
>>but
>>
>>it
>>
>>>was taking some time (about a minute) so I cancelled
>>the
>>
>>>query.
>>>
>>>It is still attempting to cancel 6 mintes later...
>>>
>>>any ideas?
>>
>>.
>>
>>.
>>
>>.|||takes a while to run this, so i stopped it - the database
is a test db, but it is sitting on the production server.
i don't want to run anything that might crash the server.
what is the 'safest' thing to do?
>--Original Message--
>OK, now what error do you get when you type in
>use <mydb>
>in Query Analyzer?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>anonymous@.discussions.microsoft.com wrote:
>> status is 12
>> BTW I am using SQL 7.0
>>--Original Message--
>>anon,
>>What error do you get? What status is the database in?
>> Run this in master:
>>select status from sysdatabases where name =<yourdbname>
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602.html
>>
>>anonymous@.discussions.microsoft.com wrote:
>>no other users except me...i can;t access the database
>> at
>>all now.
>>
>>--Original Message--
>>There may be someone else, who is the single user...
Do
>>an sp_who and see
>>
>>who that is and perhaps kill them
>>--
>>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
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:27fb401c46419$10b11fe0$a501280a@.phx.gbl...
>>
>>i eventually got a connection broken error message.
>>problem is I can't access the database now either
>>through
>>
>>query analyser or enterprise manager.
>>
>>>--Original Message--
>>>Hi ,
>>>
>>>If it is sql 2000, execute:-
>>>
>>>Alter database <dbname> set single_user with
rollback
>>immediate
>>
>>>If it is SQL 7 or older version:-
>>>
>>>Kill all the users connected to the database first
>> then
>>use sp_dboption
>>
>>>sp_dboption 'dbname','single user',true
>>>
>>>--
>>>Thanks
>>>Hari
>>>MCDBA
>>>
>>>
>>><anonymous@.discussions.microsoft.com> wrote in
message
>>>news:2810701c46417$0328d6d0$a401280a@.phx.gbl...
>>>
>>>
>>>I was trying to set a database to single user only
>>but
>>
>>it
>>
>>>was taking some time (about a minute) so I
cancelled
>>the
>>
>>>query.
>>>
>>>It is still attempting to cancel 6 mintes later...
>>>
>>>any ideas?
>>>
>>>
>>>.
>>>
>>
>>.
>>
>>.
>.
>|||Can you try detaching and attaching the database? You can do this in EM,
or using sp_detach_db, sp_attach_db - look them up in BOL.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
anonymous@.discussions.microsoft.com wrote:
> takes a while to run this, so i stopped it - the database
> is a test db, but it is sitting on the production server.
> i don't want to run anything that might crash the server.
> what is the 'safest' thing to do?
>|||just checked the SQL logs, and the following error
appeared first:
Could not open FCB for invalid file ID 49154 in database
<dbname>. Table or database may be corrupted..
Since then, getting the following error:
Time out occurred while waiting for buffer latch type 2,
bp 0x14b7be40, page (49154:-1325224958), stat 0x405,
object ID 10:641697634:0, waittime 5000. Continuing to
wait.
>--Original Message--
>Can you try detaching and attaching the database? You can
do this in EM,
>or using sp_detach_db, sp_attach_db - look them up in BOL.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>anonymous@.discussions.microsoft.com wrote:
>> takes a while to run this, so i stopped it - the
database
>> is a test db, but it is sitting on the production
server.
>> i don't want to run anything that might crash the
server.
>> what is the 'safest' thing to do?
>>
>.
>|||Hi,
It seems the database is corrupted. You have to restore from the last known
good backup file.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2859d01c4643c$cbcb4c10$a401280a@.phx.gbl...
> just checked the SQL logs, and the following error
> appeared first:
> Could not open FCB for invalid file ID 49154 in database
> <dbname>. Table or database may be corrupted..
> Since then, getting the following error:
> Time out occurred while waiting for buffer latch type 2,
> bp 0x14b7be40, page (49154:-1325224958), stat 0x405,
> object ID 10:641697634:0, waittime 5000. Continuing to
> wait.
> >--Original Message--
> >Can you try detaching and attaching the database? You can
> do this in EM,
> >or using sp_detach_db, sp_attach_db - look them up in BOL.
> >--
> >Mark Allison, SQL Server MVP
> >http://www.markallison.co.uk
> >
> >Looking for a SQL Server replication book?
> >http://www.nwsu.com/0974973602.html
> >
> >
> >
> >anonymous@.discussions.microsoft.com wrote:
> >> takes a while to run this, so i stopped it - the
> database
> >> is a test db, but it is sitting on the production
> server.
> >> i don't want to run anything that might crash the
> server.
> >> what is the 'safest' thing to do?
> >>
> >>
> >.
> >

Help! Query Question.

Hi all,
I'm new to SQL programming, and I am having a hard time figuring this one ou
t.
I have a table containing the following information:
Activity Cost Account Hours
1 A 100
1 B 200
1 C 250
1 D 100
2 A 600
2 F 200
3 B 100
3 C 200
3 D 400
I would like to create a view that will show the Cost Account with the
highest hours per activity. For the table above, the end result would be
something like this.
Activity Cost Account
1 C
2 A
3 D
I hope this makes sense. Can someone please help me on how to write the SQL
statement for this? Thank you very much in advance!
RogerI'm not sure what you want to do in the case where 2 accounts have the same
number of hours (tied for the most). I'm going to assume you want two rows
returned.
SELECT outer_table.Activity, derived_table.[Cost Account]
From Unnamed_table as outer_table
INNER JOIN
(SELECT Activity, MAX(Hours) as Hours
FROM Unnamed_table
GROUP BY Activity) as derived_table
ON outer_table.Activity = derived_table.Activity
AND outer_table.Hours = derived_table.Hours
ORDER BY Activity
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:

> Hi all,
> I'm new to SQL programming, and I am having a hard time figuring this one
out.
> I have a table containing the following information:
> Activity Cost Account Hours
> 1 A 100
> 1 B 200
> 1 C 250
> 1 D 100
> 2 A 600
> 2 F 200
> 3 B 100
> 3 C 200
> 3 D 400
> I would like to create a view that will show the Cost Account with the
> highest hours per activity. For the table above, the end result would be
> something like this.
> Activity Cost Account
> 1 C
> 2 A
> 3 D
> I hope this makes sense. Can someone please help me on how to write the S
QL
> statement for this? Thank you very much in advance!
> Roger|||Do:
SELECT t1.activity, t1.Cost
FROM tbl t1
WHERE t1.hours = ( SELECT MAX( t2.hours )
FROM tbl t2
WHERE t2.Activity = t1.Activity )
ORDER BY t1.Activity ;
-- Or
SELECT t1.activity, t1.Cost
FROM tbl t1
INNER JOIN ( SELECT Activity, MAX( hours )
FROM tbl
GROUP BY Activity ) t2 ( Activity, hours )
ON t1.Activity = t2.Activity
AND t1.hours = t2.hours
ORDER BY t1.activity ;
Anith|||Hi Anith,
Thank you for the quick response. I am a little about the SQL
statement. I only have one table and I want to create a view from that
table. But your SQL statement has 2 tables. Can you please clarify? I
think maybe most post wasn't clear. Thank you very much.
Roger
"Anith Sen" wrote:

> Do:
> SELECT t1.activity, t1.Cost
> FROM tbl t1
> WHERE t1.hours = ( SELECT MAX( t2.hours )
> FROM tbl t2
> WHERE t2.Activity = t1.Activity )
> ORDER BY t1.Activity ;
> -- Or
>
> SELECT t1.activity, t1.Cost
> FROM tbl t1
> INNER JOIN ( SELECT Activity, MAX( hours )
> FROM tbl
> GROUP BY Activity ) t2 ( Activity, hours )
> ON t1.Activity = t2.Activity
> AND t1.hours = t2.hours
> ORDER BY t1.activity ;
> --
> Anith
>
>|||Did you try to execute the SQL that either of us posted?
It returns what you want.
Anith gave 2 versions.
The first is called a correlated subquery. But, it basically joining your
table to a sql statement against the same table to figure out the max per
Activity.
The second (which is the same as what I posted) is called a derived table or
inline view. Basically, on the fly create a table that contains the activit
y
and its maximum hours from all rows for that activity. Then join this
derived table with the regular table, to only find the rows that match the
activity and the max hours found in the derived table.
There is no way to do what you want with SQL with only the one table in the
FROM. Instead you need join your table to some sql that gives you some
specific information about your table in this case, you need to figure out
the maximum hours for each activity so that you can figure out the correct
rows to return from your table.
My solution, and both of Anith's solution would work fine as the source of a
view to do exactly what you are trying to do.
Hope this makes sense.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi Anith,
> Thank you for the quick response. I am a little about the SQL
> statement. I only have one table and I want to create a view from that
> table. But your SQL statement has 2 tables. Can you please clarify? I
> think maybe most post wasn't clear. Thank you very much.
> Roger
>
> "Anith Sen" wrote:
>|||Hi Ryan,
Thank you for the response. Yes, I want to show both cost accounts if there
is a tie. I am a little about the outer_table and derived_table.
Supposed my table is called t1, how would the SQL be written? Thank you ver
y
much.
Roger
"Ryan Powers" wrote:
> I'm not sure what you want to do in the case where 2 accounts have the sam
e
> number of hours (tied for the most). I'm going to assume you want two row
s
> returned.
> SELECT outer_table.Activity, derived_table.[Cost Account]
> From Unnamed_table as outer_table
> INNER JOIN
> (SELECT Activity, MAX(Hours) as Hours
> FROM Unnamed_table
> GROUP BY Activity) as derived_table
> ON outer_table.Activity = derived_table.Activity
> AND outer_table.Hours = derived_table.Hours
> ORDER BY Activity
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Yoyo" wrote:
>|||just replace unnamed_table with t1.
as in
SELECT outer_table.Activity, derived_table.[Cost Account]
From t1 as outer_table
INNER JOIN
(SELECT Activity, MAX(Hours) as Hours
FROM t1
GROUP BY Activity) as derived_table
ON outer_table.Activity = derived_table.Activity
AND outer_table.Hours = derived_table.Hours
ORDER BY Activity
Also, see my other post to you. I tried to explain the derived table and
Anith's correlated subquery to you. Neither of which is easy to explain in
a
couple sentences if you are new.
But, basically here is another attempt
Think of the table within the () as its own table completely separate from
your t1.
(SELECT Activity, MAX(Hours) as Hours
FROM t1
GROUP BY Activity)
This will give you each activity and the max hours for that activity. One
row per activity.
Now you join your table t1 to that new table (which I called derived_table)
on both activity and hours. This helps you identify the rows in t1 that you
are interested in.
Hope this helps clear it up.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi Ryan,
> Thank you for the response. Yes, I want to show both cost accounts if the
re
> is a tie. I am a little about the outer_table and derived_table.
> Supposed my table is called t1, how would the SQL be written? Thank you v
ery
> much.
> Roger
> "Ryan Powers" wrote:
>|||Got it! Thank you very much!!!
"Ryan Powers" wrote:
> Did you try to execute the SQL that either of us posted?
> It returns what you want.
> Anith gave 2 versions.
> The first is called a correlated subquery. But, it basically joining your
> table to a sql statement against the same table to figure out the max per
> Activity.
> The second (which is the same as what I posted) is called a derived table
or
> inline view. Basically, on the fly create a table that contains the activ
ity
> and its maximum hours from all rows for that activity. Then join this
> derived table with the regular table, to only find the rows that match the
> activity and the max hours found in the derived table.
> There is no way to do what you want with SQL with only the one table in th
e
> FROM. Instead you need join your table to some sql that gives you some
> specific information about your table in this case, you need to figure out
> the maximum hours for each activity so that you can figure out the correct
> rows to return from your table.
> My solution, and both of Anith's solution would work fine as the source of
a
> view to do exactly what you are trying to do.
> Hope this makes sense.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Yoyo" wrote:
>

HELP! problem with queries in MS CRM

Hi, there,

i have this simple question.

I have MS CRM 3.0 installed on my work PC, when i try to get result from query like this

"select * from systemuserbase where fullname like '%Ж%'

SQL server returns me notthing.

i have many rows, containing words with this charcter in this column.

i don't understand.

In other DB this query would be fine and will return rows. What i miss?

thanks.

Is this an issue with Crystal? When I run the query below I seem to get the correct row:

Code Snippet

declare @.xample table (fullName varchar(20))
insert into @.xample
select 'Jon Ж McDaniel' union all
select 'John Q Public'
--select * from @.xample

select * from @.xample
where fullName like '%Ж%'

/*
fullName
--
Jon ? McDaniel
*/

|||

No, this table is standart table created from MS CRM and i don't know what whuld happend if i change column types. This is working system and i don't want to break it. Current column type is nvarchar. In other DB wich are not connected with CRM in same server i don't have problems. I have only one diference between those DB's it is coallition. In CRM it is Latin1_General_CI_AS, and in oder is Cyrillic_General_CI_AS.

I think the problem could be there. Is there way to run querys with different coallition, adn what is the right syntax?

HELP! Problem with data selection.

Hi,
People, help me with problem to make the query qExpectedRESULT
I will accept any suggestion and suppositions!
Possibly use of User Defined Functions it is a right way ?
-- START of DB Objects CREATE scripts --
CREATE TABLE [dbo].[Customers] (
[AG_ID] [int] IDENTITY (1, 1) NOT NULL ,
[AG_TYPE] [tinyint] NULL ,
[AG_STATE] [tinyint] NULL ,
[AG_CODE] [smallint] NULL ,
[AG_REG_NO] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[AG_REG_NAME] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[AG_REG_DATE] [datetime] NULL ,
[AG_PRINT_NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AG_SEARCH_NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[AG_CR_DATE] [datetime] NULL ,
[AG_MD_DATE] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DealPRICE] (
[DP_ID] [int] IDENTITY (1, 1) NOT NULL ,
[AG_ID] [int] NULL ,
[ART_ID] [int] NULL ,
[DP_DATE] [datetime] NULL ,
[DEAL_PRICE] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Items] (
[ART_ID] [int] IDENTITY (1, 1) NOT NULL ,
[ART_TYPE] [tinyint] NULL ,
[ART_STATE] [tinyint] NULL ,
[ART_FOLDER_ID] [int] NULL ,
[ART_MSK_ID] [int] NULL ,
[ART_DIN_ID] [int] NULL ,
[ART_LEVEL] [tinyint] NULL ,
[ART_INDEX] [smallint] NULL ,
[ART_NO] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[ART_NAME] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[ART_V1] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V2] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V3] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V4] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_V5] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ART_CR_DATE] [datetime] NULL ,
[ART_MD_DATE] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemsDIN] (
[DIN_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DIN_TYPE] [tinyint] NULL ,
[DIN_INDEX] [tinyint] NULL ,
[DIN_GROUP] [int] NULL ,
[DIN_NAME] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[DIN_ALTER] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_TEXT] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_TEXT_STR] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DIN_PRICE_TYPE] [tinyint] NULL ,
[DIN_PRICE_UP] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemsMASK] (
[MSK_ID] [int] IDENTITY (1, 1) NOT NULL ,
[MSK_TYPE] [tinyint] NULL ,
[MSK_INDEX] [smallint] NULL ,
[MSK_MAIN] [varchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[MSK_DESCRIPTION] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
[MSK_MASK] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[MSK_PART1] [int] NULL ,
[MSK_PART2] [int] NULL ,
[MSK_PART3] [int] NULL ,
[MSK_PART4] [int] NULL ,
[MSK_PART5] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Stores] (
[SKD_ID] [int] IDENTITY (1, 1) NOT NULL ,
[SKD_TYPE] [tinyint] NULL ,
[SKD_STATE] [tinyint] NULL ,
[SKD_ART_ID] [int] NULL ,
[SKD_UPDATED] [bit] NULL ,
[SKD_NOW_QUANT] [money] NULL ,
[SKD_NOW_REZRV] [money] NULL ,
[SKD_NOW_PREP] [money] NULL ,
[SKD_NOW_UNREG] [money] NULL ,
[SKD_NOW_MOD] [money] NULL ,
[SKD_NOW_NED] [money] NULL ,
[SKD_LIMIT_MIN] [money] NULL ,
[SKD_LIMIT_MAX] [money] NULL ,
[SKD_PRICE] [money] NULL ,
[SKD_LAST_SALE] [datetime] NULL ,
[SKD_CHG_DATE] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[AG_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DealPRICE] WITH NOCHECK ADD
CONSTRAINT [PK_DealPRICE] PRIMARY KEY CLUSTERED
(
[DP_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Items] WITH NOCHECK ADD
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[ART_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemsDIN] WITH NOCHECK ADD
CONSTRAINT [PK_ItemsDIN] PRIMARY KEY CLUSTERED
(
[DIN_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemsMASK] WITH NOCHECK ADD
CONSTRAINT [PK_ItemsMASK] PRIMARY KEY CLUSTERED
(
[MSK_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Stores] WITH NOCHECK ADD
CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED
(
[SKD_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DealPRICE] ADD
CONSTRAINT [FK_DealPRICE_Customers] FOREIGN KEY
(
[AG_ID]
) REFERENCES [dbo].[Customers] (
[AG_ID]
),
CONSTRAINT [FK_DealPRICE_Items] FOREIGN KEY
(
[ART_ID]
) REFERENCES [dbo].[Items] (
[ART_ID]
)
GO
ALTER TABLE [dbo].[Items] ADD
CONSTRAINT [FK_Items_ItemsDIN] FOREIGN KEY
(
[ART_DIN_ID]
) REFERENCES [dbo].[ItemsDIN] (
[DIN_ID]
),
CONSTRAINT [FK_Items_ItemsMASK] FOREIGN KEY
(
[ART_MSK_ID]
) REFERENCES [dbo].[ItemsMASK] (
[MSK_ID]
)
GO
ALTER TABLE [dbo].[Stores] ADD
CONSTRAINT [FK_Stores_Items] FOREIGN KEY
(
[SKD_ART_ID]
) REFERENCES [dbo].[Items] (
[ART_ID]
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.qBaseQUERY
AS
SELECT dbo.Items.*, dbo.Stores.*, dbo.ItemsDIN.DIN_NAME AS DIN_NAME,
dbo.ItemsMASK.MSK_PART1 AS MSK_PART1,
dbo.ItemsMASK.MSK_PART2 AS MSK_PART2,
dbo.ItemsMASK.MSK_PART3 AS MSK_PART3, dbo.ItemsMASK.MSK_PART4 AS MSK_PART4,
dbo.ItemsMASK.MSK_PART5 AS MSK_PART5
FROM dbo.Items LEFT OUTER JOIN
dbo.ItemsDIN ON dbo.Items.ART_DIN_ID =
dbo.ItemsDIN.DIN_ID LEFT OUTER JOIN
dbo.ItemsMASK ON dbo.Items.ART_MSK_ID =
dbo.ItemsMASK.MSK_ID LEFT OUTER JOIN
dbo.Stores ON dbo.Items.ART_ID = dbo.Stores.SKD_ART_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.qExpectedRESULT
AS
SELECT ART_ID, ART_NAME, SKD_NOW_QUANT, SKD_PRICE, 'from DealPRICE
table' AS DEAL_PRICE
FROM dbo.qBaseQUERY
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- END of DB Objects CREATE scripts --
-- Fill Tables ----
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerA','CustomerA','Customer
A')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerB','CustomerB','Customer
B')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerC','CustomerC','Customer
C')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerD','CustomerD','Customer
D')
INSERT INTO Customers(AG_REG_NAME, AG_PRINT_NAME, AG_SEARCH_NAME)
VALUES('CustomerE','CustomerE','Customer
E')
INSERT INTO Items(ART_NAME) VALUES('ItemA')
INSERT INTO Items(ART_NAME) VALUES('ItemB')
INSERT INTO Items(ART_NAME) VALUES('ItemC')
INSERT INTO Items(ART_NAME) VALUES('ItemD')
INSERT INTO Items(ART_NAME) VALUES('ItemE')
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(1,453,10.95)
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(3,675,15.95)
INSERT INTO Stores(SKD_ART_ID,SKD_NOW_QUANT,SKD_PRIC
E) VALUES(5,134,20.95)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,1,GETDATE(),10.55)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,2,GETDATE(),13)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(1,3,GETDATE(),13.5)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(2,3,GETDATE(),14.3)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(3,4,GETDATE(),15)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(4,5,GETDATE(),18.9)
INSERT INTO DealPRICE(AG_ID,ART_ID,DP_DATE,DEAL_PRIC
E)
VALUES(5,5,GETDATE(),19.1)
-- END of Fill Tables ---
Query qExpectedRESULT for Customers.AG_ID=1 must return the next list:
1 ItemA 453 10,95 10,55
2 ItemB NULL NULL 13
3 ItemC 675 15,95 13,5
4 ItemD NULL NULL NULL
5 ItemE 134 20,95 NULL
for Customers.AG_ID=2:
1 ItemA 453 10,95 NULL
2 ItemB NULL NULL NULL
3 ItemC 675 15,95 14.3
4 ItemD NULL NULL NULL
5 ItemE 134 20,95 NULL
In a real DB like:
Customers - 30 000 rows
Items - 25 000 rows
Stores - 15 000 rows
DealPRICE - 1 000 rows
im not select all 25000 rows:
SELECT qBaseQUERY.* -- base query
FROM qBaseQUERY -- about 25 000 rows
next text added on clients terminals depended on their needs, like:
WHERE qBaseQUERY.ART_MSK_ID=13 -- return 10...30 rows
AND ((((qBaseQUERY.MSK_PART1) = 1))
AND (((qBaseQUERY.ART_V1) = '100')))
AND ((((qBaseQUERY.MSK_PART2) = 3))
AND (((qBaseQUERY.ART_V2) = '050')))
AND ((((qBaseQUERY.MSK_PART3) = 12))
AND (((qBaseQUERY.ART_V3) = '058')))
AND ((((qBaseQUERY.MSK_PART4) = 11))
AND (((qBaseQUERY.ART_V4) = '001')))
AND ((((qBaseQUERY.MSK_PART5) = 36))
AND (((qBaseQUERY.ART_V5) = '105')))
AND (((qBaseQUERY.SKD_NOW_QUANT)>0) OR ((qBaseQUERY.SKD_NOW_UNREG)>0) )
ORDER BY qBaseQUERY.ART_FOLDER_ID, qBaseQUERY.ART_LEVEL,
qBaseQUERY.ART_INDEX
--
What do you think about it ?"Kachmaryk Yuriy" <kachya@.ua.fm> wrote in message
news:O39Wii2HGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> People, help me with problem to make the query qExpectedRESULT
> I will accept any suggestion and suppositions!
> Possibly use of User Defined Functions it is a right way ?
>
How about
SELECT B.ART_ID, B.ART_NAME, B.SKD_NOW_QUANT, B.SKD_PRICE, DP.DEAL_PRICE
FROM dbo.qBaseQUERY B
LEFT JOIN dbo.dealPRICE DP
ON B.ART_ID = DP.ART_ID
and AG_ID = 1
?
David

HELP! Passing parameters

Still need help passing a criteria parameter query from a SQL Function (i.e. @.StartDate) to a report header in Access Data Project where header = 'Transactions As Of [StartDate].

If anyone knows anywhere I can get help on this, I would really appreciate it. Thanks.

Could you explain this a bit more in detail ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Help! ISQL and DOS batch files

Hi all,

Can anyone help me?? I'm just a newb :D

Please consider the following:

I need to be able to query a db on server A, in a batch file, return the result (DateTime value) into a variable, and then use that date as a parameter in a query that I will query on server b.

I have the following code:

isql -E -d firstDB -S ServerA -Q "select max(load_date) from mydates"

How would I pipe the results of the above query into a variable?

I cannot create a linked server between the two servers. (Permissions)

Thanks in advance!My first suggestion would be to use either DTS or Perl instead of batch files. They simplify problems like this a bunch.

My next suggestion would be to use OSQL.EXE if you must use a batch file. It is better suited for many reasons than ISQL.EXE is.

You can send the OSQL.EXE output to a text file using the -o parameter. Once you get the data into a text file, you'll need to find a way to harvest it for use in the next query... This is where a scripting language like what exists in DTS or Perl really helps.

-PatP|||Thx, Pat.

I am also familiar with OSQL, but I've never used DTS. Looks like I have some reading to do this weekend :p

In the meantime, it's hokey, but I think I'm gonna BCP the data into the pubs db on the server as a temporary solution.

Thanks, I wouldn't have thought of it.|||If all you need is 1 parameter, then this would do:

osql -E -d firstDB -S ServerA -Q "select 'select * from other_server_table where load_date = ' + (select '''' + convert(char(10), max(load_date), 101) + '''' from mydates)" -o script.SQL

osql -E -d secondDB -S ServerB -i script.SQL|||Can you use OPENQUERY or is that also restricted? Can you use sp_Oacreate? See this link for an example of retrieving a single value with an ADODB connection and T-SQL.

http://www.davidpenton.com/testsite/scratch/dbo.sp_ExecuteAdodbScalar.txt|||If all you need is 1 parameter, then this would do:

osql -E -d firstDB -S ServerA -Q "select 'select * from other_server_table where load_date = ' + (select '''' + convert(char(10), max(load_date), 101) + '''' from mydates)" -o script.SQL

osql -E -d secondDB -S ServerB -i script.SQLNow that's just plain deviant ;)

I thought I was the only one that did perverse things like that, although I've been known to create whole scripts (breaking the 8K limit was my biggest challenge) that way!

It still isn't something I'd try to teach a newcomer, but it is a great thing to have lurking in one's bag of tricks.

-PatP|||rdjabarov, that was wild!! :) That is exactly the approach I am going to use! My query was a little too long, but I just seperated it into columns, and used "" as a col seperator. And voil! This way, I can also archive my query, as I try to avoid hard coding wherever possible. I'll have to comment a lot, but it's a whole lot less hokey than what I was planning to do!!!

vaxman- I cannot use sp_Oacreate - permissions. :(

Pat - Thanks for the suggestions, I'm still going to look into DTS, as it looks on first glance as a pretty powerful tool.

WooHoo! Thanks everyone!!|||Not really elegant, but running
isql -E -d firstDB -S ServerA -Q "select 'set MyVariable='+convert(varchar(15), max(load_date), 111) from mydates" -o myBat.bat

call myBat.batsql

Help! Im using MS SQL Views

And I need help with a query. I have a field that I want to test if it has ".bat" at the end if so I want to replace this with another field. I tried using the "replace"
but it replaces only the ".bat" portion of the field. Any idea's?

ThanksSo you want to replace the whole entire field with another field where the field has .bat?
How about
update Table1
set field1 = field2 where field1 like '%.bat'

Help! I cant write this SQL Server query ... can you?

Take the following table

ID Shipment ETA Updated
01 123 3/1/04 2/12/04
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
05 456 3/1/04 2/17/04
06 456 3/1/04 2/16/04
07 456 3/1/04 2/15/04

I need a query that will return the 2 most recently updated rows for
each shipment. So the results would look like the following:

ID Shipment ETA Updated
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
06 456 3/1/04 2/16/04
05 456 3/1/04 2/17/04

Thanks,

TeknariLets say the name of your table is Shipment_Status. Here goes

select Shipment,Id from shipment_status a where
id=(select max(id) from shipment_status b where shipment=a.shipment )
or
id=(select max(id) from shipment_status c where shipment=a.shipment
and id <>(select max(id) from shipment_status d where
shipment=c.shipment) )

Try it
Prashant

junk2@.bgtl.com (Teknari) wrote in message news:<b1e69426.0402160742.25896bac@.posting.google.com>...
> Take the following table
> ID Shipment ETA Updated
> 01 123 3/1/04 2/12/04
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 05 456 3/1/04 2/17/04
> 06 456 3/1/04 2/16/04
> 07 456 3/1/04 2/15/04
>
> I need a query that will return the 2 most recently updated rows for
> each shipment. So the results would look like the following:
> ID Shipment ETA Updated
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 06 456 3/1/04 2/16/04
> 05 456 3/1/04 2/17/04
> Thanks,
> Teknari|||You can write this in a couple of ways:

--#1 (using TOP clause)
SELECT *
FROM tbl
WHERE Updated IN ( SELECT TOP 2 t1.Updated
FROM tbl t1
WHERE t1.Shipment = tbl.Shipment
ORDER BY t1.Updated DESC );

--#2 (using an aggregate)
SELECT *
FROM tbl
WHERE( SELECT COUNT(*)
FROM tbl t1
WHERE t1.Shipment = tbl.Shipment
AND t1.Updated >= tbl.Updated) <= 2 ;

--
- Anith
( Please reply to newsgroups only )|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You might want to use ISO-8601 temporal formats,
just in case you have to exchange data with someone, either human or
machine.

I also hope that you have not written your own audit logs in SQL and
that "updated" refers to the ETA and not the PHYSICAL row. There are
tools for that kind of function, which is totally outside the scope of
an application database. Likewise, I will assume that you know better
than to use any kind of auto-increment number for a key, so I assume
that the real DDL looks like this:

CREATE TABLE ShipmentHistory
(shipment_nbr INTEGER NOT NULL,
eta DATETIME NOT NULL,
revised_eta DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (shipment_nbr, eta, revised_eta));

>> I need a query that will return the two most recently updated rows
for each shipment. <<

SELECT H1.shipment_nbr, H1.eta, MAX(H1.revised_eta),
(SELECT MAX(H2.revised_eta)
FROM ShipmentHistory AS H2
WHERE H1.shipment_nbr = H2.shipment_nbr
AND H2.revised_eta < H1.revised_eta)
FROM ShipmentHistory AS H1
GROUP BY H1.shipment_nbr, H1.eta

This will give you a NULL if the ETA changed only once.|||Celko wrote...

<snip>
Likewise, I will assume that you know better
> than to use any kind of auto-increment number for a key
<snip
Why not use an auto increment number as a key?|||You could try this

SELECT s.*
FROM shipment_status s
INNER JOIN (SELECT shipment, MAX(Updated) max_updated
FROM shipment_status
GROUP BY shipment

UNION ALL

SELECT s1.shipment, MAX(s1.Updated)
FROM shipment_status s1
INNER JOIN (SELECT shipment, MAX(Updated) max_updated
FROM shipment_status
GROUP BY shipment) s2 ON s1.shipment = s2.shipment AND
s1.Updated < s2.max_updated
GROUP BY s1.shipment) as m_all
ON s.shipment = m_all.shipment AND s.Updated = m_all.max_updated
ORDER BY s.shipment, s.ID

"Teknari" <junk2@.bgtl.com> wrote in message
news:b1e69426.0402160742.25896bac@.posting.google.c om...
> Take the following table
> ID Shipment ETA Updated
> 01 123 3/1/04 2/12/04
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 05 456 3/1/04 2/17/04
> 06 456 3/1/04 2/16/04
> 07 456 3/1/04 2/15/04
>
> I need a query that will return the 2 most recently updated rows for
> each shipment. So the results would look like the following:
> ID Shipment ETA Updated
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 06 456 3/1/04 2/16/04
> 05 456 3/1/04 2/17/04
> Thanks,
> Teknari|||>> Why not use an auto increment number as a key? <<

I have longer rants, but the Cliff Notes version is:

1) It is PHYSICAL and not LOGICAL

2) Non-relational

3) Proprietary

4) Meaningless in the data model

5) Unverifiable in the reality of the data model

6) Dangerously redundant, assuming that the table is actually a properly
designed table.

Newbies throw this on a table to fake a key because they don't know what
a key is, they are too lazy to research their industry for standards and
it looks like a sequential file's record number. They only know files
and still think in those terms.

As an aside, if this is an audit log, then you might want to look at
third party tools which are built for audits:

http://www.sswug.org/searchresults...ofind2=lumigent

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

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

You solution seemed the most elegant (at least as far as length of
query) so I went with it.

I do not have any experience using the table t1 in your query below.
It appears to me that it is a temporary table that is identical to the
table tbl which it is placed next to in the FROM statement, but I
really don't understand this.

Can somebody explain to me how it is used below or point me to a good
tutorial on the web?

Thanks,
Teknari

"Anith Sen" <anith@.bizdatasolutions.com> wrote in message news:<g7aYb.7095$hm4.6316@.newsread3.news.atl.earthlink.n et>...
> You can write this in a couple of ways:
> --#1 (using TOP clause)
> SELECT *
> FROM tbl
> WHERE Updated IN ( SELECT TOP 2 t1.Updated
> FROM tbl t1
> WHERE t1.Shipment = tbl.Shipment
> ORDER BY t1.Updated DESC );
> --#2 (using an aggregate)
> SELECT *
> FROM tbl
> WHERE( SELECT COUNT(*)
> FROM tbl t1
> WHERE t1.Shipment = tbl.Shipment
> AND t1.Updated >= tbl.Updated) <= 2 ;|||>> I do not have any experience using the table t1 in your query below.
It appears to me that it is a temporary table that is identical to the
table tbl which it is placed next to in the FROM statement, but I really
don't understand this.<<

This is a correlation name, or alias. It lets youuse the same table in
sevreal places. This is one of the reasons that SQL used to mean
"Structured Query Language"; it is that fundamental to the language. It
acts as if the engine has made a copy of the base table under the new
name that exists for the duration o the statement.

>> ... point me to a good tutorial on the web? <<

Go to the FirstSQL website or use Google to find one.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

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

Help! Emergency

Hi,
I have an issue, Someone ran an update query on the server and I need to
reverse the effect. How can I do this.
Thanks
JEither backup the transaction log, restore the last backup and reapply =
transaction logs up to just before the unwanted update,or restore the =
most recent backup to a separate db and copy the offending table to your =
real db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message =
news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I =
need to
> reverse the effect. How can I do this.
>=20
> Thanks
>=20
> J
>=20
>|||Hi,
The point_in_time restore is possible only if your database recovery moel is
set to "FULL". If it is full you can perform the below steps:-
1. Do a transaction log backup in current database
2. Restore the full database backup to a new database with norecovery option
3. restore the subsequent trasnaction log abckup to new database with
norecovery till the last backup
4. restore the final trasnaction log backup with STOPAT option mentioning
the time , RECOVERY
This will recover the new database till the time you mentioned.
Thanks
Hari
MCDBA
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:#ynRW6SOEHA.1620@.TK2MSFTNGP12.phx.gbl...
Either backup the transaction log, restore the last backup and reapply
transaction logs up to just before the unwanted update,or restore the most
recent backup to a separate db and copy the offending table to your real
db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message
news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I need
to
> reverse the effect. How can I do this.
> Thanks
> J
>

Help! Emergency

Hi,
I have an issue, Someone ran an update query on the server and I need to
reverse the effect. How can I do this.
Thanks
JEither backup the transaction log, restore the last backup and reapply =transaction logs up to just before the unwanted update,or restore the =most recent backup to a separate db and copy the offending table to your =real db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message =news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I =need to
> reverse the effect. How can I do this.
> > Thanks
> > J
> >|||Hi,
The point_in_time restore is possible only if your database recovery moel is
set to "FULL". If it is full you can perform the below steps:-
1. Do a transaction log backup in current database
2. Restore the full database backup to a new database with norecovery option
3. restore the subsequent trasnaction log abckup to new database with
norecovery till the last backup
4. restore the final trasnaction log backup with STOPAT option mentioning
the time , RECOVERY
This will recover the new database till the time you mentioned.
Thanks
Hari
MCDBA
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:#ynRW6SOEHA.1620@.TK2MSFTNGP12.phx.gbl...
Either backup the transaction log, restore the last backup and reapply
transaction logs up to just before the unwanted update,or restore the most
recent backup to a separate db and copy the offending table to your real
db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message
news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I need
to
> reverse the effect. How can I do this.
> Thanks
> J
>

Help! Emergency

Hi,
I have an issue, Someone ran an update query on the server and I need to
reverse the effect. How can I do this.
Thanks
J
Either backup the transaction log, restore the last backup and reapply =
transaction logs up to just before the unwanted update,or restore the =
most recent backup to a separate db and copy the offending table to your =
real db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message =
news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I =
need to
> reverse the effect. How can I do this.
>=20
> Thanks
>=20
> J
>=20
>
|||Hi,
The point_in_time restore is possible only if your database recovery moel is
set to "FULL". If it is full you can perform the below steps:-
1. Do a transaction log backup in current database
2. Restore the full database backup to a new database with norecovery option
3. restore the subsequent trasnaction log abckup to new database with
norecovery till the last backup
4. restore the final trasnaction log backup with STOPAT option mentioning
the time , RECOVERY
This will recover the new database till the time you mentioned.
Thanks
Hari
MCDBA
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:#ynRW6SOEHA.1620@.TK2MSFTNGP12.phx.gbl...
Either backup the transaction log, restore the last backup and reapply
transaction logs up to just before the unwanted update,or restore the most
recent backup to a separate db and copy the offending table to your real
db - if this is acceptable,
or buy something like log explorer from lumigent.
Sorry but thats the only choices.
Mike John
backup
"JD" <jk_50@.hotmail.com> wrote in message
news:Of9lU2SOEHA.892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an issue, Someone ran an update query on the server and I need
to
> reverse the effect. How can I do this.
> Thanks
> J
>

HELP! Dynamic binding of schema element names

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

Help! Debugging Stored Procedure from query analyser - "Step through disabled"

Can anybody explain how to do debug a stored procedure from SQL Query Analyser.

When i tried opening Query Analyser and pressing F8 i am able to see Object Browser on left side, i selected the d/b and expanded it then i selected a stored procdure by right click of mouse. I selected "Debug".

It shows me alert msg "SQL Debugging may not work properly if you log on as 'Local System Account' while SQl server is configured to run as a service. You can open Event Viewer to see details." DO U WISH TO CONTINUE- I selected "YES"

I am able to see 3 split windows on right side and GO, Toggle, Untoggle are enabled BUT Step Into, Step Over, Step Out...Stop debugging are disabled at menu bar.

The 1st right split window shows the proc code, 2nd split window shows Local-Global-Callstack none of them shows any values(blank), 3rd split window shows records(result) and
@.RETURN_VALUE = 0 message

I had Toggled at each and every line of the procedure in 1st split window still it doesnt respond anything.

What might be the problem, how to solve it do i need to give any permissions.

i tried logging from wind Authentication and also from Sql Authentication (sa/sa), still same problem occurs. By the way i am using SQL Server 2000.

Pls help me out

Thanks in advance
Murali Kumar

I have exactly the same problem too. Please someone tell me what the problem is, tq.

Help! can't run Update in SQL 2005

I have a table name : sale there have some item about (SN(int,PK),...Invoice(nvarchar)....)

try run a update query in SQL 2005 :

update sale set Invoive = '99999' where SN in('1','2','3')

Get the error msg:

Msg 512, Level 16, State 1, Procedure Update_Cost, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

but run in SQL 2000+SP4 is OK!

what's wrong in SQl2005?

this is working for me,

or

just send us the full test data(Table structure + Test data), check is there some trigger there for this table? go for the same statment for other table, i mean update same fileds, or so.

|||

From your description, SN is integer. The single quotes around the integers should be removed.

UPDATE sale SET Invoice = '99999' where SN in (1, 2, 3)

Martin Poon [MVP - SQL Server]

|||

Thanks a lot!

Find the root cause! after check the DB find there have trigger with updat invoice!

thanks again!