SQL compact edition: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

Poor performance with NEWID()

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[
PK_MyTable]))
So, I guess you can do a little hack like this:
select * from
(
select top 10 * from mytable
) x
order by newid()
And get fewer calls to newid()
StmtText
----
--
|--Sort(ORDER BY

[Expr1002] ASC))
|--Compute Scalar(DEFINE

[Expr1002]=newid()))
|--Top(10)
|--Clustered Index Scan(OBJECT

[HaxPlans].[dbo].[MyTable].[
PK_MyTable]))
Oh, wait a minute, you were doing an INSERT, maybe there is something
funky about the table you're inserting into? Maybe you're really
doing large numbers than 250?
But wait another minute, if you're doing an insert, WHY ARE YOU
ORDERING THE RECORDS ANYWAY? Is the destination table "flat", with no
indexes, just really an output buffer? Well, hmm, that should WORK,
and I still don't understand in that case especially why the
performance would vary so much. Just noodling around with it.
J.
On Fri, 4 Nov 2005 13:05:25 -0800, "Dean J Garrett" <info@.amuletc.com>
wrote:
>We're experiencing very poor performance on successive runs of queries such
>as the following:
>'---
>SET NOCOUNT ON
>TRUNCATE TABLE tblSurveyTemp
>INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode
,Married,ChildrenAtHome,Education,E
m
> ploymentStatus,Occupation,Industry,Incom
e,Ethnicity,DateSent)
>SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode
,Married,ChildrenAtHome,Education,E
m
> ploymentStatus,Occupation,Industry,Incom
e,Ethnicity,getdate()
>FROM tblMember t2
>WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
>('F')
>AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
>t.survey='grainactivity')
>ORDER BY NEWID()
>SELECT FirstName,LastName,Email FROM tblSurveyTemp
>'---
>
>We're using the NEWID() function to randomize the sample. CPU is always at
>100% when we run the query. The first time it runs successfully takes abou
t
>20 seconds. Second time, maybe 1 min. Third time it timed out.
>Does anyone have any advice?
>Thank you!!!
>|||You just need to generate random number to select 250 random records. So a
variation of following query can be used for this purpose.
select au_id,au_lname, au_fname,
convert(smallint,rand() * ascii(left(au_lname,1)) *
ascii(right(au_lname,1))) % 77 value1 from authors
order by value1
The newid() creates a unique value of type uniqueidentifier which is a
16-byte binary values. Thus the filtered records are being sorted on a very
wide column. The query suggested by me will be sorted on a smallint data typ
e
column which takes 2 bytes only.

Poor performance with NEWID()

We're experiencing very poor performance on successive runs of queries such
as the following:
'---
SET NOCOUNT ON
TRUNCATE TABLE tblSurveyTemp
INSERT INTO tblSurveyTemp (FirstName, LastName,
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
SELECT TOP 250 FirstName, LastName,
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
FROM tblMember t2
WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
('F')
AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
t.survey='grainactivity')
ORDER BY NEWID()
SELECT FirstName,LastName,Email FROM tblSurveyTemp
'---
We're using the NEWID() function to randomize the sample. CPU is always at
100% when we run the query. The first time it runs successfully takes about
20 seconds. Second time, maybe 1 min. Third time it timed out.
Does anyone have any advice?
Thank you!!!I dont have an answer, but a question. Why would you ever want to order by
newid()?
--
TIA,
ChrisR
"Dean J Garrett" wrote:
> We're experiencing very poor performance on successive runs of queries such
> as the following:
> '---
> SET NOCOUNT ON
> TRUNCATE TABLE tblSurveyTemp
> INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
> SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
> FROM tblMember t2
> WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
> ('F')
> AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
> t.survey='grainactivity')
> ORDER BY NEWID()
> SELECT FirstName,LastName,Email FROM tblSurveyTemp
> '---
>
> We're using the NEWID() function to randomize the sample. CPU is always at
> 100% when we run the query. The first time it runs successfully takes about
> 20 seconds. Second time, maybe 1 min. Third time it timed out.
> Does anyone have any advice?
> Thank you!!!
>
>|||On Fri, 4 Nov 2005 14:12:01 -0800, ChrisR wrote:
>I dont have an answer, but a question. Why would you ever want to order by
>newid()?
Hi Chris,
The combination of TOP ... and ORDER BY NEWID() is often used to get a
pseudo-random sample. The ORDER BY NEWID() makes sure that the rows are
scrambled in an unpredictabable way; the TOP then takes only the few
rows that happen to be first.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||This method of choosing a random set of records (250 in your case) out of a
larger set of records is pretty effective when there are not many records in
the filtered select. In your case tblMember filtered by your where clause
must produce quite a few records. SQL Server has to pull those records
togther and sort them by the newid() value. Sorting a lot of records can take
a long time. To see how many records we're talking run:
SELECT COUNT(*)
FROM tblMember t2
WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
('F')
AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
t.survey='grainactivity')
In the end you may need to choose another method to choose your records
randomly.
Good luck!
-Phil
"Dean J Garrett" wrote:
> We're experiencing very poor performance on successive runs of queries such
> as the following:
> '---
> SET NOCOUNT ON
> TRUNCATE TABLE tblSurveyTemp
> INSERT INTO tblSurveyTemp (FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
> SELECT TOP 250 FirstName, LastName,
> Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
> FROM tblMember t2
> WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
> ('F')
> AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
> t.survey='grainactivity')
> ORDER BY NEWID()
> SELECT FirstName,LastName,Email FROM tblSurveyTemp
> '---
>
> We're using the NEWID() function to randomize the sample. CPU is always at
> 100% when we run the query. The first time it runs successfully takes about
> 20 seconds. Second time, maybe 1 min. Third time it timed out.
> Does anyone have any advice?
> Thank you!!!
>
>|||Here is the article we used to figure out this technique
http://www.windowsitpro.com/Articles/ArticleID/19842/19842.html but we don't
know if it is the best performing!
Thanks
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B2EF1E86-E0AF-4FA3-A787-67C30B2AEA32@.microsoft.com...
> I dont have an answer, but a question. Why would you ever want to order by
> newid()?
> --
> TIA,
> ChrisR
>
> "Dean J Garrett" wrote:
> > We're experiencing very poor performance on successive runs of queries
such
> > as the following:
> >
> > '---
> > SET NOCOUNT ON
> > TRUNCATE TABLE tblSurveyTemp
> >
> > INSERT INTO tblSurveyTemp (FirstName, LastName,
> >
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> > ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
> > SELECT TOP 250 FirstName, LastName,
> >
Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
> > ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
> > FROM tblMember t2
> > WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex
in
> > ('F')
> > AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email
AND
> > t.survey='grainactivity')
> > ORDER BY NEWID()
> >
> > SELECT FirstName,LastName,Email FROM tblSurveyTemp
> > '---
> >
> >
> > We're using the NEWID() function to randomize the sample. CPU is always
at
> > 100% when we run the query. The first time it runs successfully takes
about
> > 20 seconds. Second time, maybe 1 min. Third time it timed out.
> >
> > Does anyone have any advice?
> >
> > Thank you!!!
> >
> >
> >
> >|||Very kinky.
I have no idea why you would get such differential results, if indeed
you are using the same parameters each time.
On a toy table, you can see that the newid() gets called BEFORE the
top function.
select top 10 * from mytable
order by newid()
StmtText
-----
|--Sort(TOP 10, ORDER BY:([Expr1002] ASC))
|--Compute Scalar(DEFINE:([Expr1002]=newid()))
|--Clustered Index Scan(OBJECT:([HaxPlans].[dbo].[MyTable].[PK_MyTable]))
So, I guess you can do a little hack like this:
select * from
(
select top 10 * from mytable
) x
order by newid()
And get fewer calls to newid()
StmtText
-----
|--Sort(ORDER BY:([Expr1002] ASC))
|--Compute Scalar(DEFINE:([Expr1002]=newid()))
|--Top(10)
|--Clustered Index Scan(OBJECT:([HaxPlans].[dbo].[MyTable].[PK_MyTable]))
--
Oh, wait a minute, you were doing an INSERT, maybe there is something
funky about the table you're inserting into? Maybe you're really
doing large numbers than 250?
But wait another minute, if you're doing an insert, WHY ARE YOU
ORDERING THE RECORDS ANYWAY? Is the destination table "flat", with no
indexes, just really an output buffer? Well, hmm, that should WORK,
and I still don't understand in that case especially why the
performance would vary so much. Just noodling around with it.
J.
On Fri, 4 Nov 2005 13:05:25 -0800, "Dean J Garrett" <info@.amuletc.com>
wrote:
>We're experiencing very poor performance on successive runs of queries such
>as the following:
>'---
>SET NOCOUNT ON
>TRUNCATE TABLE tblSurveyTemp
>INSERT INTO tblSurveyTemp (FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnicity,DateSent)
>SELECT TOP 250 FirstName, LastName,
>Email,Sex,Age,City,State,Country,ZipCode,Married,ChildrenAtHome,Education,Em
>ploymentStatus,Occupation,Industry,Income,Ethnicity,getdate()
>FROM tblMember t2
>WHERE Country IN ('U.S.') AND Age in ('13','20','30','40','50') AND Sex in
>('F')
>AND Not Exists ( Select email FROM tblSurvey t WHERE t.email=t2.email AND
>t.survey='grainactivity')
>ORDER BY NEWID()
>SELECT FirstName,LastName,Email FROM tblSurveyTemp
>'---
>
>We're using the NEWID() function to randomize the sample. CPU is always at
>100% when we run the query. The first time it runs successfully takes about
>20 seconds. Second time, maybe 1 min. Third time it timed out.
>Does anyone have any advice?
>Thank you!!!
>|||You just need to generate random number to select 250 random records. So a
variation of following query can be used for this purpose.
select au_id,au_lname, au_fname,
convert(smallint,rand() * ascii(left(au_lname,1)) *
ascii(right(au_lname,1))) % 77 value1 from authors
order by value1
The newid() creates a unique value of type uniqueidentifier which is a
16-byte binary values. Thus the filtered records are being sorted on a very
wide column. The query suggested by me will be sorted on a smallint data type
column which takes 2 bytes only.sql

Poor performance with functions in selects

We have the following query that we were trying to execute and it performs very poorly...

SELECT ITEM_ID,

PART_NO,

PART_TITLE,

dbo.SFDB_NVL_VARCHAR(DRAWING_NO_DISP,'N/A') AS DRAWING_NO,

dbo.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS DRAWING_CHG,

DRAWING_NO_DISP,

DRAWING_CHG_DISP,

REV_SOURCE,

ITEM_ID AS ASSY_ITEM_ID,

dbo.SFDB_NVL_VARCHAR(DRAWING_NO_DISP,'N/A') AS ASSY_DRAWING_NO,

SFMFG.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS ASSY_DRAWING_CHG

FROM SFSQA_CHAD_PART_DSPTCH_DSP_SEL

ORDER BY PART_NO

The function SFDB_NVL_VARCHAR is nothing but a replication of nvl function of oracle.

CREATE

FUNCTION [dbo].[SFDB_NVL_VARCHAR]

(

@.VI_SOURCE VARCHAR(4000),

@.VI_VALU_IF_NULL VARCHAR(4000)

)

RETURNS VARCHAR(4000)

BEGIN

IF ISNULL(@.VI_SOURCE,'') =''

RETURN @.VI_VALU_IF_NULL

RETURN @.VI_SOURCE

END

Now if the same query was re-written to remove the function and instead use a CASE WHEN block the performance is up significantly....

SELECT ITEM_ID,

PART_NO,

PART_TITLE,

CASE WHEN DRAWING_NO_DISP IS NULL THEN 'N/A'

WHEN DRAWING_NO_DISP = '' THEN 'N/A'

ELSE DRAWING_NO_DISP

END DRAWING_NO,

CASE WHEN DRAWING_CHG_DISP IS NULL THEN 'N/A'

WHEN DRAWING_CHG_DISP = '' THEN 'N/A'

ELSE DRAWING_CHG_DISP

END DRAWING_CHG,

DRAWING_NO_DISP,

DRAWING_CHG_DISP,

REV_SOURCE,

ITEM_ID AS ASSY_ITEM_ID,

CASE WHEN DRAWING_NO_DISP IS NULL THEN 'N/A'

WHEN DRAWING_NO_DISP= '' THEN 'N/A'

ELSE DRAWING_NO_DISP

END ASSY_DRAWING_NO,

CASE WHEN DRAWING_CHG_DISP IS NULL THEN 'N/A'

WHEN DRAWING_CHG_DISP = '' THEN 'N/A'

ELSE DRAWING_CHG_DISP

END ASSY_DRAWING_CHG

FROM SFSQA_CHAD_PART_DSPTCH_DSP_SEL

ORDER BY PART_NO

Now the execution plan in both cases shows that cost of execution of the funtion is 0%. This is weird. How can function perform so badly for as simple as the one I mentioned,.

Regards

Imtiaz

Calling functions in a SELECT statement is itself not a very good idea for the same reason that for every row that was fetched in the SELECT result, SQL Server has to execute the function (for which it has to check for an available execution plan, find the most optimum plan, use it, then return the result).|||

Tiaz:

I think Dinakar is right. I am afraid that this is just "the nature of the beast". Code that does not use functions will tend to execute better. Here are a couple of threads earlier this year that discussed around the issues of functions; reviewing these might be helpful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=868589&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=701264&SiteID=1

Dave

|||But even with such a function the execution should be fast...What is happening here...?|||

Well, I also just noticed this line:

SFMFG.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS ASSY_DRAWING_CHG

Is this a typo in which you really want DBO?

|||OKAT THAT WAS A TYPO.....I HAD THAT SCHEMA BUT REMOVED IT NOT TO CONFUSE U FOLKS....IT IS DBO

Wednesday, March 28, 2012

Poor performance

I have the following structure:

At server, I have SQL Server 2005 with a database running in compability mode 8.0 (SQL 2000). At desktops (cliente side) I have SQL Server 2005 Express. The base on the desktops access the base on the server to a copy of data through Linked Server. Both bases are in compability mode 8.0.

This is my problem:

The copy of data using a ad-hoc query INSERT INTO LOCAL-DATABASE ... SELECT .... FROM REMOTE-DATABASE (WAN) takes much more time than if I use MSDE in the desktops.

I would like to know what problem can be cause this delay.

Thanks

Did you check 'Collation Compatible' option in the linked server options?

|||

Yes I did. This option is defined "FALSE". Is It right?

Tks

|||It should be set to TRUE.

Poor performance

I have the following structure:

This is my problem:

The copy of data using a ad-hoc query INSERT INTO LOCAL-DATABASE ... SELECT .... FROM REMOTE-DATABASE (WAN) takes much more time than if I use MSDE in the desktops.

I would like to know what problem can be cause this delay.

Thanks

Did you check 'Collation Compatible' option in the linked server options?

|||

Yes I did. This option is defined "FALSE". Is It right?

Tks

|||It should be set to TRUE.

Monday, March 26, 2012

ponit in time restore

We need to do a point in time restore to 03:44 on
24/05/2004 and we have the following backups:
FULL 14:00
Hi,
POINT_IN_TIME restore will work only if your Recovery mode is FULL for that
database.
If it is FULL then:-
1. Take a backup transaction log in current database
2. Create a new database
3. Restore with full backup file with NORECOVERY (Use below command)
RESTORE database new_dbname from disk='file' with NORECOVERY, MOve
'logical_mdf' to 'physical_mdf',
move 'logical_ldf' to 'physical_ldf'
4. Restore the transaction log backup taken in step-1 with RECOVERY and
STOPAT option
RESTORE log new_dbname from disk='tran_backup_file' with RECOVERY, STOPAT
= ''May 24, 2004 03:44 AM'
Thanks
Hari
MCDBA
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1222f01c44265$de1f7470$a101280a@.phx.gbl...
> We need to do a point in time restore to 03:44 on
> 24/05/2004 and we have the following backups:
> FULL 14:00

ponit in time restore

We need to do a point in time restore to 03:44 on
24/05/2004 and we have the following backups:
FULL 14:00Hi,
POINT_IN_TIME restore will work only if your Recovery mode is FULL for that
database.
If it is FULL then:-
1. Take a backup transaction log in current database
2. Create a new database
3. Restore with full backup file with NORECOVERY (Use below command)
RESTORE database new_dbname from disk='file' with NORECOVERY, MOve
'logical_mdf' to 'physical_mdf',
move 'logical_ldf' to 'physical_ldf'
4. Restore the transaction log backup taken in step-1 with RECOVERY and
STOPAT option
RESTORE log new_dbname from disk='tran_backup_file' with RECOVERY, STOPAT
= ''May 24, 2004 03:44 AM'
Thanks
Hari
MCDBA
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1222f01c44265$de1f7470$a101280a@.phx.gbl...
> We need to do a point in time restore to 03:44 on
> 24/05/2004 and we have the following backups:
> FULL 14:00

ponit in time restore

We need to do a point in time restore to 03:44 on
24/05/2004 and we have the following backups:
FULL 14:00Hi,
POINT_IN_TIME restore will work only if your Recovery mode is FULL for that
database.
If it is FULL then:-
1. Take a backup transaction log in current database
2. Create a new database
3. Restore with full backup file with NORECOVERY (Use below command)
RESTORE database new_dbname from disk='file' with NORECOVERY, MOve
'logical_mdf' to 'physical_mdf',
move 'logical_ldf' to 'physical_ldf'
4. Restore the transaction log backup taken in step-1 with RECOVERY and
STOPAT option
RESTORE log new_dbname from disk='tran_backup_file' with RECOVERY, STOPAT
= ''May 24, 2004 03:44 AM'
Thanks
Hari
MCDBA
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1222f01c44265$de1f7470$a101280a@.phx
.gbl...
> We need to do a point in time restore to 03:44 on
> 24/05/2004 and we have the following backups:
> FULL 14:00sql

Polling in 2005

Hi folks,

Somebody got an idea about how to implement the following in SQL 2005? Or does anybody know of an example posted somewhere for this? In my opinion it's such a common problem, someone somewhere is bound to have written something about this somewhere (but I could nog find it).

We've got a table in a database and regularly an event happens that inserts a record in this table. At that moment a signal should be given to a .NET-service (in the form of a XML-message) which will do some intelligent stuff with this info.

In SQL 2000 I would implement a polling mechanism that every 10 secs (or some other appropriate interval) would do a select on the table to see if anything had changed. But with all the new features in 2005, I'm of the opinion that this could be done differently.

I've been looking at:

Notification Services, but this looks a bit like overkill for such a simple demand.
Query notifications from the Service Broker. But this is also a bit much because deletes are also a cause for a notification. Also all the articles I found are dealing with cache expiration. (Afterthought... maybe SELECT MAX(id) will work as defined query)

Ofcourse it would be nice if the signal is queued somewhere when the receiving service is temporarily not available.

Thanx in advance,

Lexwant to give us a why and what you are trying to do?

I might have a trigger fire into a log table and then take action on that...but I don't see a need|||To put a simple, a bunch of different processes can insert records in a table indicating a document is ready to be processed. Each time a new record is added a signal has to be sent to a .NET-service (in XML-form). This signal will process the document further.

Happy holidays...

Poison Message. The conversation endpoint is not in a valid state for SEND.

Hi all, i searched everywhere but couldn't find any info on the following error that i'm currently receiving:

"The conversation endpoint is not in a valid state for SEND. The current endpoint state is 'DI'."

I understand that this is due to some problem in my send/receive protocol but how do i fix this problem so that i can continue with my testing? Right now i'm forced to drop my entire test database and reinstall everytime this message shows up because i can't send/receive any messages at that point. Is there anyway to get rid of it?

Thanks in advance. This is driving me nuts.

DI stands for DISCONNECTED INBOUND and the conversation goes into this state when the peer has ended the conversation. If you try to send on a conversation in this state you'll get this error. You need to establish a clear protocol on who'w ending the conversation and how, because what s likely happening one side (initiator?) issues the END CONVERSATION verb and ends the conversation while the target is still trying to respond.

|||I think i found a solution to my own problem.

This is from the Microsoft online book:
http://download.microsoft.com/download/f/8/5/f8520d64-f109-4111-b0b0-51f1f6d2d220/Rational_Press_Service_Broker_Sample.pdf

If you look at the example in Chapter 1, you will notice that only one end of the conversation

was ended. In Figure 2.3, you can see one conversation with a state of “DI” (meaning

“disconnected inbound”), indicating that the opposite endpoint was ended with an END

CONVERSATION command but this endpoint of the conversation hasn’t been ended yet.

The other conversation state is “ER” (or “error”) because it has been open long enough

that the timeout has expired and the conversation ended with an error. If you execute an

END CONVERSATION command specifying the conversation_handle for the row in

the sys.conversation_endpoints table, it will go away.

Remembering to end all conversations is a very important programming practice. If you

forget to do this, you will fi nd thousands of rows in the sys.conversation_endpoints table

after your application has run for awhile.

If anyone has other ideas then please let me know.

Cheers!!
|||Thanks Remus for the reply, here is my sending protocol:

declare @.dh uniqueidentifier;
declare @.Message nvarchar(500);

SET @.Message = N'<MessageTypes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"><TransactionID>12345</TransactionID><MessageType>Swap</MessageType><Source>OMS</Source><IsCancel>false</IsCancel></MessageTypes>';

BEGIN DIALOG CONVERSATION @.dh
FROM SERVICE ClientServiceOMS
TO SERVICE 'TranProcessorService'
ON CONTRACT TradeCon
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @.dh
MESSAGE TYPE MessageTypes(@.Message)

END CONVERSATION @.dh;

And here is my receiving protocol (I removed all error handling for simplicity):

CREATE PROCEDURE ServiceProc
AS
DECLARE @.MsgXml xml, @.MsgTypeName nvarchar(128),
@.dh uniqueidentifier, @.Message nvarchar(500);

SET @.ErrNS = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
SET @.EndDlgNS = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

BEGIN TRAN;

WAITFOR (RECEIVE TOP(1)
@.MsgXml = CAST(message_body as xml),
@.MsgTypeName = message_type_name,
@.dh = conversation_handle
FROM ServiceQue) timeout 1000;

-- Send reply message
SET @.Message = N'<ResponseMessage><Message>Received</Message></ResponseMessage>';
SEND ON CONVERSATION @.dh
MESSAGE TYPE ResponseMessage(@.Message);

END CONVERSATION @.dh;
PRINT '(ServiceProc) Reply Message Sent Successfully.'

COMMIT TRAN

So are my "END CONVERSATION" statements in the correct place?

Thanks a lot.
|||Hello again, I just wanted to clairfy that the receiving procedure is called automatically by the receiving queue to process the messages. So i guess i'm not understanding exactly when to end the conversation.

The above receiving procedure is incorrect, here is the correct version:

CREATE PROCEDURE ServiceProc
AS
DECLARE @.MsgXml xml, @.MsgTypeName nvarchar(128),
@.dh uniqueidentifier, @.Message nvarchar(500);

BEGIN TRAN;

WAITFOR (RECEIVE TOP(1)
@.MsgXml = CAST(message_body as xml),
@.MsgTypeName = message_type_name,
@.dh = conversation_handle
FROM ServiceQue), TIMEOUT 1000;

-- Send reply message
SET @.Message = N'<ResponseMessage><Message>Received</Message></ResponseMessage>';
SEND ON CONVERSATION @.dh
MESSAGE TYPE ResponseMessage(@.Message);

END CONVERSATION @.dh;
PRINT '(ServiceProc) Reply Message Sent Successfully.'

COMMIT TRAN

I really appreciate all the help.

Thanks
|||

Scorpio8 wrote:

BEGIN DIALOG CONVERSATION @.dh
FROM SERVICE ClientServiceOMS
TO SERVICE 'TranProcessorService'
ON CONTRACT TradeCon
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @.dh
MESSAGE TYPE MessageTypes(@.Message)

END CONVERSATION @.dh;

For one, you are doing fire-and-forget and that has some problems, see here: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx. Second, the target is trying to respond on a conversation that was already ended by the initiator (hence the error you're getting).

Remove the END CONVERSATION on the sending side, have the initiator wait for the response from the target and end it's side of the conversation after it received a response.

|||Thank you very much Remus, that article and your other articles were very helpful. I got everything working now. But even though all the conversations end up gracefully i still end up with an entry in the sys.conversation_endpoints queue on both database instances:

select state, state_desc from sys.conversation_endpoints

state state_desc
CD CLOSED

Do i need to clear this queue every now and then or should it automatically clear up when the conversations end?

|||

Hi, i found an answer to my question in the book "Pro SQL Server 2005 Service Broker" which i got over the weekend, great book. The answer is on page 59:

"As soon as you process the EndDialog message on the initiator side, the conversation will end on both sides and then discarded from memory. However, for security reasons, it takes about 30 minutes until the initiator's conversation endpoint is deleted from sys.conversation_enpoints catalog view."

Just how in the heck were we mortals supposed to figure this out by ourselves!! lol

Friday, March 23, 2012

Point label - formatting decimal places

I have calculated a number of fields with the following method:
=SUM(Fields!Asian.Value) / SUM(Fields!Asian.Value + Fields!Alaskan.Value +
Fields!Black.Value + Fields!Hispanic.Value + Fields!Caucasian.Value +
Fields!AmericanIndian.Value + Fields!NotReported.Value) * 100 & "%" & " Asian"
This gives me my Asian population percentage, but when it renders it carries
on so long that I lose my " Asian" off the chart. I just want to limit the
expression to two decimal places. I tried to put the expression to format
the number in the format code as ##.00 as I would a table to limit the
decimal places to two places. What do I need to do to make this happen?
--
Thanks,
ChrisNot sure if you ever got a response.
The formatcode only works if the datatype of your expression is not a
string. In your example however, the expression will generate a string. Try
to use the FormatNumber function inside the calculation and apply the format
code directly:
=FormatNumber( SUM(Fields!Asian.Value) / SUM(Fields!Asian.Value +
Fields!Alaskan.Value + Fields!Black.Value + Fields!Hispanic.Value +
Fields!Caucasian.Value + Fields!AmericanIndian.Value +
Fields!NotReported.Value) * 100, 2) & "%" & " Asian"
See also:
http://msdn.microsoft.com/library/en-us/script56/html/vsfctFormatNumber.asp
Alternatively you could use the Format function which accepts a format code
as argument. E.g.: =Format( ..., "N2")
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctformat.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"cmcdavid" <cmcdavid@.discussions.microsoft.com> wrote in message
news:8E76FFB4-15E6-4ED0-841E-B7C722BC193B@.microsoft.com...
> I have calculated a number of fields with the following method:
> =SUM(Fields!Asian.Value) / SUM(Fields!Asian.Value + Fields!Alaskan.Value +
> Fields!Black.Value + Fields!Hispanic.Value + Fields!Caucasian.Value +
> Fields!AmericanIndian.Value + Fields!NotReported.Value) * 100 & "%" & "
Asian"
> This gives me my Asian population percentage, but when it renders it
carries
> on so long that I lose my " Asian" off the chart. I just want to limit
the
> expression to two decimal places. I tried to put the expression to format
> the number in the format code as ##.00 as I would a table to limit the
> decimal places to two places. What do I need to do to make this happen?
> --
> Thanks,
> Chris

Wednesday, March 21, 2012

PLZ HELP: number of calls at a time

I have a traffic table which contains the following field:

START_DATE_TIME (e.g. 2/14/2006 7:19:54 AM)

and I want to the following;

how many calls we have every minute (count) starting from 00:00 to 23:59?

what's the maximam number of calls and what time was it?

-- Create tets table

create table #trafic(

id int identity not null primary key,

start datetime

)

--Insert sample data

insert into #trafic values(getdate())

insert into #trafic values(dateadd(hour, 1, getdate()))

insert into #trafic values(dateadd(hour, 2, getdate()))

insert into #trafic values(dateadd(hour, 3, getdate()))

insert into #trafic values(dateadd(minute, 10, getdate()))

--Show my sample date

select * from #trafic

--Result

id start

-- --

1 2007-03-06 11:14:28.983

2 2007-03-06 11:14:29.000

3 2007-03-06 12:14:29.000

4 2007-03-06 13:14:29.000

5 2007-03-06 14:14:29.000

6 2007-03-06 11:24:29.000

7 2007-03-06 11:24:29.000

--Calls per minute

select count(id)

, cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2)) --Expression for getting hour:minute part of call datetime

from #trafic

group by cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

--Result

-- --

2 11:14

2 11:24

1 12:14

1 13:14

1 14:14

(5 row(s) affected)

--Show max calls

select top 1 count(id)

, cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

from #trafic

group by cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

order by count(id) desc

--Results

-- --

2 11:24

(1 row(s) affected)

--If few minutes have maximuns call number

select top 1 with ties count(id)

, cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

from #trafic

group by cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

order by count(id) desc

--Result

-- --

2 11:14

2 11:24

(2 row(s) affected)

Plz Help!

Hi guys
I have installed SQL 7.0 for the first time and now when I try to
connect in query analyzer I get the following error message:

Unable to connect to server \\localhost:

server:Msg17,level16,state1
[microsoft][odbc sql server driver][dbnet lib]sql server does not exist

or access denied

I have already enabled the TCP/IP
I am using windows XP service pack 2
Thanx a lot in advance for ur kind help
regards
Mr NoviceMrNovice (akhil.gupta04@.gmail.com) writes:
> I have installed SQL 7.0 for the first time and now when I try to
> connect in query analyzer I get the following error message:
> Unable to connect to server \\localhost:
>
> server:Msg17,level16,state1
> [microsoft][odbc sql server driver][dbnet lib]sql server does not exist
> or access denied
>
> I have already enabled the TCP/IP
> I am using windows XP service pack 2
> Thanx a lot in advance for ur kind help
> regards

What did you specify in Query Analyzer for server? "\\localhost"? That's
not a magic name. Try . (a single dot) or "(local)".

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Tuesday, March 20, 2012

PLS-00201 Error

Hi,

I am getting the following error :-
------------------
PLS-00201: identifier '<schema>.ABC_DROP_INDEX_PRC' must be declared
------------------
Can anyone guide me on resolving this issue??

Thanks.I'm guessing this question belongs in the SQL Server forum. If not the moderators will move it to a more appropriate forum.

ADMIN|||I need more info on it. What did you do? What statement was executed when you recieved this error? Which program and as much as possible.

Thank you.|||It looks like it is an Oracle error involved right limitations.|||

Quote:

Originally Posted by iburyak

It looks like it is an Oracle error involved right limitations.

Send me a PM if you need it moved again.|||Let's see his respond first.
Sometimes they never come back to view respond at all ... :)|||Hi,
Thanks to all for the suggestions provided, specially the last one!!
Wel, the answers was needed a little bit urgently and so i thought that i could get a reply from this forum.
I was able to fix the problem occuring. As stated earlier, it was a problem related to the access only...
"The rules for accessing other users objects are different when COMPILING
procedures/functions/packages.
Grants must be given directly, not through a role, to the compiling
usercode
(DBA is a role).
Normal rules apply when executing"
....
So i had to Grant privileges to one of the access_role to be able to execute procedure so it should work now.
---
Thanks anyways.

pls..help me to find out the error in the code

hi,

when i tried to run the following code , the code keeps on running without creating the required table...............

pls can anyone help me to figure out the problem........

waiting for your replies...

int row_head = 3;

int col_head = 3;

TextBox[] columns1=new TextBox[5];

TextBox[] rows = new TextBox[2];

for (int i = 0; i < 3; i++)

columns1[ i ] = new TextBox();

for (int i = 0; i < 2; i++)

rows[ i ] = new TextBox();

columns1[0].Text = "col1";

columns1[1].Text = "col2";

columns1[2].Text = "col3";

rows[0].Text = "rowh1";

rows[1].Text = "rowh2";

String[] description=new string[10];

for (int k = 0; k < 10; k++)

description[k] = k.ToString();

int index_value = 0;

int des_index = 0;

string datatype = "";

string command = "";

SqlConnection sqlCN = new SqlConnection(connectionString);

for (int i = 0; i < col_head + 1; i++)

{

if (i == 0)

datatype += "(col " + i + " varchar(20),";

else if (i + 1 == col_head+1)

datatype += "col" + i + " varchar(20))";

else

datatype += "col" + i + " varchar(20),";

}

MessageBox.Show(datatype);

MessageBox.Show("create table " + textBox1.Text + " " + datatype);

string selectCommandText = "create table " + textBox1.Text +datatype;

MessageBox.Show("executed");

try

{

SqlCommand sqlCmd = new SqlCommand(selectCommandText, sqlCN);

sqlCN.Open();

MessageBox.Show("open ");

// the following command is not executed.....

sqlCmd.ExecuteNonQuery();

MessageBox.Show("open and execu");

for (int i = 0; i < row_head + 1; i++)

{

command="";

if (i == 0)

{

command="' ',";

for(int j=0;j<col_head;j++)

{

if(j+1==col_head)

command+="'"+columns1[j].Text+"'";

else

command+="'"+columns1[j].Text+"',";

}

else

{

command = "'"+rows[index_value++].Text+"',";

for (int j = des_index; j < des_index + col_head; j++)

{

if (j +1== des_index+col_head)

command += "'" + description[j] + "'";

else

command += "'" + description[j] + "',";

}

des_index = des_index + col_head;

}

MessageBox.Show("INSERT INTO " + textBox1.Text + " values(" + command + ")");

sqlCmd.CommandText = "INSERT INTO " + textBox1.Text + " values("+command+")";

sqlCmd.ExecuteNonQuery();

}

MessageBox.Show("fini");

selectCommandText = "SELECT * FROM " + textBox1.Text;

sqlCmd = new SqlCommand(selectCommandText, sqlCN);

sqlCmd.ExecuteNonQuery();

SqlDataAdapter sqlDA = new SqlDataAdapter(selectCommandText, sqlCN);

DataSet DS = new DataSet();

sqlDA.Fill(DS);

MessageBox.Show("fini 2");

DataGridView1.DataSource = DS.Tables[0].DefaultView;

}

catch (Exception eio)

{ }}

Sweety

Appearantly you are getting an error during the execution. Having a try / catch block, with no actions or error retrieval functionalty will keep the error under the covers.

Please post the error message in here which ca be retrieved by reading the eio.Message and post the script which is used for the creation of the table.

BTW if you want to create a table you could use SMO rather than doing a script composing and executing this against the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hello,

I tried to print the message , I got " Incorrect syntax near '0'"..can some one tell me what does it mean...

bye

Sweety

|||

This is a syntax error in your query. Post the query (commandtext of the command here)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

This is your mistake: You have put a space after col so your create table command looks like create table (col 0 varchar(20), ... which is not correct, remove the space and it will start working.

for (int i = 0; i < col_head + 1; i++)

{

if (i == 0)

datatype += "(col " + i + " varchar(20),";

else if (i + 1 == col_head+1)

datatype += "col" + i + " varchar(20))";

else

datatype += "col" + i + " varchar(20),";

}

Thanks

Waseem

PLS. HELP. SQL NEWBIE

hello all. please tell me why the following update staement doesn't
work.

what i want to do is update tblmaster.mcol2 based on the value of
tblheader.hcol2

hcol2 values:
1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2

i tried it using query analyzer but it still adds even though the
value of hcol2 is 2.

create table tblmaster ( mcol1 nvarchar(3),
mcol2 float )

insert into tblmaster values ('001', 1)
insert into tblmaster values ('002', 1)
insert into tblmaster values ('003', 1)
insert into tblmaster values ('004', 1)
insert into tblmaster values ('005', 1)

create table tblheader ( hcol1 int,
hcol2 smallint )

create table tbldetails ( dcol1 int,
dcol2 nvarchar(3),
dcol3 float,
dcol4 float )

insert into tblheader values (1, 1)
insert into tblheader values (2, 1)
insert into tblheader values (3, 2)

insert into tbldetails values ( 1, '001', 1, 10 )
insert into tbldetails values ( 1, '002', 1, 10 )

insert into tbldetails values ( 2, '001', 1, 10 )
insert into tbldetails values ( 2, '003', 2, 10 )

insert into tbldetails values ( 3, '004', 1, 10 )
insert into tbldetails values ( 3, '005', 2, 10 )

declare @.lo as int
declare @.hi as int

set @.lo = 1
set @.hi = 3

UPDATE tblmaster
SETmcol2 =
CASE h.hcol2
WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
END
FROM tblmaster t, tblheader h, tbldetails d
WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi

select * from tblmaster

TIA,

diegoHi Rey

It is not clear how your tables are actually related but you may want to try
something like:

"Rey Guerrero" <rey_guerrero@.hotmail.com> wrote in message
news:d401c59b.0412220652.54dfa7cb@.posting.google.c om...
> hello all. please tell me why the following update staement doesn't
> work.
> what i want to do is update tblmaster.mcol2 based on the value of
> tblheader.hcol2
> hcol2 values:
> 1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
> 2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2
> i tried it using query analyzer but it still adds even though the
> value of hcol2 is 2.
>
> create table tblmaster ( mcol1 nvarchar(3),
> mcol2 float )
> insert into tblmaster values ('001', 1)
> insert into tblmaster values ('002', 1)
> insert into tblmaster values ('003', 1)
> insert into tblmaster values ('004', 1)
> insert into tblmaster values ('005', 1)
> create table tblheader ( hcol1 int,
> hcol2 smallint )
> create table tbldetails ( dcol1 int,
> dcol2 nvarchar(3),
> dcol3 float,
> dcol4 float )
> insert into tblheader values (1, 1)
> insert into tblheader values (2, 1)
> insert into tblheader values (3, 2)
> insert into tbldetails values ( 1, '001', 1, 10 )
> insert into tbldetails values ( 1, '002', 1, 10 )
> insert into tbldetails values ( 2, '001', 1, 10 )
> insert into tbldetails values ( 2, '003', 2, 10 )
> insert into tbldetails values ( 3, '004', 1, 10 )
> insert into tbldetails values ( 3, '005', 2, 10 )
> declare @.lo as int
> declare @.hi as int
> set @.lo = 1
> set @.hi = 3
> UPDATE tblmaster
> SET mcol2 =
> CASE h.hcol2
> WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> END
> FROM tblmaster t, tblheader h, tbldetails d
> WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi
> select * from tblmaster
> TIA,
> diego|||Hi Rey

It is not clear how your tables are actually related but you may want to try
something like:

UPDATE t
SET mcol2 =
CASE h.hcol2
WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
END
FROM tblmaster t
JOIN tbldetails d ON t.mcol1 = d.dcol2
JOIN tblheader h ON h.hcol1 = d.dcol1
WHERE h.hcol1 BETWEEN @.lo and @.hi

As tblmaster has is a one to many relationship with tbldetails you will have
to either restrict which rows match or use an aggregate

UPDATE t
SET mcol2 = mcol2 + val
FROM tblmaster t
JOIN ( SELECT d.dcol2, SUM( CASE WHEN h.col2 = 1 THEN d.dcol3 * dcol4
ELSE -1 * dcol3 * dcol4 END ) as val
FROM tbldetails d
JOIN tblheader h ON h.hcol1 = d.dcol1
WHERE h.hcol1 BETWEEN @.lo and @.hi GROUP BY d.dcol2 ) d ON t.mcol1 =
d.dcol2

John

Pls. help me optimize this stored procedure

Hi,

I'm kind of rusty when it comes stored procedures and I was wondering if someone could help me with the following SP. What I need to do is to query the database with a certain parameter (ex. Culture='fr-FR' and MsgKey='NoUpdateFound') and when that returns no rows I want it to default back to Culture 'en-US' with the same MsgKey='NoUpdateFound'). This way our users will always get an error message back in English if their own language is not defined. Currently I'm doing the following,

- Check row count on the parameter passed to sql and if found again do a select returning the values
- else do a select on the SQL with the default parameters.

I'm guessing there is a way to run a SQL and return it if it contains rows otherwise issue another query to return the default.

How can I improve this query. Your help is greatly appreciated. Also, if I do any assignment since 'Message' is defined as text I get the following error message

Server: Msg 279, Level 16, State 3, Line 1
The text, ntext, and image data types are invalid in this subquery or
aggregate expression.

CREATE PROCEDURE GetMessageByCulture
(
@.Culture varchar(25),
@.MsgKey varchar(50)

)
AS
SET NOCOUNT ON;

IF ( (SELECT COUNT(1) FROM ProductUpdateMsg WHERECulture=@.Culture AND MsgKey='NoUpdateFound') > 0)
SELECT Message FROM ProductUpdateMsg WHERECulture=@.Culture ANDMsgKey=@.MsgKey
ELSE
SELECT Message FROM ProductUpdateMsg WHERE Culture='en-US' ANDMsgKey=@.MsgKey
GO

Thank you
M.

Why don't you use resource files with globalization? This functionality is built in; all you have to do is edit an XML file to update the messages, and the message will always be displayed in the default language if the user's culture is not supported.

|||

IF EXISTS (SELECT * FROM ProductUpdateMsg WHERECulture=@.Culture AND MsgKey='NoUpdateFound')

SELECT Message FROM ProductUpdateMsg WHERECulture=@.Culture ANDMsgKey=@.MsgKey
ELSE
SELECT Message FROM ProductUpdateMsg WHERE Culture='en-US' ANDMsgKey=@.MsgKey

I am using * for the first statement because I don't know your columns. Ideally, you just only select one column instead of *.

Monday, March 12, 2012

PLS HELP: Problem altering columns with full text serach enabled

Hi,
I have the following script which enables full text search on the database:
sp_fulltext_database 'enable'
sp_fulltext_catalog 'cntdocimg', 'create'
sp_fulltext_table 'ContactDocumentImage', 'create', 'cntdocimg',
'PK_ContactDocumentImage'
sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1,
'sDocType'
sp_fulltext_table 'ContactDocumentImage', 'activate'
sp_fulltext_catalog 'cntdocimg', 'start_full'
sp_fulltext_table 'ContactDocumentImage', 'start_change_tracking'
sp_fulltext_table 'ContactDocumentImage', 'start_background_updateindex'
This script creates full text search for column "imgDocumentImage" of
table "ContactDocumentImage" and defines type of stored document by
looking at column "sDocType".
Now, the problem is that i need to alter the column sDocType to make it
nvarchar(10) instead of nvarchar(3) as it currently is; but when i run
ALTER statement it says that sDocType column is used by some other
object. Then i tried to disable full text search, and it did NOT help
either.
Could anyone please please please help me here!
Thank you in advance,
Andrey
First off I think this statement is incorrect
sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1,
'sDocType'
it should be something like this
sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1033,
'sDocType'
replace 1 with your lcid.
Secondly, the full-text index is the problem here. There is little you can
do without dropping the fulltext index, then making the change, and then
readding it.
One option is to come up with a new extension XXX lets say and bind the
iFilter to this extension. So if the extension is config and you want to
have the word iFilter index it, you would put XXX in the column for
documents of the config extension and then add the office iFilter pesistent
handler to XXX, like this
[HKEY_CLASSES_ROOT\.XXX\PersistentHandler]
@.="{98de59a0-d175-11cd-a7bd-00006b827d94}"
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MuZZy" <tnr@.newsgroups.nospam> wrote in message
news:uxNZPiaDHHA.3476@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have the following script which enables full text search on the
> database:
> ----
> sp_fulltext_database 'enable'
> sp_fulltext_catalog 'cntdocimg', 'create'
> sp_fulltext_table 'ContactDocumentImage', 'create', 'cntdocimg',
> 'PK_ContactDocumentImage'
> sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1,
> 'sDocType'
> sp_fulltext_table 'ContactDocumentImage', 'activate'
> sp_fulltext_catalog 'cntdocimg', 'start_full'
> sp_fulltext_table 'ContactDocumentImage', 'start_change_tracking'
> sp_fulltext_table 'ContactDocumentImage', 'start_background_updateindex'
> ----
>
> This script creates full text search for column "imgDocumentImage" of
> table "ContactDocumentImage" and defines type of stored document by
> looking at column "sDocType".
> Now, the problem is that i need to alter the column sDocType to make it
> nvarchar(10) instead of nvarchar(3) as it currently is; but when i run
> ALTER statement it says that sDocType column is used by some other object.
> Then i tried to disable full text search, and it did NOT help either.
> Could anyone please please please help me here!
> Thank you in advance,
> Andrey
|||Hi Hilary,
Thanks for the response! 1 as lcid works in SQL 2000, but you are right,
in SQL 2005 it has to be 1033.
It's fine if i have to drop the fulltext index, but i'm not sure how,
and which piece of the script i will need to run after.
Could you please help me based on the script below?
sp_fulltext_database 'enable'
sp_fulltext_catalog 'cntdocimg', 'create'
sp_fulltext_table 'ContactDocumentImage', 'create', 'cntdocimg',
'PK_ContactDocumentImage'
sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1,
'sDocType'
sp_fulltext_table 'ContactDocumentImage', 'activate'
sp_fulltext_catalog 'cntdocimg', 'start_full'
sp_fulltext_table 'ContactDocumentImage', 'start_change_tracking'
sp_fulltext_table 'ContactDocumentImage', 'start_background_updateindex'
Thanks a lot!
Andrey
Hilary Cotter wrote:
> First off I think this statement is incorrect
> sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1,
> 'sDocType'
> it should be something like this
> sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1033,
> 'sDocType'
> replace 1 with your lcid.
> Secondly, the full-text index is the problem here. There is little you can
> do without dropping the fulltext index, then making the change, and then
> readding it.
> One option is to come up with a new extension XXX lets say and bind the
> iFilter to this extension. So if the extension is config and you want to
> have the word iFilter index it, you would put XXX in the column for
> documents of the config extension and then add the office iFilter pesistent
> handler to XXX, like this
>
> [HKEY_CLASSES_ROOT\.XXX\PersistentHandler]
> @.="{98de59a0-d175-11cd-a7bd-00006b827d94}"
>
>
|||Try drop fulltext index on TableName
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MuZZy" <tnr@.newsgroups.nospam> wrote in message
news:456486B0.6050700@.newsgroups.nospam...[vbcol=seagreen]
> Hi Hilary,
> Thanks for the response! 1 as lcid works in SQL 2000, but you are right,
> in SQL 2005 it has to be 1033.
>
> It's fine if i have to drop the fulltext index, but i'm not sure how, and
> which piece of the script i will need to run after.
> Could you please help me based on the script below?
> sp_fulltext_database 'enable'
> sp_fulltext_catalog 'cntdocimg', 'create'
> sp_fulltext_table 'ContactDocumentImage', 'create', 'cntdocimg',
> 'PK_ContactDocumentImage'
> sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'add', 1,
> 'sDocType'
> sp_fulltext_table 'ContactDocumentImage', 'activate'
> sp_fulltext_catalog 'cntdocimg', 'start_full'
> sp_fulltext_table 'ContactDocumentImage', 'start_change_tracking'
> sp_fulltext_table 'ContactDocumentImage', 'start_background_updateindex'
>
> Thanks a lot!
> Andrey
>
> Hilary Cotter wrote:
|||Hilary Cotter wrote:
> Try drop fulltext index on TableName
>
I'm using SQL 2000 - it doesn't have DROP FULLTEXT statement
Any other ideas?
Thank you,
Andrey
|||try sp_fulltext_column 'tablename', 'columnName', 'drop',
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MuZZy" <tnr@.newsgroups.nospam> wrote in message
news:OAR8Eu$DHHA.1748@.TK2MSFTNGP02.phx.gbl...
> Hilary Cotter wrote:
> I'm using SQL 2000 - it doesn't have DROP FULLTEXT statement
> Any other ideas?
> Thank you,
> Andrey
|||Hilary Cotter wrote:
> try sp_fulltext_column 'tablename', 'columnName', 'drop',
>
Hi Hilary,
I actually tried even more than that to completely disable fulltext
search for the database:
sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'drop'
sp_fulltext_table 'ContactDocumentImage', 'drop'
sp_fulltext_database 'disable'
I was hoping it would disable fulltext search and "unlock" the column
sDocType for altering. It didn't help - i still get the same error when
trying to ALTER that column:
Server: Msg 5074, Level 16, State 7, Line 6
The object 'CompanyDocumentImage' is dependent on column 'sDocType'.
Server: Msg 4922, Level 16, State 1, Line 6
ALTER TABLE ALTER COLUMN sDocType failed because one or more objects
access this column.
Any ideas?
Thank you,
Andrey
|||I am unable to repro your problem. What sp are you running?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MuZZy" <tnr@.newsgroups.nospam> wrote in message
news:456A53A3.5000603@.newsgroups.nospam...
> Hilary Cotter wrote:
> Hi Hilary,
> I actually tried even more than that to completely disable fulltext search
> for the database:
> sp_fulltext_column 'ContactDocumentImage', 'imgDocumentImage', 'drop'
> sp_fulltext_table 'ContactDocumentImage', 'drop'
> sp_fulltext_database 'disable'
>
> I was hoping it would disable fulltext search and "unlock" the column
> sDocType for altering. It didn't help - i still get the same error when
> trying to ALTER that column:
> Server: Msg 5074, Level 16, State 7, Line 6
> The object 'CompanyDocumentImage' is dependent on column 'sDocType'.
> Server: Msg 4922, Level 16, State 1, Line 6
> ALTER TABLE ALTER COLUMN sDocType failed because one or more objects
> access this column.
>
> Any ideas?
> Thank you,
> Andrey
|||Hilary Cotter wrote:
> I am unable to repro your problem. What sp are you running?
>
SP4 with Hotfix for SP 4

PLS HELP! SQLServerAgent Service could not be started

Hi,
SQLServerAgent Service stopped and could not be re-startated. I kept getting
the following meassge:
"Could not start serveragent on local computer. The service did not return
an error. This could be an internal windows error. If problem persists,
contact your system administrator"
It has been working before it stopped and couldn't restart after server
re-boot. It is using the same domain account with Admin rights as the
SQLServer Service which is has restarted and running.
This is very urgent please because it is a Production Server.
Thanks for your help.
Egbon.Anything in the Agent errorlog?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Egbon" <vnjowusi@.gosps.com> wrote in message news:eFx2%23dhmDHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> SQLServerAgent Service stopped and could not be re-startated. I kept getting
> the following meassge:
> "Could not start serveragent on local computer. The service did not return
> an error. This could be an internal windows error. If problem persists,
> contact your system administrator"
> It has been working before it stopped and couldn't restart after server
> re-boot. It is using the same domain account with Admin rights as the
> SQLServer Service which is has restarted and running.
> This is very urgent please because it is a Production Server.
> Thanks for your help.
> Egbon.
>
>|||I have this message in the Application event log.:
"SQLServerAgent could not be started (reason: Unable to connect to server
'(local)'; SQLServerAgent cannot start). "
Thanks
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u0eAUfhmDHA.2404@.TK2MSFTNGP12.phx.gbl...
> Anything in the Agent errorlog?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Egbon" <vnjowusi@.gosps.com> wrote in message
news:eFx2%23dhmDHA.1084@.tk2msftngp13.phx.gbl...
> > Hi,
> > SQLServerAgent Service stopped and could not be re-startated. I kept
getting
> > the following meassge:
> >
> > "Could not start serveragent on local computer. The service did not
return
> > an error. This could be an internal windows error. If problem persists,
> > contact your system administrator"
> >
> > It has been working before it stopped and couldn't restart after server
> > re-boot. It is using the same domain account with Admin rights as the
> > SQLServer Service which is has restarted and running.
> > This is very urgent please because it is a Production Server.
> >
> > Thanks for your help.
> >
> > Egbon.
> >
> >
> >
>|||I think Tibor is talking about the SQL Server Agent error log and not the
Windows Application event log...
look at this topic in BOL...
'Using the SQL Server Agent Error Log'
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Egbon" <vnjowusi@.gosps.com> wrote in message
news:OSSuabimDHA.1960@.TK2MSFTNGP12.phx.gbl...
> I have this message in the Application event log.:
> "SQLServerAgent could not be started (reason: Unable to connect to server
> '(local)'; SQLServerAgent cannot start). "
> Thanks
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:u0eAUfhmDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > Anything in the Agent errorlog?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Egbon" <vnjowusi@.gosps.com> wrote in message
> news:eFx2%23dhmDHA.1084@.tk2msftngp13.phx.gbl...
> > > Hi,
> > > SQLServerAgent Service stopped and could not be re-startated. I kept
> getting
> > > the following meassge:
> > >
> > > "Could not start serveragent on local computer. The service did not
> return
> > > an error. This could be an internal windows error. If problem
persists,
> > > contact your system administrator"
> > >
> > > It has been working before it stopped and couldn't restart after
server
> > > re-boot. It is using the same domain account with Admin rights as the
> > > SQLServer Service which is has restarted and running.
> > > This is very urgent please because it is a Production Server.
> > >
> > > Thanks for your help.
> > >
> > > Egbon.
> > >
> > >
> > >
> >
> >
>

Pls help!

Hi all

i am a new user to .NET comunity and i need help in the following problem

1. I have a table called student_master which has rollno a PK and various other fields which are used to get the corresponding details.

2. I also have a table called attendance with the following fields rollno an FK, date and hrs

3. what i need to do is, on selection of a day, i need to copy all the roll numbers from te student_master table and the date to the selected date and hrs as default 0 by just one transaction

example

student_master table

rollno |name

1 | xxx

2 | xxx

what i should get in attendance table is somwwhat like this

rollno|date|hrs

1 |12 |0

2 |12 |0

1 |13 |0

2 |13 |0

Thanks in advance

Hi Grietcap

Not sure I understand you completely - especiallywhy you seem to have two dates in your returned table (12th and 13th)if you are selecting by a date. But in any case can I offer thefollowing select statement which may be of help:-

"SELECT[student_master].[rollno], [attendance].[date], [attendance].[hours]FROM [student_master] INNER JOIN [attendance] ON[student_master].[rollno] = [attendance].[rollno] WHERE(([attendance].[date] = @.date) AND ([attendance].[hours] = 0))"

You can run this select in the following VB.net function

Function MyQueryMethod(ByVal [date] As Date) As System.Data.SqlClient.SqlDataReader
Dim connectionString AsString = "server='XXX'; user id='XXXX'; password='XXXX';Database='XXXX'"
Dim sqlConnection AsSystem.Data.SqlClient.SqlConnection = NewSystem.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String ="SELECT [student_master].[rollno], [attendance].[date],[attendance].[hours] FROM "& _
"[student_master] INNER JOIN [attendance] ON [student_master].[rollno]= [attendance].[rollno] WHERE (([attendance].[date] = @.date) AND([attend"& _
"ance].[hours] = 0))"
Dim sqlCommand AsSystem.Data.SqlClient.SqlCommand = NewSystem.Data.SqlClient.SqlCommand(queryString, sqlConnection)

sqlCommand.Parameters.Add("@.date", System.Data.SqlDbType.DateTime).Value = [date]

sqlConnection.Open
Dim dataReader AsSystem.Data.SqlClient.SqlDataReader =sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function

Hope this helps

Regards

Mike

Friday, March 30, 2012

Poor performance with NEWID()

Poor performance with NEWID()

Poor performance with functions in selects

Wednesday, March 28, 2012

Poor performance

Poor performance

Monday, March 26, 2012

ponit in time restore

ponit in time restore

ponit in time restore

Polling in 2005

Poison Message. The conversation endpoint is not in a valid state for SEND.

Friday, March 23, 2012

Point label - formatting decimal places

Wednesday, March 21, 2012

PLZ HELP: number of calls at a time

Plz Help!

Tuesday, March 20, 2012

PLS-00201 Error

pls..help me to find out the error in the code

PLS. HELP. SQL NEWBIE

Pls. help me optimize this stored procedure

Monday, March 12, 2012

PLS HELP: Problem altering columns with full text serach enabled

PLS HELP! SQLServerAgent Service could not be started

Pls help!

SQL compact edition

Blog Archive

About Me