Showing posts with label helloi. Show all posts
Showing posts with label helloi. Show all posts

Friday, March 30, 2012

Help on this

Hello:

I am currently running a query and it is taking like 6 hours to run.

what I do is:

I loop through user's table (20000 users) using a cursor, for each user I call a stored procedure "EXEC ...", then in that called stored procedure, I have an a set of 6 IF statements, and in each if statement I have a call for a simple stored procedure,.

Can anyone tell me, how can i optimize that query?

thanks a lot

Don't use a cursor? We'd need to know more specifics about what it's actually doing to determine if you can do this as a set operation instead of row-by-row (cursor).

Marcie

|||

Hello Marcie:

Thank you for your reply.

I sent you an email from your Blog, it has some codes.

thanks a lot,

|||

Hi SNT2,

I don't have access to that email account during the day, sorry. Also, if you'll post the code here (or at least an overview of what it does), other people will be able to help you with your problem too.

Take care,

Marcie

|||

Hello, I will list the Stored Procedures here: As I said, there is a main procedure, which loops using a cursor through all the records, and upon getting each account I call a method, the first method is the general one:

CREATE PROCEDURE ActiveUserAnalysis_AnalyzeUsers( @.AnalysisPeriod int = 30, -- the number of days over which the refills are checked @.CustGrpId int = 101, -- the group that we want to analyze @.AnalysisId int out)ASBEGIN SET NOCOUNT ON --create the analysis record INSERT INTO ActiveUserAnalysis ( AnalysisTime, AnalysisPeriod, CustGrpId) VALUES ( getdate(), @.AnalysisPeriod, 101) SET @.AnalysisId = @.@.identityDECLARE @.UserCount int SET @.UserCount = 0 --loop over all active users and analyze them DECLARE cActiveCustomers CURSORREAD_ONLYFOR select customerid, username from activecustomersview where custgrpid=@.CustGrpIdDECLARE @.customerid intDECLARE @.username varchar(30)OPEN cActiveCustomersFETCH NEXT FROM cActiveCustomers INTO @.customerid, @.usernameWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN SET @.UserCount = @.UserCount + 1 EXEC ActiveUserAnalysis_AnalyzeActiveUser @.AnalysisId, @.AnalysisPeriod, @.CustomerId, @.UserName   ENDFETCH NEXT FROM cActiveCustomers INTO @.customerid, @.usernameENDCLOSE cActiveCustomersDEALLOCATE cActiveCustomers UPDATE ActiveUserAnalysis SET ActiveUserCount = @.UserCount WHERE AnalysisId = @.AnalysisId SELECT Classification, count(*) FROM ActiveUserAnalysis_Results WHERE AnalysisId = @.AnalysisId GROUP BY ClassificationENDGO


Now the method we are calling above for each user is:

CREATE procedure ActiveUserAnalysis_AnalyzeActiveUser( @.AnalysisId int, @.AnalysisPeriod int, @.customerid int, @.username varchar(30))asbegin DECLARE @.TokenType varchar(4) DECLARE @.Expires datetime DECLARE @.Value int DECLARE @.LastActivity datetime DECLARE @.RefillType varchar(30) DECLARE @.LastRefillDate datetime DECLARE @.RefillCount int DECLARE @.TBSUserName varchar(30) --First collect info EXEC ActiveUserAnalysis_GetUsageToken @.customerid, @.TokenType out, @.Value out, @.Expires out SELECT TOP 1 @.LastActivity = LastActivity FROM PPPAccount WHERE CustomerId = @.CustomerId --Classify user -- 1- Refill EXEC ActiveUserAnalysis_CheckUserLastRefills @.CustomerId, @.AnalysisPeriod, @.RefillType out, @.LastRefillDate out if @.@.rowcount > 0 BEGIN INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'Refill', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires) RETURN END -- 2- NoLogin: He hasn't logged in at all during period If datediff (d, @.LastActivity, getdate()) > @.AnalysisPeriod BEGIN INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'NoLogin', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires) RETURN END --3 Other. If we reached here, then the user does not fit with any of the above categories INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'Other', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires)ENDGO

I didn't include all cases, but i listed only 3 of them.

Then in every case I am calling a procedure too.

Can that be optimzed?

Thanks Marcie

|||

Yes, but it will take some significant work. You haven't included the stored procedures/views that this depends on, so I can't really give you code that will work 100%.

Start off by creating a view for many of the stored procedures that this thing depends on... Can you post the code for say...

ActiveUserAnalysis_CheckUserLastRefills? Hopefully that doesn't call any more stored procedures/views.

|||

Hello, thank you for helping me.

The SP is as follows:

CREATE procedure ActiveUserAnalysis_CheckUserLastRefills(@.CustomerId int,@.AnalysisPeriod int, -- in days,@.Refilltype char(5) out,@.LastRefillDate datetime out)asselect top 1 @.RefillType=c.ISKCatCode, @.LastRefillDate = datepurchasedfrom isk i inner join iskcategory c on i.iskcatid = c.iskcatidwhere customerid = @.customerid and datediff(d, datepurchased, getdate()) <= @.AnalysisPeriod and i.CreatedNewCustomer = 0order by datepurchased descGO

Thank you.|||

Hello Marcie:

Can you help in that please?

thanks

|||

SomeNewTricks2 wrote:

I am currently running a query and it is taking like 6 hours to run.

what I do is:

I loop through user's table (20000 users) using a cursor, foreach user I call a stored procedure "EXEC ...", then in that calledstored procedure, I have an a set of 6 IF statements, and in each ifstatement I have a call for a simple stored procedure,.

Can anyone tell me, how can i optimize that query?


That's an awful big haystack. Have you pinpointed thebottleneck? I'd suggest running the query from Query Analyzerwith Show Execution Plan turned on. This should give you an ideaof the slow parts.

Also, this will not likely make an appreciable difference in yourexecution time, but instead of a cursor I'd use a #temp table (see anexample of this approach in this post:http://forums.asp.net/511669/ShowPost.aspx).|||

SomeNewTricks2 wrote:

Hello Marcie:

Can you help in that please?

thanks

My gut feel is still that you could probably do all of this with one (large) Update statement, rather than looping through thousands of items and doing them one at a time. If that gets too complex (or is not possible), using a #temp table as Terri suggests will make this job easier, and much faster than the cursor approach.

Marcie

|||

Thank you all.

Do you think using a loop over a temp table, is better than cursor?

What about the "if" statements, is using them ok?

thanks

|||

SomeNewTricks2 wrote:

Do you think using a loop over a temp table, is better than cursor?


Yes I do, that's why I suggested it. Here's a little background information:SQL Server Temp Table Performance.

SomeNewTricks2 wrote:

What about the "if" statements, is using them ok?


I think using them is OK, but as far as I understand it, when SQLServer compiles the stored procedure it will use the execution planbased on whichever parameters were passed to it the first timethrough. So the execution plan may not be optimized for otherparameters. But I have also read that SQL Server often recompilesstored procedures, so I don't know how much of an issue that truly is.

Again, I urge you to identifiy your bottleneck(s). You havepresented a huge haystack to look through, and it could be that onesmall part needing optimization is causing all of the performanceissues. We don't really have enough information to be able tohelp you.

|||

Sorry, been away for a few days... I didn't see that you were stuffing some of values from ActiveUserAnalysis_GetUsageToken in your inserts, could you post that code? As long as it's pretty simple, I'll have some working code for ya that should be magnitudes faster than what you are currently doing by using set-based rather than cursor-if based logic.

Cursoring over a temp table won't be faster than cursoring over a query, however, you will have less transaction collisions (possibly). The article the above poster referenced, is saying temp tables are faster if by using one you can avoid a cursor and use set-based logic, which is what you'll probably have to do, but possibly not, but it doesn't eliminate the need to move away from the cursor-if logic.

|||

First of all, thank you Terri for your help.

Here is the code:

create procedure ActiveUserAnalysis_GetUsageToken
(
@.customerid int,
@.TokenType varchar(4) out,
@.Value int out,
@.Expires datetime out
)
as
SELECT TOP 1 /* get only first (highest priority) token */
@.TokenType=t.TokenTypeId, @.Value=u.Value, @.Expires=u.Expires
FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.customerid = @.customerid
AND u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)
ORDER BY y.Priority, u.Created


Thanks a lot.

|||

Ok, let's start here. First step is to unroll this stored procedure into a view so that we can reuse it later. Heh, it would figure that you would have this buried somewhere, this is definately the hardest part of unravelling your cursor logic, so here goes. I am going to make the assumption that a single user can not have two (or more) tokens created with the same priority created at the same "Created". If this assumption is not correct, please let me know what combination of fields would be required to ALWAYS return no more than a single record.

Coding by hand here, so might be a syntax error somewhere (sorry).

CREATE VIEW vw_CustomerUsageToken

AS

SELECT t1.customerid,t1.TokenTypeId,t1.Value,t1.Expires
FROM (SELECT u.customerid,t.TokenTypeId,u.Value,u.Expires,y.priority,u.created FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)) t1

LEFT JOIN (SELECT u.customerid,t.TokenTypeId,u.Value,u.Expires,y.priority,u.created FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)) t2 on t1.customerid=t2.customerid AND (t2.priority<t1.priority OR (t2.priority=t1.priority AND t2.created<t1.created))

WHERE t2.customerid IS NULLsql

Help on string sorting

Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/defaul...kb;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
--
With Regards,
Raghavendra MudugalPlease post a repro script.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/defaul...kb;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
--
With Regards,
Raghavendra Mudugal|||Hi
This does sounds like you have a binary collation and mixed case data. You
may want to change the collation to something more intuitive or use
something like:
SELECT customerid
FROM MyTable
ORDER BY customerid COLLATE Latin1_General_CI_AS
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
> Hello
> I have a customer table created in the mssql server 2000, in this table i
> am
> using
> customerid as "CUST_0001" and so on..
> When i say order by on customerid column, the IDs are not sorted in a
> proper
> way
> Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
> order by customerid puts "CUST_0010" in the second row. but it supposed to
> in the 10thr row.
> Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting
> on
> this
> column. but nothing helped.
> Please advise me on this and what way it can be achived. I am not going to
> change the format of the ID which i am storing. that confirmed. but i need
> that to be sorted
> in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are
> changed.
> http://support.microsoft.com/defaul...kb;en-us;322112
> the above link where i looked to setting up the COLLATION.
> Hope to receive your advice as soon. Its urgent.
> --
> With Regards,
> Raghavendra Mudugal|||Hello John Bell,
Thanks for your reply.
I tried your suggetion,, but its not helping me in any way.
I looked in the "SQL Collation Name" list in SQL books on-line and tried
all names giving with that "customerid" column, but all resulted the same,
no difference at all. and its still showing the "CUST_0010" customer id
in the 2nd place.
please help.
With Regards,
Raghavendra Mudugal
"John Bell" wrote:

> Hi
> This does sounds like you have a binary collation and mixed case data. You
> may want to change the collation to something more intuitive or use
> something like:
> SELECT customerid
> FROM MyTable
> ORDER BY customerid COLLATE Latin1_General_CI_AS
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data.
> John
> "Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
> in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
>
>|||If it always starts with CUST_ and it always ends with digits you could
do
order by convert(int,replace(CustomerID,'CUST_",''))
http://sqlservercode.blogspot.com/|||well an annoying cheat kinda way u could do it i suppose is in the slect
statement, sort out the integers with substring (CustomerID, 6,10)-(is that
right? i dont remember the format it was, anyway u get the concept, the
column name then the character it starts and character it ends and all shoul
d
be integers) and Cast() the whole thing as INT, and make is AS SortNumber or
something. THen order by SortNumber instead of costomerID
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Lol well SQL beat me to it but i recommend CAST function over CONVERT becaus
e
the books say that CONVERT was only used for backward compatibility and CAST
should perform faster and avoid getting obsolete in later SQL versions
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Hello,
And thanks for your reply. I am ok with this Idea, but is this the only way
to order the customerid just by taking those four digits and just sorting th
em
as numerical sort and use it?
Because, presently I have 1.5 to 1.6 lac of customers listed in the database
.
And is that code works faster? I had no problems using the string sort in
SQL6.5, and i never used to convert the id to int . in 2000 lots of things a
re
changed.
Hope to receive your valuabel response as soon.
Thanks for your time.
--
With Regards,
Raghavendra Mudugal
"SQL" wrote:

> If it always starts with CUST_ and it always ends with digits you could
> do
> order by convert(int,replace(CustomerID,'CUST_",''))
> http://sqlservercode.blogspot.com/
>|||Hi
You may want to post DDL and sample that will re-create your problem.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:8ED10297-AFDE-4502-AC78-6C2729BDA1FB@.microsoft.com...[vbcol=seagreen]
> Hello,
> And thanks for your reply. I am ok with this Idea, but is this the only
> way
> to order the customerid just by taking those four digits and just sorting
> them
> as numerical sort and use it?
> Because, presently I have 1.5 to 1.6 lac of customers listed in the
> database.
> And is that code works faster? I had no problems using the string sort in
> SQL6.5, and i never used to convert the id to int . in 2000 lots of things
> are
> changed.
> Hope to receive your valuabel response as soon.
> Thanks for your time.
> --
> With Regards,
> Raghavendra Mudugal
>
> "SQL" wrote:
>sql

Help on string sorting

Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/default...b;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
With Regards,
Raghavendra Mudugal
Please post a repro script.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/default...b;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
With Regards,
Raghavendra Mudugal
|||Hi
This does sounds like you have a binary collation and mixed case data. You
may want to change the collation to something more intuitive or use
something like:
SELECT customerid
FROM MyTable
ORDER BY customerid COLLATE Latin1_General_CI_AS
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
> Hello
> I have a customer table created in the mssql server 2000, in this table i
> am
> using
> customerid as "CUST_0001" and so on..
> When i say order by on customerid column, the IDs are not sorted in a
> proper
> way
> Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
> order by customerid puts "CUST_0010" in the second row. but it supposed to
> in the 10thr row.
> Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting
> on
> this
> column. but nothing helped.
> Please advise me on this and what way it can be achived. I am not going to
> change the format of the ID which i am storing. that confirmed. but i need
> that to be sorted
> in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are
> changed.
> http://support.microsoft.com/default...b;en-us;322112
> the above link where i looked to setting up the COLLATION.
> Hope to receive your advice as soon. Its urgent.
> --
> With Regards,
> Raghavendra Mudugal
|||Hello John Bell,
Thanks for your reply.
I tried your suggetion,, but its not helping me in any way.
I looked in the "SQL Collation Name" list in SQL books on-line and tried
all names giving with that "customerid" column, but all resulted the same,
no difference at all. and its still showing the "CUST_0010" customer id
in the 2nd place.
please help.
With Regards,
Raghavendra Mudugal
"John Bell" wrote:

> Hi
> This does sounds like you have a binary collation and mixed case data. You
> may want to change the collation to something more intuitive or use
> something like:
> SELECT customerid
> FROM MyTable
> ORDER BY customerid COLLATE Latin1_General_CI_AS
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data.
> John
> "Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
> in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
>
>
|||If it always starts with CUST_ and it always ends with digits you could
do
order by convert(int,replace(CustomerID,'CUST_",''))
http://sqlservercode.blogspot.com/
|||well an annoying cheat kinda way u could do it i suppose is in the slect
statement, sort out the integers with substring (CustomerID, 6,10)-(is that
right? i dont remember the format it was, anyway u get the concept, the
column name then the character it starts and character it ends and all should
be integers) and Cast() the whole thing as INT, and make is AS SortNumber or
something. THen order by SortNumber instead of costomerID
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Lol well SQL beat me to it but i recommend CAST function over CONVERT because
the books say that CONVERT was only used for backward compatibility and CAST
should perform faster and avoid getting obsolete in later SQL versions
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Hello,
And thanks for your reply. I am ok with this Idea, but is this the only way
to order the customerid just by taking those four digits and just sorting them
as numerical sort and use it?
Because, presently I have 1.5 to 1.6 lac of customers listed in the database.
And is that code works faster? I had no problems using the string sort in
SQL6.5, and i never used to convert the id to int . in 2000 lots of things are
changed.
Hope to receive your valuabel response as soon.
Thanks for your time.
With Regards,
Raghavendra Mudugal
"SQL" wrote:

> If it always starts with CUST_ and it always ends with digits you could
> do
> order by convert(int,replace(CustomerID,'CUST_",''))
> http://sqlservercode.blogspot.com/
>
|||Hi
You may want to post DDL and sample that will re-create your problem.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:8ED10297-AFDE-4502-AC78-6C2729BDA1FB@.microsoft.com...[vbcol=seagreen]
> Hello,
> And thanks for your reply. I am ok with this Idea, but is this the only
> way
> to order the customerid just by taking those four digits and just sorting
> them
> as numerical sort and use it?
> Because, presently I have 1.5 to 1.6 lac of customers listed in the
> database.
> And is that code works faster? I had no problems using the string sort in
> SQL6.5, and i never used to convert the id to int . in 2000 lots of things
> are
> changed.
> Hope to receive your valuabel response as soon.
> Thanks for your time.
> --
> With Regards,
> Raghavendra Mudugal
>
> "SQL" wrote:

Monday, March 26, 2012

Help on Profiler

Hello:
I have setup a sqltrace to trace another sql server and
write to local sql table. I have try to defind the
template but it can't save the trace server and write to
local sql table. Is it possible to execute the sql trace
by simple way?
Thanks!
It's never a good idea to trace and have it write directly to a table. You
should always have it write to a file on a locally attached drive. You can
then easily import the file into a table later. This way you will have the
least amount of impact on the server while tracing. See the following and
the associated sp's in BooksOnLine.
sp_trace_create
fn_trace_gettable
Andrew J. Kelly SQL MVP
"Alan" <anonymous@.discussions.microsoft.com> wrote in message
news:2ef801c4a22e$3624e740$a601280a@.phx.gbl...
> Hello:
> I have setup a sqltrace to trace another sql server and
> write to local sql table. I have try to defind the
> template but it can't save the trace server and write to
> local sql table. Is it possible to execute the sql trace
> by simple way?
> Thanks!
sql

Monday, March 19, 2012

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim Wile
Jim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:

> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim WileJim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:

> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Monday, March 12, 2012

Help needed to build "splitted by year totals" query

Hello
I have a table with the following fields
DateFROM
DateTO
Ammount
Storing, you have guessed it, amounts for a period.
My problem is that I will need to split (and prorate) amount per year.
For instance if I have
DateFROM ! DateTO ! Ammount
--
01/jan/04! 31/dec/05! 100
I'd like the query to return two rows
DateFROM ! DateTO ! Ammount
--
01/jan/04! 31/dec/04! 50
01/jan/05! 31/dec/05! 50
Ideally I'd need to account for bissextile years...
Any idea how to achieve this. Definitely too complex for me :)
Thanks & regards
--alexTUsing a calendar table would solve this issue quite easily. Build one for
the required duration and use it in your query involving the JOIN. If you
need a specific query, refer to: www.aspfaq.com/5006 and post required
information for others to repro your problem.
Anith

Wednesday, March 7, 2012

Help needed

Hello
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
C.VENDOR_NUMBER)
Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
> C.VENDOR_NUMBER)
>
>
|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
>
|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:

> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed

Hello
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
C.VENDOR_NUMBER)Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
> C.VENDOR_NUMBER)
>
>|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
>|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:

> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)