No Cursor

How would I take the total of the failures column below so I can do %'s of
the total. I can do it with an ADO recordset from VB, but I'm trying to do
it in TSQL 1st.
Thanks
Paul
DECLARE @.BeginDate DateTime
DECLARE @.EndDate DateTime
Set @.BeginDate = '2005-05-12'
Set @.EndDate = '2005-05-16'
Select Top 5
Dept,
SubString(Category + '-' + SubCategory,0, 50) as 'Failure',
Sum(NumFailures) as 'Failures'
From DBO.FTC_TESTRESULTS
Where Dept = '6600'
AND TestDate Between @.BeginDate AND @.EndDate
GROUP BY SubString(Category + '-' + SubCategory,0, 50),Dept
ORDER BY Dept, Sum(NumFailures) DESC
Dept Failure
Failures PCT
-- ---- --
6600 Misc-No Speed Sensor Signal 28
28/70
6600 Seam Leaks-Rear Retainer to Rear Case 21
21/70
6600 Noise-Neutral
10 10/70
6600 Misc-Stud/Bolt Missing 6
ETC
6600 Noise-All Modes
5
Total
70SELECT TOP 5
dept,
SUBSTRING(category + '-' + subcategory,0, 50) AS 'Failure',
SUM(numfailures) AS 'Failures',
SUM(numfailures) /
(SELECT CAST(SUM(numfailures) AS REAL)
FROM dbo.FTC_TESTRESULTS)
FROM dbo.FTC_TESTRESULTS
WHERE dept = '6600'
AND testdate BETWEEN @.begindate AND @.enddate
GROUP BY SUBSTRING(category + '-' + subcategory,0, 50),dept
ORDER BY dept, SUM(numfailures) DESC
David Portas
SQL Server MVP
--|||Dave,
Will I need to further narrow the divisor... I want to divide by 70...
the total of the top 5, it is currently dividing by the entire population .
Bottom of post is the original code that returns top 5 with no divisor.
dept Failure
Failures
-- ---- -- --
--
6600 Misc-No Speed Sensor Signal 28
8.5995086E-3
6600 Seam Leaks-Rear Retainer to Rear Case 21
6.4496314E-3
6600 Noise-Neutral 10
3.071253E-3
6600 Misc-Stud/Bolt Missing 6
1.8427519E-3
6600 Noise-All Modes 5
1.5356265E-3

> SELECT TOP 5
> dept,
> SUBSTRING(category + '-' + subcategory,0, 50) AS 'Failure',
> SUM(numfailures) AS 'Failures',
> SUM(numfailures) /
> (SELECT CAST(SUM(numfailures) AS REAL)
> FROM dbo.FTC_TESTRESULTS)
> FROM dbo.FTC_TESTRESULTS
> WHERE dept = '6600'
> AND testdate BETWEEN @.begindate AND @.enddate
> GROUP BY SUBSTRING(category + '-' + subcategory,0, 50),dept
> ORDER BY dept, SUM(numfailures) DESC
> --
> David Portas
> SQL Server MVP
> --
>
Select Top 5
Dept,
SubString(Category + '-' + SubCategory,0, 50) as 'Failure',
Sum(NumFailures) as 'Failures'
From DBO.FTC_TESTRESULTS
Where Dept = '6600'
AND TestDate Between @.BeginDate AND @.EndDate
GROUP BY SubString(Category + '-' + SubCategory,0, 50),
Dept
ORDER BY Dept, Sum(NumFailures) DESC|||On Wed, 25 May 2005 20:25:51 -0400, Paul Ilacqua wrote:

>Dave,
> Will I need to further narrow the divisor... I want to divide by 70...
>the total of the top 5, it is currently dividing by the entire population .
>Bottom of post is the original code that returns top 5 with no divisor.
Hi Paul,
Try this one. I couldn't test it, since you didn't provide CREATE TABLE
and INSERT statements (see www.aspfaq.com/5006).
SELECT TOP 5 Dept,
SUBSTRING(Category + '-' + SubCategory, 0, 50) AS Failure,
SUM(NumFailures) AS Failures,
SUM(NumFailures) /
(SELECT SUM(Failures)
FROM (SELECT TOP 5 SUM(NumFailures) AS Failures
FROM dbo.FTC_TestResults
WHERE Dept = '6600'
AND TestDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY SUBSTRING(Category + '-' + SubCategory, 0, 50)
ORDER BY SUM(NumFailures) DESC) AS d) AS Pct
FROM dbo.FTC_TestResults
WHERE Dept = '6600'
AND TestDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY SUBSTRING(Category + '-' + SubCategory,0, 50)
ORDER BY SUM(NumFailures) DESC
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Works perfectly... and I spent the last couple of days figuring out how
it works. Now I think I got it. Thanks again.
Paul
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9mse911404i9c32u9cak1o4e40gl16t6b7@.
4ax.com...
> On Wed, 25 May 2005 20:25:51 -0400, Paul Ilacqua wrote:
>
> Hi Paul,
> Try this one. I couldn't test it, since you didn't provide CREATE TABLE
> and INSERT statements (see www.aspfaq.com/5006).
> SELECT TOP 5 Dept,
> SUBSTRING(Category + '-' + SubCategory, 0, 50) AS Failure,
> SUM(NumFailures) AS Failures,
> SUM(NumFailures) /
> (SELECT SUM(Failures)
> FROM (SELECT TOP 5 SUM(NumFailures) AS Failures
> FROM dbo.FTC_TestResults
> WHERE Dept = '6600'
> AND TestDate BETWEEN @.BeginDate AND @.EndDate
> GROUP BY SUBSTRING(Category + '-' + SubCategory, 0, 50)
> ORDER BY SUM(NumFailures) DESC) AS d) AS Pct
> FROM dbo.FTC_TestResults
> WHERE Dept = '6600'
> AND TestDate BETWEEN @.BeginDate AND @.EndDate
> GROUP BY SUBSTRING(Category + '-' + SubCategory,0, 50)
> ORDER BY SUM(NumFailures) DESC
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

No connection to local SQL Server when no network connection

Hi !
We are using Windows XP,
ADO.NET in our application (programming language: C#),
MS SQL Server 2000.
The computer is a SONY notebook.
SQL server is running on the same machine as the application.
The application has worked in many, many cases under W2K and even XP.
But on that computer it doesn't work:
When there is no network connection,
then the application CANNOT connect to our DB.
On the other hand, Enterprise Manager CAN connect.
When we plug in the network and restart the computer,
then the application CAN connect.
However, SQL Server is running locally !
The connection string for connecting our app to the database is something
like:
sqlConnection.ConnectionString =
"packet size=4096;user id=BIS;persist security info=false;initial
catalog=BIS; server=(local)";
How can we fix that problem ?
Your kind help would be appreciated.
Peter
I have had similar issues - am not sure why though.
However,I seem to be able to connect by either using the (local) or the
actual PC name - it just seems to change its behaviour depending on the
network connectivity status.
Hope this is of some help.
LeMuppet
lemuppet
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message411117.html

No connection to local SQL Server when no network connection

Hi !
We are using Windows XP,
ADO in our application,
MS SQL Server 2000.
(Programming language: Delphi 5;
ADO components: from Adonis;
The computer is a SONY notebook).
SQL server is running on the same machine as the application.
The application has worked in many, many cases under W2K and even XP.
But on that computer it doesn't work:
When there is no network connection, then the application CANNOT connect to
our DB.
On the other hand, Enterprise Manager CAN connect.
When we plug in the network and restart the computer, then the application
CAN connect.
However, SQL Server is running locally !
The connection string for connecting our app to the database is something
like:
'Provider=SQLOLEDB.1; Password="";Persist Security Info=True;User
ID=Bis;Initial Catalog=BIS;Data Source=127.0.0.1'
How can we fix that problem ?
Is this a problem of MS SQL Server administration ?
Your kind help would be appreciated.
PeterThe soluition is to use named pipes to connect as
Enterprise manager will if IP: 127.0.0.1 cannot connect.
Change your connection string to :
'Provider=SQLOLEDB.1; Password="";Persist Security
Info=True;User
ID=Bis;Initial Catalog=BIS;Data Source=(local)'
This should fix the problem.
>--Original Message--
>Hi !
>We are using Windows XP,
>ADO in our application,
>MS SQL Server 2000.
>(Programming language: Delphi 5;
> ADO components: from Adonis;
> The computer is a SONY notebook).
>SQL server is running on the same machine as the
application.
>The application has worked in many, many cases under W2K
and even XP.
>But on that computer it doesn't work:
>When there is no network connection, then the application
CANNOT connect to
>our DB.
>On the other hand, Enterprise Manager CAN connect.
>When we plug in the network and restart the computer,
then the application
>CAN connect.
>However, SQL Server is running locally !
>The connection string for connecting our app to the
database is something
>like:
> 'Provider=SQLOLEDB.1; Password="";Persist Security
Info=True;User
>ID=Bis;Initial Catalog=BIS;Data Source=127.0.0.1'
>How can we fix that problem ?
>Is this a problem of MS SQL Server administration ?
>Your kind help would be appreciated.
>Peter
>
>.
>|||> Change your connection string to :
> 'Provider=SQLOLEDB.1; Password="";Persist Security
> Info=True;User
> ID=Bis;Initial Catalog=BIS;Data Source=(local)'
It worked.
Thanks a lot !

No columns defined in rowset

Hello Experts,
I have a stored procedure. I used ADO to called this stored procedure. In
runtime, it says "No columns defined in rowset".
Here is my stored procedure:
========================================
========
CREATE PROCEDURE [sp_MigrateAccount]
@.i_vchSessionID VARCHAR(50),
@.o_intRetVal int OUTPUT
AS
-- Variables for error handling.
DECLARE @.intError int
DECLARE @.insAcct_error int
DECLARE @.insAcctSum_error int
DECLARE @.insAddr_error int
DECLARE @.drop_error int
DECLARE @.intRecordErrorCounter int
-- Temporary storage
DECLARE @.chSourceAccountID char(12)
DECLARE @.vchSourceAccount char(128)
DECLARE @.vchSourceMainPhone char(32)
DECLARE @.vchSourceFax char(32)
DECLARE @.chSourceUserfield5 char(80)
DECLARE @.m_cintMigrateSucceed int
DECLARE @.m_cintRecordDataError int
DECLARE @.m_cintInsertToRealTableError int
DECLARE @.m_cintDuplicateKeyError int
SET NOCOUNT ON
-- Assign value to constants
SET @.m_cintMigrateSucceed = 0
SET @.m_cintRecordDataError = 2
SET @.m_cintInsertToRealTableError = 3
SET @.m_cintDuplicateKeyError = 7
-- Perform data validation --
-- Check for duplicate ACCOUNTID
SET @.intRecordErrorCounter = 0
SELECT @.intRecordErrorCounter = COUNT (a1.ACCOUNTID)
FROM sysdba.ACCOUNT AS a1
WHERE a1.ACCOUNTID IN
(SELECT ACCOUNTID
FROM ACCOUNT_TEMP
WHERE SessionID = @.i_vchSessionID)
IF (@.intRecordErrorCounter > 0) -- There exists at least one validation
error.
BEGIN
-- Update ErrorColumn in the source table. --
-- 1 is an arbitrary value for the ErrorColumn.
-- If ErrorColumn is NULL or 0, this record has no error.
UPDATE sysdba.ACCOUNT_TEMP
SET ErrorColumn = @.m_cintDuplicateKeyError
WHERE (SessionID = @.i_vchSessionID)
AND ACCOUNTID IN
(SELECT ACCOUNTID
FROM ACCOUNT)
-- No need to update the destination table, thus we quit the SP
-- Note that the error number used by SQL Server is greater than 0.
SET @.intError = @.m_cintDuplicateKeyError
GOTO Quit_SP
END
-- Up to this point, the data validation is passed.
-- We can insert record into the destination table. now.
BEGIN TRAN dataMigration
-- Insert records to the ACCOUNT table
-- People from newsgroup said that you are required to
-- enter uppercase Account Name into ACCOUNT_UC field.
INSERT INTO ACCOUNT
(ACCOUNTID, ACCOUNT, MAINPHONE, FAX, [DESCRIPTION], SECCODEID,
ADDRESSID, SHIPPINGID, ACCOUNTMANAGERID, CREATEUSER, MODIFYUSER,
CREATEDATE,
MODIFYDATE, ACCOUNT_UC)
SELECT ACCOUNTID, ACCOUNT, MAINPHONE, FAX, [DESCRIPTION], SECCODEID,
ADDRESSID, SHIPPINGID, ACCOUNTMANAGERID, CREATEUSER, MODIFYUSER,
CREATEDATE,
MODIFYDATE, ACCOUNT_UC
FROM sysdba.ACCOUNT_TEMP
WHERE SessionID = @.i_vchSessionID
SET @.insAcct_error = @.@.ERROR
IF (0 = @.insAcct_error) BEGIN
COMMIT TRAN dataMigration
END
ELSE
BEGIN
IF (0 <> @.insAcct_error)
-- Const m_cintInsertToRealTableError = 3
SET @.intError = @.m_cintInsertToRealTableError
ROLLBACK TRAN dataMigration
END
Error_Handler:
Quit_SP:
IF (@.intError IS NULL)
-- No value assigned to @.intError in the
-- body of this stored procedure.
-- Thus, migrate data is successful.
-- Const m_cintMigrateSucceed = 0
SET @.o_intRetVal = @.m_cintMigrateSucceed
ELSE
SET @.o_intRetVal = @.intError -- Failure encountered.
RETURN
GO
========================================
=
Here is my VBScript that used ADO to call the store procedure:
========================================
===
' :
' ADO database connection....
' :
With ADOCmd
.CommandText = "sp_MigrateAccount"
.CommandType = adCmdStoredProc
strInputText = i_strSessionID
lngParamLen = Len(strInputText)
If lngParamLen <= 0 Then
lngParamLen = 1
End If
Set ADOParam = .CreateParameter("@.i_vchType", adVarChar,
adParamInput, lngParamLen, strInputText)
.Parameters.Append ADOParam
Set ADOParam = .CreateParameter("@.o_intRetVal", adInteger,
adParamOutput)
.Parameters.Append ADOParam
Set .ActiveConnection = objCon
.Execute
lngRetVal = .Parameters("@.o_intRetVal").Value
End With
' :
' :
' :
'=======================================
==
The ".Execute" statement caused the "No columns defined in rowset".
If I try the following statement:
.Execute , , (adCmdStoredProc Or adExecuteNoRecords)
Or,
.Execute , , adExecuteNoRecords
I cannot get the output value (@.o_intRetVal) from the stored procedure.
May I know how to solve this problem? Thanks a lot.
SammySa wrote:
> Hello Experts,
> I have a stored procedure. I used ADO to called this stored
> procedure. In runtime, it says "No columns defined in rowset".
That does not sound like an ADO error. It sounds more like a T-SQL error.
ADO errors usually refer to recordsets, fields and records. "rowset" makes
it sound
like a T-SQL error.
Have you verified that this procedure runs correctly in Query Analyzer? Like
this:
declare @.ret int
exec sp_MigrateAccount 'some session id',@.ret output
select @.ret [Output Value]

> Here is my stored procedure:
> ========================================
========
> CREATE PROCEDURE [sp_MigrateAccount]
Nothing to do with your error, but using "sp_" for a non-system stored
procedure can cause a slight performance dip, since the query engine treats
all procedures beginning with this prefix as system procedures, looking for
them first in Master, and only looking for them in the current database when
it fails to find them in Master. If you mistakenly name your procedure the
same as a real system procedure, you've got a knotty debugging problem ahead
of you ...

> @.i_vchSessionID VARCHAR(50),
> @.o_intRetVal int OUTPUT
> AS
> -- Variables for error handling.
> DECLARE @.intError int
<snip>
> SET NOCOUNT ON
I doubt this is the problem, but I generally make this the first statement
in my procedures - before any other statement, even DECLARE's.

>
<snip>
> ========================================
=
>
> Here is my VBScript that used ADO to call the store procedure:
> ========================================
===
> ' :
> ' ADO database connection....
> ' :
> With ADOCmd
> .CommandText = "sp_MigrateAccount"
> .CommandType = adCmdStoredProc
> strInputText = i_strSessionID
> lngParamLen = Len(strInputText)
> If lngParamLen <= 0 Then
> lngParamLen = 1
> End If
> Set ADOParam = .CreateParameter("@.i_vchType", adVarChar,
> adParamInput, lngParamLen, strInputText)
> .Parameters.Append ADOParam
> Set ADOParam = .CreateParameter("@.o_intRetVal",
> adInteger, adParamOutput)
> .Parameters.Append ADOParam
> Set .ActiveConnection = objCon
> .Execute
> lngRetVal = .Parameters("@.o_intRetVal").Value
> End With
> ' :
> ' :
> ' :
> '=======================================
==
> The ".Execute" statement caused the "No columns defined in rowset".
> If I try the following statement:
> .Execute , , (adCmdStoredProc Or adExecuteNoRecords)
Should be:
.Execute , , (adCmdStoredProc + adExecuteNoRecords)
HTH,
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.sql

No ADO type command for Data Flow?

I'm probably not looking in the right place, but all I could find when creating a data flow task was OLE DB Commands. I was trying to utilize a dataaccesslayer piece of code that we use every where in our projects, but because it uses ADO and not OLE DB, it caused an issue between the column data types.

Is there an ADO command object available? Or are we forced to use the OLE DB command object? All I was looking to do was to Execute a SQL command. There's an object on the Control Flow level to do that, but not on the Data Flow levelnot sure why that is.

Thanks,

Jeff Tolman
E&M Electric

Its because executing a stand-alone SQL statement isn't relevant for a data-flow. As you have observed you can do that in the Execute SQL Task.

What exactly is it that you want to do? What is your SQL statement?

-Jamie

|||

You would only use the data flow task if you wanted to process many rows of data - either aggregating them to pass them to your command, or issuing the command for every row that passes.

If you have existing code, you can likely acheive that using the Script Component - but be sure your scenario is appropriate first.

Can you describe some more about what you are trying to acheive?

Donald

|||

It's not necessarily a particular command. It's using ADO vs. OLE DB. Our DataAccessLayer code uses ADO, which I was able to import into the VSA scripting editor and that seemed to work just fine. However the output of the Script Component feeds records to separate SQL commands, but the only thing that I see that's available is the OLE DB Command. I know I could probably do this within the Script component, but it just seems to be more logically designed and layed out this way.

Thanks,

Jeff

|||

I have a set of records coming in to a Script Component and based on the data and lookups into the destination table I'm determining if data should be deleted, inserted or updated in the destination table. It's basically a table copy routine with a little intelligence for deletes.

Jeff

|||

JazzGeek wrote:

It's not necessarily a particular command. It's using ADO vs. OLE DB. Our DataAccessLayer code uses ADO, which I was able to import into the VSA scripting editor and that seemed to work just fine. However the output of the Script Component feeds records to separate SQL commands, but the only thing that I see that's available is the OLE DB Command. I know I could probably do this within the Script component, but it just seems to be more logically designed and layed out this way.

Thanks,

Jeff

Sorry, I'm really really confused. In SSIS you don't access data using the OLE DB Command component. You do it with a source adapter. There are many source adapters including one for OLE DB and one for ADO.

Are you saying that you want apply modifications using ADO rather than OLE DB?

-Jamie

|||

Hey Jamie,

I was just hoping to use our DataAccessLayer code library (which utilized ADO) to perform DB commands against our databases. I realize that with the OLE DB Command object you can execute a SQL command based on any of the input column data. Since I couldn't find an ADO Command object, I thought I'd use our DataAccessLayer library within a Script component to do it.

Thanks for the comments!

Jeff