Fetching Data from a web service

Hello All,

I have to fetch data in an ssis package from a set of web services. what is the best way of doing this?

The web services are session based, this means that multiple calls are needed to complete one operation. like one to log on, second onwards to execute calls, and the last one to log out.

(there is some cookie management also required to logon successfully into the web services).

Should we write a custom task which will fetch the data for us? Or just write a C# component which is invoked from SSIS?

Regards,

Abhishek.

MSDN Student wrote:

Hello All,

I have to fetch data in an ssis package from a set of web services. what is the best way of doing this?

The web services are session based, this means that multiple calls are needed to complete one operation. like one to log on, second onwards to execute calls, and the last one to log out.

(there is some cookie management also required to logon successfully into the web services).

Should we write a custom task which will fetch the data for us? Or just write a C# component which is invoked from SSIS?

Regards,

Abhishek.

Its your decision. If its something that will need to be done in many packages then a custom component is the way to go. If its just for this package, calling from the script task will be adequate.

-Jamie

federated query architecture advice

I'm involved in a project building a federated query app. There is an ASP.NET web app talking to a central SQL Server 2005 db. Users input query parameters to the web ap, the request is submitted as a stored procedure to a central db, and from there it is distributed to the selected remote SQL Server 2005 db servers with similar but not necessarily identical schemas where the actual data resides.

We must wait for all servers to execute the query and return the results, then aggregate those query results and present them to the web app user.

The question is, what is the best way to design this with Server 2005? We need to be able to initiate multiple concurrent queries, then wait (up to some max timeout value) for all responses to return. What way(s) do I have with SQL Server 2005 to (1) initiate multiple queries, and (2) wait on the events (queries) to complete, up to some max timeout value?

I've looked at Service Broker some but that (maybe) seems like more than we need because all we're doing are queries. The tricky part seems to be initiating concurrent queries and waiting for all responses. Any advice or comments are appreciated.

Using SQL Server 2005 and ADO.NET 2.0 will enable you to do async queries / commands to the database. THis might be the best solution for you.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||I was hoping for a single stored procedure (sp) with a parameter naming the data sources to be queried. The sp would start multiple parallel queries, then consolidate the results into a single result set to return to the caller. Any ideas along these lines?|||

Just to get some understanding why do need to use federated searches. Are you running into scale problems?

The one solution is to use partitioned views, this enables the Query Optimiser to figure out which servers to query.

|||

We have multiple SQL Server databases with essentially identical schemas. These databases are located remotely from our central server (assuming we'll connect via vpn or...?). The idea is for a stored procedure call to be made to the central server that will in turn query the remote servers, with the consolidate / federated result set from all servers involved returned to the caller as a single result set.

Looks like I need to look into partitioned views; I presume this requires linked servers? If so, I've heard about problems with links staying "up" over extended periods when the remote servers are not connected to a LAN - any feedback on this question?

|||

Federation is for searching across multiple servers in a single location (in my opinion) not bringing remote data together.

Based on what you have said I believe you should be looking at a replication model. Each of your sites replicates data to the central point. The queries at the central point are then on local copies of the data.

Doing distributed queries when you have a poor connection is just not practical. Replication can handle this level of connectivity.

If you have Site A, Site B and SIte C and you need to have users in all sites seeing data from the other sites, you probably want to look at peer to perr transactional replication.

|||

Normal replication is not an option. Here's the scenario: we have multiple heterogeneous "source" systems (Oracle, Informix, SQL Server, etc.), each unique and different. Co-located with each source system is a SQL Server db. On a daily basis, the relevant tables from the source systems (which can be several GB's in total) will be copied into equivalent SQL Server tables using SSIS. (This is why replication to a central db is not an option; take this as a given, trust me). From the source schema, the data will be transformed (again via SSIS) into a "provider" schema on the co-located server. These provider schemas will be similar but not identical. We want to query and aggregate results from the provider schemas.

For example, if we have 4 sites (A, B, C, and D), and the user wants to query sites A and C, we want to fire-off parallel queries at site A nd site C. The rowset structure from the two sites will be identical, but the queries that produce them will not necessarily be identical. The final step is to combine the result sets from sites A and C and return the combined rowset to the stored procedure caller.

Once again, the co-located SQL Server boxes will be accessed over the Internet (via vpn?).

|||When you say trust me are you referring to office politics or are you referring to a technical problem? It appears (to me) that the co-located Sql Server databases (sources) could replicate to a single SQL Server database (target) from which querying could be performed. The target database could define a field to show the source of the data co-located Sql Server databases.

Faster way to do this?

I want to know the # of users on our web site for each month in a given year. I'm looking for a faster way to do this--perhaps one that can leverage an index instead of reading the entire table! (My avg disk queue right now is above 7 and the query takes about 90 seconds).

Here's my current SP. Basically I'm calculating each month/year and using UNION to join them together, then pivot to rotate.

USE [TNS]

GO

/****** Object: StoredProcedure [dbo].[Unique_Login_IPs] Script Date: 05/07/2007 12:38:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[Unique_Login_IPs]

(

@.year1 int,

@.year2 int

)

AS

BEGIN

SET NOCOUNT OFF;

-- Define the years for testing purposes

set @.year1 = 2006

set @.year2 = 2007

SELECT month,[2006] as y2006,[2007] as y2007

FROM

(

SELECT @.year1 AS year, 1 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 1)) as tmpy1_1

UNION

SELECT @.year1 AS year, 2 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 2)) as tmpy1_2

UNION

SELECT @.year1 AS year, 3 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 3)) as tmpy1_3

UNION

SELECT @.year1 AS year, 4 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 4)) as tmpy1_4

UNION

SELECT @.year1 AS year, 5 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 5)) as tmpy1_5

UNION

SELECT @.year1 AS year, 6 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 6)) as tmpy1_6

UNION

SELECT @.year1 AS year, 7 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 7)) as tmpy1_7

UNION

SELECT @.year1 AS year, 8 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 8)) as tmpy1_8

UNION

SELECT @.year1 AS year, 9 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 9)) as tmpy1_9

UNION

SELECT @.year1 AS year, 10 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 10)) as tmpy1_10

UNION

SELECT @.year1 AS year, 11 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 11)) as tmpy1_11

UNION

SELECT @.year1 AS year, 12 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year1) AND (MONTH(logged) = 12)) as tmpy1_12

UNION

SELECT @.year2 AS year, 1 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 1)) as tmpy1_1

UNION

SELECT @.year2 AS year, 2 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 2)) as tmpy2_2

UNION

SELECT @.year2 AS year, 3 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 3)) as tmpy2_3

UNION

SELECT @.year2 AS year, 4 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 4)) as tmpy2_4

UNION

SELECT @.year2 AS year, 5 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 5)) as tmpy2_5

UNION

SELECT @.year2 AS year, 6 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 6)) as tmpy2_6

UNION

SELECT @.year2 AS year, 7 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 7)) as tmpy2_7

UNION

SELECT @.year2 AS year, 8 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 8)) as tmpy2_8

UNION

SELECT @.year2 AS year, 9 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 9)) as tmpy2_9

UNION

SELECT @.year2 AS year, 10 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 10)) as tmpy2_10

UNION

SELECT @.year2 AS year, 11 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 11)) as tmpy2_11

UNION

SELECT @.year2 AS year, 12 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @.year2) AND (MONTH(logged) = 12)) as tmpy2_12

) piv

PIVOT

(

SUM(cnt)

FOR year IN

([2006],[2007])

) as child

END

You didn't indicate if you were using SQL 2000 or SQL 2005.

This is an example of a 'single pass' collection and display of data -it may give you an idea of how to improve your current procedure. (This process will work in both SQL 2000 and SQL 2005. It uses the Northwind database.)

Code Snippet

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'spAnnualSalesByMonth'
)
DROP PROCEDURE dbo.spAnnualSalesByMonth
GO

CREATE PROCEDURE dbo.spAnnualSalesByMonth
AS
SELECT
max( dt.[Year] ) AS 'Year'
, convert( varchar(12), max( dt.Jan ), 1 ) AS 'Jan'
, convert( varchar(12), max( dt.Feb ), 1 ) AS 'Feb'
, convert( varchar(12), max( dt.Mar ), 1 ) AS 'Mar'
, convert( varchar(12), max( dt.Apr ), 1 ) AS 'Apr'
, convert( varchar(12), max( dt.May ), 1 ) AS 'May'
, convert( varchar(12), max( dt.Jun ), 1 ) AS 'Jun'
, convert( varchar(12), max( dt.Jul ), 1 ) AS 'Jul'
, convert( varchar(12), max( dt.Aug ), 1 ) AS 'Aug'
, convert( varchar(12), max( dt.Sep ), 1 ) AS 'Sep'
, convert( varchar(12), max( dt.Oct ), 1 ) AS 'Oct'
, convert( varchar(12), max( dt.Nov ), 1 ) AS 'Nov'
, convert( varchar(12), max( dt.[Dec] ), 1 ) AS 'Dec'
FROM
( SELECT
datepart( year, o.OrderDate ) AS 'Year'
, datepart( month, o.OrderDate ) AS 'Month'
, CASE when ( datepart( month, o.OrderDate )) = 1 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Jan'
, CASE when ( datepart( month, o.OrderDate )) = 2 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Feb'
, CASE when ( datepart( month, o.OrderDate )) = 3 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Mar'
, CASE when ( datepart( month, o.OrderDate )) = 4 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Apr'
, CASE when ( datepart( month, o.OrderDate )) = 5 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'May'
, CASE when ( datepart( month, o.OrderDate )) = 6 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Jun'
, CASE when ( datepart( month, o.OrderDate )) = 7 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Jul'
, CASE when ( datepart( month, o.OrderDate )) = 8 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Aug'
, CASE when ( datepart( month, o.OrderDate )) = 9 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Sep'
, CASE when ( datepart( month, o.OrderDate )) = 10 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Oct'
, CASE when ( datepart( month, o.OrderDate )) = 11 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Nov'
, CASE when ( datepart( month, o.OrderDate )) = 12 THEN sum( od.Quantity * UnitPrice ) ELSE 0 END AS 'Dec'
FROM Orders o
JOIN [Order Details] od
ON o.OrderID = od.OrderID
GROUP BY
datepart( year, o.OrderDate )
, datepart( month, o.OrderDate )
) dt
GROUP BY dt.[Year]
ORDER BY dt.[Year]
GO

EXECUTE dbo.spAnnualSalesByMonth

(Output clipped for display)

Year Jan Feb Mar Apr May Jun Jul
--
1996 0.00 0.00 0.00 0.00 0.00 0.00 30,192.10
1997 66,692.80 41,207.20 39,979.90 55,699.39 56,823.70 39,088.00 55,464.93
1998 100,854.72 104,561.95 109,825.45 134,630.56 19,898.66 0.00 0.00

However, if you are using SQL 2005, there may be more efficency gained by using the new PIVOT operator. (This is untested.)

Code Snippet

DECLARE @.LogSummary table
( IPAddress varchar(15),

LogYear char(4) NOT NULL,
LogMonth char(2) NOT NULL,
)

INSERT INTO @.LogSummary
SELECT DISTINCT
IPAddress,
year( Logged),
month( Logged )
FROM ServiceLog
WHERE Method = 'LOGIN'

SELECT *
FROM @.LogSummary
PIVOT ( count( IPAddress ) FOR LogMonth
IN ( [01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12] )) AS LogPivot

faster page update using SQL Server data

Hi,

What is the fastest way to get informations from a SQL BD (using stored procedure) in a WEB Page ?

This web page need to be updated EVERY SECONDE !
Javacript / OleDB / SQLConnection / ... ?

I plan to use a a usercontrol containing the informations, am I right ?

thank you for your help,You can get HTML code directly using the Web Tasks functionality in SQL Server ... I think this is the easiest and the fastest way to get web pages directly from SQL Server ...

Refer to SQL Server BOL for further info ...

Faster database

I have a database called MySessions that holds some volatile information for
web sessions.
This database should be configured for fastest read/write operations.I don't
need to back it up (In a catrastrophic failure I just need to fix the
structure, and the data loss is not important).
Which settings should I set? Which recovery model should I use?
Kyle.SQL Server always operate with full integrity, so there no setting which will reduce integrity of
data and hence potentially increase performance. Simple recovery mode will not reduce amount of work
done for you operations, it will only automatically re-use space in the transaction log files
(instead of waiting for you to perform a log backup). The only exception to this is for
create/rebuild/drop index, SELECT INTO and bulk loading data (under certain circumstances) which can
go in minimally logged mode if simple recovery mode.
So except for that last exception, the only thing you can do is to work at the physical level, like
adventuring the data by using RAID 0 and stuff like that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Bush" <kyle@.bush.com> wrote in message news:eqwPB01IGHA.2900@.TK2MSFTNGP14.phx.gbl...
>I have a database called MySessions that holds some volatile information for web sessions.
> This database should be configured for fastest read/write operations.I don't need to back it up
> (In a catrastrophic failure I just need to fix the structure, and the data loss is not important).
> Which settings should I set? Which recovery model should I use?
> Kyle.
>

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText)
>|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest SP
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying problem
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
connectionString, CommandType commandType, String commandText)
See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Dataset(String
> connectionString, CommandType commandType, String commandText)
>
|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Fast SP is timing out

I have an SP that is called on every page load for our web application
(asp.net).
The SP is basically like this:
SELECT * FROM Messages
WHERE StartDate < GETDATE() and EndDate > GETDATE()
Messages has like 15 rows in it, so this is NOT a slow SP.
This SP normally executes in like 1/1000 of a second. On my laptop I can
run a loop of 10,000 times executing this SP and it finishes without error
after less than 10 seconds.
We have PLENTY of long running SPs that do a ton of work but all day today I
have been getting timeouts for this one SP that should be able the fastest S
P
we have in our entire system.
To access the SP I am using the Microsoft Data Access Application Blocks
SqlHelper class's ExecuteDataset method.
Now... I have read that some people suggest that the solution to this is to
increase the timeout of the command object. This would be the right answer
for long running SPs, that need 30+ seconds to run, but this SP should need
0.001 seconds, so I don't think that is the problem.
Also... I have read other problems where people say that while using the
DAAB they get errors in some instances, but it seems like those are related
to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying proble
m
they report is that the connection is not closed, but our website only has 3
connections to the database right now, so we are not leaking connections.
Can anyone shed some light on this, or give me some ideas about how to track
this down? This code has been working w/o problem from the first day I put
it into production and it just started to fail today for no apparent reason.
Here is the stack trace:
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText, SqlParameter&
#91;]
commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
connectionString, CommandType commandType, String commandText)See if this table is locked by some process. Use sp_who to determine
blocking.
See if the following help:
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
http://vyaskn.tripod.com/watch_your_timeouts.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"cmay" <cmay@.discussions.microsoft.com> wrote in message
news:E631DCBD-5D6D-461E-8820-062AE37F86F3@.microsoft.com...
> I have an SP that is called on every page load for our web application
> (asp.net).
> The SP is basically like this:
> SELECT * FROM Messages
> WHERE StartDate < GETDATE() and EndDate > GETDATE()
> Messages has like 15 rows in it, so this is NOT a slow SP.
> This SP normally executes in like 1/1000 of a second. On my laptop I can
> run a loop of 10,000 times executing this SP and it finishes without error
> after less than 10 seconds.
> We have PLENTY of long running SPs that do a ton of work but all day today
I
> have been getting timeouts for this one SP that should be able the fastest
SP
> we have in our entire system.
> To access the SP I am using the Microsoft Data Access Application Blocks
> SqlHelper class's ExecuteDataset method.
> Now... I have read that some people suggest that the solution to this is
to
> increase the timeout of the command object. This would be the right
answer
> for long running SPs, that need 30+ seconds to run, but this SP should
need
> 0.001 seconds, so I don't think that is the problem.
> Also... I have read other problems where people say that while using the
> DAAB they get errors in some instances, but it seems like those are
related
> to 1) calling ExecuteREADER not ExecuteDataset, and 2) the underlying
problem
> they report is that the connection is not closed, but our website only has
3
> connections to the database right now, so we are not leaking connections.
>
> Can anyone shed some light on this, or give me some ideas about how to
track
> this down? This code has been working w/o problem from the first day I
put
> it into production and it just started to fail today for no apparent
reason.
>
> Here is the stack trace:
>
> Message: Timeout expired. The timeout period elapsed prior to completion
of
> the operation or the server is not responding.
> Stack: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
> at
>
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at
> Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection
> connection, CommandType commandType, String commandText, SqlParameter[
]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText,
SqlParameter[]
> commandParameters)
> at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String
> connectionString, CommandType commandType, String commandText)
>|||Vyas,
I changed the SP so that it is no longer reading any data from the
database. Now, instead of reading from the table I am just creating a
table variable and returning it, and i am still getting the timeout
errors.
I think this eliminates the locking issue b/c there is no longer any
data being read.
I think also that we can eliminate performance, as this has to be the
fastest SP in our entire database.
What else could be going on?
Chris

Failure writing properties running SSIS package from a Web Service

I am attempting to run an SSIS package from a web service. Right now both the service and package are on my local machine which is running XP. I have accessed the web service from a client application in debug mode. I am not sure if it is actually running under aspnet_wp.exe because it is XP and a development environment? (separate question)? The package fails with a series of OnError messages similar to:

The result of the expression ""/c DEL /F /Q \"" + @.DeployFolder + "\\catalog.diff.lz\""" on property "Arguments" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

An initial supposition is that the permissions of the web service are inadequate for the package. I have the authentication as "Windows" and <identity impersonate="true" /> in the Web.Config file. When I break in the debugger the Environment.UserName and Environment.UserDomainName are mine and I am an Admin on the box.
the authorization is 'deny users="?".

The article that describes basic implementation of this in a Web Service states:

With its default settings for authentication and authorization, a Web

service generally does not have sufficient permissions to access SQL

Server or the file system to load and execute packages. You may have to

assign appropriate permissions to the Web service by configuring its

authentication and authorization settings in the web.config

file and assigning database and file system permissions as appropriate.

A complete discussion of Web, database, and file system permissions is

beyond the scope of this topic.

And how!

Note that the load is fine and that this is a run time error and that the package runs correctly when run manually from SQL Server using the 'run package' menu item in the Object Explorer tree of the SQL Server Management Console.

I need to know if this is an ASP.NET issue per se or XP or if this is even a security issue. And how to solve it! This is critical path so an expeditious reply with a solution would be greatly appreciated.Can't believe I left this out but the the web service is running under Integrated Windows authentication.

Failure installing sql server 2005 express on vista ultimate x64, error 29506

Whenever I try to install SQL Server 2005 Express Edition on my Vista Ultimate x64 platform I get error 29506. All I could find on the web was that this is probably due to an access control problem. I have tried installing it as the local administrator, as a member of domain admins, and as the domain administrator. All get the same error. My domain is a Small Business Server domain. This may be part of the problem, but I can't imagine how.

When trying to force access to the domain administrator, I also seem to get an access problem.

Any suggestions?

First off I would try the sp1 version, but other then that the SP2 is the next supported release on Vista, there is a ctp release but the final release has not been made...

When installing VS Express you need to run the install under elevated permissions, I do this by running a command prompt as an administrator (Right clicking on the icon and selelcting run as admin), then run the exec by using the command prompt.

|||

Steve:

Were you ever able to get this issue resolved?

Thanks!

Rory O. Cahill

Failure installing sql server 2005 express on vista ultimate x64, error 29506

Whenever I try to install SQL Server 2005 Express Edition on my Vista Ultimate x64 platform I get error 29506. All I could find on the web was that this is probably due to an access control problem. I have tried installing it as the local administrator, as a member of domain admins, and as the domain administrator. All get the same error. My domain is a Small Business Server domain. This may be part of the problem, but I can't imagine how.

When trying to force access to the domain administrator, I also seem to get an access problem.

Any suggestions?

First off I would try the sp1 version, but other then that the SP2 is the next supported release on Vista, there is a ctp release but the final release has not been made...

When installing VS Express you need to run the install under elevated permissions, I do this by running a command prompt as an administrator (Right clicking on the icon and selelcting run as admin), then run the exec by using the command prompt.