I was wondering if it is possible to continuously query a real-time
datastream using SQL Server. Does anyone have any experience of this? I
have found LINUX based systems such as Borealis and STREAM. I would
prefer to use a Windows based system as the program using the query
results is Windows based.
I am using the DTS wizard to import tables from an AS/400. Some of the tables in the job are quite large and Sql Server either hangs up or completely crashes. My current configuration is WIN NT, SP4.0 SQL Server 7.0 no Service pack. Any suggestions?
View 1 Replies View RelatedError in the TDS datastream: data(14) greater than block(-132/46)
This is the error I recieve, I have recently installed MSSQL2005 and have SP2 loaded as well. Anyone out there had this problem?
Can I run queries against a SQL DB that is running on SQL Server 2005 Enterprise from the Express edition? I just need to get the info in a table. I have been told I have windows authentication to the DB, but the DB does not show up in my Express' list of networked severs.
Your help is appreciated. I have only worked with MySQL so this is all new to me.
Is there any way to deploy reports to a Report Manager in SQL Server Reporting Services 2005 if the reports query SQL Server 2000 databases? Or are only 2005 databases able to deploy to the web?
Also, if I have a report that queries both, can I deploy it to the web on the 2005 Report Manager with a linked server to the 2000 server?
Creating a web application. Running a simple query "SELECT username FROM vwCustomer"
vwCustomer is a view built on top of an Access DB which is set up as a linked server. Within SQL Server that view responds immediately. But when I try to access it from an ASP page it takes over 20 seconds to respond.
Used the following to create the linked server:
EXEC sp_addlinkedserver 'DBName', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:databasesdatabase.mdb'
GO
EXEC sp_addlinkedsrvlogin 'DBName', FALSE, NULL, 'Admin', ''
GO
The view is simply(this works fine in SQL Server):
SELECT * FROM DBName.db.dbo.customer
The ASP page:
SELECT * FROM vwCustomer
Can anyone give me a hint as to where the bottleneck is? Or how I can figure that out using SQL Profiler or something?
Please help!
Hi there,
I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.
SELECT * from RemoteDB.TESTDB.dbo.Objects
WHERE Last_Updated > '2008-05-21 10:51:00'
AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)
Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database. This would work just perfectly, no problems.
Now, the local database is actually on a PDA running SQL Server Compact Edition. There is currently no support for creating a linked environment. I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.
Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?
Thanks in advance
I need to create a package that will monitor a table in a source system and when a flag is set, load data from other tables in this source system to my destination system. Today this is accomplished with a SQL Agent job that executes every 15 minutes. If there is no work to do the job simply exits. I would like to create a SQLIS package that checks this control table every 30 seconds. Can I create a package that runs continuously?
View 16 Replies View RelatedI have small db and load is very less. I am set full backup at 9:00 pm once a day,and set transaction log backup to every 15 min.then i am taking the transaction log backup in 9:15,9:30,9:45....... My question is I lost my data between 9:15 to 9:30 . in those time i will do some transactions.Then how to recover my data even with out lasing single transactions.
View 4 Replies View RelatedHi,
Bit of a newbee question:
I'm after hosting a website with basically a table in it that is linked to a MS SQL Server 2005 database, which I want to update on a pretty much continual basis from my own server PC which I'm running a data mining tool that updates the MS SQL Server 2005 database.
Any idea on how I would achieve this, or any pointers would be much appreciated.
Cheers,
Tom
I have a query that uses a lot of joins, subqueries and temp tables (I inherited it and am leary about rewriting it). I tried to rewrite it using table variables at one point, but the run time just got ridiculous for any query spanning more than a few months.
It currently runs as-is in about 3 minutes or less in Management Studio; however, when I try to run it via SRS or the Visual Studios 2005 Designer it runs indefinitely. It also runs indefinitely if I try to refresh the fileds or run it from the data tab (basically any time it has to call the stored procedure).
Does anyone have an idea of how I can get this report to run or know why it runs differently in SRS than in Management Studio?
Here is the very long procedure:
CREATE PROCEDURE [dbo].[ISP_RPT_RENEWAL_RETENTION_TEST]
(
@FROM DATETIME,
@TO DATETIME,
@ASOF DATETIME,
@REPORT VARCHAR(10) = 'ADHOC')
AS
SET NOCOUNT ON
--DECLARE @FROM DATETIME
--DECLARE @TO DATETIME
--DECLARE @ASOF DATETIME
--DECLARE @REPORT VARCHAR(10)
--
--SET @REPORT = 'YTD'
--SET @FROM = '1/1/2006'
--SET @TO = '10/31/2006'
--SET @ASOF = '10/31/2006'
IF (@REPORT = 'ADHOC' OR @REPORT IS NULL OR @REPORT = '')
BEGIN
--Check to see if user has input data
--First of the previous month
IF (@FROM IS NULL OR @FROM = '') SET @FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1
IF (@TO IS NULL OR @TO = '') SET @TO = dbo.PreviousMonthEndDate(GetDate())
IF (@ASOF IS NULL OR @ASOF = '') SET @ASOF = dbo.PreviousMonthEndDate(GetDate())
END
ELSE IF @REPORT = 'MTD'
BEGIN
--Disregard user input and run for MTD
--First of the previous month
SET @FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1
SET @TO = dbo.PreviousMonthEndDate(GetDate())
SET @ASOF = dbo.PreviousMonthEndDate(GetDate())
END
ELSE IF @REPORT = 'YTD'
BEGIN
--Disregard user input and run for YTD
SET @FROM = dbo.FirstOfTheYear(GetDate())
SET @TO = dbo.PreviousMonthEndDate(GetDate())
SET @ASOF = dbo.PreviousMonthEndDate(GetDate())
END
SET @FROM = dbo.MidnightDate(@FROM)
SET @TO = dbo.MidnightDate(@TO)
SET @ASOF = dbo.MidnightDate(@ASOF)
/*** if user runs report before month end is closed, then use data from premium table
if however user runs report after month end close, then pull from reinsurance table ***/
DECLARE @CHECKDATE DATETIME
SELECT @CHECKDATE = MAX(MONTHENDDATE) FROM MPATREINSURANCE
WHERE MONTHENDDATE = @TO
/*********************************************** IDENTIFY ALL RENEWALS ***************************************************/
SELECT DISTINCT
RC.POLICYNO POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,
MCITCUST.FULLNAME ,MAX(ISNULL(RC1.POLICYNO,RC.PREVIOUSPOLICYNO)) PREVIOUSPOLICYNO ,RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS PMRSTATUS,V.COVERAGE
INTO #MPAV_RENEWALS
FROM MPATPMRC RC
INNER JOIN MCITCUST ON
RC.COMPANYID = MCITCUST.COMPANYID AND
RC.INSUREDNAMENO = MCITCUST.NAMENO
LEFT OUTER JOIN MPATCGLUWVERSIONS V ON
V.POLICYID = RC.POLICYID AND
V.PMRSEQUENCE = RC.PMRSEQUENCE AND
V.SUBCOMPANYID = RC.SUBCOMPANYID
INNER JOIN MCITCUST M1 ON
MCITCUST.COMPANYID =M1.COMPANYID AND
MCITCUST.CURRENTNAMENO = M1.CURRENTNAMENO
INNER JOIN MPATPMRC RC1 ON
RC1.COMPANYID = M1.COMPANYID AND
RC1.INSUREDNAMENO = M1.NAMENO
LEFT OUTER JOIN MPATCGLUWVERSIONS V1 ON
V1.POLICYID = RC1.POLICYID AND
V1.PMRSEQUENCE = RC1.PMRSEQUENCE AND
V1.SUBCOMPANYID = RC1.SUBCOMPANYID
WHERE
RC.POLICYTYPE ='CGL' AND
RC.POLICYEFFDATE >= '1/1/1998' AND
--FIND THE VERY FIRST VALID TRANSACTION BY LOOKING AT WHICH ONE OF RN/NB/RW IS THE LATEST
RC.TXNISSUED <= @ASOF AND
RC1.TXNISSUED <= @ASOF AND
RC.PMRSEQUENCE = (SELECT MAX(PMRSEQUENCE) FROM MPATPMRC RC2
WHERE RC2.POLICYID = RC.POLICYID AND
RC2.SUBCOMPANYID = RC.SUBCOMPANYID AND
RC2.POLICYYEAR = RC.POLICYYEAR AND
RC2.TXNISSUED <= @ASOF AND
RC2.TXNTYPE IN ('RN','NB','RW','RE') AND
( (RC2.POLICYNO = RC.POLICYNO AND RC.POLICYNO IS NOT NULL)
OR (RC.POLICYNO IS NULL)
) AND
RC2.PMRSTATUS IN ('HS', 'EX', 'IN','PC','PA')AND
RC2.PROCESSINGSTATUS IN ('*','P','A','I','Q')
)
AND ((RC1.TXNTYPE ='CN' AND RC1.CANCELMETHOD <> 'F') OR (RC1.CANCELMETHOD IS NULL OR RC1.TXNTYPE <> 'CN'))
AND RC1.PMRSEQUENCE = (
SELECT MAX(PMRSEQUENCE) FROM MPATPMRC
WHERE MPATPMRC.POLICYID = RC1.POLICYID AND
MPATPMRC.POLICYNO = RC1.POLICYNO AND
MPATPMRC.TXNISSUED <= @ASOF AND
MPATPMRC.PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND
MPATPMRC.PROCESSINGSTATUS IN ('*','C')AND
MPATPMRC.TXNTYPE NOT IN ('EA','ER' )) AND
(
(RC1.TXNTYPE <>'CN' AND RC1.POLICYEXPDATE = RC.POLICYEFFDATE ) OR
(RC1.TXNTYPE ='CN' AND RC1.TXNEFFECTIVE = RC.POLICYEFFDATE )
)
AND (V.COVERAGE = 'B' AND V1.COVERAGE = 'P' OR V.COVERAGE = 'P' AND V1.COVERAGE = 'B' OR
V.COVERAGE = V1.COVERAGE)
GROUP BY RC.POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,
MCITCUST.FULLNAME , RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS ,V.COVERAGE
/**************************************FIND LAST VERSION*********************************/
SELECT
RC.POLICYNO,
RC.POLICYYEAR,
RC.POLICYID,
RC.SUBCOMPANYID,
RC.PMRSEQUENCE,
RC.TXNTYPE,
POLICYEXPDATE = CASE WHEN RC.TXNTYPE = 'CN' THEN RC.TXNEFFECTIVE ELSE RC.POLICYEXPDATE END,
REPORTDATES =CASE
WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE <= RC.TXNISSUED THEN RC.TXNISSUED
WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE > RC.TXNISSUED THEN RC.TXNEFFECTIVE
ELSE RC.POLICYEXPDATE END,
CN_REASON = CASE WHEN RC.TXNTYPE = 'CN' THEN
(CASE WHEN RC.TXNSUBTYPE IN ('PF','NP') THEN 'Non-pay'
WHEN RC.TXNSUBTYPE IN ('IN') THEN 'Ins Request'
WHEN RC.TXNSUBTYPE IN ('UW') THEN 'UW Reasons' ELSE 'Other' END)
Else '' END,
CN_METHOD = CASE WHEN RC.TXNTYPE = 'CN' THEN CANCELMETHOD ELSE '' END,
INS.FULLNAME INSURED,
RC.INSUREDNAMENO,
INS.CURRENTNAMENO,
RC.STATE,
RC.ORIGINCEPTIONDATE
INTO #LAST_VERSION
FROM MPATPMRC RC
INNER JOIN --LOOKING UP THE LATEST VERSION OF POLICIES
(SELECT DISTINCT POLICYNO, POLICYID, SUBCOMPANYID, MAX( PMRSEQUENCE) LASTPMR
FROM MPATPMRC
WHERE
POLICYTYPE ='CGL' AND
PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND
PROCESSINGSTATUS IN ('*','C') AND
TXNTYPE NOT IN ('EA','ER')
AND MPATPMRC.TXNISSUED <= @ASOF
GROUP BY POLICYNO, POLICYID, SUBCOMPANYID) LV ON
RC.POLICYNO = LV.POLICYNO AND
RC.SUBCOMPANYID = LV.SUBCOMPANYID AND
RC.POLICYID = LV.POLICYID AND
RC.PMRSEQUENCE = LV.LASTPMR
LEFT OUTER JOIN MCITCUST INS ON
RC.INSUREDNAMENO = INS.NAMENO
/*=============================================IDENTIFY RENEWALS FOR THE PERIOD==============================*/
SELECT R.POLICYNO,
R.CURRENTNAMENO,
R.POLICYID,
R.PMRSEQUENCE,
R.SUBCOMPANYID,
R.POLICYEFFDATE,
R.FULLNAME,
R.PREVIOUSPOLICYNO,
R.INSUREDNAMENO,
R.PROCESSINGSTATUS,
R.PMRSTATUS,
R.COVERAGE
INTO #RENEWALS
FROM #MPAV_RENEWALS R
LEFT OUTER JOIN #LAST_VERSION LV ON
R.POLICYNO = LV.POLICYNO
--take off pendings
WHERE LV.CN_METHOD <> 'F' AND
POLICYEFFDATE BETWEEN @FROM AND @TO
/* LETS GET RENEWAL BROKER */
SELECT
R.POLICYNO,
P.NAMENO,
C.FULLNAME,
A.CITY
INTO #RN_BRK
FROM #RENEWALS R
INNER JOIN MPATPRODUCERS P ON
R.POLICYID = P.POLICYID AND
R.PMRSEQUENCE = P.PMRSEQUENCE AND
R.SUBCOMPANYID = P.SUBCOMPANYID AND
PAYOR = 1
INNER JOIN MCITCUST C ON
P.NAMENO = C.NAMENO
INNER JOIN MCITADDR A ON
C.MAILINGADDRESS = A.ADDRESSID
/*==============================================GET RENEWING PREMIUMS=========================================*/
--this gives us written premium as of report date
SELECT DISTINCT
R.POLICYNO,
R.POLICYID,
MAX(R.SUBCOMPANYID) SUB,
MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,
MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,
MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,
MAX(MPATPMRC.UNDERWRITER)UW,
RN_DIRECT = SUM(WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),
RN_CEDED_REIN = SUM(WRITTENPREM3),
CEDED_RETAINED = 0
INTO #RN_PREM
FROM #RENEWALS R
LEFT OUTER JOIN MPATPMRC MPATPMRC ON
MPATPMRC.POLICYNO = R.POLICYNO
LEFT OUTER JOIN MPATPREMIUMS MPATPREMIUMS ON
MPATPMRC.COMPANYID = MPATPREMIUMS.COMPANYID AND
MPATPMRC.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND
MPATPMRC.POLICYID = MPATPREMIUMS.POLICYID AND
MPATPMRC.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID
WHERE @CHECKDATE IS NULL AND
MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND
((MPATPMRC.TXNISSUED >= MPATPMRC.TXNEFFECTIVE AND MPATPMRC.TXNISSUED BETWEEN @FROM AND @TO) OR
(MPATPMRC.TXNEFFECTIVE >= MPATPMRC.TXNISSUED AND MPATPMRC.TXNEFFECTIVE BETWEEN @FROM AND @TO))
GROUP BY R.POLICYNO , R .POLICYID
UNION ALL
--this gives us written premium as of report date
SELECT DISTINCT
R.POLICYNO,
R.POLICYID,
MAX(R.SUBCOMPANYID) SUB,
MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,
MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,
MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,
MAX(MPATPMRC.UNDERWRITER) UW,
RN_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,
RN_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,
CEDED_RETAINED= SUM(
(CASE WHEN CEDEDPERCENT1 <> 0 THEN
((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT2 <> 0 THEN
((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT3 <> 0 THEN
((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)
)
FROM #RENEWALS R
LEFT OUTER JOIN MPATPMRC MPATPMRC ON
MPATPMRC.POLICYNO = R.POLICYNO
LEFT OUTER JOIN MPATREINSURANCE MPATREINSURANCE ON
MPATPMRC.COMPANYID = MPATREINSURANCE.COMPANYID AND
MPATPMRC.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND
MPATPMRC.POLICYID = MPATREINSURANCE.POLICYID AND
MPATPMRC.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID AND
MPATPMRC.TREATYID = MPATREINSURANCE.TREATYID
WHERE @CHECKDATE IS NOT NULL AND
MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND
((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED BETWEEN @FROM AND @TO) OR
(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE BETWEEN @FROM AND @TO))
GROUP BY R.POLICYNO , R .POLICYID
/*======================= looks for all policies EXPIRED during report time==========================*/
SELECT
LV.POLICYYEAR,
LV.POLICYNO,
LV.POLICYID,
LV.SUBCOMPANYID,
LV.PMRSEQUENCE,
LV.TXNTYPE,
LV.POLICYEXPDATE,
REASONDROPPED =
CASE WHEN LV.TXNTYPE = 'CN' AND CN_REASON = 'Non-pay' AND R.DESCRIPTION IS NULL
THEN 'Cancelled for non pay'
ELSE
ISNULL(ISNULL(R.DESCRIPTION, CLOSEREASONDESC),'No reason entered') END,
CN_METHOD,
V.COVERAGE,
CURRENTNAMENO,
INSUREDNAMENO,
INSURED,
LV.STATE,
ORIGINCEPTIONDATE,
CLOSECATEGORYDESC,
CLOSEREASONDESC,
COMPETITORDESC,
MKTFOLLOWUPDESC
INTO #EXPIRED
FROM #LAST_VERSION LV
LEFT OUTER JOIN MCITINSURED ON
LV.INSUREDNAMENO = MCITINSURED.NAMENO
LEFT OUTER JOIN MSOTDROPREAS R ON
MCITINSURED.REASONDROPPED = R.REASONID
LEFT OUTER JOIN MPATCGLUWVERSIONS V ON
V.POLICYID = LV.POLICYID AND
V.PMRSEQUENCE = LV.PMRSEQUENCE AND
V.SUBCOMPANYID = LV.SUBCOMPANYID
LEFT OUTER JOIN MSOTCLOSECATEGORIES CC ON
MCITINSURED.CLOSECATEGORYID = CC.CLOSECATEGORYID
LEFT OUTER JOIN MSOTCLOSEREASONS CR ON
MCITINSURED.CLOSEREASONID = CR.CLOSEREASONID
LEFT OUTER JOIN MSOTCOMPETITORS CO ON
MCITINSURED.COMPETITORID = CO.COMPETITORID
LEFT OUTER JOIN MSOTMKTFOLLOWUP MK ON
MCITINSURED.MKTFOLLOWUPID = MK.MKTFOLLOWUPID
WHERE REPORTDATES BETWEEN CAST(CAST(@FROM AS VARCHAR(11)) AS DATETIME) --DF CHANGE
AND CAST(CAST(@TO AS VARCHAR(11)) AS DATETIME)
/*==============================================GET EXPIRED POLICY INFO=========================================*/
--GET INFO FOR ALL TXNS SO U ALSO GET THE PREMIUMS
SELECT DISTINCT
MAX(MCITCUST.NAMENO) NAMENO,
R.RISKSEQUENCE,
MAX(MCITCUST.CURRENTNAMENO) CURRENTNAMENO,
MAX(MCITCUST.FULLNAME) FULLNAME,
RC.POLICYNO,
RC.POLICYID,
MAX(RC.SUBCOMPANYID) SUB,
MAX(RC.POLICYYEAR) POLICYYEAR,
EXPDATE = MAX(EX.POLICYEXPDATE),
SALES = SUM(CASE WHEN PR.COVERAGETYPE ='P' AND PR.PMRSEQUENCE = EX.PMRSEQUENCE THEN ISNULL(PR.SALES,0) ELSE 0 END),
MAX(RC.UNDERWRITER) UW,
P.HAZARDCLASS,
P.CLASSCODE PRODUCTCODE,
MAX(P.DESCRIPTION) [DESCRIPTION],
DISTRIBUTOR = (CASE WHEN P.DISTRIBUTOR = 1 THEN 'YES' ELSE 'NO' END),--R.PRODUCTCODE
MEMBER = MAX(CASE WHEN MEMBER = 1 THEN 'DEVICE' ELSE 'EXP' END),
SUM(WRITTENPREM1 + WRITTENPREM2) LAYER1_2,
EX_LIMIT = MAX(CASE WHEN V.COV_PROD_AGGREGATE = '1000000' OR V.COV_CGL_AGGREGATE = '1000000' THEN 1
WHEN V.COV_PROD_AGGREGATE = '2000000' OR V.COV_CGL_AGGREGATE = '2000000' THEN 2
WHEN V.COV_PROD_AGGREGATE = '3000000' OR V.COV_CGL_AGGREGATE = '3000000' THEN 3
WHEN V.COV_PROD_AGGREGATE = '4000000' OR V.COV_CGL_AGGREGATE = '4000000' THEN 4
WHEN V.COV_PROD_AGGREGATE = '5000000' OR V.COV_CGL_AGGREGATE = '5000000' THEN 5
WHEN V.COV_PROD_AGGREGATE = '6000000' OR V.COV_CGL_AGGREGATE = '6000000' THEN 6
WHEN V.COV_PROD_AGGREGATE = '7000000' OR V.COV_CGL_AGGREGATE = '7000000' THEN 7
WHEN V.COV_PROD_AGGREGATE = '8000000' OR V.COV_CGL_AGGREGATE = '8000000' THEN 8
WHEN V.COV_PROD_AGGREGATE = '9000000' OR V.COV_CGL_AGGREGATE = '9000000' THEN 9
WHEN V.COV_PROD_AGGREGATE = '10000000' OR V.COV_CGL_AGGREGATE = '10000000' THEN 10 END),
EX_BROKER_GROUP =
(SELECT [DESCRIPTION] FROM MSOTCOMMPLAN
WHERE
SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND
COMMISSIONPLAN =
(SELECT TOP 1 COMMISSIONPLAN FROM MAGTCOMMPLAN
WHERE NAMENO = MPATPRODUCERS.NAMENO AND SUBCOMPANYID =MPATPRODUCERS.SUBCOMPANYID AND
COMMRATEEFFDATE <= EX.POLICYEXPDATE
ORDER BY COMMRATEEFFDATE DESC )),
MAX(BROKER.FULLNAME) BRK_NAME,
MAX(MPATPRODUCERS.COMMISSIONRATE * 100) RATE,
MAX(AA.CITY) BRK_CITY
INTO #EX
FROM MPATPMRC RC
INNER JOIN MPATPREMIUMS PR ON
RC.POLICYID = PR.POLICYID AND
RC.SUBCOMPANYID = PR.SUBCOMPANYID AND
RC.PMRSEQUENCE = PR.PMRSEQUENCE
INNER JOIN #EXPIRED EX ON
EX.POLICYNO = RC.POLICYNO
INNER JOIN MCITCUST ON
RC.COMPANYID = MCITCUST.COMPANYID AND
RC.INSUREDNAMENO = MCITCUST.NAMENO
LEFT OUTER JOIN MPATCGLUWRISKS R ON
PR.RISKSEQUENCE = R.RISKSEQUENCE AND
EX.POLICYID = R.POLICYID AND
EX.PMRSEQUENCE = R.PMRSEQUENCE AND
EX.SUBCOMPANYID = R.SUBCOMPANYID
LEFT OUTER JOIN MSOTPRODUCTS P ON
R.PRODUCTCODE = P.PRODUCTCODE
LEFT OUTER JOIN MPATCGLUWVERSIONS V ON
V .POLICYID = EX.POLICYID AND
V .PMRSEQUENCE =EX.PMRSEQUENCE AND
V .SUBCOMPANYID = EX.SUBCOMPANYID
INNER JOIN MPATPRODUCERS ON
EX.SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND
EX.POLICYID = MPATPRODUCERS.POLICYID AND
EX.PMRSEQUENCE = MPATPRODUCERS.PMRSEQUENCE AND
MPATPRODUCERS.PAYOR = 1
INNER JOIN MCITCUST BROKER ON
MPATPRODUCERS.COMPANYID = BROKER.COMPANYID AND
MPATPRODUCERS.NAMENO = BROKER.NAMENO
LEFT OUTER JOIN MCITADDR AA ON
BROKER.MAILINGADDRESS = AA.ADDRESSID
WHERE RC.PMRSTATUS IN ('IN','EX','HS','CN') AND
RC.PROCESSINGSTATUS IN ('*','C')
GROUP BY R.RISKSEQUENCE ,RC.POLICYNO, RC.POLICYID,P.HAZARDCLASS, P.CLASSCODE,
MPATPRODUCERS.SUBCOMPANYID, MPATPRODUCERS.NAMENO, EX.POLICYEXPDATE,P.DISTRIBUTOR
ORDER BY RC.POLICYNO
/*****************************************************************************************************************/
SELECT
R.POLICYNO,
EX_DIRECT = SUM( WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),
EX_CEDED_REIN = SUM(WRITTENPREM3),
EX_CEDED_RETAINED= 0
INTO #EX_PREM
FROM MPATPMRC R
INNER JOIN #EXPIRED E ON
R.POLICYNO = E.POLICYNO
LEFT OUTER JOIN MPATPREMIUMS ON
R.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND
R.POLICYID = MPATPREMIUMS.POLICYID AND
R.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID
WHERE @CHECKDATE IS NULL AND
((R.TXNISSUED >= R.TXNEFFECTIVE AND R.TXNISSUED <= @TO) OR
(R.TXNEFFECTIVE >= R.TXNISSUED AND R.TXNEFFECTIVE <= @TO))
GROUP BY R.POLICYNO
UNION ALL
SELECT
R.POLICYNO,
EX_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,
EX_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,
EX_CEDED_RETAINED=
SUM(
(CASE WHEN CEDEDPERCENT1 <> 0 THEN
((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT2 <> 0 THEN
((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT3 <> 0 THEN
((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)
)
FROM MPATPMRC R
INNER JOIN #EXPIRED E ON
R.POLICYNO = E.POLICYNO
LEFT OUTER JOIN MPATREINSURANCE ON
R.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND
R.POLICYID = MPATREINSURANCE.POLICYID AND
R.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID
WHERE @CHECKDATE IS NOT NULL AND
((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED <= @TO) OR
(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE <= @TO))
GROUP BY R.POLICYNO
/******************************************************************************************************************/
EXECUTE ISP_RPT_MKTSEGMENT
SET NOCOUNT OFF
SELECT
E.POLICYNO EXP_POLICY,
E.POLICYEXPDATE,
A.UW,
E.INSURED INS_NAME,
STATE INS_STATE,
BROKER,
BROKER_GROUP = CASE WHEN BROKER_GRP LIKE '%Others%' THEN 'Others'
WHEN BROKER_GRP LIKE '%Preferred%' THEN 'Preferred'
WHEN BROKER_GRP LIKE '%Marsh%' THEN 'Marsh'
WHEN BROKER_GRP LIKE '%Champion%' THEN 'Champion'
ELSE 'Others' END, COMM_RATE,
DOMINANT_PROD DOM_PROD,
DOMINANT_PROD_DESC DESCR,
MK.DISTRIBUTOR,
DOMINANT_HAZ DOM_HAZD,
EX_LIMIT,
MEMBER,
A.SALES,
MKT_SEGMENT,
ISNULL(EX_DIRECT,0) TOTAL_WRTTN,
(ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0)) TOTAL_CEDED,
CEDED_REINSURE = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_REIN,0) END ,
CEDED_RETAINED = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_RETAINED,0) END ,
NWP_2M = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ((ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0))) END,
XOL_NWP = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ISNULL(EX_CEDED_REIN,0) END,
REASON_LOST =
CASE WHEN R.POLICYNO IS NULL THEN
(SELECT 'LOST -'+REASONDROPPED FROM #EXPIRED WHERE POLICYNO = E.POLICYNO)
ELSE '' END,
ISNULL(R.POLICYNO, '') RN_POLICY,
ISNULL(RN_DIRECT,0) RN_TOTAL_WRTTN,
(ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0)) RN_TOTAL_CEDED,
RN_CEDED_REINSURE = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_CEDED_REIN,0) END ,
RN_CEDED_RETAINED = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(CEDED_RETAINED,0) END ,
RN_NWP_2M = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ((ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0))) END,
RN_XOL_NWP = CASE WHEN @CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ISNULL(RN_CEDED_REIN,0) END,
[INSURED INFORCE TODAY?] = CASE WHEN (SELECT COUNT(*)
FROM MPATPMRC
WHERE PMRSTATUS ='IN' AND
INSUREDNAMENO IN
(SELECT NAMENO FROM
MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)
)>0 THEN 'YES' ELSE 'NO' END,
[FIRST_JOINED_DATE] =
ISNULL(
(SELECT MIN(DATEJOINED) FROM MCITINSURED
WHERE NAMENO IN
(SELECT DISTINCT NAMENO FROM
MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)), ORIGINCEPTIONDATE),
[# OF POLICY YEARS] = (
SELECT COUNT(DISTINCT POLICYYEAR)
FROM MPATPMRC
WHERE PMRSTATUS IN ('EX','HS','IN') AND TXNTYPE IN ('CV','NB','RN','RW') AND PROCESSINGSTATUS ='*'
AND POLICYNO IS NOT NULL AND
INSUREDNAMENO IN (SELECT DISTINCT NAMENO FROM
MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)),
MONTHS = (CASE WHEN MONTH(E.POLICYEXPDATE) = 1 THEN 'January'
WHEN MONTH(E.POLICYEXPDATE) = 2 THEN 'February'
WHEN MONTH(E.POLICYEXPDATE) = 3 THEN 'March'
WHEN MONTH(E.POLICYEXPDATE) = 4 THEN 'April'
WHEN MONTH(E.POLICYEXPDATE) = 5 THEN 'May'
WHEN MONTH(E.POLICYEXPDATE) = 6 THEN 'June'
WHEN MONTH(E.POLICYEXPDATE) = 7 THEN 'July'
WHEN MONTH(E.POLICYEXPDATE) = 8 THEN 'August'
WHEN MONTH(E.POLICYEXPDATE) = 9 THEN 'September'
WHEN MONTH(E.POLICYEXPDATE) = 10 THEN 'October'
WHEN MONTH(E.POLICYEXPDATE) = 11 THEN 'November'
WHEN MONTH(E.POLICYEXPDATE) = 12 THEN 'December' END),
BRK_CITY,
RB.FULLNAME RN_BROKER,
RB.CITY RN_BRK_CITY,
ORIG_EXP =
(SELECT MIN(POLICYEXPDATE) POLICYEXPDATE
FROM
MPATPMRC
WHERE POLICYNO = E.POLICYNO AND TXNTYPE IN ('NB','RN','RW')) ,
DROP_CATEGORY = CASE WHEN R.POLICYNO IS NULL THEN CLOSECATEGORYDESC ELSE '' END ,
DROP_REASON = CASE WHEN R.POLICYNO IS NULL THEN CLOSEREASONDESC ELSE '' END ,
COMPETITOR = CASE WHEN R.POLICYNO IS NULL THEN COMPETITORDESC ELSE '' END ,
MKTFOLLOWUP = CASE WHEN R.POLICYNO IS NULL THEN MKTFOLLOWUPDESC ELSE '' END,
MKT_SEGMENTSORT = MS.SORTORDER
FROM #EXPIRED E
LEFT OUTER JOIN #RENEWALS R ON
E.POLICYNO = R.PREVIOUSPOLICYNO
LEFT OUTER JOIN
(SELECT POLICYNO, SUM(SALES)SALES, MAX(UW) UW, SUM(LAYER1_2) PREM_2MIL, MAX(EX_LIMIT) EX_LIMIT,
MAX(BRK_NAME) BROKER, MAX(EX_BROKER_GROUP) BROKER_GRP, MAX(RATE) COMM_RATE, MAX(BRK_CITY) BRK_CITY
FROM #EX
GROUP BY POLICYNO ) A ON
E.POLICYNO = A.POLICYNO
LEFT OUTER JOIN #EX_PREM EP ON
E.POLICYNO = EP.POLICYNO
LEFT OUTER JOIN #RN_PREM RP ON
R.POLICYNO = RP.POLICYNO
LEFT OUTER JOIN MPAT_MKTSEGMENTS MK ON
A.POLICYNO = MK.POLICYNO
LEFT OUTER JOIN MSOTMARKETSEGMENT MS ON
MK.MKT_SEGMENT = MS.MARKETSEGMENT
LEFT OUTER JOIN #RN_BRK RB ON
R.POLICYNO = RB.POLICYNO
WHERE ISNULL(E.CN_METHOD,'X') <> 'F'
ORDER BY R.POLICYNO
We have transactional replication. After creating replication, we found that many blocking and deadlocks are occurring continuously. Will it cause any blocking on publisher db.
View 4 Replies View RelatedHi
We have setup transactional replication between 2 databases on SQL Server 2000 SP3a (~70GB), using a concurrent snapshot (to prevent locking out of the live database) to initilaise the data and a pull subscription from the second database.
From analysing the msdistribution_history table in the distribution database on the subscriber it appears that the snapshot is being applied in a continuous loop to the subscriber database. Viewing the comments column in the msdistribution_history table we can see the following sequence of events occuring
Initialising
Applied script 'snapshot.pre'
Then it applies all the schema files .sch
Then it applies all the index files .idx
The it bulk copies the data in (bcp)
Then it creates the Primary Keys
Then it applies all the trigger files .trg
Then it applies all the referential integrity files .dri
These all complete successfully but then the process kicks off again immediately after reapplying the snapshot. We are unaware of any settings that may be causing this.
Any help on what maybe causing this would be much appreciated.
Hi,
I've been trying to use Analysis Services 2005 Cube as a data source, query it via MDX and then use the data returned elsewhere in SSIS.
However, I've been unable to get this working and can't find any information regarding how this can be done. Surely it should be possible when I can get this working even in Excel?
I've looked in December edition of BOL and no luck - have also sent a feedback to BOL regarding this and have been told that "it should be possible, since there is a way to send SQL queries to AS." However the person I was speaking with knew of no one who had actually tried this scenario and to try posting here.
Any help as to how to get this done would be greatly appreciated.
NR
I've been digging for a while to try to find something, even had a consultant looking for me, but came up empty haned.
What I want to do is this:
I activated the Security Audit level for all logins. I want to be able to report last login date for every user from the log, and all login events for SA or any other defined super users.
Is it possible to Query the SQL Server Logs to report this information?
This is for proposed Sarbox Database Security settings.
I know that a heterogeneous query joining tables from two different servershas performance penalties but is the same true when joining tables from twodifferent databases on the same SQL Server 2000 instance?We are looking at setting up a Data Warehouse using DTS on a SQL Server boxand I'm wondering about the best way to logically set it up; i.e. one bighonking db or several dbs determined by some logical organization. With thelatter there will still be some queries that would need data from more thanone db and I'm wondering if that will have worse performance than if theywere all in one db.I thought that was the case in older versions of SS, but I couldn't findanything in 2000's BOL that indicated a problem with that.TIA
View 1 Replies View RelatedI am remotely logging onto a MS SQL 6.0 server using sp_addlinkedsrvlogin.
When I try to query against the table on this server I get the following error
message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: The data source can not be used, because it DBMS version is less than 6.5.0.]
I know that 6.0 doesn't support OLEDB but shouldn't 7.0 be able to access this
using odbc. Do I need to do anything other than set up the dsn on odbc.
Any feedback on this would be great.
Cheers,
bhanly@apcc.com
Any idea on how to query active directory to find out list of groups nad users etc..using sql server?
View 6 Replies View RelatedHow do I specify the Catalogue while querying a linked server?
For exmaple, I have a remote SQL 2000 server as a linked server on my server. I will refer to it as "ServerRemote." I was given access to that server through my Active Directory Domain account and the catalogue 'master' was set as my default database.
Aside from having the admin change my default database, is there a way to query a linked server and specify the catalogue in the query?
Right now I use the following code to query the default catalogue for the linked server:
SELECT * FROM ServerRemote...ViewName
I've tried various queryies for a different catalogue but always get a table not found error:
SELECT * FROM ServerRemote..DatabaseName.ViewName
or
SELECT * FROM ServerRemote.DatabaseName..ViewName
or
SELECT * FROM ServerRemote.DatabaseName.ViewName
all return errors
Hi all,
How do I query two tables in different databases on the same SQL Server?
In short, I want to do:
Select A.*
from database 1. table 1 as A
inner join database 2. table 1 as B
on A.COL1 = B.COL1
Both database 1 and 2 are on the same SQL Server.
Please advise.
Thanks,
V
I have an xml document that (for this example) I've simplified to look like this:
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
[code]....
When I try querying the xml document in SQL, I get nothing back, unless I remove the schema information. I'm using this:
declare @x xml
select @x = P
from openrowset (bulk 'E:VehicleOption0514.xml', single_blob) as Products(P)
declare @hdoc int
exec sp_xml_preparedocument @hdoc output, @x
[Code] ....
I have a plain XML variable
<BS>
<B Id="5" />
<B Id="3" />
<B Id="4" />
<B Id="6" />
<B Id="15" />
<B Id="7" />
</BS>
When I insert this into a temp table, the order get mixed up.
SELECT I.value('@Id', 'INT') ProductId
INTO #ProductList
FROM @Products.nodes('/BS/B') AS T(I)
When I select out of #ProductList the order is random and different from the XML.
I am currently migrating our Intranet from SQL Server 7 to SQL Server2000 and have hit a problem with one of the applications. Theapplication in question executes the folloiwing query against thedatabase:select * from employee where emp_id = 760In SQL Server 7 this works without a problem, however, in SQL server2k the following error is returned:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error convertingthe varchar value 'Con25' to a column of data type int.This is clearly because the column emp_id is a varchar, I understandthat this code has not exactly followed best practices but do not wantto have to trawl through the application and surround all instanceswith apostrophes.Is it possible to make SQL 2k act in the same way as SQL 7 or will Ihave to trawl through the code and correct the SQL statements.Thanks for any help.John
View 2 Replies View RelatedI have a linked SQL Server on another machine which is created using a stored procedure that executes the following:
EXEC sp_addlinkedserver @server = 'AchillesMixed', @srvproduct = ' ', @provider = 'SQLNCLI', @datasrc = 'ArchillesMixed', @catalog = 'DB_INTRANET'
The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:
SELECT * FROM DB_INTRANET...Employees
I get the following error:
OLE DB provider "SQLNCLI" for linked server "DB_Intranet" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
The logins are Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.
I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.
Does anyone have any suggestions please.
I would like to query a linked server and join that database to my local database. I finally was able to get it to work and I got this query to work:
select * from openquery([hcda-storagesrvlaserfiche], 'select * from toc where parentid=358')
I would like to join one of my tables from my local database to the query. How can I do this? Thanks!
We have this query that pulls number of days worked from the current Quarter to Date.
(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked'
FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd))
AS VARCHAR) AS daysworked, ActivityId AS totalcalls
FROM PhoneCall AS p
WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR,
DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb)
AS [Days Worked],
I need changing it to bring up LAST Quarter's data.
Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.
Thanks.
I have SQL 2K with SP4. Oracle client is installed on the Server. I have created linked server to Oracle. I am abld to connect to Oracle Server through DTS but when I query an oracle linked server SQl Server crashes with "EXCEPTION_ACCESS_VIOLATION" error.
Info on the Server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I have the SQL dump if you need that. Please help.
Thanks
Sreejith
HI,
I'm trying to implement a site search. The only problem is that some of the pages have more than 8000 characters (like the press release pages) so I had to use the TEXT datatype (sql server 2000). I wrote a simple stored procedure to query the column, however it does not return any results. I'm assuming this is due to the fact that it is the TEXT datatype. ANY help would be GREATLY appreciated. Code below:
CREATE PROCEDURE sp_SiteSearch
@keyword text
AS
SELECT PageText
FROM tbl_Pages
WHERE PageText LIKE '%@keyword%'
GO
Hello --I'm trying to get SQL Server 2000 on a Windows 2000 Server to be ableto query an Active Directory. We've got two domain servers one Win2000and one Win2003. However, I'm having problems:I've run the following query to setup the linked server:sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces','ADSDSOObject', 'adsdatasource'Using Enterprise Manager I've changed the login details for the ADSIlinked server to:Be made using the following security context:Username: MyDomainAdministratorPassword: ****I've then tried to run the following query in Query Analyzer:SELECT * FROMOpenQuery(ADSI,'<LDAP://CN=Users,DC=MyDomain,DC=com>;(&(objectCategory=Person)(objectClass=User));name,ad spath')But get the following error message:Could not execute query against OLE DB provider 'ADSDSOObject'.Does anyone have any ideas on what this message means or how to getthis working?Thanks--James Allanjames [at] allan-home.co.uk
View 2 Replies View RelatedI've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.
Error:
Msg 7372, Level 16, State 4, Line 1
Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723
Linked Server Script:
/****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true'
I've tried searching the Internet for solution but yielded no results. Can anyone help?
I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.
I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.
I started with a normal select statement selecting each column with a join to the project and then a where clause using
(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...
The rest of the columns(all with OR between them).
The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.
Obviously because I have all the columns in the select statement... So I need something like
Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))
I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.
Sample Table Data
MessageContentType | BodySegment
xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....
Current Query - HardCode Script
SELECT
ID,MsgContentType
BODYSEGMENT,
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan
FROM
s
When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"
Concat MsgContentType Column
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan