How Would You Handle This Situation?

Apr 29, 2008

Here is the basic situation.

(1) I have 2 packages that run independently of eachother.
(2) Both are scheduled in SQLServer Agent Jobs as separate jobs.
(3) Job A is scheduled to run every 12 hours and Job B is scheduled to run every 10 minutes.
(4) However, I want to prevent Job B from running if Job A happens to be running.
(5) It is unknown exactly how long Job A will take to finish so I can't schedule Job B around it.

The way I wanted to approach this situation is as follows.

Within Job A's package, create a "marker" file when the package starts and delete it when the package finishes. So the existence of this marker file will tell Job B's package if it should run or not.

The concept is simple, but I'm not sure how to implement this.

For example, to create the marker file, I would use a File System Task, but I don't see an option in there to "create file". (However, I do see an option for "delete file".) Also, what task would I use in Job B's package to check if the marker file exists.

Lastly, If you have better approach, I would like to hear about it.

Thank you.




View 11 Replies


ADVERTISEMENT

Error Log Peppered With --&&> 'The Conversation Handle Is Missing. Specify A Conversation Handle.'

Dec 3, 2007

Hi

I'm using service broker and keep getting errors in the log even though everythig is working as expected

SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.

I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.

In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'

error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'

I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?

i can provide code of the stored procs if that helps.

thanks.

View 10 Replies View Related

HELP - Urgent Situation

Feb 10, 2004

Hey all

I've got a DTS package with a script that does this:

Set xlApp = CreateObject("Excel.Application")

This fails though. Do I have to install MS Office on the machine the SQL Server is on? Or can I simply copy a few DLLs over and register them??

Thanks! I gotta get this solved within 5 hrs :(

View 9 Replies View Related

Sorting Situation

May 14, 2004

Hi,

I am trying to get data as it is available in the table but every time I run sql statement it shows in asending order. How can I avoid this asending order situation?

Select distinct db_contract,title1 from titles where db_contract=39624
db_contract Title1
39624 HIGHWAY
39624 M-24-001
39624 M-24-002
39624 R-24-003
39624 M-24-006
39624 Z-24-007

Any help will be appreciated.

View 7 Replies View Related

Do I Use An Index In This Situation?

Jun 1, 2008

Hi everyone -

I am relatively new to this and trying to make my database as efficient as possible.

Here is the situation. In my database, there are multiple "stocks" that each have a "pricing history". When something happens that changes the price of the stock, an entry containing information about the time the change occured, who changed it, etc. is added.

I have many stocks. The information of these stocks is independant in the sense that I never really need to grab/compare information about two stocks at once. I only look at one stock at a time.

So, do I set up a "pricing history" table for every single stock (pricinghist1,pricinghist2,etc.)? Or do I set up one "pricing history" table it and index the table by stock number?

Are these equivilant? Is one more efficient than the other?

Thanks!
N

View 3 Replies View Related

A Tough Situation To Get ID....

Apr 17, 2008



Hello All

I have a table called Tax Act

In that I have Tax Act ID. Which is supposed to be NOT NULL.

I have to create IDs by my self using some MAX and MAX+1 incrementing function.

Please guide me on how to

View 3 Replies View Related

What's The Best Way To Approach This Situation?

Jul 12, 2007

Hello everyone,



I'm creating a database for a new application and I'm currently facing a design problem, regarding a business requierment for the membership module.



The Membership Module of the application has several business requierments, specified by the client. One of them is the ability to add and remove details about their members. So far, I've created a schema, named Person, which will contain a number of tables responsible for everything related with the membership, as an individual.



To help you guys understand the design I'm trying to implement, I'll post the fields of two of the tables that belong to the Person schema, as follows:



Person.Base

Id (uniqueidentifier)

UniqueIdTypeId (uniqueidentifier)

UniqueId (uniqueidentifier)

Password (char(88))

PasswordSalt (char(10))

PasswordRecoveryQuestion (nvarchar(256)) [NULLABLE]

PasswordRecoveryAnswer (char(88)) [NULLABLE]

CreationDate (datetime)

AuthenticationWindowStartDate (datetime)

AuthenticationWindowAttemptCount (tinyint)

IsActive (bit)

IsBanned (bit)

IsLocked (bit)

Status (bit)



Person.Emails

PersonId (uniqueidentifier)

EmailAddress (nvarchar(256))

CreationDate (datetime)

ValidationCode (char(10))

ValidationDate (datetime) [NULLABLE]

IsValid (bit)

IsPrimary (bit)

IsRollbackTarget (bit)

Status (bit)



So far so good. This design works great to preserve the data integrety. Nonetheless, this is where the problems start. Now, imagin you need to let someone from that company add an item to the user (through the application). Let's say we want to allow the company application manager to add an item to the person called "PreviousEmployer". Such item would then be used for statics, thus would probably need to be indexed.



In order to meet this business requierment I would create some addicional tables. Let's get started:



Person.CustomFields

Id (uniqueidentifier)

Name (nvarchar(50))

Description (nvarchar(3000)) [NULLABLE]

Status (bit)



Person.CustomField_Value

FieldId (uniqueidentifier)

Value (nvarchar(450))



This could work just fine if both are indexed (that's why the nvarchar size is set to 450). But I'm guessing this is far from the optimal solution for many reasons, one of them being the efficiency of the index if the company decides to go and use this for a flag (true or false [bit]). Another "solution" breaking scenario would be if the company wants to add the CV of the person, situation in which we were unlikely to be able to add a file in this datafield.



How would you guys approach this issue? The bottom line is that the client needs to be able to add pretty mcuh any type of custom field and perform searches againts it. So, besides being a dynamic solution it needs to be efficient.



Best regards and thanks in advance.

View 11 Replies View Related

How Can I Use Cascade Delete For The For The Following Situation.

Jan 25, 2008

Hi, I have the following tables:
Categories {Category_ID, Column2, ...} 
Articles { Article_ID, Category_FK, Column3, ...}
Discussions {Discussion_ID, Article_FK, Column3, ...}
Now, all what I have is just category_ID value (Let us say 3), how can I do cascade delete to delete category's record that its ID = 3 and delete all articles and all discussions that found in that category? 
 

View 2 Replies View Related

Very Urgent Situation (SQL Server 6.5)

Apr 3, 2002

I have a very urgent situation and to do with SQL Server 6.5, cannot start and the error message I got was: Initdata: Suballocation for buffer pages failed (838860 Bytes requested. Can anyone help me urgent matter.

Regards,

View 1 Replies View Related

Reproduce A Deadlock Situation

Dec 6, 1999

I need help on reproducing a deadlock. The sample that I have so far is only creating blocking and it never deadlocks. It's because I'm not sure what order the system tables are being help when creating temp tables in a transaction, therefore, I can't tell which pages need to be requested from another transaction to cause the deadlock. Any help will be much appreciated.

Thanks,
Mike

View 2 Replies View Related

Is This A Stored Procedure Situation?

Jun 21, 2004

We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.

The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.

So... here's my question:

After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha – if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!

Thanks!
whill96205 the Noob :confused:

View 3 Replies View Related

How To Fix This Situation On Replication?[Urgent.]

Jun 26, 2004

Hi, all..
I by mistake Overwrites distribution DB.
Server1 and Server2 replicates each other.
Applications are using Server1, and Server2 is back up purpose.
When server1 fails application redirected SErver2, using same data since there is replication.
Server2 had distribution DB.

Now there is no distribution DB.. it's messed up...
I want to restore replication between Server1 and Server2.
I tried to make replication from Server1, I got errors..

How can I do this???

View 1 Replies View Related

Tricky Replication Situation

Nov 18, 2005

I have a Transaction replication running between 2 SQl 2000 Servers. The distributor is on seperate machine.

Server1 is an online Db which is sending replicated trn to Server2 which is a reporting Server. Because of space issues, I have to remove about 100 Million Rows from Server1 Table without affectiing Server2 Table (Server 2 Retains those rows)
Is there any Stored Proc available to disable the replication only during that time ?

Thanks

View 3 Replies View Related

Produce All Combinations For A Situation

Sep 6, 2013

How would I write a query to produce all combinations for a situation such as the following?

Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.

Adjective table looks like e.g.

ID Adj
1 good
2 so so
3 bad

Noun table looks like e.g.

ID Noun
1 apple
2 orange
3 banana

And the result set would look like

This is good apple.
This is so so apple.
This is bad apple.
This is good orange.
This is so so orange.
This is bad orange.
This is good banana.
This is so so banana.
This is bad banana.

I would take a stab at this myself and post even something that doesn't work...

View 1 Replies View Related

Do I Need A Derived Table For This Situation

Feb 1, 2007

OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW.
CONDITION 1: RateReview field should have yesterday's date
CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier
CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL
ONe Customer can have many transwactions
Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets.
Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and
do not send any email. This would be done by some tool known as StrongMail.
SELECT
CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID,
CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID,
Film_id as MovieId,
First_nm as FirstName,
Last_nm as LastName,
Email_nm as EmailAddress
FROm
Table1 where RateReview_dm >= dateadd(day, datediff(day, 0, getdate()), -1) -- Greater or same as Yesterday day
and RateReview_dm < dateadd(day, datediff(day, 0, getdate()), 0) -- Less than today's date
and
and Terminate_dm is null
(I don;t know what condition to give that same customer good should not be send email again if send once)
i don't know whether i need to create a derive table or it can work without drive table

Cananyone help me with this query please

View 2 Replies View Related

Stuck - Cant Figure Out A Query For This Situation

Jan 3, 2006

I have run into a problem, I have 2 fields in my database, both keyfields:Table 1=====Field X <key>Field Y <key>In field X, there are say about 3 records for each unique Field Y. Ilet my users query the data base like follows:Enter the Codes you want: 1000 and 3000 and 8500So I want to pick up records where there will be the above values forAll Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there iseven ONE of the X values not matching a record without a matching Xvalue, leave it out.i.e:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBX=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCWhen the query runs, I want to see the following records:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBBUT NOT:X=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCbecause one of the X values was not matched (the last X value =9999 andnot one of the requirements of the search)So I guess I want something like this:SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES(X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD^^ Hope the above makes sense... but I am really stuck. The only otherway I think I could do it is, copy all records that match all 3 Xvalues into a temp table, and weed out any that are missing any one ofthe X values after they are copied but, I am running this on MYSQL 5.0Clustered, and there is not enough room in memory for it probably...and query time has to remain under a second.Anyhelp would be appreciated...

View 2 Replies View Related

Help With SQL Update Query -- Not Sure How To Approach This Situation

Jul 20, 2005

Hello,I have a rather large table in MS SQL 2000 that I'm writing reports inCrystal from, but I'm unsure how to get the various data files (fromdifferent departments) into the table. Below is a small chunk of thetable:DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;Be nefitsI'm getting data from multiple places, but as I import the data, Iwant it to populate the table with DeptCode and Type being the primarykeys.If someone sends me the following file:DeptCode;Type;AveMonthVolume;WorkedFTE1000;Budget;100;2001010;Target;233;433And I get the following file from another source:DeptCode;Type;TotHours;Benefits1000;Budget;433;4001010;Target;33;43I want a simple way to import all this into the table so it looks likethis:DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;Be nefits1000;Budget;100;200;433;4001010;Target;233;433;33;43The data will be coming in delimited text and in Excel format. Iassume I can import his into MS SQL Enterprise Manager directly, but Ican't find any simple way of running in update query to import mydata. Also, I'd hate to use MS Access as the middle-man if at allpossible... but if this is the only option, I'll do it.Does anyone have other suggestions? Above is only a small example...the table has about 25 columns with various data I'll be collectingfrom 3-4 departments, so it's a hodge podge of data to combine intoone report.Thanks for any suggestions or ideas in solving this. Thanks inadvance...Alex.

View 2 Replies View Related

Quagmire Situation (Database Or Network)

Jul 6, 2007

I am here in a peculiar situtation i am not very sure the problem which i am facing as of know is a Database or Network.



One of our organisation application is connected to SQLServer 2005 (clustered) database was working fine before few days back we gone though a windows 2003 server patch update activity and server was restarted and a failover occured.



The day patch activity is done we are facing data loss problem over the network.



Checked the SQLServer2005 ErrorLog but no error related to database.



Few user are facing problem as mentioned below

Error : Checknetwork Documentation

Search the net but no satisfactory answer.



Please help me with a resolution or work around.



One more Issue :

While pinging the server some time we get request Time out.This Request time out is for 1 ms.

Do this also effects the thick client application connectivity with the Database.









regards

Sufian

View 1 Replies View Related

Would Service Broker Work For This Situation?

Apr 17, 2007

So we have Jobs that are created via web portal and stored in the database.



There is a "Job Processor" webservice that currently polls the database to find out if there are any new jobs to process.



Ideally I would like to place these Jobs in a queue and have the queue fire a message to the Job Processor indicating that there is work to be done.



Thoughts?

View 1 Replies View Related

How To Update Into A Sql Server... Simple Situation Please Help

Apr 29, 2008



hello,

I recently transfered date into my sql server source table from Access data base but at that time one column was blank. ID and mainID so mainID was missing

Now i received and excell sheet. in which again i have ID and mainID both provided .

please let me knwo how do i update those mainID into the sqlserver cource table column using available ID.

thank youi

View 3 Replies View Related

Servers Different Location How To Solve Situation

Sep 19, 2007


Is there a way to connect to two servers located at remote location in ssis.
Here is what i want to achieve..
I Have created a package which will load data from a text file on one sql server1.
What i want to do is after data is loaded i want to connect to different server on the remote location and then i want to check to compare the data from server 1 with the existing data in server 2.If the data in server 1 has new records then it should insert that data in to table in server 2.else update.

Servers are located remote and are under two different networks.
Can this be achieved using ssis or how can i implement this.

View 4 Replies View Related

Conversation Handle Reuse And Conversation Handle XXX Not Found

Jan 18, 2008



We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?

Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?

I have attached an example of one of the queuing procs below:




Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);

SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)

END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;

View 2 Replies View Related

Weird Situation - Stored Procedure Executed Twice

Aug 16, 2006

In SQL 2005 I have a stored procedure as below:@sub_no smallint OUTPUTBEGINBEGIN TRANSACTIONINSERT...INTOSET @user_no = (SELECT ...... FROM ....WHERE sub_no = @sub_no)INSERT...INTOEXE another_stored_procedure (it includes also BEGIN...COMMIT)EXE another_stored_procedure (it includes also BEGIN...COMMIT)SET @sub_no = .......COMMIT TRANSACTIONWhen Visual Studio (ASP.NET 2005) is open and I run the program,procedure is executed once without any problem. If I publish theproject and put files on another server (or even use the publishedfiles from my machine) I have an error because stored procedure isexecuted twice. @sub_no is used as input/output parameter.I followed/trace the steps in procedure and it seems that procedure isexecuted once with correct value of @sub_no. The second time procedureis executed, the value that it was assigned before COMMIT is used,which gives an error because the INSERT values have NULL values.In ASP.NET I call the store procedure once.What could be the reason ?Thanks a lot for any help.

View 2 Replies View Related

PLEASE TELL ME HOW TO WRITE A BATCH FILE PROGRAM FOR THIS SITUATION

Apr 29, 2008

hello,


I have to write a batch file program that will just copy the names of the files from a particular folder.

Which means some command that could go into that folder through a path provided and get/copy just the name of the file.



Regards

Thank You

View 2 Replies View Related

Nasty Mirroring Split Brain Situation

Mar 27, 2007

Yesterday I had a nasty mirroring problem.



The principal and the mirror server are both running SQL Server 2005 64-bit Enterprise Edition. Witness is running Workgroup Edition. We are running in high availability mode (SAFETY ON).



3 nights before something strange happened and 1 database failed over (out of the six being mirrored) for some unknown reason. The other 5 didn't. We failed that one back and all seemed ok over the weekend.
We came in Monday and found that the main live OLTP database was showing as the Principal on BOTH servers (in SMO/Management Studio and also in sys.database_mirroring). This is the "split brain" scenario that the presence of the witness is supposed to prevent. Both databases were accessible with a USE statement - clearly not right.



I pondered what to do, eventually I decided to remove mirroring from this database. That was ok until suddenly a few minutes later we realised the (original) Principal was in recovery! uisers of course were kicked out/unable to connect. I tried to force recovery with RESTORE DATABASE dbname WITH RECOVERY but it complained users were connected!



I had to KILL the users then recovery proceeded and the database became available again. I forced the mirror offline to prevent accidental usage.



This is obviously a nasty situation where mirroriing - which is supposed to prevent downtime - actually caused it instead.



I intend to log a call with CSS but I wanted to warn other users if they encounter something similar - it has shaken my confidence in mirroring quite severely.

View 4 Replies View Related

Question On How To Display Data In This Unique Situation Using Matrix.....

Mar 16, 2007

Hi guys,

Man I do come up with strange scenarios, but that is the joy of working in software field right ? ;-)
First off, thanks to anyone taking their time to read this, and Ihope this post paints a clearer picture better than my previous posts.

I have an old stored procedure (which I didn't create) that produces a dataset of the following:-

((All names and values had been changed to protect confidentiality))

region           agent_type               mailpackage1                    mailpackage2              mailpackage3
New York        Agenttype1                       2000                             2300                          0
New York        Agenttype2                          0                                   0                               5
New York        Agenttype3                        150                                2                             4000
Central            Agenttype2                        1234                              5678                        9
Central            Agenttype4                        435                                1                               0
MidWest         Agenttype1                        555                                0                               0
West                Agenttype1                        1                                    45                             0
West                Agenttype2                        0                                    2                               3

A little bit of explanation:-
Each region can have any type of agents, specified by the number to distinguish different agent types. these agent types mail specific packages to their customers depending on the situation and what the customers asked for. the numbers in each mail package indicate the total that had been sent out by a particular type of an agent. So in this case we are not dealing wtih how many agents are there, just how many packages had been sent out by a specific type of an agent in a region.

Previously the report was produced like you would see in the above dataset. However the client would want it the other way around. Though I didn't show it here, there are plenty of other packages but I am picking three for clarity sake.

So the "new" Report would have to look something like this.

Region: New York
                                                    AgentType1              AgentType2                AgentType3              AgentType4
Package1                                        2000                             0                                  150                            0
Package2                                        2300                             0                                  2                                0
Package3                                          0                                  5                                  4000                          0

--------------- break page ---------------------------

Region: Central
                                                    AgentType1              AgentType2                AgentType3              AgentType4
Package1                                        0                               1234                              0                                   435
Package2                                        0                                5678                             0                                      1
Package3                                        0                                  9                                  0                                      0

---------- break Page -------------------- and so on

I had created a table in the RS  that looked like the above with expressions written into the each cell that holds a value. The expression is

=IIF(Fields!agent_type = "AgentType1", mailpackage1.Value, Cint(0)) in the first row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage2.Value, Cint(0)) in the second row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage3.Value, Cint(0)) in the third row, First column of the table.
And so on....... alternating between agent_type and mailpackage for each cell.

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
                                                    AgentType1              AgentType2                AgentType3              AgentType4
Package1                                        2000                             0                                  0                                0
Package2                                        2300                             0                                  0                                0
Package3                                          0                                  0                                  0                                0

--------------- break page ---------------------------

Region: New York
                                                    AgentType1              AgentType2                AgentType3              AgentType4
Package1                                          0                                  0                                  0                                0
Package2                                          0                                  0                                  0                                0
Package3                                          0                                  5                                  0                                0

--------------- break page ---------------------------


Region: New York
                                                    AgentType1              AgentType2                AgentType3              AgentType4
Package1                                          0                                  0                                  150                            0
Package2                                          0                                  0                                  2                                 0
Package3                                          0                                  0                                  4000                          0

--------------- break page ---------------------------

(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

The question is, is there anything else I  could have done to prevent this  ? as you can see, the data is correct and placed in their right cells but somehow, they won't join together. I got a feelin that it has something to do with the expression that I had put  in each cell.

Can someone help or point me in the right direction ? This is really bothering me and I couldn't figure out why it was doing this. Couldnt find any links or maybe i am putting in the wrong keywords in the search. Thanks muchly !

Bernard Ong

View 14 Replies View Related

Strange Situation With Windows Authentication And Undocumented Function GET_SID

Dec 21, 2004

SQL Server 2000
Windows 2000 Advanced Server

We recently moved our servers from one domain to another. Now, we can't grant Windows users access to any databases. We CAN create the user IDs successfully via Enterprise Manager, and get a corresponding row in MASTER..SYSXLOGINS, but can't click on the Database Access tab in the SQL Server Login Properties dialog and grant accesss to any databases. When we try to do so, we get error 15401, "Windows user or group xxx not found". The underlying call is to MASTER..SP_GRANTDBACCESS, and running that via Query Analyzer returns the same error (naturally)

Looking through the code of SP_GRANTDBACCESS, I've determined that what is failing is a call to the undocumented TSQL function GET_SID. This proc takes two parameters, the first is either G<nt group name> or U<nt user name> and the second is NULL in the call in SP_GRANTDBACCESS. If I execute

SELECT GET_SID('U<valid user>', NULL)

it returns NULL, however, if I run

SELECT SUSER_SID(<valid user>)

then I get the Windows SID of whatever valid user name I supply.

We have 3 servers in question, namely production, development and test. We noticed the problem on production. Curiously enough, development and test worked fine.

NOW THE PLOT THICKENS. If I run this query on the dev box....

SELECT SUSER_SID(<id>), GET_SID('U<id>',NULL)

...the first function returned the SID, BUT THE SECOND FUNCTION DID NOT!!! How could that be? Clearly GET_SID was working inside of SP_GRANTDBACCESS, but not as a discrete call. So I went into the master database and added code to print out the SID returned by GET_SID to the proc. Lo and behold, SP_GRANTDBACCESS promptly failed with a 15401 error. It continues to get 15401s now, even after I returned it to the original code. What gives? Now my dev box has the same error production has, and all I did was recompile SP_GRANTDBACCESS a couple of times. FWIW, I did *not* ever make any changes to SP_GRANTDBACCESS on production.

Why doesn't GET_SID() work outside of SP_GRANTDBACCESS?
Why did recompiling SP_GRANTDBACCESS break it permanently?

It almost seems like the query compiler can't correctly link a call to GET_SID to the correct function in some DLL, except I thought that the compiled code didn't survive a restart, and all TSQL procs were recompiled the first time they were called after a restart. If that's the case, then the compiler is - or at least was - producing a functioning compiled version of SP_GRANTDBACCESS after every restart.

View 3 Replies View Related

Instance TCP Port Misconfiguration On Cluster Leads To Unrecorverable Situation....

Jan 10, 2007

Hi all. I have ran into a annoying situatin on sql cluster. Config is:
w2k3 EE x64 r2 - two nodes
sql 2005 x64 standart v2153
2instances, active-active cluster.
While changing tcp port configuration of one instance (using configuration manager) I have acidentaly created that setting:
- All IP dynamic port and All IP port was set to same number while separate IPs have enabled dynamic ports and does not have static port set.
This situation leads to unability of starting the instance anymore. There is no need wide explanation, its quite clear - while starting instace, it was configured with static port and with dynamic port with the same number, so port conflict error takes place. Problem was how to escape from this situation. On non clustered sql is quite easy, because these settings are stored in registry and also can be changed thorough Configuration manager, easy solution. Nor on the cluster.. Modifying the registry even on both nodes was unefective, every try to start instance fails with port conflict error and bad config was saved to registry again. I wasnt able to find where this config resides and change it to corect values. I have revealed only that it is not stored in instance master DB (restoring it from backup). Looks like its held somehow by cluster service itself, but I didnt find where it should be and how to edit it. So the last possible option take place (time was against me) - reinstalation of whole clustered instance. Nice example of how one click can create you a 2 days of work... Iam still interested if there is a smooth and easy way out of this situatin:-) So if anyone knows..... If there is not, I realy warn everybody.

DAvid

View 1 Replies View Related

Is There A Better Way To Handle This IF..ELSE IF?

Mar 30, 2004

Do I have other option beside using IF..ELSE IF? TIF



-- GET INFORMATION OF THE JOB
DECLARE @JOBIDAS Char(10)
DECLARE @VRUSERVICEHRSAS Decimal(18,2)
DECLARE @VRUSERVICEMINAS Decimal(18,2)
DECLARE @BILLEDFLATAS Decimal(18,2)
DECLARE @BILLREGRATEAS Decimal(18,2)
DECLARE @MIN_HRSAS Decimal(18,2)

DECLARE @COUNT_GREATER_MINTinyInt
DECLARE @COUNT_LESS_MINTinyInt

SET @VRUSERVICEMIN = 46
SET @BILLEDFLAT = 0
-- PROCESS ONLY RECORDS WHERE THERE IS NO FLAT FEE
IF @BILLEDFLAT = 0
BEGIN
IF @VRUSERVICEMIN BETWEEN 0 AND 15
BEGIN
SET @VRUSERVICEMIN = .25
END
ELSE IF @VRUSERVICEMIN = 15
BEGIN
SET @VRUSERVICEMIN = .25
END
ELSE IF @VRUSERVICEMIN BETWEEN 15 AND 30
BEGIN
SET @VRUSERVICEMIN = .5
END
ELSE IF @VRUSERVICEMIN = 30
BEGIN
SET @VRUSERVICEMIN = .5
END
ELSE IF @VRUSERVICEMIN BETWEEN 30 AND 45
BEGIN
SET @VRUSERVICEMIN = .75
END
ELSE IF @VRUSERVICEMIN = 45
BEGIN
SET @VRUSERVICEMIN = .75
END
ELSE IF @VRUSERVICEMIN > 45
BEGIN
SET @VRUSERVICEMIN = 1
END
END

PRINT @VRUSERVICEMIN

View 6 Replies View Related

How Can I Handle An Error

Apr 1, 2007

Is it possible to catch and error and then keep the process going in a stored procedure?
So if an update encounters a primary key violation on a row, is it possible to skip that row and keep the process going?

View 4 Replies View Related

How To Handle Particular Sql Error

Oct 21, 2007

Hi! I have some try .. catch block trying to insert some data into database. During its action duplicate key row insert error could raise, for example. The question is how could I know distinguish it from other sql errors? Object ex (Catch ex As Exception) has only message property '{"Cannot insert duplicate key row in object 'dbo.Group_Courses' with unique index 'IX_Group_Courses'.The statement has been terminated."}' and type System.Data.SqlClient.SqlException. Knowing the type of error is not enough, because there are different SqlExceptions. Even the message is not unique for this error, because now i deal with 'dbo.Group_Courses'  and then it could be other table. Is there something that unique identifies each error? For example error code. If it exists, where could I get it?
Thanks in  advance!
 

View 2 Replies View Related

How Does Sql Handle Dates

Mar 13, 2004

I test my the now function and it is getting the right date. When I try to send that to the sql database I have it turns it into 1/1/1900. Does anyone know why this is happening, I have tried everything Here is my code:

sql = "Insert into tblguestbook(date, name, city, state, email, Url, Comments)Values ('"
sql = sql & Request.Form(Now) & "','"
sql = sql & Request.Form("nametxt") & "','"
sql = sql & Request.Form("citytxt") & "','"
sql = sql & Request.Form("statetxt") & "','"
sql = sql & Request.Form("emailtxt") & "','"
sql = sql & Request.Form("urltxt") & "','"
sql = sql & Request.Form("commentstxt") & "');"

View 1 Replies View Related

How To Handle Max Row Size

Dec 31, 2004

Hi,

I have a table with one field set at nvarchar (4000)
This is sometimes not big enough and I get an error that the max row size has been reached.

How do people handle the error when the max row size is reached and gracefully inform the user?

Also, should I really be using Ntext instead, would this be better - is there a performance penalty?

Much obliged.

RG

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved