How Would You Handle This Simple Task?
Apr 10, 2008
Say I have 10 tables that need to be loaded from one database to another. There is minimal transformation from source to target and it's pretty much a column to column mapping.
Would you make 10 separate packages, each one loading a specific table, or 1 package that loads all 10 at the sametime?
My inclination is to make 10 separate packages because there mostly will be foregin key constraints on some of the tables. And if I created 10 packages, I would make a 11th package (I guess some people call this the master package) that sequuences the run order for the 10 individual ones.
In this scenario, say I need to have a configuration file that sets the environment (dev vs prod). Would you make a confiuration file for each of the individual packages (this would be repetative, setting the same name and value 10 times) or is there a way to have a configuration for the master package where you would set the value just once?
View 8 Replies
ADVERTISEMENT
Feb 4, 2005
I need to find a better way to handle DTS Job Failure issue. Currently, we have about 50 jobs which executed through DTS packages. Everytime when sources were not there or came in late, the DTS sent out an email to my page which I carried every day. Some those came in during the holidays even though I know the source party won’t generate the source files at that day.
Trying to avoid to get beep everytime when job was failed. Someone suggested that it is possible to add kind of executable file within the Custom task and let it trigger the DTS packages. If, for example, a holiday then don’t run the package so I won’t get the page.
Any hints suggestions would be greatly appreciated
J827
View 5 Replies
View Related
Jun 29, 2006
I'd need to create a file and then populate it. Any link/advice would be very appreciated.
Thanks in advance
View 5 Replies
View Related
Mar 25, 2008
Hi All,
I need to send out email when error occurs in the package. Is it a good practice to put the send email task in the event handler? Then MaximumErrorCount is set to 1. But for some reason, some time I saw more than one email are sent out. Please advise. Thanks
View 6 Replies
View Related
Apr 24, 2008
Hi
I am new to SSIS. I wanted to do a daily ETL load from sourceA to targetB database
Please let me know the best method to that i should follow to do the below task
1. Get the Yesterday's date or last months's from-date & to-date dynamically.
2. Pass the from-date and to-date to my source SQL [e.g select emp_name, emp_dept from emp where hiredate between @param1 and @param2]. Acually my source sql has subquery and the parameters has to be passed to the subquery's where clause.
3. Insert the resultset into a staging table in the targetB database.
4. Join this targetB table with another table in sourceC db
5. finally load the result set in to the targetT database table
6. How to configure/schedule this package so that it runs daily, automatically pass the yesterday's date to the package.
I have done this using Oracle PL/SQL but my requirement is to do this in SSIS. I am not sure which control flow or transformation items to be used to do this in an effecient way.
Please guide me.
View 4 Replies
View Related
Dec 8, 2006
I was creating an SSIS package, seemingly simple. One of the tasks I needed to do involved copying our production database to a seperate DB instance for staging. I wanted an SSIS package that would stage production so we could push and test changes.
My first problem is that I tried to create a simple Transfer Database task. When I tried to run the task, I got an error stating that our Stored Procedure, DtsRun, could not be scripted. It's an encrypted stored procedure.
A little annoyed, I decided that I'd do a transfer SQL objects instead. Now I don't want to hard-code my objects to xfer based on the current schema and edit this SSIS script every time we make a DB change; I just want it to copy ALL objects except for my encrypted proc. I'll just build a list with with another task.
I decided that I'll write a script that generates a list of proc names, and I'll pass that to the Copy SQL Server Objects task. Well apparantly I can't assign a collection value with a script. I have to create ANOTHER package that modifies THIS package because (1) The entire package isn't exposed to the script task and (2) object variables can't be used in expressions.
Am I missing something? So because I use a single encrypted stored procedure, I have to write 2 fairly complex packages to copy my database? That's just stupid. I can understand if SQL can't move the encrypted package while the database is online. I could deal with that. But the extremes I have to go to JUST to copy my database are truly rediculous.
I was really excited about SSIS, but I've been really dissapointed. The performance of the designer on my machine is really poor (I have a Core T2700 with 2GB RAM). Just about every error message I've gotten, both design-time and run-time, I've had to google because they're so obscure. And if you can only store blittable types and strings in variables, that severely limits the functionality of a package (without going to programmatic extremes).
Most of all, I'm really dissapointed in the expression system, If the CLR is loaded into the SSQL/SSIS runtime, then why am I coding it using ANOTHER proprietary coding syntax???? I thought .NET was meant to keep people from having to redesign the wheel???
Microsoft is constantly pushing C#, yet I can only script in VB.NET, which means I now have code-bases in different languages.
Overall, I'm really dissapointed with SSIS. The usefulness of the added functionality in SSIS packages is shrowded by the massive list of nuances.
View 8 Replies
View Related
Feb 26, 2007
I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis. The email is sent using an activex script in a "SQL 2000 DTS Package Task". When executed manually, the email is sent ok. When scheduled (and run under our SQL agent account), it fails. Can anyone point me in the right direction? Is this a permissions issue?
'-- this script seems to cause problems, but only when scheduled --
dim mailer
set mailer = CreateObject("CDO.Message")
dim cdoconfig
const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
set cdoconfig = CreateObject("CDO.Configuration")
with mailer
set .Configuration = cdoconfig
.BodyPart.charset = "unicode-1-1-utf-8"
.BodyPart.ContentTransferEncoding = "quoted-printable"
.Fields("urn:schemas:httpmail:importance").Value = 2
.Fields.Update
.Subject = "Notification"
.From = "donotreply@test.com"
.TextBody = "TEST"
.Bcc = "someone@test.com"
.Send
end with
'-------------
Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.
Any help would be appreciated.
View 2 Replies
View Related
May 28, 2007
I'd like to transfer some records between the following 2 tables. Surely this should be a no-brainer - what am i missing that is making this so impenetrable?
I am currently: Hoping someone can help me get here: (this is my first time of using SSIS btw).
here is the source table (MS Sql Server 2005, SP 2)
CREATE TABLE [dbo].[imagine_divs](
[div_mnemonic] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_date] [int] NULL,
[div_amount] [float] NULL,
[div_status] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_curr_mnem] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_upd_date] [int] NULL,
[div_payment_date] [int] NULL
) ON [PRIMARY]
here is the target table (Oracle 9i)
CREATE TABLE myschema.imagine_divs
(div_mnemonic NVARCHAR2(11),
div_date NUMBER(*,0),
div_amount NUMBER(20,5),
div_status NVARCHAR2(16),
div_curr_mnem NVARCHAR2(11),
div_upd_date NUMBER(*,0),
div_payment_date NUMBER(*,0))
/
I used the SSIS Import and Export wizard to copy data between the two tables, and attempted to execute it. I use Sql Native Provider on source, and Native Ole DBOracle Provider for OLEDB. however, I get an error:
[Destination - IMAGINE_DIVS [37]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-12571: TNSacket writer failure".
I notice that the wizard has created a data flow task with 3 steps: source - imagine_divs, Destination - IMAGINE_DIVS and "data conversion 1".
Data Conversion 1 seems to be taking my source nvarchar columns and converting them to DT_STR with twice the size (for example div_mnemonic become DT_STR, size: 22).
If I change the mappings in the OLE DB Destination Editor, such that only the numeric and date-typed columns are included in the transfer, it works fine.
If I include any string-typed column in the destination editor mappings, I get the TNS Packet Writer error. If I remove the Data Conversion step and connection teh source and destination tasks directly, i get validation errors saying that:
Error 2 Validation error. Data Flow Task: OLE DB Destination [294]: Columns "div_mnemonic" and "DIV_MNEMONIC" cannot convert between unicode and non-unicode string data types. Package4.dtsx 0 0
this is despite the fact that everything is unicode here (right?)
what in the world is going on?
View 4 Replies
View Related
Sep 4, 2007
Has anyone done this? I can't find anything in the documentation
that describes this. The closest I get is to the InnerObject property
of the TaskHost class. There is an example of programming a bulk
insert task. But I can't find anything on programmatically setting
the column mappings (source to dest) of a simple data flow task. Any
help is appreciated!
View 7 Replies
View Related
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
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
Jun 21, 2007
OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
Try
ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
thanks
John
View 5 Replies
View Related
May 26, 2004
Hey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
View 1 Replies
View Related
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
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
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
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
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
Jan 13, 2006
Hi,
I would like to handle a sql error in t-sql and return a certain value in case error occurs. For example if I would like to add a record I want to return a certain identity value or maybe a status of transaction (0 for incomplete, 1 for succesfull trans).
If error occurs in sql I cannot return any values back to asp.net because of What I am doing at the moment is catching an error in asp.net and then displaying an error message. Is there a way to return only a return value to asp.net and somehow handle the error in t-sql?
Thanks
View 1 Replies
View Related
Feb 28, 2001
I don't know what's wrong with SQL2000 setup, the problem is:
whenever I execute a query with a date/time such as 02/02/200,
SQL give me an error message saying that the date is "Out of Range".
Any ideas, thanks in advance.
View 4 Replies
View Related
May 20, 2002
I'm having trouble with something and I was hoping that you could point me in the right direction.
Here's the scenario:
I have a VB application that clients use to add records to a SQK 2K DB. The info they have added that day is shown in a grid. They have the ability to edit items in the grid, and then update those changes to the database. The problem is that sometimes they change the values to something they shouldn't. To combat this I've started experimenting with check constraints. In query analyzer I test the constraint by trying to update an entry to an 'illegal' value. When I do this, I get an error saying: "Server: Msg 547, Level 16, State 1, Line 1" and the change is not made. What I'd like to do is to give the user a dialog box notifying him of the error. Is there a way to have a sub-routine or stored procedure be triggered when a message of this type is generated?
My specs are: W2K pro clients, SQL2K on an NT4 sp6a server. Application is written in VB6.
Any help is greatly appreciated.
thanks,
-scott
View 1 Replies
View Related
May 15, 2001
I have a select statement that works, but I know there has to be a better
way( I apologize for being TSQL brain dead today). Here is the statement
SELECT PATIENT_ACPT_STATUS, DISP_NOTES, RECORD_ID, modified_by, last_name, ddate
FROM PATIENT_MEDICATION_DISPERSAL_
Where (ddate = convert(char(10),getdate(),101) and
(MODIFIED_BY = CURRENT_USER) and
rec_status = 1) or
(ddate = convert(char(10),getdate(),101) and
(PATIENT_ACPT_STATUS = 1)) OR
(ddate = convert(char(10),getdate(),101) and
(MODIFIED_BY = 'open'))
View 1 Replies
View Related
Jul 9, 2006
Hi All,
We have got a new HP server and SQL is preinstalled, But when we are trying to start SQL, it gives an error "The handle is invalid".
We tried to reinstall the SQL, but , being a server t doesn't allow to do so
Please assist.
regards,
Jatin
View 1 Replies
View Related
Sep 10, 2006
I have a need when a Update, Insert or Delete is done to a record in DB "A", it will send the appropriate UID to a different table in different DB "B".
My first thought was to have a trigger on the table in DB "A" simply call a stored procedure on DB "B" and do the UID.
However - my question is what is the best approach and what's the best way to establish the connection to DB "B" for the UID from within DB "A"? We can't use linked servers - DNSLESS string would be the preferred connect way. Not sure how to execute it within a trigger.
Is even using a Trigger to Stored Proc the best way?
What about Transaction Replication - which I've never attempted - is that a better way?
Just looking for some guidance here so I don't needlessly burn time down a path that isn't recommended or simply won't work.
Thanks as always for your input,
Peter
View 5 Replies
View Related
May 4, 2004
Hi,
I build a local cube from a relation database. In the database there are 1:n relations.
Is there a way to handle 1:n relations?
For example:
I have a table LOGGEDFLAW and a table LOGGEDREASON with a 1:n relation between them. We create a select statement of these tables and as an result we get duplicate records of LOGGEDFLAW each time more than 1 record of LOGGEDREASON are associated to 1 record of LOGGEDFLAW - this is the standard result I get with an relational JOIN operation. Now I want to count the LOGGEDFLAWs without the duplicates generated by the 1:n relationship.
Best regards,
Thorsten
View 2 Replies
View Related
May 7, 2008
Hi All,
Please help me out how to implement the locking in below scenario
Req -
There are two tables Table1 & Table2
If I will insert in table1 then related data fields will be auto updated in table2 , similarly based on the data in table2 table1 data needs to be updated.
Now the sync of table1 & table2 is working fine.
My prob is we are handling the updation/insertion from the UI screens . Two separate screen for each table. When we have multiple user accessing the screens say - User1 updates table1 and User2 updates table2 then we need to implement the locking so that at one time one screen will allow updation in the table1 and hence table2.
The other screen shouldnt allow updation in table2 and hence in table1.
This is very common locking functionality ...but am not getting any way to implement it , Please advise.
Srain.
View 3 Replies
View Related
Aug 23, 2007
I have a friend who is doing a voting application for one of his customers and they are concerned about the volume that sql server can handle. He's looking a single sql server 2005 with plenty of hd space and 4gb of memory. The app will look to see if you voted and then insert a record accordingly.
Are there any papers out there or apps that can show the amount a server can handle?
thanks.
View 8 Replies
View Related
Jul 23, 2005
I already asked this question; however, I am giving all the detailsnow:We get large files(millions of records) and we need to load it into ourtables using import export wizard. Some of the fields in the file canbe Null and so we are forced to create table with fields that allowNulls with default ''. However when we insert data into these tablesit puts Null in those fields even though we have a default '' (I do notthink we have any work around for that; do we?)Finally we need to go through each field and update it to '' if it is aNull and that takes LOT OF TIME.If (select count (*) from <tablename> where <columname> is Null) >0BeginUpdate <tablename>set <columnName> = ''where <columnName> is NullendPlease let me know if there are any work arounds for this crisis ?Thank you very much in advance!
View 1 Replies
View Related
Jul 20, 2005
I need to know if there is a better way to construct this SQL statement.(Error handling is omitted)MS SQL Server 2000Insert into FSSUTmpSelect a.acct_no, a.ac_nm, a.ac_type, 10, -1,-1 * sum(CHARINDEX(convert(char(4), b.post_yr), @post_yr) * CHARINDEX('-',CONVERT(char(2), b.post_prd - @post_prd - 1)) * b.prd_trn_amt),-1 * sum(CHARINDEX(convert(char(4), b.post_yr), @post_yr) * CHARINDEX('0',CONVERT(char(1), b.post_prd)) * b.prd_trn_amt)FROM GLAccounts a, GLBalances bWHERE b.cmpny_cd = a.cmpny_cdAND b.acct_no = a.acct_noAND a.cmpny_cd = @cmpny_cdAND ac_ctrl_type between '200' and '219'Group by a.acct_no, a.ac_nm, a.ac_typeThe part I’m wondering about is the 2 sum sections.The GLBalances table has following important fields:Post_yr -- the posting yearPost_prd – the posting periodPrd_trn_amt – The beginning balances if the period is 0, or the nettransactions for periods 1 through 12.The first sum gives the current balance as of the period @post_prd by addingall of the periods from 0 to @post-prdThe second sum is just the beginning balance.It is doing a conditional sum by using CHARINDEX to be 0 if the recordshould not be added and 1 if it should.There is a problem as it stands when you are looking for the balances whenthe @post_prd is 9 or greater because “b.post_prd - @post_prd – 1” willbe –10 or smaller. Then the CONVERT(char(2) ….. is an error, so CHARINDEXis 0 when it needs to be 1.I can fix that by using SIGN and it will work fine. What I what to know, isthere a better way to populate the table, where one of the values is aconditional sum?This is a STORED PROCEDURE from a commercial product, so I can’t changeanything else other than the STORED PROCEDURE.
View 1 Replies
View Related
Jul 20, 2005
Hi,What exactly does the above (subject) error mean? I'm getting it from anadp file when used by a few people at the same time (each user has thefile in their own filespace though). Access is through windowsauthentication and it only seemed to occur during an update of aspecific table.. The problem is it didn't happen to everyone and Ican't recreate it at all on my own, so am wondering if it was somethingto do with the level of traffic to/from the server at that specific time.Any clues?Cheers,Chris
View 4 Replies
View Related
Jun 21, 2006
Does anyone knows of a tool that will help me manage my queries? I have 100's of them and all scattered. On my PC at home, some in my laptop, some of them at work, some in memory sticks. Then, when I need them I can't find the one I'm looking for so I end up writing the query again.
Any ideas?
View 4 Replies
View Related
Feb 19, 2008
Hello
I am developing a smart device application using Visual Studio 2005 and SQL Server Compact Edition.
Since we cannot write stored procedures, functions, views what is the best way to write/read to/from the database tables other than writing inline sql.
Any ideas and suggestions regarding this would be helpful.
Thanks
View 1 Replies
View Related
