Max Query: February 2012

Monday, February 27, 2012

Help with new server registration

I am trying for the first time to learn what to do and how to use SQLServer. I am following instructions in Books on Line to make a New Server Registration. The instructions read as follows:

Connecting to Servers

The toolbar of the Registered Servers component has buttons for the Database Engine, Analysis Services, Reporting Services, SQL Server Mobile, and Integration Services. You can register any of these server types for convenient management. Try this exercise to register the AdventureWorks database.

To register the AdventureWorks database

On the Registered Servers toolbar, click Database Engine if necessary. (It may already be selected.)

Right-click Database Engine, point to New, and then click Server Registration. The New Server Registration dialog box opens.

In the Server name text box, type the name of your SQL Server instance.

In the Registered server name box, type AdventureWorks.

On the Connection Properties tab, in the Connect to database list, select AdventureWorks, and then click Save.

I did steps 1 and 2 no problem. At step 3, for server name I typed MARKSDESKTOP\SQLEXPRESS

At step 4 I typed: Adventureworks

At step 5 I went to Connection Properties and at the "Connect to database drop down box there were 2 choices: <default> or <browse server> (not Adventureworks). If I click on browse server, The browse server for Database window pops up but Adventureworks is not listed there either.

I did a search on my C drive and there are lots of Adventureworks files present so I must have downloaded the database OK.

Does anyone know where I go from here to connect to the Adventureworks database so I can continue with this tutorial?

Please help. Thanks

Mark

Hi,

did you attach the database on the registered server first. if you instaleld the databse via msi, the database is not automatically attached.

1. Register the server first (without any set database, it uses the default then)
2. Right click in the server explorer on "Connect" --> Object Explorer
3. Naviagte on the object explorer to Databases, right click and select Attach..
4. Click add and select the Adventureworks MDF file, click ok and you are done, you should see the adventureworks db now in user databases.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||do you mind telling me what books or website you are using to learn SQL? I am in the process of learning it myself. Thanks in advance.|||

I can't imagine that you know less than me but so far I have been going through the following tutorial:

http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx?notification_id=1721521&message_id=1721521

The amount of good it is doing is questionable. If you have any other suggestions, let me know.

Good luck.

Help with nested Query ?

I have three Tables Student, Courses, Marks

Table : Student
Columns
StudentID <PK>
First Name
Last Name

Table : Grades
Columns
StudentID <FK>
Grade

Table : Courses
Columns
StudentID <FK>
CourseID
CourseDesc

Now to get all the course descriptions which this particular student is taking based on the StudentID we do something like this :

SELECT c.courseDesc
FROM Courses c, Student s
WHERE s.StudentID = '100'
AND s.StudentID = c.StudentID

The above will work

But If I need to do it in nested query how can I do it : Something like

SELECT * FROM

(

SELECT c.courseDesc
FROM Courses c, Student s
AND s.StudentID = c.StudentID

)

WHERE s.StudentID = '100'

Thanks for the help.

Harsimrat

If I do something like this, it should work and its not happy

SELECT * FROM

(

SELECT c.courseDesc, s.StudentID

FROM Courses c, Student s

WHERE s.StudentID = c.StudentID

)

WHERE s.StudentID = '100'

|||

You need to give the derived table an alias to get it to work

Code Snippet

SELECT * FROM

(

SELECT c.courseDesc, s.StudentID

FROM Courses c, Student s

WHERE s.StudentID = c.StudentID

) as items

WHERE s.StudentID = '100'

Though, why the subquery?

Help With Nested Query

I am having trouble with the following query.

Important Tables:
Product (table of products)
--ProductID
--ProductName

ProductCategories (Associates a Product with one or more categories)
--ProductID
--CategoryID

Category (table of categories that a product may fall under)
--CategoryID
--CategoryName

Information:

Basically I have a product that falls into two categories. Therefore there are two records in the ProcuctCategories Table. I am trying to create a query that will find all products that are in categories 1 & 2.

Attempted Solution:
SELECT * FROM Product
WHERE (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =1))
AND
(ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =2))

This returned zero records though it should have returned the product that is in categories 1&2.

I would appreciate any help available.

Thank you,
-PatrickI am trying to create a query that will find all products that are in categories 1 & 2.do a regular many-to-many join, but use GROUP BY on the product, and HAVING to retain only those products which were in more than one category
select ProductName
from Category C
inner
join ProductCategories PC
on C.CategoryID = PC.CategoryID
inner
join Product P
on PC.ProductID = P.ProductID
where C.CategoryID in (1,2)
group
by ProductName
having count(*) > 1|||You are going to kick yourself, but the reason your query failed to return records is because you were trying to compare outer "ProductID"s to inner "CategoryID"s.

...WHERE (ProductID IN (SELECT CategoryID...???

You can rewrite your query more simply like this:

select Product.*
from Product
inner join ProductCategories Cat1 on Product.ProductID = Cat1.ProductID
inner join ProductCategories Cat2 on Product.ProductID = Cat2.ProductID
where Cat1.CategoryID = 1 and Cat2.CategoryID = 2

Use the DISTINCT keywork if the query returns multiple records.

help with nested Query

Hi
I have 2 tables. The first has employee information and the second has
payroll information. I need to find out people who are not in the
payroll but in the employee table.
Since the payroll has multiple instances i have to filter it and find
out for each payroll.
I don't think i have explained it very well so here is the data set.
hope someone can help me with this.
Thanks in advance
prit

Tbl Employee
PlanIDSSN
1001111111111
1001222222222
1001333333333

TblPayrolldetail
IDNumPlanID SSN
11001111111111
11001222222222
21001222222222
21001333333333

Required RESULT required(Missing employees from payroll)
IDNumSSN
1333333333
2111111111I think this could be what you're looking for:

SELECT I.idnum, E.ssn
FROM
(SELECT DISTINCT idnum
FROM PayrollDetail) AS I
CROSS JOIN Employees AS E
LEFT JOIN PayrollDetail AS D
ON I.idnum = D.idnum
AND E.ssn = D.ssn
WHERE D.idnum IS NULL

If you have another table for the entity represented by Idnum then use that
table in place of the derived table "I".

--
David Portas
SQL Server MVP
--

Help with nested inner joins

Hi,
I want to find some people in my SQLServer 2000 database. It's a quite large
database, with approx 200 tables.
Together with the person, I want some information attached to him. However,
this information is in another table that can be reached via some other
tables.
My question is:
How do I most efficiently extract this information? Is inner joins a good
option or is there a better way. If I need information from table1 and table
5, is this a good idea?
SELECT table1.ID, table5.info
FROM table1
INNER JOIN table2 ON table1.xxx = table2.xxx
INNER JOIN table3 ON table2.xxx = table3.xxx
INNER JOIN table4 ON table3.xxx = table4.xxx
INNER JOIN table5 ON table4.xxx = table5.xxx
Thanks,
Mats-LennartWithout seeing DDL, I can only go on assumptions...
I am assuming that the only logical way to connect tabel1 to table5 is via
tables 2, 3, and 4. Based on this, I believe the SQL below is the only way
to get the data you want.
If you post DDL (table creates, primary and foreign keys) for the tables
involved, folks may be able to explain another way to do it, or possibly
changes to your database structure.
"Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
news:e1rQtquNGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to find some people in my SQLServer 2000 database. It's a quite
large
> database, with approx 200 tables.
> Together with the person, I want some information attached to him.
However,
> this information is in another table that can be reached via some other
> tables.
> My question is:
> How do I most efficiently extract this information? Is inner joins a good
> option or is there a better way. If I need information from table1 and
table
> 5, is this a good idea?
> SELECT table1.ID, table5.info
> FROM table1
> INNER JOIN table2 ON table1.xxx = table2.xxx
> INNER JOIN table3 ON table2.xxx = table3.xxx
> INNER JOIN table4 ON table3.xxx = table4.xxx
> INNER JOIN table5 ON table4.xxx = table5.xxx
> Thanks,
> Mats-Lennart
>

Help with Nested Case Statements

Hi,

I am trying to write a query which is something like this :

CASE WHEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END

WHEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END

WHEN CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

END AS Reason_Code_Description

But this is obviously not correct because the WHEN statements do not have a THEN statement - but I dont have anything to do in THEN statement becuase the nested CASE statements take care of everything. Can anybody please help me modify the query so that it works?

Thanks !!

It is not clear what you are attempting to accomplish. Perhaps if you were to present the entire concept, and the entire query, we might be better able to help you.|||

SELECT CASE crp.Title_Code WHEN crp1.Title_Code
THEN ar.Description + '|' + ar1.Description,
WHEN crp2.Title_Code
THEN ar.Description + '|' + ar2.Description,
WHEN crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END
UNION
SELECT CASE crp1.Title_Code WHEN crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description ,
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END
UNION
SELECT CASE crp2.Title_Code WHEN crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

Of course you need to incorporate your JOINS.

Just my twist on it,

Adamus

|||

Agreed. Can you at least make it clear if you are trying to get one value back, or three? Each of the WHEN clauses needs a boolean expression to determin if it is used. So if you want one value, it should be something like

CASE WHEN <boolean condition>
THEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END

WHEN <boolean condition>
THEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END

WHEN <boolean condition>
THEN CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

END AS Reason_Code_Description

|||

I think I need to give the complete description here..This is the whole query - I modified it to get it to working..But my query returns only one record whereas it should return 4 different records if Reason_Code_ID1,Reason_Code_ID2,Reason_Code_ID3,Reason_Code_ID4 have different values for look up field of Title_Code and should concatenate the description for ones that have same Title_Code value.

Select CASE WHEN ar1.Description is NOT NULL AND ar2.Description is not null AND ar3.Description is not null
THEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END
WHEN ar2.Description is not null AND ar3.Description is not null
THEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END
WHEN ar3.Description is not null THEN
CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END
END AS Reason_Code_Description,
crp.CRP_Score_Reason_Code_ID,
crp.Title_Code
From ADF_CRP_Score s (nolock)
LEFT OUTER JOIN CRP_Score_Reason_Code crp
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm (nolock)
JOIN CCR..ADF_Reference ar (nolock)
ON arm.Lookup_ID = ar.Lookup_ID AND ar.Language = 'F'
ON arm.Bureau_Code_ID = '1'
AND arm.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar.Code = crp.CRP_Score_Reason_Code_ID
AND arm.Segment = 'CRP'
ON (crp.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID1)
LEFT OUTER JOIN CRP_Score_Reason_Code crp1
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm1 (nolock)
JOIN CCR..ADF_Reference ar1 (nolock)
ON arm1.Lookup_ID = ar1.Lookup_ID AND ar1.Language = 'F'
ON arm1.Bureau_Code_ID = '1'
AND arm1.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar1.Code = crp1.CRP_Score_Reason_Code_ID
AND arm1.Segment = 'CRP'
ON (crp1.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID2)
LEFT OUTER JOIN CRP_Score_Reason_Code crp2
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm2 (nolock)
JOIN CCR..ADF_Reference ar2 (nolock)
ON arm2.Lookup_ID = ar2.Lookup_ID AND ar2.Language = 'F'
ON arm2.Bureau_Code_ID = '1'
AND arm2.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar2.Code = crp2.CRP_Score_Reason_Code_ID
AND arm2.Segment = 'CRP'
ON (crp2.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID3)
LEFT OUTER JOIN CRP_Score_Reason_Code crp3
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm3 (nolock)
JOIN CCR..ADF_Reference ar3 (nolock)
ON arm3.Lookup_ID = ar3.Lookup_ID AND ar3.Language = 'F'
ON arm3.Bureau_Code_ID = '1'
AND arm3.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar3.Code = crp3.CRP_Score_Reason_Code_ID
AND arm3.Segment = 'CRP'
ON (crp3.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID4)

Please help.

Thanks!

|||

That is quite a SQL statement :) Can you give us some sample DDL and INSERT statements to make this more clear (and more simple) and sample results (expecting 2 rows, not 4 :)

Thanks

|||

There must be a more difficult way to code this. :)

You might be able to filter this query down in the WHERE clause instead of having a multitude of JOINS and SELECT CASE

What exactly is the goal of this query?

Adamus

|||

SELECT rc.Reason_Code_Description FROM
ADF_CRP_Score s (nolock)
LEFT OUTER JOIN CRP_Score_Reason_Code rc ON
(rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID1)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID2)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID3)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID4)

This was the initail query that returnd following results :

CBSSD|Number of purchases in the previous 12 months.||||D|
CBSSD|Total monthly payments.||||E|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

I had to modify the query so that if Title_code (D,E,K,L) in above example are same , like below

CBSSD|Number of purchases in the previous 12 months.||||D|
CBSSD| Age of oldest retail account.||||D|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

then the data segments should be concatenated into one to give this result:

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

What my modified query returns is :

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|

And what I would like to return is :

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|
CBSSD|Total high credit .|Total balance||||K|
CBSSD|Number of sales.||||L|

I know its a bit confusing and complex and that is why I am here ..I am totally confused...:)

Please help.

Thanks!

|||

Although the below example isn't a complete solution to your problem [as I don't have either the table definitions or the patience to unpick your CASE statements :) ], it should help to start you off thinking of solutions along similar lines.

The example requires SQL Server 2005.

Chris

DECLARE @.MyTable TABLE ([Desc] VARCHAR(100) NULL, [Code] CHAR(1))
INSERT INTO @.MyTable
SELECT 'Number of purchases in the previous 12 months.', 'D' UNION ALL
SELECT 'Age of oldest retail account', 'D' UNION ALL
SELECT NULL, 'D' UNION ALL
SELECT NULL, 'D' UNION ALL
SELECT 'Total high credit.', 'K' UNION ALL
SELECT NULL, 'K' UNION ALL
SELECT 'Total balance', 'K' UNION ALL
SELECT NULL,'K' UNION ALL
SELECT 'Number of sales.', 'L' UNION ALL
SELECT NULL, 'L' UNION ALL
SELECT NULL, 'L' UNION ALL
SELECT NULL, 'L'

DECLARE @.MyCodeTable TABLE ([Code] CHAR(1))
INSERT INTO @.MyCodeTable
SELECT 'D' UNION
SELECT 'K' UNION
SELECT 'L'

SELECT 'CBSSD|'
+ REPLACE(
REPLACE(
(SELECT REPLACE(ISNULL(mt.[Desc], '') + '|', ' ', '~') AS [data()]
FROM @.MyTable mt
WHERE mt.[Code] = mct.[Code]
ORDER BY 1 DESC
FOR XML PATH('')), ' ', '')
, '~', ' ')
+ mct.[Code] + '|' AS [String]
FROM @.MyCodeTable mct
/*Output
CBSSD|Number of purchases in the previous 12 months.|Age of oldest retail account|||D|
CBSSD|Total high credit.|Total balance|||K|
CBSSD|Number of sales.||||L|
*/

Help with nearest neighbour problem

Hi,

I need help with creating an sql statement that determines the nearest neighbour in a lookup table to my dataset by its date column. My attempts minimizing the date difference in a cross join are lacking performance.

I have two tables:

Table 1 (Data; 13000 datasets):

ID, date
==========
1, 12.12.2006
2, 28.12.2006
3, 05.01.2007

and Table 2 (Lookup; 4000 datasets):

date, margin
==========
05.12.2006, 2.80
27.12.2006, 2.86
01.01.2007, 3.01
10.01.2007, 2.99

Expected result:

ID, date, margin
==========
1, 12.12.2006, 2.80
2, 28.12.2006, 2.86
3, 05.01.2007, 3.01

Any help is much desired

Kaicould you explain please how to calculate "nearest"|||could you explain please how to calculate "nearest"

For a given date in the data table I'm looking for the closest date in the lookup table, e.g. MIN(Datediff(dd,date(data),date(lookup))).|||that datediff might produce negative numbers, and MIN will take the largest negative number

do you perhaps mean MIN(ABS(...)) ?|||that datediff might produce negative numbers, and MIN will take the largest negative number

do you perhaps mean MIN(ABS(...)) ?

You're right, it's MIN(ABS(...)). But how do I integrate this into a view to do effective lookups?|||effective? i would imagine this to depend on the existence of appropriate indexes

the following works (i tested it on your data) but i dunno how slow it's gonna be for your large tables...with X
( ID
, TDate
, a
, LDate
, margin
)
as (
select T.ID
, T.Date as TDate
, abs(datediff(dd,T.Date,L.Date)) as a
, L.Date as LDate
, L.margin
from table1 as T
cross
join lookup as L
)
select ID
, TDate
, a
, LDate
, margin
from X as D1
where a =
( select min(a)
from X
where ID = D1.ID )|||That's quite some nifty code. Unfortunately I had to rewrite the code to not use the "WITH" statement as MS SQL Server 2000 apparently doesn't support this. Anyway I've came down to 1 min. processing time from 57 min. without touching the indexes, so thanks a lot.

This is my final code:

Select ID
, TDate
, a
, LDate
, margin
from
(
select T.ID
, T.Date as TDate
, abs(datediff(dd,T.Date,L.Date)) as a
, L.Date as LDate
, L.margin
from table1 as T
cross
join lookup as L
) X
where a =
(
select min(abs(datediff(dd,T.Date,L.Date))) as amin
from table1 as T
cross
join lookup as L
where T.ID = X.ID
)

help with myspace

can anyone help me solve this problem.when i open up an artists myspace i cant get any music to play from the standalone player just get an error loading xml document.i have installed the latest flash player from adobe but still have the problem

hello. You may have installed a security update from microsoft that can create conflicts with myspace and other sites with flash content. I had the same problem and uninstalled an update and myspace content was working again. I am running win 2000 pro.

help with myspace

help with my UPDATE query

I'm receiving this error: Incorrect syntax near the keyword 'SET'

CODE:

Code Snippet

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
UPDATE dbo.pnpcart_Customer_Details
SET dbo.pnpcart_Customer_Details.Prefix = @.Prefix,
SET dbo.pnpcart_Customer_Details.FirstName = @.FirstName,
SET dbo.pnpcart_Customer_Details.MiddleName = @.MiddleName,
SET dbo.pnpcart_Customer_Details.LastName = @.LastName,
SET dbo.pnpcart_Customer_Details.Address = @.Address,
SET dbo.pnpcart_Customer_Details.City = @.City,
SET dbo.pnpcart_Customer_Details.State = @.State,
SET dbo.pnpcart_Customer_Details.Zip = @.Zip,
SET dbo.pnpcart_Customer_Details.HomePhone = @.HomePhone,
SET dbo.pnpcart_Customer_Details.CellPhone = @.CellPhone,
SET dbo.pnpcart_Customer_Details.Email = @.Email
END

Thanks for the help in advanced!

-Thanks,
Rich

Use the following query...(are you forget your where clause, the current query will update all the records in the table)

UPDATE dbo.pnpcart_Customer_Details
SET Prefix = @.Prefix,
FirstName = @.FirstName,
MiddleName = @.MiddleName,
LastName = @.LastName,
Address = @.Address,
City = @.City,
State = @.State,
Zip = @.Zip,
HomePhone = @.HomePhone,
CellPhone = @.CellPhone,
Email = @.Email

Where (logical expression)

|||GREAT! That works perfectly for now. I'm going to have to revamp my code to work with a JOIN along with asp.net grid control.

Thanks,
Rich

Help with my SP please

Hey guys, I have a question.
I have a table with 2 decimal fields 10,2.
In my sp i get those fields and divid them by an number.
I get like 5 or 6 0's trailing the calculation.
Ex: 191.6/2 I get 95.800000
Why?
Here is my sp:
CREATE PROCEDURE sp_SummaryReport
(
@.startdate datetime,
@.enddate datetime
)

AS
BEGIN

SELECT tblLegendReportAbv.ReportType AS ReportType,
SUM(tblSummaryData.Volume) AS Volume,
SUM(tblSummaryData.NetEffect)/COUNT(tblSummaryData.DataID) AS NetEffect,
SUM(tblSummaryData.GrossEffect)/COUNT(tblSummaryData.DataID) AS GrossEffect
FROM tblSummaryData
INNER JOIN tblLegendReportAbv ON LTRIM(RTRIM(LOWER(tblSummaryData.ReportType))) = LTRIM(RTRIM(LOWER(tblLegendReportAbv.ReportAbv)))
WHERE tblSummaryData.WeekEndDate BETWEEN @.startdate AND @.enddate
GROUP BY tblSummaryData.ReportType,tblLegendReportAbv.Repor tType

END
GO

Please helpThat's beacause the implicit conversion made by SQL Server when dividing to different types. For more info see "Data Type Precedence" in your SQL help file.

But this only an aesthetic problem, it can be easily solved with an explicit conversion or with a cast like this:

cast(191.6/2 as decimal(10,2))

For more info on cast and convert see "CAST and CONVERT" in your SQL help file.

Best regards!|||Thank you.

help with my select

I hope this is the right place.

I have a proc here, it selects the data needed, but when i put in a sum aggrate the service if sumed up, but it sums all of the records. what i am trying to do is sum up the first service fees that appear.

would you folks be kind and point me in the right direction as how i can acomplish this?

here is my proc code.

DECLARE @.EOBFileName NVARCHAR(50)
SET @.EOBFileName = 'B835255227__CHSEP__2107032414052256619'
SELECT DISTINCT ISNULL(slp.pkServiceLinePayment, 0) AS PaymentKey
, ISNULL(slp.fkClaim, 0) AS ClaimKey
, ISNULL(slp.fkServiceLine, 0) AS ServiceLineKey
, ISNULL(slp.fkInsurance, 0) AS InsuranceKEy
, ISNULL(slp.ServiceDate, 0) AS ServiceDate
, ISNULL(slp.ServiceCode, 0) AS ServiceCode
, ISNULL(slp.ServiceFee, '') AS ServiceFee
, ISNULL(slp.InsurancePayment, '') AS InsurancePayment
, ISNULL(c.fkRenderingServiceProvider, '') AS ProviderKey
, ISNULL(ent.NM103, '') AS ProviderName
, ISNULL(slp.CurrentStatus, '') AS Status
, ISNULL(pat.NM103, '') + ', ' + ISNULL(pat.NM104, '') AS PatientName
, ISNULL(ins.PlanID, '') AS Policy
, ISNULL(ins.GroupPlanID, '') AS GroupID
, ISNULL(slp.PayerClaimTrace, '') AS ClaimTrace
, ISNULL(slpa.GroupCode, '') AS GroupCode
, ISNULL(grp.CodeDescription, '') AS GroupDescription
, ISNULL(slpa.ReasonCode1, '') AS ReasonCode1
, ISNULL(rcode1.CodeDescription, '') AS ReasonDesc1
, ISNULL(slpa.MonetaryAmount1, '') AS Amount1
, ISNULL(slpa.Quantity1, 1) AS Qt1
, ISNULL(slpa.ReasonCode2, '') AS ReasonCode2
, ISNULL(rcode2.CodeDescription, '') AS ReasonDesc2
, ISNULL(slpa.MonetaryAmount2, '') AS Amount2
, ISNULL(slpa.Quantity2, '') AS Qt2
, ISNULL(slpa.ReasonCode3, '') AS ReasonCode3
, ISNULL(rcode3.CodeDescription, '') AS ReasonDesc3
, ISNULL(slpa.MonetaryAmount3, '') AS Amount3
, ISNULL(slpa.Quantity3, '') AS Qt3
, ISNULL(slpa.ReasonCode4, '') AS ReasonCode4
, ISNULL(rcode4.CodeDescription, '') AS ReasonDesc4
, ISNULL(slpa.MonetaryAmount4, '') AS Amount4
, ISNULL(slpa.Quantity4, '') AS Qt4
, ISNULL(slpa.ReasonCode5, '') AS ReasonCode5
, ISNULL(rcode5.CodeDescription, '') AS ReasonDesc5
, ISNULL(slpa.MonetaryAmount5, '') AS Amount5
, ISNULL(slpa.Quantity5, '') AS Qt5
, ISNULL(slpa.ReasonCode6, '') AS ReasonCode6
, ISNULL(rcode6.CodeDescription, '') AS ReasonDesc6
, ISNULL(slpa.MonetaryAmount6, '') AS Amount6
, ISNULL(slpa.Quantity6, '') AS Qt6
, ISNULL(slpr.Qualifier + ' ' + slpr.RemarkCode + ' - ' + rkcode.CodeDescription, '') AS Remark
FROM tbl_ServiceLine_Payments slp
INNER JOIN tbl_Claim_Info c
ON slp.fkClaim = c.pkClaim
INNER JOIN tbl_Entities ent
ON c.fkRenderingServiceProvider = ent.pkEntity
INNER JOIN tbl_Entities pat
ON c.fkPatient = pat.pkEntity
INNER JOIN tbl_Patient_Insurance_Plans ins
ON slp.fkInsurance = ins.pkInsurance
LEFT OUTER JOIN tbl_ServiceLine_Payments_AdjustmentCodes slpa
ON slp.pkServiceLinePayment = slpa.fkServiceLinePayment
LEFT OUTER JOIN tbl_Claim_Adjustment_Group_Codes grp
ON slpa.GroupCode = grp.ClaimAdjustmentGroupCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode1
ON slpa.ReasonCode1 = rcode1.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode2
ON slpa.ReasonCode2 = rcode2.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode3
ON slpa.ReasonCode3 = rcode3.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode4
ON slpa.ReasonCode4 = rcode4.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode5
ON slpa.ReasonCode5 = rcode5.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode6
ON slpa.ReasonCode6 = rcode6.ClaimAdjustmentReasonCode
LEFT OUTER JOIN dbo.tbl_ServiceLine_Payments_RemarkCodes slpr
ON slp.pkServiceLinePayment = slpr.fkServiceLinePayment
LEFT OUTER JOIN dbo.tbl_Claim_Advice_Remark_Codes rkcode
ON slpr.RemarkCode = rkcode.ClaimAdviceRemarkCode
WHERE (slp.EOBFileName LIKE @.EOBFileName)
ORDER BY ClaimKey, PaymentKey

here is the table that is created.

PaymentKey ClaimKey ServiceLineKey InsuranceKEy ServiceDate ServiceCode ServiceFee InsurancePayment ProviderKey ProviderName Status PatientName Policy GroupID ClaimTrace GroupCode GroupDescription ReasonCode1 ReasonDesc1 Amount1 Qt1 ReasonCode2 ReasonDesc2 Amount2 Qt2 ReasonCode3 ReasonDesc3 Amount3 Qt3 ReasonCode4 ReasonDesc4 Amount4 Qt4 ReasonCode5 ReasonDesc5 Amount5 Qt5 ReasonCode6 ReasonDesc6 Amount6 Qt6 Remark
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
1107 52 5589 416 02/12/2007 97124 22.00 17.6 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 4.40 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1108 52 5588 416 02/12/2007 97014 15.00 11.84 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 2.96 0 42 Charges exceed our fee schedule or maximum allowable amount. 0.20 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1109 52 5587 416 02/12/2007 98941 36.00 13.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.20 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1109 52 5587 416 02/12/2007 98941 36.00 13.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 2 Coinsurance Amount. 15.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1110 52 5586 416 02/10/2007 72100 38.00 30.4 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.60 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1111 52 5585 416 02/10/2007 72040 35.00 28 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1112 52 5584 416 02/10/2007 97014 15.00 11.84 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 2.96 0 42 Charges exceed our fee schedule or maximum allowable amount. 0.20 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1113 52 5583 416 02/10/2007 98943 25.00 0 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 185 The rendering provider is not eligible to perform the service billed. 25.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1114 52 5582 416 02/10/2007 99211 22.00 0 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations 97 Payment is included in the allowance for another service/procedure. 22.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0 HE M144 - Pre-/post-operative care payment is included in the allowance for the surgery/procedure.
1115 52 5581 416 02/10/2007 98942 46.00 21.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 9.20 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1115 52 5581 416 02/10/2007 98942 46.00 21.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 2 Coinsurance Amount. 15.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0

(11 row(s) affected)

Hi,

could you post it in a more readable format, please?

help with my select

I hope this is the right place.

would you folks be kind and point me in the right direction as how i can acomplish this?

here is my proc code.

here is the table that is created.

(11 row(s) affected)

Hi,

could you post it in a more readable format, please?

Help with my query

Hi,

I would like to get the data which return employee code associate with the order that is already assigned to them. But on those orders, I also would like to create different column counting how many orders are finished, and how many orders are still in process.

Can anyone help me with the query?

=============================================================

SELECT
e.EmployeeCode,
COUNT(oa.OrderID) as OrderCount
FROM Employee e
INNER JOIN OrderAssignment oa ON oa.EmployeeID = e.EmployeeID
WHERE e.DivisionCode = 'COM'
GROUP BY e.EmployeeCode

==============================================================

Above query will return the number of orders assigned to employee, however, I also need to get how many orders finished and how many orders still in process.

Thanks in advance.

SELECT
e.EmployeeCode,
COUNT(oa.OrderID) as OrderCount,

SUM(CASE WHERE oa.OrderStatus='Finished' THEN 1 ELSE 0 END) AS Finished,

SUM(CASE WHERE oa.OrderStatus='InProcess' THEN 1 ELSE 0 END) AS InProcess
FROM Employee e
INNER JOIN OrderAssignment oa ON oa.EmployeeID = e.EmployeeID
WHERE e.DivisionCode = 'COM'
GROUP BY e.EmployeeCode

Help with my hosting!

I've hosted a website in a freehosting account which supports .net 2.0 and SqlServer 2005Express and they only gave me an ftp access which i can access my website, i found that there's the "App_Data" folder which i placed my database in.
Now here's the message i get when i try to connect to the SQL Express DB
An attempt to attach an auto-named database for file I:\Data\Web\qsh.eu\cipherasp_015f1bdd-1338-4a35-9873-b29232b472c9\www\App_Data\cipherasp.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share
So, what should i do!

This threadhttp://forums.asp.net/thread/903667.aspx is talking about this problem.

And you can take a look at this article:An attempt to attach an auto-named database for file failed.

And also you can google it for more.

Hope it helps.

|||Hey man, Belive it or not, i tried all of these and nothing worksThe problem also that i have no access to the site on the server except the ftp account which all i can do is copy and paste the database in the app_data folder.|||

Does the account used to open db connection has full-control on that remote folder where the database files locates?

|||How do i allow "everyone" to edit, modify delete on my database file before i ftp to the host?!|||I don't mean to allow everyone for that Smile

Then do you have "User Instance=true" in your conection string? If so remove it and try again.

Help with my first report!

Hi Team,

I am trying to create my first report here. My report will show the employee name with corresponding order assigned to them. I already created one datasets which returned the employee name, then I created another one which take the employee name and returned count of the order.

My questions is:

1) is that possible to passing the parameter from the first datasets which contain of customer name?

2) is there any better way of doing this (maybe using one datasets instead of two).

Any respond I will really appreciate.

Anyone please!|||

Make one dataset with query like this:

select empName, count(orderID) as orderCount
from employees
inner join orders on employees.name = orders.employeeName

But better if you use employee ID rather than his name for joining tables.

|||Thank you.

Help with multiple Left Joins

Hi All,
this is my first time posting here as i cannot find the answer myself.
I have couple tables i want to join and i can't seem to get it right. I
have the following tables:
Part: (Part ID), PartDescription
Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
I want a query of the Available qty >0 for every part we have. When i
do a query like this, i get 5363 records.
SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM dbo.PART left outer JOIN
dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_ID
WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost
Then i want to add a column for this query, the inventory_Trans.Qty
that has type =O. I tried the query below and it doesn't
work...obviously ican't inner join again from PART_WAREHOUSE as it
does the left join based on that table, so this wouldn't work:
SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM dbo.PART
left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_IS
left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
dbo.INVENTORY_TRANS.PART_ID
WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
(INVENTORY_TRANS.TYPE='O')
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost
I tried using this, but i am not familiar with this syntax and i am
getting errors.
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1, PART p2
LEFT JOIN
dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN
dbo.INVENTORY_TRANS as i on p2.ID =
dbo.INVENTORY_TRANS.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
p1.unit_material_cost
Order by p1.warehouse_Id
so i am out of ideas. Can anyone enlighten me about how to do this:'
thank you so much in advance.Although this probably isn't the answer that you are looking for, but I'm
wondering why you are using the GROUP BY clause in your query? You typically
use GROUP BY when using an aggregate function in the SELECT statement, such
as COUNT. Try running the second and third queries without the GROUP BY
clause.
Try
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN dbo.INVENTORY_TRANS as i on w.ID = i.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
Order by p1.warehouse_Id
"lytung@.gmail.com" wrote:

> Hi All,
> this is my first time posting here as i cannot find the answer myself.
> I have couple tables i want to join and i can't seem to get it right. I
> have the following tables:
> Part: (Part ID), PartDescription
> Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
> Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
> I want a query of the Available qty >0 for every part we have. When i
> do a query like this, i get 5363 records.
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART left outer JOIN
> dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
>
> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work...obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
>
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART
> left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_IS
> left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
> dbo.INVENTORY_TRANS.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
> (INVENTORY_TRANS.TYPE='O')
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
> I tried using this, but i am not familiar with this syntax and i am
> getting errors.
> SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
> w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
> FROM PART p1, PART p2
> LEFT JOIN
> dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
> LEFT JOIN
> dbo.INVENTORY_TRANS as i on p2.ID =
> dbo.INVENTORY_TRANS.PART_ID
> WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
> group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
> p1.unit_material_cost
> Order by p1.warehouse_Id
>
> so i am out of ideas. Can anyone enlighten me about how to do this:'
> thank you so much in advance.
>|||no that gave me an error.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'warehouse_Id'.
But even if that wo rked the logic doens't make sense.
I want the second query to be based on the first query. Maybe its not
about doing two joins but what i probably need is a transaction query.
First i need this:
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
where ( w.AVAILABLE_QTY > 0)
Then i need the i.type='O' (from inventory_trans) based on those
results. I hope this make sense!|||Hi
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART left outer JOIN
> dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
is it possible that part with given ID doesn't belong to a part_warehouse?
in other words can you get null as warehouse_id in above query?
the second thing - group by clause here is really not necessary

> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work...obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
how about this?
SELECT pw.WAREHOUSE_ID, p.ID, p.DESCRIPTION, p.UNIT_MATERIAL_COST,
pw.AVAILABLE_QTY
FROM dbo.PART p left outer JOIN
( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID
) ON p.ID = pw.PART_ID
WHERE (pw.AVAILABLE_QTY > 0)
AND (itr.TYPE='O')
HTH
Peter|||Hi Peter,
thanks for replying. The query you gave me ended up with too many
records. You are right, i dont need the group by statement.
Part_Warehouse has 2 Primary Keys: Part_ID, and WAREHOUSE_ID
you skipped out the PART_WAREHOUSE join to PART. I guess for this join
it doesn't have to be a left join, but it has to be joined. The second
join has to be left, which you did...
I am getting

with mixing the joins. What is the general rule
of multiple joins? does the second join depend on the previous join? or
can they be independent?|||use parentheses to prioritize joins. the outer table is joined to result of
join in parentheses.
can you show the ddl of these tables and some sample data and describe
result you would like to obtain?
part_warehouse is a table that relates parts and warehouses?
> you skipped out the PART_WAREHOUSE join to PART. I guess for this join
> it doesn't have to be a left join, but it has to be joined. The second
> join has to be left, which you did...
FROM dbo.PART p left outer JOIN
( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID
) ON p.ID = pw.PART_ID
no, I left joined PART to the result of inner join between PART_WAREHOUSE
and INVENTORY_TRANS.
again, do you have PARTs without WAREHOUSEs?
peter

Help with multiple jobs failing

Hello,
For some reason a couple of our jobs have been failing lately. We get
the following msg on the details of the job. Any ideas? Thanks in advance.
Msg:
Unable to connect to SQL Server 'COMPUTERNAME\INSTANCENAME'. The step
failed.
1. Is this the same server where the jobs are running?
2. Have you checked your SQL Agent logs for any more information on these
error messages?
3. Can you connect to this named isntance using Query analyser, SEM, etc?
4. Was this sql server "moved" from another box, or any such thing?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Vikram,
1. Yes
2. See below output
3. Yes
4. We did upgrade the sql box a couple of months ago. We created scripts
and ran them against the new instance. The seem to be ran fine from Query
analyser. It's not the same job that fails everytime either it seems to be
random.
jake
2004-06-16 08:19:24 - ? [393] Waiting for SQL Server to recover databases...
2004-06-16 08:21:22 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2004-06-16 08:21:23 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1596
2004-06-16 08:21:23 - ? [101] SQL Server computername\DBSERVER version
8.00.760 (0 connection limit)
2004-06-16 08:21:23 - ? [102] SQL Server ODBC driver version 3.85.1025
2004-06-16 08:21:23 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is computername\DBSERVER
2004-06-16 08:21:23 - ? [310] 1 processor(s) and 992 MB RAM detected
2004-06-16 08:21:23 - ? [339] Local computer is computername running Windows
NT 5.2 (3790)
2004-06-16 08:21:23 - ! [364] The Messenger service has not been started -
NetSend notifications will not be sent
2004-06-16 08:21:23 - ? [129] SQLAgent$DBSERVER starting under Windows NT
service control
2004-06-16 08:21:23 - ? [392] Using MAPI32.DLL from C:\WINDOWS\SYSTEM32
(version 1.0.2536.0)
2004-06-16 08:21:23 - ? [196] Attempting to start mail session using profile
'Outlook'...
2004-06-16 08:21:25 - ? [353] Mail session started (using MAPI1)
2004-06-16 08:21:25 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2004-06-17 05:19:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-17 05:19:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-18 02:04:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:14 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-19 05:13:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-20 05:20:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-21 05:16:45 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 05:16:45 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-21 05:17:53 - ! [298] SQLServer Error: 17, SQL Server does not exist
or access denied. [SQLSTATE 08001]
2004-06-21 05:17:53 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-06-21 08:04:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 08:04:08 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-22 02:18:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:18:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-22 07:01:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:10 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-23 02:19:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 07:30:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:15:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:15:41 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:16:53 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:16:53 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:08:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:08:23 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:09:27 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:09:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:18:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:27:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-28 02:03:04 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 05:16:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 08:50:06 - ? [131] SQLAgent$DBSERVER service stopping due to a
stop request from a user, process, or the OS...
2004-06-28 08:50:07 - ? [358] Mail session ended
2004-06-28 08:50:09 - ? [098] SQLServerAgent terminated (normally)
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:N8GBxNPXEHA.328@.cpmsftngxa10.phx.gbl...
> 1. Is this the same server where the jobs are running?
> 2. Have you checked your SQL Agent logs for any more information on these
> error messages?
> 3. Can you connect to this named isntance using Query analyser, SEM, etc?
> 4. Was this sql server "moved" from another box, or any such thing?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
|||Vikram,
1. Yes
2. See below output
3. Yes
4. We did upgrade the sql box a couple of months ago. We created scripts
and ran them against the new instance. The seem to be ran fine from Query
analyser. It's not the same job that fails everytime either it seems to be
random.
jake
2004-06-16 08:19:24 - ? [393] Waiting for SQL Server to recover databases...
2004-06-16 08:21:22 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2004-06-16 08:21:23 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1596
2004-06-16 08:21:23 - ? [101] SQL Server computername\DBSERVER version
8.00.760 (0 connection limit)
2004-06-16 08:21:23 - ? [102] SQL Server ODBC driver version 3.85.1025
2004-06-16 08:21:23 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is computername\DBSERVER
2004-06-16 08:21:23 - ? [310] 1 processor(s) and 992 MB RAM detected
2004-06-16 08:21:23 - ? [339] Local computer is computername running Windows
NT 5.2 (3790)
2004-06-16 08:21:23 - ! [364] The Messenger service has not been started -
NetSend notifications will not be sent
2004-06-16 08:21:23 - ? [129] SQLAgent$DBSERVER starting under Windows NT
service control
2004-06-16 08:21:23 - ? [392] Using MAPI32.DLL from C:\WINDOWS\SYSTEM32
(version 1.0.2536.0)
2004-06-16 08:21:23 - ? [196] Attempting to start mail session using profile
'Outlook'...
2004-06-16 08:21:25 - ? [353] Mail session started (using MAPI1)
2004-06-16 08:21:25 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2004-06-17 05:19:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-17 05:19:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-18 02:04:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:14 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-18 05:21:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-19 05:13:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-20 05:15:57 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-20 05:20:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 02:14:18 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-21 05:16:45 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 05:16:45 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-21 05:17:53 - ! [298] SQLServer Error: 17, SQL Server does not exist
or access denied. [SQLSTATE 08001]
2004-06-21 05:17:53 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-06-21 08:04:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-21 08:04:08 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-22 02:18:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:18:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-22 05:20:39 - ! [382] Logon to server 'computername\DBSERVER' failed
(SaveAllSchedules)
2004-06-22 07:01:32 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:10 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 02:17:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-23 02:19:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 07:30:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-23 08:19:30 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:15:41 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:15:41 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-24 05:16:53 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-24 05:16:53 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:08:23 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:08:23 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:09:27 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-25 02:09:32 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-25 02:18:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:27:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-26 05:29:40 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-06-28 02:03:04 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 05:16:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-06-28 08:50:06 - ? [131] SQLAgent$DBSERVER service stopping due to a
stop request from a user, process, or the OS...
2004-06-28 08:50:07 - ? [358] Mail session ended
2004-06-28 08:50:09 - ? [098] SQLServerAgent terminated (normally)
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:N8GBxNPXEHA.328@.cpmsftngxa10.phx.gbl...
> 1. Is this the same server where the jobs are running?
> 2. Have you checked your SQL Agent logs for any more information on these
> error messages?
> 3. Can you connect to this named isntance using Query analyser, SEM, etc?
> 4. Was this sql server "moved" from another box, or any such thing?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Help with multiple jobs failing

Help with Multiple inserts

HI,
How do I rewrite the first query using the datafrom query 2 so that I
can do multipe inserts for all the units given this input:
@.EnrolmentID,
@.dteEnroled,
@.Outcome,
@.CourseID
-- 1. This creates one unit enrolment using parameters
INSERT INTO tblUnitEnrolment (EnrolmentID,EnrolDate,Outcome,
QualUnitID) VALUES (@.EnrolmentID,@.dteEnroled,@.Outcome, @.QualUnitID)
-- 2. This gets a list of Unit ID's for a given Course
SELECT QualUnitID FROM QualUnits WHERE QualID=(SELECT QualID FROM
COURSES WHERE CourseID=@.intCourse)Hi
I'm not sure understand your question
INSERT INTO tblUnitEnrolment (EnrolmentID,EnrolDate,Outcome,
QualUnitID) SELECT @.EnrolmentID,@.dteEnroled,@.Outcome, QualUnitID FROM
QualUnits WHERE QualID=(SELECT QualID FROM
COURSES WHERE CourseID=@.intCourse)
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1130322728.990452.313140@.g43g2000cwa.googlegroups.com...
> HI,
> How do I rewrite the first query using the datafrom query 2 so that I
> can do multipe inserts for all the units given this input:
> @.EnrolmentID,
> @.dteEnroled,
> @.Outcome,
> @.CourseID
>
> -- 1. This creates one unit enrolment using parameters
> INSERT INTO tblUnitEnrolment (EnrolmentID,EnrolDate,Outcome,
> QualUnitID) VALUES (@.EnrolmentID,@.dteEnroled,@.Outcome, @.QualUnitID)
> -- 2. This gets a list of Unit ID's for a given Course
> SELECT QualUnitID FROM QualUnits WHERE QualID=(SELECT QualID FROM
> COURSES WHERE CourseID=@.intCourse)
>|||Thanks - that does the trick

Help with multiple IIFs, or need suggestion of better solution.

I am trying to check multiple fields from a db to see if they have either a 1 or 0 value, and if there is a 1, then write a value into a text box. I need to check multiple fields, and if all of them are checked then I have to insert the value for each into the text box. If it was just checking one condition it woudl be easy, because I could just nest IIF's until it was true.

So I can't do because once the truth clause is satisfied it will exit the loop: IIF(Fields!Fielda.Value = 1,"Fielda",IIF(Fields!Fieldb.Value=1,"Fieldb"....)

I also cannot do:
=IIfFields!Fielda.Value=1,"Fielda,"")
=IifFields!Fieldb.Value=1,"Fieldb,"")

Is there a way to have a whole bunch of IIF's, or can anyone think of another way to do this?

Much appreciated.

Use the "And" operator. It would look like this:

iif (Fields!Fielda.Value = 1 and Fields!Fieldb.Value=1 and Fields!Fieldc.Value=1, "Fielda", "")

|||Ryan, I appreciate the answer, but I think you misunderstood. I want it to say if Fielda = 1 then insert text, and if Fieldb = 1 then insert text, not if all of them = 1.

This would be the ideal situation:

=IIF(Fields!Fielda.Value=1,"Fielda","")
IIF(Fields!Fieldb.Value=1,"Fieldb","")
IIF(Fields!Fieldc.Value=1,"Fieldc","")
And so on for all the fields for this particular text box.

Or another example (that I've tried that did not work)
=IIF(Fields!Fielda.Value=1,"Fielda","") &
IIF(Fields!Fieldb.Value=1,"Fieldb","") &
IIF(Fields!Fieldc.Value=1,"Fieldc","") &

I can't use what you said because that would only evaluate one statement, and I need to evaluate 8 different statements. That's the problem. Is there a way to have mutliple seperate IIF's in an expression like I have above? If not, is there another solution?
|||

One question is what is the datatype on the database field? If it is boolean then you should be able to do:

=IIF(Fields!Fielda.Value,"Fielda","") + IIF(Fields!Fieldb.Value,"Fieldb","")...

The + should work for concatenation since all of the fields area string. Another thing that I have seen is that you may have to do CDec on the database fields to force a datatype match.

=IIF(CDec(Fields!Fielda.Value)=1,"Fielda","") + IIF(CDec(Fields!Fieldb.Value)=1,"Fieldb","") ...

|||

Use the Report Properties.Code.Custom Code feature.

1.Create a function in the CODE section

2. Pass all your field values to the function

3. The return value is used in the textbox.

You have a lot more coding power in the CODE section than you do with expressions.

Hope this helps.

|||

Can you do it in SQL using case statement ?

|||

=switch(Fields!FieldA.Value = 1, "A", Fields!FieldB.Value = 1, "B", true, "")

Thanks, Donovan.

Help with multiple counts..

I have a application table that I have turned into a cube. It includes attributes of a organizaiton name, month, and year. This all works fine.

I would like to add another table to the cube that counts a visits. It also has attributes of organization name, month and year.

I am confused where I make the associations so they can operate in the same cube.

Do I join the tables in the datasource and just add the measure in the cube? Do I add a new dimention in the cube?

Any help in this area would be great. I am basically looking for a cube like so..

Month Year

Organization Name ApplicationCount (From the application table)

Visit Count (From the visit table)

Thanks in advance,

Mardo

If it has a similar structure, just a different measure, then you could add this table as a separate measure group with a measure in it that maps to the Visit Count. If these two table are completely identical you might be able to join them with a view or a named query in the DSV so that Visit Count is just added as an extra column to the existing cube, this might be a more efficient approach.

Help with Multiple connections in a CLR stored procedure

Here's what I'm trying to accomplish:

1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.

The problem I have is that I can't open the second connection. Here's some sample code:

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {

//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {

SqlConnection conn2 = new SqlConnection();
conn2.ConnectionString = "Server=localhost;Database=TestDb;Integrated Security=SSPI";
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "up_TestProc";
int serviceFilterId = Convert.ToInt32(reader["accountId"]);
SqlParameter parm = new SqlParameter("@.accountId", serviceFilterId);
cmd2.Parameters.Add(parm);
conn2.Open();
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}

reader.Close();
conn.Close();

}

};

When I attempt to execute the procedure, I get the following:

Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.up_TestClr(String& dbName)

. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:

USE master

CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'

CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN

GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin

Resulted in:

Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'SN', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'TestLogin', because it does not exist or you do not have permission.
ANY IDEAS/SUGGESTIONS?

<AKS@.discussions.microsoft.com> wrote in message news:68f11211-89fe-4a3f-972c-512cddfc8ec4@.discussions.microsoft.com... 1. Open a connection and retrieve a datareader, using the context connection.2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection. Unfortunately, you can only have a single context connection open at once. Have you considered using a DataSet instead of the DataReader? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||Ugh. I didn't realize that I could use a DataSet inside a CLR-based SP. I guess somehow, all the examples I've seen used the SqlDataReader.

That should work fine. Thx.|||

Not sure how using a DataSet helps, but...

He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.

This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.

Are you saying this is not allowed?

My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:

<code>

public static SqlConnection NewConnection()

{

SqlConnection connection2 = new SqlConnection("context connection=true");

connection2.Open();

SqlCommand command = new SqlCommand("select @.@.servername, db_name()", connection2);

SqlDataReader sdr = command.ExecuteReader();

sdr.Read();

string serverName = sdr.GetString(0);

string dbName = sdr.GetString(1);

return new SqlConnection("Server=" + serverName + ";Database=" + dbName + ";Trusted_Connection=yes;Enlist=false");

}

</code>

|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?

Help with Multiple connections in a CLR stored procedure

Here's what I'm trying to accomplish:

1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.

The problem I have is that I can't open the second connection. Here's some sample code:

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {

//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {

reader.Close();
conn.Close();

}

};

When I attempt to execute the procedure, I get the following:

Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.up_TestClr(String& dbName)

. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:

USE master

CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'

CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN

GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin

Resulted in:

That should work fine. Thx.|||

Not sure how using a DataSet helps, but...

He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.

This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.

Are you saying this is not allowed?

My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:

<code>

</code>

|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?

Monday, February 27, 2012

Help with new server registration

Help with nested Query ?

Help With Nested Query

help with nested Query

Help with nested inner joins

Help with Nested Case Statements

Help with nearest neighbour problem

help with myspace

help with myspace

help with my UPDATE query

Help with my SP please

help with my select

help with my select

Help with my query

Help with my hosting!

Help with my first report!

Help with multiple Left Joins

Help with multiple jobs failing

Help with multiple jobs failing

Help with multiple jobs failing

Help with Multiple inserts

Help with multiple IIFs, or need suggestion of better solution.

Help with multiple counts..

Help with Multiple connections in a CLR stored procedure

Help with Multiple connections in a CLR stored procedure

Max Query

Blog Archive

About Me