Writing ETL Error Records To A Common Table
Jun 13, 2007
I am working on building a template/design pattern for a DTS to SSIS upgrade project.
During our ETL processing, if we encounter a record that cannot be inserted into a destination table, we'd like to be able to write the entire record out to a common error/reject table. The obvious problem is that every SSIS package that is using this template will of course be dealing with varying table schemas.
I was thinking that if there were a way that I could transform the error record/buffer row into XML, I could then achieve my goal of having a common table to receive errors/rejects.
Has anyone done something like this, or have suggestions on how we might accomplish?
View 7 Replies
ADVERTISEMENT
Oct 17, 2007
I am developing an application in vb.net 2005 using SQL Server 2000.
In this I have two tables SessionMaster and SessionChild.
Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type,
Fields of session child - SessionChildId, SessionMastId, UserName, Comment.
SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields.
Please how to write trigger to insert record into both tables at a time.
View 2 Replies
View Related
Aug 3, 2006
I am having this error when using execute query for CTE
Help will be appriciated
View 9 Replies
View Related
Feb 15, 2008
Hi,
I have a problem when updating tables in a SQL Server 2000. I am able to make a select form tables but I can't insert data. I got this error :
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
SQLExpress database file auto-creation error:
View 3 Replies
View Related
Dec 26, 2007
I am creating a query which will show patients that are enrolled in more than one program, but I need to exclude those patients that enrolled more than once in the same program. Here's part of the code:
SELECT member_id, service_id
FROM pat_prg_info ppi
This query produces results like the following:
member_id service_id
1001 1
1001 2
1003 9
1003 9
1004 2
1004 9
I would like to exclude 1003, since this member_id is enrolled twice in service_id 9. How can I accomplish this?
View 1 Replies
View Related
Aug 8, 2007
I'm kinda embarrased, it's been quite awhile since I've played with SQL or Access but here's my brain fart. I need to open an access DB by the name of eHomes. Inside there are two tables AD_IMAGE & PROPERTY. Property is the main table and stores most of the info. It has a field in it called AD_ID and others except for the image file name. The AD_IMAGE table just keeps the image file name and has 3 fields: ID - AD_ID - & Image1. The problem is when the ad is deleted from the property table the AD_IMAGE table record referring to the PROPERTY.AD_ID doen't get deleted. So, I just want the AD_IMAGE table cleaned up to match what's active in the PROPERTY table using the common AD_ID fields in both tables.
Thanks
View 1 Replies
View Related
Aug 26, 2015
I've inherited a table of members that has the following structure:
CREATE TABLE [dbo].[dimMember](
[dimMemberId] [int] IDENTITY(1,1) NOT NULL,
[dimSourceSystemId] [int] NOT NULL CONSTRAINT [DF_dimMember_dimSourceSystemId] DEFAULT ((-1)),
[MemberCode] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](250) NOT NULL,
[LastName] [nvarchar](250) NOT NULL,
[Code] ....
Based on the way the data loads into the table there's a possibility of some records being near duplicates of each other. For example, we can have a member that has records that have the same first name, last name, SSN, but different addresses, membercodes, subscribercode etc... This can happen in pretty much any variation thereof.
What I want to do, is add a new column and use that to group the similar records under based on comparing on several columns. By this I mean, if a member matches 4 of the 7 values below with another member, we would group these:
First Name (1st 3 characters)
Last Name
DOB
CurrentAddress1
MemberCode
SSN
SubscriberCode
I'm at a loss of how to structure the SQL to update the new column in the table.
View 9 Replies
View Related
Dec 26, 2006
Hi,
Has anyone worked on a system that needs per day around 1 Million writes, almost that many reads and deletes once every 1-2h. File sizes range from 2KB-1MB only that 85% of the files are in the 1-8KB range.
This is all done in a single table with a GUID key.
If yes could you tell me what problems did you encounter, any suggestions etc.
Thank you in advance
View 14 Replies
View Related
Jul 17, 2015
I have an SSIS package that is creating an Excel worksheet and writing data to it. It works fine when i run it inside Visual Studio. But when it runs as a scheduled job it writes the header and no data. I turned on logging and the log even says it is writing the 10,456 rows that it should be.Â
But they are not showing up in the Excel document. The job is setup as 32 bit and writing to Excel 97-2003. The job ends normally and does not generate any type of messages that are out of the ordinary. This is running on SQL Server 2008r2.
View 4 Replies
View Related
Mar 19, 2007
I have a set of parent/child records that need to be exported to a space delimited Flat File. Each parent record must be followed by 3 child records, each on their own line with different format.
I have a prototype using the Derived Column component that concatinates the various fields of each record into one "wide" text column. This fools SSIS to think that each row has the same format. Then I merge them together using an artificial sort id. But this seems overly tedious and very brittle.
What would be the best approach to writing these records out? I'm hoping there is a better more maintainable method.
Thanks,
Jon
View 4 Replies
View Related
Jul 23, 2005
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies
View Related
May 20, 2008
I have multiple Common Table Expressions i.e CTE1,CTE2,...CTE5.
I want to Create Temperory Table and inserting data from CTE5. Is this possible to create?
like:
create table #temp (row_no int identity (1,1),time datetime, action varchar(5))
insert into #temp select * from cte5
View 4 Replies
View Related
May 15, 2007
hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
View 2 Replies
View Related
May 28, 2008
Hi
I was studying Common Table expression in Sql server 2005.
I have written the code
Declare @PictureArray as varchar(200)
Set @PictureArray = '';
with UserProfile_CTE(UserPicture)
As(
select @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
select @PictureArray
)
select * from UserProfile_CTE
But I am getting the error
Incorrect syntax near '='
I am getting the error in the lineselect @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
But I don't know the reason for this,
Kindly advice
Regards
Karan
View 3 Replies
View Related
Jan 7, 2006
hi i am using MS SQL Server 2000, i want to delete the common row from a table
table name EMP
rows
Eno Name Sal
1 Jim 1000
2 Mark 1200
1 Jim 1000
2 Mark 1250
1 Jim 1300
no primary key defile in to the table ....
i want to delete the row of
1 Jim 1000
please anybody can can help me ...
samarjit
View 1 Replies
View Related
Jul 20, 2005
What is the SQL Server equivalent of DB2 common table expressions? Forexample,with gry(year,count) as(select floor(sem/10),count(distinct ssn)from gradesgroup by floor(sem/10))select year,sum(count) Head_Count from grygroup by yearhaving year >= 1980;N. ShamsundarUniversity of Houston
View 2 Replies
View Related
Jan 22, 2008
Hi there!
I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.
When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.
First, what do you think of that implementation?
We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...
What is the common solution for this need?
Thanks
Frantz
View 4 Replies
View Related
Aug 31, 2006
Hi guys,
I know its not a good idea to give you a big query...but/...
I have a query (given below) with Common table expressions. It is giving me error : The multi-part identifier "dbo.tmpValidServices_ALL.Service" could not be bound. for all the fields .. Do you have any idea.. I fed up with analysing query.. made me mad.. please help me...
----------------------------------------------
DECLARE @sql nvarchar(MAX), @paramlist nvarchar(4000)
SET @sql = 'WITH Q1 (SiteID,Programme,Complete,PID,COC,NotionalSum,TVMinsSUM,Postcode,Suburb,Qty)
AS
(SELECT
dbo.tmpValidServices_ALL.SiteID,
dbo.tmpValidServices_ALL.Programme,
dbo.tmpValidServices_ALL.Complete AS Complete,
dbo.tmpValidServices_ALL.RID AS PID,
dbo.tmpValidServices_ALL.COC# AS COC,
(dbo.lkpService.Notional$) AS NotionalSum,
(TVMins) AS TVMinsSUM,
dbo.tmpDemographics_ALL.Postcode,
dbo.tmpdemographics_ALL.Suburb,
count(*) as Qty
FROM lkpService
INNER JOIN dbo.tmpValidServices_ALL vs ON dbo.lkpservice.code = dbo.tmpValidServices_ALL.Service
INNER JOIN dbo.tmpDemographics_ALL ON dbo.tmpValidServices_ALL.RID = dbo.tmpDemographics_ALL.RID '
IF @COCGroup IS NOT NULL
SELECT @sql = @sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON dbo.tmpValidServices_ALL.COC = c.pvcode '
SELECT @sql = @sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '
SELECT @sql = @sql + 'WHERE dbo.lkpService.Schedule = @Schedule '
IF @StartDate IS NOT NULL
SELECT @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete >= @StartDate ) '
IF @EndDate IS NOT NULL
SELECT @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete <= @EndDate ) '
IF @SiteID IS NOT NULL
SELECT @sql = @sql + 'AND (ag.EXACT# = @SiteID) '
IF @COCGroup IS NOT NULL
SELECT @sql = @sql + ' AND (c.pvcode = @COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @COCGroup)) '
IF @Provider IS NOT NULL
SELECT @sql = @sql + 'AND (dbo.tmpValidServices_ALL.Provider = @Provider) '
IF @Postcode IS NOT NULL
SELECT @sql = @sql + 'AND (dbo.tmpdemographics_ALL.Postcode = @Postcode) '
SELECT @sql = @sql + ' GROUP BY dbo.tmpValidServices_ALL.SiteID,
dbo.tmpValidServices_ALL.Programme,
dbo.tmpValidServices_ALL.Complete ,
dbo.tmpValidServices_ALL.RID ,
dbo.tmpValidServices_ALL.COC# ,
dbo.tmpDemographics_ALL.postcode,
dbo.tmpdemographics_ALL.Suburb,
(dbo.lkpService.Notional$),
(dbo.lkpService.TVMins) )
SELECT COUNT(DISTINCT PID + CAST(Complete AS varchar(20)) + CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Visits,
COUNT(DISTINCT PID + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Patients,
COUNT(DISTINCT CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20)) + CAST(PID AS varchar(20))) AS COCs,
SUM(NotionalSum) AS NotionalSum,
SUM(TVMinsSUM) AS TVMinsSUM,Postcode,Suburb,sum(Qty) as Qty
FROM Q1
Group by Postcode,Suburb
Order by Postcode,Suburb '
SET @paramlist = '@SiteID as numeric,
@COCGroup as varchar(20),
@Postcode as varchar(20),
@StartDate DateTime,
@EndDate DateTime,
@Schedule varchar(20),
@Provider varchar(20)'
EXEC sp_executesql @sql, @paramlist, @SiteID, @COCGroup, @Postcode, @StartDate, @EndDate, @Schedule, @Provider
View 2 Replies
View Related
Aug 16, 2007
I'm not sure if I'm even starting at the right place.
We have a web portal that we use to send email TSN Templates. When we press this link, it queries our SQL database but then times out. When we go to our logs to see waht has happened, this is what we get: Can anyone point me in the right direction?
Timestamp: 15/08/2007 9:00:13 AM
Message: HandlingInstanceID: 5228cfc1-fbc3-4c64-98ab-468d0a99ba85
An exception of type 'System.Web.HttpException' occurred and was caught.
------------------------------------------------------------------------
08/15/2007 19:00:13
Type : System.Web.HttpException, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Message : Request timed out.
Source :
Help link :
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite :
Stack Trace : The stack trace is unavailable.
Additional Info:
MachineName :
TimeStamp : 15/08/2007 9:00:13 AM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.0.2697.40343, Culture=neutral, PublicKeyToken=d6be776f5b1b3d1f
AppDomainName : /LM/W3SVC/1/ROOT-1-128316415134243452
ThreadIdentity :
WindowsIdentity : NT AUTHORITYNETWORK SERVICE
Category: General
Priority: 0
EventId: 100
Severity: Error
Title:Enterprise Library Exception Handling
Machine:
Application Domain: /LM/W3SVC/1/ROOT-1-128316415134243452
Process Id: 36976
Process Name: C:WINDOWSSysWOW64inetsrvw3wp.exe
Win32 Thread Id: 35804
Thread Name:
Extended Properties:
For more information, see Help and Support Center at
View 1 Replies
View Related
Feb 20, 2007
I have managed to write my first CTE SQL that handles recursion but I have a problem with distinct - can't get that to work!!
My CTE:WITH StudentsHierarchy(SystemID1, GroupID, AccessType, AccessGroupID, StudentID, HierarchyLevel) AS
(
--Base Case
SELECT
SystemID,
GroupID,
AccessType,
AccessGroupID,
StudentID,
1 as HierarchyLevel
FROM UserGroup a
UNION ALL
--Recursive step
SELECT
u.SystemID,
u.GroupID,
u.AccessType,
u.AccessGroupID,
u.StudentID,
uh.HierarchyLevel + 1 as HierarchyLevel
FROM UserGroup u
INNER JOIN StudentsHierarchy uh ON
u.GroupID = uh.AccessGroupID
)
Select sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student s
where sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy sh
WHERE AccessType = 'S'
and I would like to have a distinct on the StudentID like:Select DISTINCT sh.StudentID, sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student s
where sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy sh
WHERE AccessType = 'S'
How should I do?
View 1 Replies
View Related
Jan 6, 2012
I would like to know if SQL Server creates automatic index on the used tables' common fields.
For example:
Code:
CREATE VIEW [dbo].[ORMAN]
AS
SELECT dbo.PARSEL.OBJECTID, dbo.PARSEL.ADAPARSEL, dbo.PARSEL.ORMANADI, dbo.PARSEL.MULKIYET, dbo.PARSEL.ALAN, dbo.PARSEL.YERKOD,
dbo.PARSEL.UYG_KAN, dbo.PARSEL.KomNo, dbo.PARSEL.KadKan, dbo.PARSEL.Mescere, dbo.PARSEL.ACIKLAMA, dbo.PARSEL.URETIM_YONTEMI,
[Code] .....
On the above view example, if table PARSEL has 1 million records do I have to create an index for PARSELDURUM or when I create a view does SQL server create automatic virtual index?
View 6 Replies
View Related
Jun 27, 2014
When I connect two table should they always have a column in common with each other?For example the 'patients' and 'doctors' tables in that ER are connected to say each doctor can have many patients and each patient can have many doctors, but they have no columns in common. Is it right?
View 8 Replies
View Related
Mar 11, 2013
Basically I have a table of users (id, name), a lookup table preferences (id, title) and the table of user-pref (idUser, idPref).Given a user id X I wanted to select a list of users, ordered by the highest amount of coincidence in the preference table...
something like
SELECT * FROM users WHERE id <> X ORDER BY -number of common preferences-
View 7 Replies
View Related
Jan 27, 2014
I have a table activity which show the activities of the site. An activity could be related with no other entity or it could be related with an account, or with a contact or with an opportunity. An opportunity and a contact could be also related with an account.
My problem is that I need to show the activity and the related account (if there is any) but because the relationship with an account could be done by different ways, I dont know how to do it without join the account table several times.
This is my current query:
SELECT AC.IdActividad, CU.idcuenta
FROM Actividades AC LEFT JOIN Tareas TA ON TA.IdActividad = C.IdActividad
LEFT JOIN ActividadesXCuenta AXC ON AC.IdActividad = AXC.IdActividad LEFT JOIN dbo.Cuentas CU ON axc.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXOportunidad axo ON AC.IdActividad = axo.IdActividad LEFT JOIN dbo.Oportunidades o ON axo.IdOportunidad = o.IdOportunidad LEFT JOIN dbo.Cuentas CU1 ON o.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXContacto axco ON AC.IdActividad = axco.IdActividad LEFT JOIN dbo.Contactos con ON axco.IdContacto = con.IdContacto LEFT JOIN dbo.Cuentas CU2 ON o.IdCuenta = CU.IdCuenta
As you see, I have the table Cuenta joined 3 times. That means that I need to put the ActivityID field 3 times but based on how the report was developed, I could not do that.
View 3 Replies
View Related
Jul 31, 2014
While writing store procedure in db most of Time i will Use common Table to write select queries for selecting more than seven table whether it reduce speed performance or it won't
;with cte
{
}
View 2 Replies
View Related
Jan 10, 2014
With my new data, need result for below scenario ( use CTE )
´`````````````
CREATE TABLE
´´´´´´´´´´´´´´´´
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Code] ....
===============================
Insert DATA TABLE 1
===============================
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AP' ,'AIR' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FL' ,'FLIGHT' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FP' ,'FLIGHT-Pas' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SE' ,'SEA' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SP' ,'SHIP' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'RD' ,'ROAD' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BU' ,'BUS' ,'No')
===============================
Insert DATA TABLE 2
===============================
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'1' ,'AP' ,'-1','4' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'2' ,'FL' ,'1','2' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'3' ,'FP' ,'2','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'4' ,'SE' ,'-1','0' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'5' ,'SP' ,'4','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'6' ,'RD' ,'-1','6' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'7' ,'BU' ,'6','3' )
========
RULE
========
1.QA of Parent Row value Greater than 0- Consider Parent and 1st Node.From the available result,
2.HasNodes = Yes, Display Parent and 1st Node
3.HasNodes = No, Display Parent Only.
================
Expected Result
================
Mode_Info|Mode_Detail|QA
AIR||4
AIR|FLIGHT|2
ROAD||6
===============
My Query
=============
learning CTE To apply for HasNodes Rules.
;with MyCTE1 as (
select
CONVERT(nvarchar(MAX),RTRIM(T2.Mode_Info)) Mode_Info, T1.Mode_Parent_Sno, T1.Mode_Sno, T1.QA ,T2.Has_Nodes
from [Travel_Quantity] T1
left join [Travel_Master] T2 on T1.Mode_Id = T2.Mode_Id
) ,
MyCTE2 as (
[code]....
View 4 Replies
View Related
Jan 28, 2008
Hi,
I'd like to perform a number of different operations on my Common Table expression but I seem to be limited to only one operation. For example I cannot both delete duplicate rows and then perform a select statement. I can only execute one of the statements referencing the common table expression.
What is wrong with my syntax?
;With OrderedTable
AS
(
select Row_number() OVER (partition BY SSNumber order by Department_Id desc ) AS ROWID,* from Employee
)
delete from OrderedTable where RowId != 1
SELECT COUNT(*),SSNumber FROM OrderedTable group by Department_Id order by count(*) desc
View 1 Replies
View Related
Sep 5, 2007
I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:
WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */
END
ELSE
BEGIN
/* Do something else here */
END
When I try this, I get a syntax error near the keyword 'IF'
Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!
View 3 Replies
View Related
Oct 4, 2006
I tried to use WITH to factor out the shared star join portion from a sql statement as it's usually the most expensive part.
However, examing the execution plan shows that the WITH clause is merely a syntactic suger that will internally be plugged back as derived tables where the same star join is executed repeatedly.
Is the intermediate rowset produced by a WITH caluse ever shared during the query execution?
View 5 Replies
View Related
Feb 20, 2006
Hi guys,
I'm trying to have two common table expression in my stored procedure, but I'm receiving errors when executing it, I found that they can't exist side by side,once I removed 1 of them, the stored procedure executed successfully.
The following are the errors
Code:
Msg 156, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'With'.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 27
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 33
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
I'm using SQL Server Express
View 4 Replies
View Related
Aug 30, 2006
Hi,
We are developing the web application using ASP.NET 2.0 using C# with Backend of SQL Server 2005.
Now we have one requirement with my client, Already our application live in ASP using MS Access Database. In Access Database our client already wrote the query, those query call the another query from same database. So I want to over take this functionality in SQL Server 2005.
In ASP Application, We call this query €œ081Stats€? from Record set.
This €˜081Stats€™ query call below sub query itself
Master Query: 081Stats
SELECT DISTINCTROW [08Applicants].school, [08Applicants].Applicants, [08Interviewed].Interviewed, [Interviewed]/[Applicants] AS [interviewed%], [08Accepted].Accepted, [Accepted]/[Applicants] AS [Accepted%], [08Dinged].Dinged, [Dinged]/[Applicants] AS [Dinged%], [08Waitlisted].Waitlisted, [Applicants]-[Accepted]-[Dinged] AS Alive, [08Matriculating].Matriculating, [Matriculating]/[Accepted] AS [Yield%]
FROM ((((08Applicants LEFT JOIN 08Interviewed ON [08Applicants].school = [08Interviewed].school) LEFT JOIN 08Accepted ON [08Applicants].school = [08Accepted].school) LEFT JOIN 08Dinged ON [08Applicants].school = [08Dinged].school) LEFT JOIN 08Waitlisted ON [08Applicants].school = [08Waitlisted].school) LEFT JOIN 08Matriculating ON [08Applicants].school = [08Matriculating].school;
Sub Query 1: 08Accepted
SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 2: 08Applicants
SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 3: 08Dinged
SELECT statusTbl.school, Count(1) AS Dinged
FROM statusTbl
WHERE (((statusTbl.decision)=0) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 4: 08Interviewed
SELECT statusTbl.school, Count(1) AS Interviewed
FROM statusTbl
WHERE (((statusTbl.interview)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 5: 08Matriculating
SELECT statusTbl.school, Count(1) AS Matriculating
FROM statusTbl
WHERE (((statusTbl.userdec)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
So now I got the solution from SQL Server 2005. I.e. Common Table Expressions, So I got the syntax and other functionality, Now my doubts is how do implement the CTE in SQL Server 2005, where can I store the CTE in SQL Server 2005.
How can I call that CTE from ASP.NET 2.0 using C#?
CTE is replacing the Stored Procedure and Views, because it is memory based object in SQL Server 2005.
How can I implement the CTE, where can I write the CTE and where can I store the CTE.
And how can I call the CTE from ASP.NET 2.0 using C#.
It€™s Very Urgent Requirements.
We need your timely help.
With Thanks & Regards,
Sundar
View 1 Replies
View Related
Nov 19, 2007
Hi,
I have a stored procedure that return 0 or 1 row and 10 columns. In my subsequent queries I only need 1 column from those 10 columns. Is there any better way other than creating or declaring temp table and than making a select from that table.
So I am looking int CTE to execute stored procedure and than make a selection from CTE, but CTE does not allow me to execute stored procedure. Is there any other better way of acheiving this.
This is what I want to change:
Declare @Col Varchar(10)
Decalre @TempTable(Col1 Int, Col2 Varchar(10), Col3 Varchar(10), Col4 Varchar(10))
Insert into @TempTable
Exec Procedure @Paramter
select @Col = col2 from @TempTable
INTO
With CTE(Col2, Col3, Col4) AS
(
Exec Procedure @Paramter)
select @Col = col2 from @TempTable
Thanks
Punu
View 6 Replies
View Related
Oct 26, 2007
After reading all the comments about microsoft ole db provider for db2, I've started converting a project from ibm db2 provider to microsoft ole db provider, for the reason that the server would not need a ibm client software installation anylonger.
However in one of the dataflows there is a select statement on the DB2 database (AS400 iseries DB2 V5R4) that uses common table expression. The Oledb data source in the Dataflow returns zero input columns when I use the microsoft provider instead of the ibm provider. When I set it back to use the ibm oledb provider the input columns are available again.
The common table expression is used in the select statement like:
WITH x AS (SELECT ... FROM mytable WHERE ...)
SELECT x.*, (x.a - x.b) As diff
FROM x
We are not allowed to change anything in the DB2 database, so using views or sprocs is not an option.
Did anyone else experience this and can it be avoided without changing the selct logic?
View 3 Replies
View Related
