I have a query which spends a lot of time calculating my CASE WHEN -statements.
My query looks like this
SELECT TOP 250
UserId,
CASE WHEN
(someCol*0.4+someOtherCol*0.3) > 99 THEN 99
ELSE
(someCol*0.4+someOtherCol*0.3)
END
FROM
(
SELECT
UserId,
CASE WHEN @myparam > 50 THEN
CASE WHEN Col5+@myincrease > 99 THEN 99 ELSE Col5+@myincrease END
ELSE
CASE WHEN Col6+@myincrease > 99 THEN 99 ELSE Col6+@myincrease ENDEND as someCol,
CASE WHEN Col8+@myincrease3 > 99 THEN 99 ELSE Col8+@myincrease3 END as SomeOtherCol
FROM
SomeTable
) t1
This is just a little bit of the full query. I cannot write the whole query since it contains alot of different views and calculations, but I have traced it down to that all these case when-statements is taking a lot of time to do. So I hope that this example will be enough for you to understand my problem.
I know about some tricks that can replace a CASE WHEN, for example using COALESCE or BETWEEN but that does not seem to work in my case.
Hope someone in here can help me solve a problem im having with a view. I'm tring to build a view that show me the sales history for any given machine in a Machine Database (MDB). Each machine is handled as a seperate "project".
My query works fine as long as a machine has only been sold once ... but as soon as the machine is being sold as used to another customer the view fails because of my "subqueries" return more then one value. (Error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)
I hope someone in here has a ideer on how to get the information I need out - because my CASE statements clearly dosn't do the trick.
-------------------------------------------------------- SELECT crm5.contact.name AS Customer, crm5.pmembtype.name AS Ownership, crm5.pv_salespress.InstallationDate AS [Install Date], CASE WHEN (SELECT crm5.pv_salespress.comptr1_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.pv_salespress.comptr1_Used FROM crm5.pv_salespress) WHEN (SELECT crm5.pv_salespress.comptr2_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.pv_salespress.comptr2_Used FROM crm5.pv_salespress) WHEN (SELECT crm5.pv_salespress.comptr3_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.pv_salespress.comptr3_Used FROM crm5.pv_salespress) ELSE (SELECT crm5.pv_salespress.Used FROM crm5.pv_salespress) END AS Used, CASE WHEN (SELECT crm5.pv_salespress.comptr1_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = crm5.pv_salespress.comptr1_seller) WHEN (SELECT crm5.pv_salespress.comptr2_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = crm5.pv_salespress.comptr2_seller) WHEN (SELECT crm5.pv_salespress.comptr3_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = crm5.pv_salespress.comptr3_seller) ELSE (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = 35) END AS [Installed By], crm5.pv_salespress.RemovalDate AS [Removal Date], crm5.pv_salespress.RemovalReason AS Reason, crm5.pv_salespress.ActionBy AS [Removed By], crm5.project.project_id, crm5.sale.sale_id FROM crm5.contact INNER JOIN crm5.sale INNER JOIN crm5.project INNER JOIN crm5.projectmember ON crm5.project.project_id = crm5.projectmember.project_id INNER JOIN crm5.pmembtype ON crm5.projectmember.mtype_idx = crm5.pmembtype.PMembType_id INNER JOIN crm5.projstatus ON crm5.project.status_idx = crm5.projstatus.ProjStatus_id ON crm5.sale.project_id = crm5.project.project_id ON crm5.contact.contact_id = crm5.projectmember.contact_id RIGHT OUTER JOIN crm5.pv_salespress ON crm5.sale.sale_id = crm5.pv_salespress.Sale_idx
Here's the code I'd like to update, and below it a set of sample data:
Declare @StartDate DateTime Declare @EndDate DateTime Set @StartDate = '20-mar-2008' Set @EndDate = '25-mar-2008'
SELECT
COUNT (iqs.childid) as Cnt ,CASE
--Search Categories and Create Initial Groupings-- WHEN Category LIKE '%Jewel%' THEN 'Accessories' WHEN Category LIKE '%Beauty%' THEN 'Accessories' WHEN Category LIKE '%Accs%' THEN 'Accessories' WHEN Category LIKE '%Gift%' THEN 'Accessories' WHEN Category LIKE '%Grooming%' THEN 'Accessories' WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand' WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand' WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand' WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand' WHEN Category LIKE '%Female%' THEN 'Womenswear' WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand' WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand' WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand' WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand' WHEN Category LIKE '%Male%' THEN 'MensOwnBrand' END AS CategoryGroup ,CONVERT(VARCHAR(10), iqs.StatusDate, 103) AS StatusDate
FROM InventoryQueryStatus iqs JOIN InventoryStatus [is] ON [is].StatusID = iqs.StatusID JOIN Inventory i ON i.InventoryID = iqs.InventoryID JOIN InventoryCategory ic ON ic.CategoryID = i.CategoryID WHERE iqs.StatusID = 31000 and Category NOT LIKE 'Force%' --AND iqs.StatusDate >=GETDATE()-1 AND iqs.StatusDate BETWEEN @StartDate AND @EndDate GROUP BY CASE
--Search Categories and Create Initial Groupings-- WHEN Category LIKE '%Jewel%' THEN 'Accessories' WHEN Category LIKE '%Beauty%' THEN 'Accessories' WHEN Category LIKE '%Accs%' THEN 'Accessories' WHEN Category LIKE '%Gift%' THEN 'Accessories' WHEN Category LIKE '%Grooming%' THEN 'Accessories' WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand' WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand' WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand' WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand' WHEN Category LIKE '%Female%' THEN 'Womenswear' WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand' WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand' WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand' WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand' WHEN Category LIKE '%Male%' THEN 'MensOwnBrand' END ,CONVERT(VARCHAR(10), iqs.StatusDate, 103)
SELECT CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END AS Population_Range, COUNT(CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END) AS [No. Of Countries] FROM Country GROUP BY CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.
So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:
http://64.225.154.232/temp_dbDiagram.gif
I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.
SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL, topic.TOPIC_NAME, topic.TOPIC_URL FROM tbl_CCF_Headlines headline INNER JOIN tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID WHERE (headline.HEADLINE_DATE IN (SELECT TOP 3 HEADLINE_DATE FROM tbl_CCF_HEADLINES GROUP BY HEADLINE_DATE ORDER BY HEADLINE_DATE DESC)) ORDER BY headline.HEADLINE_DATE DESC
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
SELECT Loan.loan_No AS Loan_No, Loan.customer_No AS Customer_No, Customer.first_name AS First_name, Customer.second_name AS Second_name, Customer.surname AS Surname, Customer.initials AS Initials, Bank.Bank_name AS Bank_name, Branch.Branch_name AS Branch_name, Branch.branch_code AS Branch_code , Bank_detail.bank_acc_type AS Bank_acc_type, Transaction_Record.transaction_Amount AS Transaction_Amount, Transaction_Record.transaction_Date AS Transaction_Date, Loan.product AS Product, Product.product_Type AS Product_Type, Product_Type.loan_Type AS Loan_Type
FROM Transaction_Record INNER JOIN Loan ON Transaction_Record.loan_No = Loan.loan_No INNER JOIN Product ON Loan.product = Product.product INNER JOIN Customer ON Loan.customer_No = Customer.customer_no INNER JOIN Bank_detail ON Customer.customer_no = Bank_detail.customer_no INNER JOIN Branch ON Bank_detail.Branch = Branch.Branch INNER JOIN Bank ON Branch.Bank = Bank.Bank INNER JOIN Product_Type ON Product.product_Type = Product_Type.product_Type
I have a WHERE clause that could be an "=" or a "LIKE" depending uponif the passed variable is populated or not. I would like to know thebest way to write the WHERE clause to make it dynamically switchbetween the 2 and make best use of the indexes.CREATE TABLE myTable(ID INT PRIMARY KEY CLUSTERED, COUNTY VARCHAR(50))CREATE INDEX IDX_myTable_County ON myTable(COUNTY)DECLARE @COUNTY VARCHAR(50)SET @COUNTY = 'SANTA CLARA' -- Could also be SET @COUNTY = NULLSELECT ID FROM myTableWHERE COUNTY LIKE (CASE WHEN @COUNTY IS NOT NULL THEN @COUNTY ELSE '%'END)This does not seem like best practice to me because I am forced to use"LIKE" even when @COUNTY is populated with data. Ultimately I'd like:WHERE (CASE WHEN @COUNTY IS NOT NULL COUNTY = @COUNTY ELSE COUNTY LIKE'%' END)but that is incorrect syntax on "=".Also, I do not want to use a dynamically built statement. Is there away around this?Thanks,Josh
We are load testing SQL 2005 and I need to re-write the index scripts that we have from 2000. Is there an easier way to rewrite the scripts without having to go to each job, then each step and modify it?
Our current index script template is:
Create NonClustered Index [IdxABCDE] On dbo.blahblah(blahID) With FillFactor = 90, Statistics_NoRecompute On [Index2] Go
and I need to rewrite it as:
ALTER INDEX [IdxABCDE] ON [dbo].[blahblah] REBUILD WITH (FILLFACTOR = 90, ONLINE = OFF,SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE = ON, MAXDOP=4)
I am thinking I would have to rewrite the scripts from scratch. We have hundreds of index scripts. So before I brace myself to lot of typing, just wanted to find out if there is any easier way..
Thanks..
Dinakar Nethi ************************ Life is short. Enjoy it. ************************ http://weblogs.sqlteam.com/dinakar/
I have a complex stored procedure that utilises inner joins, and in the WHERE clause there is defined a subquery. This subquery has now cause a performance hit to the ponit where the server is returning a timeout. Need to find an alternate fast solution.....
SELECT BE.BlogEntryID
FROM vw_BlogEntry BE
INNER JOIN @BlogView BC ON BC.CommonID = BE.BlogCommonID
INNER JOIN vw_Blog B ON B.BlogID = BC.BlogID
WHERE (
... ) AND (
.... )
AND
(
-- GET ENTRIES WHERE COMMENT COUNT IS AT LEAST @CommentCount (..or @CommentCount = 0)
@CommentCount <= 0
OR BE.CommonID IN (SELECT bc.EntryCommonID FROM vw_BlogComment_Current bc
INNER JOIN tblVersionDetails vd ON bc.VersionID = vd.VersionID
WHERE
IsNull(@CommentStatus,'') = ''
OR vd.Status IN (SELECT * FROM fn_CsvToString(@CommentStatus))
This works, but it's highly unefficient and generates a lot of IO. Is there another way to do it without the use of temp tables?
Code Block select eh.* from Equipment_History eh where Equipment_History_ID in (select top 2 Equipment_History_ID from Equipment_History eh1 where Equipment_ID in (select Equipment_ID from Equipment_History eh2 where Equipment_History_ID in (select min(Equipment_History_ID) from Equipment_History eh3 where eh2.Equipment_ID = eh3.Equipment_ID and eh2.Equipment_Status_Type in (1,2,3))) and eh1.Equipment_ID = eh.Equipment_ID order by Equipment_History_ID)
Equipment_History_ID is PK
Let me know what other information would be useful, I can barely understand my logic from looking at the code but it does in fact work.
Hi,I have an SQL assignment to do and at my school we use SQL *Plus therehowever I don't have Oracle at home, where I would like to do the work ,so Iwas wondering whats the easiest way to get an SQL environment up so I cancode in that then just paste it into SQL *Plus later.I don't really want to install Oracle on my home pc and I was wondering ifthere are other IDE's for SQl that would fit my need for this.I discovered an instant SQL *Plus client that sounded really promising butwhen i unpacked it, it was just a load of dll's so I think it wasn't what Ithought it was.So does anyone know of anything that might be able to help me out here?Any advice much appreciated!Thanks--Ant
I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for Select emp_no, emp_name, event_date, Test_no, Code, Test_result From test_table1 order by emp_no
[code]...
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.because of my application changes all these three statements above are throwing the error:
Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).
I have altered the sp_help_revlogin (from Microsoft article # 246133 )
PLEASE TEST/FIX before you use this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_help_revlogin_2005]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_help_revlogin_2005 @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @logintype char(1)
DECLARE @logindisabled int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, type, is_disabled FROM master.sys.server_principals
WHERE name <> 'sa' and type in ('S','U','G')
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, type, is_disabled FROM master.sys.server_principals
WHERE name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled
I have often come across discussions on this forum saying that CURSORs are expensive in time (processing power?).
Having used CURSORs to processing a mere 2000+ record (not much at all) which took a fair while to complete, I now realize why you guys are saying CURSORs are expensive.
But is there alternatives to using CURSORs in the situation where I try to process every records returned by a particular query?
Say for example, i want to update columns that comes from different tables for every record that is returned by a SELECT JOIN query. there is no way that i can do that with a single UPDATE statement cause i can't do JOIN with UPDATE query.
This stored procedure is not able to accept the value from the parameter, because the application is passing it, in the format ymd and sql server is specting it in ydm.
I tried to set the dateformat = ymd in the store procedure, thing that did not work. I have to set it before exec my-stored procedure. But I cant from my application.
Hi!fact: sp_setapprole can't be invoked from within a stored procedure
scenario: my users are validated in the database! I have a Users table that contains user related information, incl. passwords and profiles. Actually users login through an applicational SP -- svc_login @username, @password -- and all users have the same permissions. desired scenario:Have 1 app. role per user's role; once the user log in through the svc_login, I would like to change his appl. role based on his profile. I already saw that i can't do this at DB side! Is there any known alternative to this kind of scenarios? Or must I rely on the front-end application and share the role password with it?Thanks in advancejmn
I have a activeX script like below in DTS and I am trying to rewrite it in SSIS. What is the best way to do this? Can I do this using a look up table? or other transformers in SSIS '********************************************************************** ' Visual Basic Transformation Script '************************************************************************
' Copy each source column to the destination column Function Main() if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then DTSDestination("RACE_NATIVE_AM_IND") = "Y" else if DTSSource("RACE_AMERICAN_INDIAN_YN") = "2" then DTSDestination("RACE_NATIVE_AM_IND") = "N" end if end if
if DTSSource("RACE_ASIAN_YN") = "1" then DTSDestination("RACE_ASIAN_IND") = "Y" else if DTSSource("RACE_ASIAN_YN") = "2" then DTSDestination("RACE_ASIAN_IND") = "N" end if end if
if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "1" then DTSDestination("RACE_BLACK_IND") = "Y" else if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "2" then DTSDestination("RACE_BLACK_IND") = "N" end if end if
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then DTSDestination("RACE_HAWAIIAN_IND") = "Y" else if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "2" then DTSDestination("RACE_HAWAIIAN_IND") = "N" end if end if
if DTSSource("RACE_CAUCASIAN_YN") = "1" then DTSDestination("RACE_WHITE_IND") = "Y" else if DTSSource("RACE_CAUCASIAN_YN") = "2" then DTSDestination("RACE_WHITE_IND") = "N" end if end if
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 9 then if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then DTSDestination ("RACE_CD") = 40 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource ("RACE_ASIAN_YN") = "1" then DTSDestination ("RACE_CD") = 16 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource ("RACE_AFRICAN_AMERICAN_YN") = "1" then DTSDestination ("RACE_CD") = 32 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then DTSDestination ("RACE_CD") = 51 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource("RACE_CAUCASIAN_YN") = "1" then DTSDestination("RACE_CD") = 31 DTSDestination("RACE_MULT_IND") = "N" DTSDestination("RACE_OTH_IND") = "N" end if end if end if end if end if else if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 10 then DTSDestination("RACE_CD") = 99 DTSDestination("RACE_MULT_IND") = "N" DTSDestination("RACE_OTH_IND") = "N" else if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) < 9 then DTSDestination("RACE_CD") = 52 DTSDestination("RACE_MULT_IND") = "Y" DTSDestination("RACE_OTH_IND") = "N" end if end if end if
I am trying to do this in a stored procedure and I am lacking permissions. Not sure if they will allow me permission so is there an alternative? I want to do it in a procedure and prefer not to use DTS.
BULK INSERT tablename FROM 'C:filename.txt' WITH ( ROWTERMINATOR = ' ' )
Read an excel file into the database (I have this working) Loop through the result set and concatinate the [Code] to a variable Every 200 rows I would like to use the varible that contains 200 codes e.g. Myvar = ABC,ABC1,ABB1,ABB2...... into another task, the variable would be reset after the task has been complete for the next 200 rows I have no idea how I can perform the kind of paging that I am after, I know the Foreach control loops through each record and I could use Scriptmain to attach to the variable but I'm not sure how to perform an additional task every 200.
I have 3 SQL databases: A, B and C. A and B are branch databases while C is the central (HQ) database. A & B will be constantly updated while C will updated occasionally and usually will be used for reporting/data browsing. Even though A & B are from different branch, they do have share some data records.
Every day, I will have to sync A & B's new/updated data to C and C to A/B. I know I can use Replication but I can't. You see, A & B are located in our own branch server while C was hosted on a third-party server. The webserver provider for C would not allow me to do any Replication on C.
What other alternatives that I can use to synchronise all 3?
DTS? XML transfer? It looks as if I have no choice but to write my own sync scripts? :((
Anyone has encountered similar situations? Any recommended SQL Tool programs?
Please help. I'm at a loss of what Im going to do.
I would like to know what alternatives are avaliable to SQL Server Merge Replication. I am also looking for Third Party Tools. Can anyone name a few for me.
Hello all, I'm new to SSIS and this forum, and this is my first post.
We're migrating a 2000 DTS ETL process to 2005 SSIS. We really like the enhanced functionality of SSIS thus far.
One problem we have with our 2000 process is that runs at 1:00am each morning. The scheduling is done via a distributed scheduling tool called Maestro. Our process pulls data from a mainframe-based DB2 OLTP and reformats it into SQL Server reporting tables. We have nightly mainframe batch processing that updates the DB2 tables, and we need those updates on a nightly basis.
The mainframe batch process starts at 8:00pm each evening. It finishes normally by 1:00am 90% of the time, but it is 20+ years old, and has its share of problems, especially during month-end. The problems can't be resolved until the next business day in some cases.
We'd like to elegantly connect the two processes somehow so the SSIS ETL process kicks off when the mainframe batch process finishes. I intentionally didn't use the word 'trigger' up until this point.
It would not be a problem to modify the mainframe batch process to insert or update a DB2 table that SSIS has access to, but I don't think we can get the mainframe batch process to update SQL Server 2005 tables...?
I've googled everything about GUID, seems there is no good thing to say about it. Here's my scenario, a purchase order (PO) application:
We want to have a centralized database with remote sites connected to it
Some of the sites are without connection, they will have their own servers with scheduled replication to the centralized database. The design is something like this:
Each PO will have many revisions
Each revision will have many PO line items
Each PO line item will have many Delivery Schedules In the past i used int IDENTITY as transaction ID in revision and line item tables.
transaction ID in revision table is FK to line item table, and transaction ID line item table is FK to Delivery Schedules table.
This work well in standalone database.
Now that we need to merge replicates, int IDENTITY produced in remote DB will conflict with IDENTITY produced in central DB.
I'm thinking of using GUID to replace int IDENTITY.
Question:
How bad is my decision?
Can't GUID size indexing problem be solved with partitioning?
Can you suggest other alternatives to GUID, based on the above scenario? Thanks in advance
Suposse that some models are deployed in Report Server for a while, and users have developed some ad-hoc reports on them using Report Builder, (some of the models are SSAS Cubes).
Modifications are required for a Model, what is the procedure to deploy this modifications? What happens with ad-hoc reports of this Model? Rewrite all the reports is a VERY BAD option, I agree that some reports must be rewrote, but only if they reference objects no longer valids in new model.
I suposse that the procedure for SSAS Cube Models will be different for a Relational Database Source because metods of generating models are so different. (I am particularly curious about Cubes, I can't figure out how I can do it)
Hello everybody, I'm new using ASP 2.0, I was trying to find out how do i tell a SqlDataSource the parameters it will be using for the select query. I was looking at the Configure Data Source wizard and when I press the where botton to create the parameters, I do not know how two of the available Source selections work which are: (Query String, and Session). What I want to understand is how do I pass the parameter value during the form load like I used to do with ASP 1.1. I used to write: during the pageload event: me.dataAdapter.selectparameter("@param").value = [myVar] me.dataAdapter.fill(Me.ds)
I tried to do something like this for the SqlDataSource but didnt work. can anyone tell me how can I acomplish this?
Hopefully someone can suggest a better solution than what I'm currently hobbling along with.Basically, I've got a table that has rows inserted (with a timestamp) whenever there is a change to one of the values of a particular "item". So, what I want is to return a dataset of the latest value for each category, for each particular item. I'm guessing that what I'm trying to acheive is doable in some elegant and performant fashion. Something maybe involving a ROLLUP or WITH CUBE or something amazingly obvious. But for the time being, I've got a less-elegant query that returns the correct data. It just uses subqueries.Here's the T-SQL to run my scenario: DECLARE @actionHistoryTable TABLE ( itemID int, actionType int, actionValue nvarchar(50) NULL, actionTime datetime )INSERT @actionHistoryTable VALUES( 1000, 1, 'fork', '1/1/2008')INSERT @actionHistoryTable VALUES( 1000, 2, '27', '1/2/2008')INSERT @actionHistoryTable VALUES( 1000, 3, '200', '1/12/2008')INSERT @actionHistoryTable VALUES( 1000, 2, '1', '1/1/2008')INSERT @actionHistoryTable VALUES( 1000, 3, '204', '1/1/2008')INSERT @actionHistoryTable VALUES( 1000, 1, 'ball', '1/3/2008')INSERT @actionHistoryTable VALUES( 1026, 2, '20', '1/10/2008')INSERT @actionHistoryTable VALUES( 1026, 2, NULL, '1/5/2008')INSERT @actionHistoryTable VALUES( 1026, 1, 'hotdog', '1/6/2008')INSERT @actionHistoryTable VALUES( 1026, 3, '2511', '1/8/2008')INSERT @actionHistoryTable VALUES( 1026, 3, '375', '1/7/2008')INSERT @actionHistoryTable VALUES( 1026, 1, 'mustard', '1/5/2008')INSERT @actionHistoryTable VALUES( 1013, 1, 'rock', '1/2/2008')INSERT @actionHistoryTable VALUES( 1013, 1, 'paper', '1/21/2008')INSERT @actionHistoryTable VALUES( 1013, 3, '10', '1/20/2008') -- JUST DISPLAY THE RAW TABLE FOR THIS EXAMPLESELECT * FROM @actionHistoryTable -- THIS RETURNS THE RESULTS I'M WANTING, IT ROLLS-UP THE LATEST VALUE FOR EACH ACTION_TYPE FOR EACH ITEMIDSELECT aht.itemID ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '1' ORDER BY aht2.actionTime DESC ) as 'latest type 1 value' ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '2' ORDER BY aht2.actionTime DESC ) as 'latest type 2 value' ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '3' ORDER BY aht2.actionTime DESC ) as 'latest type 3 value'FROM @actionHistoryTable ahtGROUP BY aht.itemID Is there a better way?-Steve
hello there... i need help with cross tab query with sum(case actually iam using ms access 2007 and vb.net 2005 i have problems with the sintaxis ill paste the code below:
TRANSFORM SUM(cantidad*(case tipo WHEN 'Entrada' else 1 then 1 end 0)-cantidad(case tipo WHEN 'Salida' else 1 then 0 end)) AS TOTAL SELECT S.nombre,S.grupo,SUM(cantidad) AS Sub FROM Movimiento AS M, Punto AS P, Sorteo AS S WHERE(M.id_sorteo = s.id_sorteo And M.id_punto = P.id_punto) GROUP BY P.nombre, S.nombre, S.grupo ORDER BY grupo PIVOT P.nombre"
i need the correct sintaxis for this sentece: SUM(cantidad*(case tipo WHEN 'Entrada' else 1 then 1 end 0)-
or another way to do this , the finally of this query is get the actual inventory.
