I have the following trigger that will insert the correctly if none of the items in the #tempKCS exist in the destination table. However if one of the items in the #tempKCS table do exist in the Sales table then the insert does not execute for the other itmes in the #temoKCS list that are not already listed in the Sales Table. Below is the trigger. Tell me where I am making my mistake. Thanks
CREATE TRIGGER [Insert_KCSales] on [dbo].[Reclines]
FOR INSERT, UPDATE
AS
SELECT KitItemSum.Date, Kits.KitItemNo AS ItemNo, (Kits.Quantity * KitItemSum.QtySold) As QtySold, ((Kits.[Percentage]/100) * KitItemSum.AmtSold) AS AmtSold, KitItemSum.Division, 0 AS QtyReturned, 0 AS AmtReturned Into #TempKCS
FROM (SELECT Summary.Recdate AS [Date], Summary.ItemNo, Sum(Summary.Qty) AS QtySold, SUM((Summary.Amount-Summary.DiscAmt)) AS AmtSold, Summary.Division
FROM (SELECT DISTINCT R.Recno, R.Recdate,I.RecLineNo, I.ItemNo, I.Qty, I.Amount, I.DiscAmt, I.Division FROM Inserted I
INNER JOIN Kits K ON I.ItemNo = K.MasterItemNo INNER JOIN Receipt R ON I.RecNo = R.RecNo INNER JOIN Items ON I.ItemNo = Items.ItemNo) AS Summary
GROUP BY Summary.RecDate, Summary.ItemNo, Summary.Division) AS KitItemSum INNER JOIN Kits ON KitItemSum.ItemNo = Kits.MasterItemNo;
INSERT INTO Sales ([Date], ItemNo, Division, QtySold, AmtSold, Qtyreturned, AmtReturned)
SELECT [Date], ItemNo, Division, QtySold, Amtsold, Qtyreturned, Amtreturned
FROM #TempKCS
WHERE NOT EXISTS (SELECT S.[Date], S.[ItemNo] FROM Sales S INNER JOIN #TempKCS KCS on S.ItemNo = KCS.ItemNo AND S.[Date] = KCS.[Date]);
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
We've altered an UPDATE trigger - now it doesn't work properly. When updating a record (via a web application) by clearing the value for a particlar column, we get error:
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Code = 800a0c93 Source = ADODB.Recordset Description = Operation is not allowed in this context.
What the trigger does is prevent a zero-length string from being inserted into the database, which violates a check constraint, and instead inserts NULL. The web app - whose code we cannot modify - apparently does this. This was working fine, until we altered the trigger to add an additional column for update. Our SQL developer person has left, so us less knowledgable (about SQL) folks are trying to pick up the slack.
trigger code:
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
ALTER TRIGGER trigViaNetCardholderUpdateEmptyStrings ON viewViaNetCardholder INSTEAD OF UPDATE AS UPDATE tblIDCross SET tblIDCross.chUSCId = CASE WHEN ins.chUSCId = '' THEN NULL ELSE ins.chUSCId END, tblIDCross.chNineDigit = CASE WHEN ins.chNineDigit = '' THEN NULL ELSE ins.chNineDigit END, tblIDCross.chPID = CASE WHEN ins.chPID = '' THEN NULL ELSE ins.chPID END, tblIDCross.chEmployeeId = CASE WHEN ins.chEmployeeId = '' THEN NULL ELSE ins.chEmployeeId END, tblIDCross.chAIMSNumber = CASE WHEN ins.chAIMSNumber = '' THEN NULL ELSE ins.chAIMSNumber END, tblIDCross.intCustomerType = ins.intCustomerType FROM INSERTED ins WHERE tblIDCross.intUSCardId = ins.intUSCardId
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
How to debug the following trigger which is not inserting any data into `DUMPINGGRD`:
quote: delimiter $$ CREATE TRIGGER ins_File BEFORE INSERT ON REPORT FOR EACH ROW BEGIN set @filenum=(Select max(File_Id) from DUMPINGGRD); if(@filenum=0) then --checking whether record is present or not begin new.File_Id=1;--if no record is present then set file_id=1
[Code] ....
The following is the structure of the tables involved:
quote: CREATE TABLE REPORT ( R_Id int(10) NOT NULL AUTO_INCREMENT, R_Type varchar(255) not null, R_Title varchar(255) not null, U_Id int(10) not null, File_Id int(10) not null,
I really need your help now, and I know I can always count on this group for tough answers to tough questions. OK, here's my dilemma. I have my trigger, which upon a record being inserted into db1.table1, inserts the same record into db2.table2 (SQL 7 db on the same server). What's happening is only a few of the fields are getting over there, but most are ending up NULL or 0. Everything besides the following records are inserting into the other database table properly:
Note: The following fields have their Default Value set to (0) SystemID EventStatusID Coop NewProductFlag TVSupportFlag RadioSupportFlag FSISupportFlag RollbackPricing PleaseContactFlag
Default Value set to (1): KitInformationID
Here is the trigger:
CREATE TRIGGER EmoesImport ON mc_events FOR INSERT AS IF @@ROWCOUNT<>0
BEGIN
SET IDENTITY_INSERT mcweb2.dbo.mc_events ON
DECLARE @SystemID int DECLARE @EventID int DECLARE @AccountID int DECLARE @BillingContactID int DECLARE @EventName varchar(100) DECLARE @EventStatusID tinyint DECLARE @Coop bit DECLARE @CoopSupplier varchar DECLARE @SamplesPerDay int DECLARE @BrochuresPerDay int DECLARE @AverageDailyMovement int DECLARE @SalesGoal int DECLARE @NumberofDays int DECLARE @NumberofHours int DECLARE @NumberofStores int DECLARE @WeekNumber tinyint DECLARE @PreferredHourID tinyint DECLARE @PreferredHourother char(20) DECLARE @PreferredDate1 varchar(20) DECLARE @PreferredDate2 varchar(20) DECLARE @NewProductFlag bit DECLARE @TVSupportFlag bit DECLARE @RadioSupportFlag bit DECLARE @FSISupportFlag bit DECLARE @RollbackPricing bit DECLARE @PleaseContactFlag bit DECLARE @EventStoreSelection tinyint DECLARE @EventClusterID int DECLARE @KitInformationID tinyint DECLARE @KitDescription varchar(1000) DECLARE @KitOther varchar(200) DECLARE @MCProgNum varchar(7) DECLARE @rowguid uniqueidentifier
SELECT @SystemID = SystemID FROM INSERTED SELECT @EventID = EventID FROM INSERTED SELECT @AccountID = AccountID FROM INSERTED SELECT @BillingContactID = BillingContactID FROM INSERTED SELECT @EventName = EventName FROM INSERTED SELECT @EventStatusID = EventStatusID FROM INSERTED SELECT @Coop = Coop FROM INSERTED SELECT @CoopSupplier = CoopSupplier FROM INSERTED SELECT @SamplesPerDay = SamplesPerDay FROM INSERTED SELECT @BrochuresPerDay = BrochuresPerDay FROM INSERTED SELECT @AverageDailyMovement = AverageDailyMovement FROM INSERTED SELECT @SalesGoal = SalesGoal FROM INSERTED SELECT @NumberofDays = NumberofDays FROM INSERTED SELECT @NumberofHours = NumberofHours FROM INSERTED SELECT @NumberofStores = NumberofStores FROM INSERTED SELECT @WeekNumber = WeekNumber FROM INSERTED SELECT @PreferredHourID = PreferredHourID FROM INSERTED SELECT @PreferredHourother = PreferredHourother FROM INSERTED SELECT @PreferredDate1 = PreferredDate1 FROM INSERTED SELECT @PreferredDate2 = PreferredDate2 FROM INSERTED SELECT @NewProductFlag = NewProductFlag FROM INSERTED SELECT @TVSupportFlag = TVSupportFlag FROM INSERTED SELECT @RadioSupportFlag = RadioSupportFlag FROM INSERTED SELECT @FSISupportFlag = FSISupportFlag FROM INSERTED SELECT @RollbackPricing = RollbackPricing FROM INSERTED SELECT @PleaseContactFlag = PleaseContactFlag FROM INSERTED SELECT @EventStoreSelection = EventStoreSelection FROM INSERTED SELECT @EventClusterID = EventClusterID FROM INSERTED SELECT @KitInformationID = KitInformationID FROM INSERTED SELECT @KitDescription = KitDescription FROM INSERTED SELECT @KitOther = KitOther FROM INSERTED SELECT @MCProgNum = MCProgNum FROM INSERTED SELECT @rowguid = rowguid FROM INSERTED
I've just noticed some strange behavior that seems like a bug to me. It's much easier to follow an example of it that to outright explain it, so here goes.
I have a table defined with a NOT NULL constraint on a column and a default clause: -- DROP TABLE TestTable CREATE TABLE TestTable ( TestField0 varchar(10), TestField1 varchar(10) NOT NULL DEFAULT ('a') )
I have a view defined on the table, in this example case, the view just mirrors the table one to one: -- DROP VIEW TestView CREATE VIEW TestView as SELECT TestField0, TestField1 FROM TestTable
So far so good, if I run this statement, it works as I would expect and inserts the value and the default goes into the other field: INSERT INTO TestView (TestField0) SELECT 'test'
Now... If I add an INSTEAD OF trigger to the view, and have it perform the insert for me, I get an error with the same insert stmt: -- DROP TRIGGER TestTrigger CREATE TRIGGER TestTrigger ON TestView INSTEAD OF INSERT AS BEGIN INSERT INTO TestTable (TestField0, TestField1) SELECT TestField0, COALESCE(TestField1, 'X') FROM inserted END
Notice the trigger will ensure that a null value cannot be inserted into TestField1. If I run this insert stmt though I get an error: INSERT INTO TestView (TestField0) SELECT 'test'
Server: Msg 233, Level 16, State 2, Line 1 The column 'TestField1' in table 'TestView' cannot be null.
Suppose you have a simple DML "execute as..." trigger that looks like this:
CREATE TRIGGER dbo.myTrigger  ON dbo.myTable  WITH Execute As Owner  AFTER INSERT AS
[code]...
which runs as "owner" so that the user inserting into "myTable" won't have the privs to edit the audit table.How does one capture the identity of the user whose insert statement caused the trigger to fire? I've tried "current_user" and "system_user" without success when "execute as ..." is used: they each return a value not related to the identity of the user running the original insert.without an "execute as" clause, what prevents a user from adding to or editing the audit table directly?
I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?
--Table being called.
ALTER PROCEDURE dbo.wbGetxxxxxUserReturns
@nxxxxtyId smallint,
@sxxxxxxxxUser varchar(32),
@sxxxxName varchar(32)
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
( NAME_COL varchar(64), ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2 select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB] GO /****** Object: Trigger   Script Date: 24/07/2015 3:41:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
I have writen a small program in a Query window that runs fine if I hilight and run small chuncks. (I have listed the statements with out the clauses so it is more easly viewed here.)
The problem is if I simply execute the Query window I get massive errors that don’t make sence. I am guessing I need some GO and BEGIN/END statements? But I don’t know where I should use them.
I would really appreciate a few pointers so I can just run the Query window.
drop table SourceFile drop table ReferenceFile
SELECT TOP INTO SourceFile FROM
SELECT TOP INTO ReferenceFile FROM
-- test data INSERT ReferenceFile (AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip) values () INSERT SourceFile (AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip) values (22) -- test data
drop table MATCHTEST SELECT TOP 1 AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip INTO MATCHTEST FROM AddressCleaned_npidata_20050523_20071112
DELETE MATCHTEST
ALTER TABLE MATCHTEST ADD REF_ML VARCHAR(2)
INSERT MATCHTEST () SELECT FROM ReferenceFile r inner join SourceFile s on s.New_Last_Name = r.New_Last_Name
I'm very new in SSIS. I've created 3 packages in the project. sometimes when i modified the project and save/save-all it, when i tried to build (isn't this used to deploy?), I am being asked by this:
Package 1 has been modified outside the source editor. Do you want to reload it?
When I press on yes, all my modifications were not save. If i answered no, the build process stops. i dunno if this is because the build process is already finished or it was terminated because i chose 'NO'. When i tried to rebuild it again, it will ask me the same question.
What is the proper way to save and build the project? When it says ' do you want to reload it?', does it mean reloading the old copy before modification?
Hello, i have something like this, i want the annoucements (status = 0) to be on top, then topics with (status = 1) below, then the rest of the topics. So i tried: SELECT forum_topics.id, forum_topics.status, forum_topics.username AS starter, forum_topics.subject, forum_topics.closed, forum_topics.answerpostid, forum_topics.views, forum_topics.answers, forum_topics.lastanswer, forum_topics.lastanswerid, forum_topics.created AS started, forum_answer.username, forum_answer.answer, forum_answer.created FROM forum_topics LEFT OUTER JOIN forum_answer ON forum_answer.id = forum_topics.lastanswerid WHERE (boardid = @ID OR boardid = 0) ORDER BY (status) ASC, (created) ASC Problem is that they are not sorted diffrently, when i change the (created) ASC to (created) DESC i get the same result and the rows are not sorted, they only get sorted by status so i have status=0 at the top, then status=1 then the rest. How do i get them to be sorted first by status ASC then by created ASC/DESC? Patrick
I installed MDAC 2.6 SP1 on my 2000 machine in order to get SQL server 7.0 to work properly on my machine. I'm trying to get installed on server which is on NT 4.0 SP6 so that when I save a DTS package on my 2000 machine and run it on the server it doesn't get the error client access ODBC driver not capable. I intalled MDAC 2.6 SP1 on the server but it doesn't look like its updating it. The install goes fine. I reboot but when I go into the ODBC connections and look at the driver for the client acceess it still has version 6.00.00.05 while my 2000 machine says 6.00.07.00
Any ideas on why its not updating. Can I just take the DWBODBC.DLL from my machine and copy it to the server?
We have SQL Server 7.0 running on an NT 4.0 sp5 box. Whenever we stop the SQL Server Service, we get the message that SQL Agent will also be stopped. If we do this, all the services seem to stop fine, but when we try to start the SQL Server Agent Service back up, it won't let us. In order for it to restart, we have to reboot the server so that it starts 'automatically'. Does anyone have any ideas as to why this is?
I define one of my columns as FLOAT. Some of the values are negative and positive with precision 8 or 12. When I run updates against that column and then check the data all values are 0.
Hello friends, I have developed an ASP.NET 2.0 application. The problem is that the IIS server returns the next error:
SQL Server does not allow remote connections
Sometimes returns:
SQL Server does not exists.
My question is if there is any known bug with ASP.NET and SQL Server. My sql server have activated the Remote Connections (its working properly on other web page).
Its a problem of the number of connections?? Its a problem ofbad configuration?? Its a IIS problem??
We installed SQL Server 2005 on windows 2000 with IIS 5. After installation, I can see report manager using http://localhost/reports. But I can only the top part. There's no My Reports or anything, just "Home / My subscriptions / Help".
Hi i given my query here. in that joings betweens tables is correct, but i am getting the following error ora-00933 sql command not properly ended can we have exists function inside if condition??
select distinct loc.authorization from account acct, location loc, iom_order iom where loc.location_id = acct.location_id and acct.account_id = iom.account_id and iom.iom_order_id =140
if(loc.authorization is null and exists( select distinct line.INTRALATA_PIC_FREEZE ,line.INTERLATA_PIC_FREEZE from line line, iom_order iom, activity act where ((line.INTRALATA_PIC_FREEZE='F' or line.INTERLATA_PIC_FREEZE='F')and line.line_id = act.component_id and act.component = 23 and act.iom_order_id = 140) union all select distinct trunk.INTRALATA_PIC_FREEZE ,trunk.INTERLATA_PIC_FREEZE from trunk trunk, activity act where ( (trunk.INTRALATA_PIC_FREEZE='F' or trunk.INTERLATA_PIC_FREEZE='F')andtrunk.trunk_id = act.component_id and act.component = 25 and act.iom_order_id = 140) select distinct rcf.INTRALATA_PIC_FREEZE ,rcf.INTERLATA_PIC_FREEZE from remote_call_fwd rcf, activity act where((rcf.INTRALATA_PIC_FREEZE='F' or rcf.INTERLATA_PIC_FREEZE='F')and rcf.remote_call_fwd_id = act.component_id and act.component = 24 and act.iom_order_id = 140) select distinct tsg.INTRALATA_PIC_FREEZE ,tsg.INTERLATA_PIC_FREEZE from nodal_tsg tsg, activity act where ((tsg..INTRALATA_PIC_FREEZE='F' or tsg.INTERLATA_PIC_FREEZE='F')and tsg.nodal_tsg_id = act.component_id and act.component = 5 and act.iom_order_id = 140))) Then select distinct loc.authorization from order2misc o2m, location loc, iom_order iom where loc.location_id = o2m.serv_loc_id and iom.iom_order_id =140 else select distinct loc.authorization from account acct, location loc, iom_order iom where loc.location_id = acct.location_id and acct.account_id = iom.account_id and iom.iom_order_id =140 end if;
Hi,Currently, I am on SQL2000 SP3 and Windows 2003.I am trying to restore the full database backup and transaction logbackup on the development server. The full database restore is not aproblem, but the transaction log restore happened during the day is.Everytime when I try to compare the transaction in the database(select the last update date from one of my table as a benchmark, thenbackup the transaction log for restore) to the development db with therestored transaction log, I got none of the transaction from the logbackup.What did I do wrong? Is my concept about using the transaction logrestore wrong?Thanks in advance.
I have about 7 tables I need to join, but am having a lot ofdifficulty with the joins, that I need some help on.I'll provide the details of four tables to illustrate the scenario.I have one table called Product, which contains a complete list ofproducts.I have another table called Date, which contains a complete list ofdates.I have a table called sales, which contains Product, date and Qty.I have a table called Purchases which contains product, date and qty.I want to link my Purchases and sales tables to my Product and datetables.On each row I want product from the Product table, Date from the datetable, Sales Qty from the sales table and Purchases Qty from thePurchases table.I've been messing around with full outer, inner joins etc, but can'tget it right.All help greatfull.Regards,Ciarán
I have 2 servers running in a clustered mode(active/active). In which we installed SP2 on peak production time but somehow it resulted in failure i.e it installed partially and sql server is unstable as of now. I'd like to know if it would be fine to restart the server without full installation of SP2 and what could be the implications of a server restart in such a scenario. Pls help urgent!!!!!!!
Hi, I am exporting a table to a fixed width flat file using "Ragged Right" format. Though i specify carriage return for the last column, the text file is not taking it up.
I am doing the following for connection cmFlatFile.properties("Format").SetValue(cmFlatFile,"RaggedRight") cmFlatFile.properties("RowDelimiter").SetValue(cmFlatFile,vbCrLf)
and while adding the column i am setting up the width as follows
For cols = 0 to vColumns.Count -1 Dim col As IDTSConnectionManagerFlatFileColumn90 = ff.Columns.Add() if cols = vColumns.Count - 1 Then col.ColumnDelimiter = vbCrLf end if cols.ColumnType = "FixedWidth" cols.Columnwidth = 20 ...... Next cols
Still the rows are exported next to each other just like when we use FixedWidth.
I've my SSIS Packages, having multiple Configuration stored in SQL Server Table named [SSIS Configurations].
And it's devloped on BHUDEV Server ON Devlopmentdb Database. So durring development Default Cofigurations are set for BHUDEV Server.
Problem is that when I install my Package on SQL Server on different Server for example JOHN, and now my database is ProductionDB and [SSIS Configurations] table will be on ProductionDB database. And I've made required changes in [SSIS Configurations] table. So when I run the Package even then it picks configuration information from BHUDEV.Developmentdb.dbo.[SSIS Configurations] table. That's my problem.
But If when during execution time, I change the connection information then it works fine.
Hi, Long story, Ihad SQL 2000 happily installed and running for a long time. Attempted to load SQL 2005 alongside, but in retrospect see that I used the same install directory as SQL 2000. Found that the SQL 2000 databases still ran, in that I was able to run applications that rely on them. Enterprise manager ans Query Analyzer are still in the start/program list, but when I try to open them they say that the DSN isn't there, and the odbc driver is missing. Also if I try to configure the system SQL DSN in ODBC datasources I get the same message. I guess the SQL 2005 install has scrambled the ODBC setup.Tried deinstalling SQL 2005, but the problem persists. Tried reloading mdac-typ, same problem. Tried reinstalling SQL2000, same problem. Tried fully deinstalling SQL2000 from Add/remove program, said it completed, but the shortcuts are still there, the applications that use the db's still work, only it no longer appears in Add/Remove programs. Is there a command line string to manually remove it, or where do I go to repair my mdac, or is this what I actually want to do?
Hi, I am using a sub-report in my main report, passing a customerid parameter into the subreport. The subreport dataset then either returns rows, or it doesn't.
When the dataset returns rows, I am using either COUNT(field) or CountRows(dataset) in a textbox, which then displays fine.
However, if the dataset returns NO ROWS, i need the textbox to return a '0' - which it does if i run the subreport on it's own.
If i run the main report, and the subreport returns no rows, no matter what expressions I use to try and get the '0' to display on the main report....it doesn't.
As a test I also put a background colour on the text control in the subreport, and it looks like the whole text control is not displaying
This is only an issue when 0 rows are returned, but i must display a 0 in this case!?!
I have several packages where I set up a SQL Server log provider. The packages are all called from jobs.
However, for some reason, only ONE of the packages is logging to the sysdtslog90 table in my specified database.
All of packages have the identical set up for the logging. Here are my settings under logging:
PROVIDERS AND LOGS: Name: SSIS Log Provider for SQL Server (box is checked) Configuration: ServerName.DBName (I use this same connection for all the packages)
DETAILS: OnError is checked
CONTAINERS: All items are checked
So what's going on here? I have successfully logged to this table using test cases in BIDS, and as I mentioned, ONE of the packages is actually logging to the table when it's called from the job.