Recipe for a good, solid maintenance plan

Hello

I'm in the proces of a major revision of the maintenance plans for the SQL servers in our company, and in connection with that I would like to hear how other people are doing this.

Here's a quick rundown of the plan:

Critical DBs will be backed up on tape (daily incremental, weekly full, w. Veritas Backup Exec 9.0). Also, there will be full daily disk backups for easy quick recovery. These will be done locally, as I have bad experiences trying to backup across a network share.

DBs of medium importance will be backed up fully every day on disk. These BAK-files will then be backed up on tape, if I find it necessary.

Although I rarely restore from the tapes, I think they're nice to have in case the office burns down or who knows what.

The maintenance plans will be split into 3:
1. System DBs maint. plan
2. Critical importance DBs maint. plan
3. Medium importance DBs maint. plan

The more I think about it, the more I think I might just classify all production DBs as critical and all test DBs as medium. Maybe that would make more sense.

For all disk backups, optimization and integrity checks (and backup) will be done daily. For DBs of critical importance (eg. production DBs) Transaction log back will be done as well. What's a good schedule for this? Once every 3 hours or so? Every hour? How much burden does this operation put on the server?

I guess that's about it so far. If anyone has any suggestions or comments, I would be very pleased to hear them.

MNJFrequency of trx. log backups depends on the level of activity of action queries and recoverability requirements. In one of our databases here we're doing 15-minute trx. log dumps and the resulting file varies from 800MB to 2.5GB in size. Another database barely creates a 100K logs but we're doing dumps every 30 minutes for its point-in-time recoverability requirements. It all depends.

As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)|||[i]As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)

Why is it beneficial to backup the system DBs last? Since I keep system and user DBs separated into different maint. plans, I guess I can just schedule the system maint. plan to occur 15 min. after the user main. plan?

MNJ|||For one, if MSDB is backed up last, - it will contain the latest backup information of all other databases, as well as itself. This information is available when looking at the General tab of database Properties window.

As per scheduling, - as I said earlier, I have execution of all maintenance plans in one batch with SQLMAINT. If you're using Scheduled Tasks, then you can add a step with SQLMAINT -PlanName <sys_db_maint_plan> after your application databases.|||Good point with system DBs, I will take that into consideration. I suppose if I just make sure to schedule them a bit apart, it should work out ok.

MNJ

Receiving errors when using foreach loop and excel connection manager...

Purpose: Need to import excel source data into SQL Server 2005 tables. Excel source data comes in nulitple excel files with the same structure but different data. I would appreciate someone taking a look at the following information and notifying me of what I am doing incorrectly.

I Inserted a foreach loop container, a data flow task located inside the foreach loop contaiiner, an excel and SQL Server 2005 connections.

After trying multiple times I went the following URL and followed step by step direction on how to connect excel workbooks dynamically: http://msdn2.microsoft.com/en-us/library/ms345182.aspx . I also used http://www.sqlstrings.com/ as a reference when creating the connection string.

Creating a Foreach Loop Container:

1. Opened foreach loop container 2.Set the Enumerator to 'Foreach File Enumerator" and configured the enumerator by setting the directory location and file base name to E:\Clients\Dep Comm\BEA\BEA_Test_Source and *PersonnelExpense*.xls respectively. 3. Clicked Variable Mapping; created two variables called, "ExcelFile", and "ExtProperties" and closed out of the foreach loop container.

I. Created Excel Connection:

1. Created excel connection called, “Dynamic Excel Connection Manager,” that initially pointed to one of the excel workbooks.
2. Went to the connection properties by right clicking the connection manager.
3. Expanded Expressions and clicked the ellipsis button to bring up property expressions
4. Chose Connection String in the Property.
5. Clicked the Expression Ellipsis button.
6. Put the following inside the Expression multi line text box:

A. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

7. Clicked the Evaluate Expression button to get the following:
   1. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties=""
8. Clicked Ok button
9. Inserted a Data flow task inside the foreach loop container.

II. Configured Tasks that is associated with Dynamic Excel Connection Manager or Package:

1. Set the Foreach loop container Delay Validation to true.
2. Set the Data Flow Task Container Delay Validation to true.
3. Set the Dynamic Excel Connection Manager Delay Validation to true.
4. Set the SQL Server Connection Manager Delay Validation to true.
5. Set the Package Delay Validation to true.
6. Package Locale ID set to English

Ran the package after connecting the excel source data flow to the OLEDB destination and have inserted part of the error in this post. Please see below.

Error: 0xC0202009 at Package, Connection manager "Dynamic Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.Source: "Microsoft JET Database Engine"Hresult: 0x80004005Description: "Could not find installable ISAM.".

I modified the connection string after receiving the error by removing the extended properties. The following is the modified connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile]

I repeated step I.6 above and received the following expression: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=

I ran the package and received the following error in part: OLE DB record is available.Source: "Microsoft JET Database Engine"Hresult: 0x80004005Description: "Unrecognized database format 'E:\Clients\Dep Comm\BEA\BEA_Test_Source\PersonnelExpense_OCCs_051007.xls'."

I did not find anything helpful when I searched for the above errors and would very much appreciate anyone’s assistance on this issue as this issue needs to be taken care of ASAP.

Does anyone have any ideas as to why I received this error and what can I do to resolve this issue?

Your assistance in this matter is truly appreicated!

Thanks!!

Lee

Are there headers on the Excel spreadsheets?

The error is looking for a database format where the headers are matched as column headers.

If there are no headers, it doesnt' know how to match it.

Just my twist on it,

Adamus

|||

Hi Adamus,

I appreciate your feedback. To answer your question, the first row does contain column headers.The data flow task has an excel source file which uses the Dynamic Excel Connection manager that I mentioned in my original post. The excel source file connects to a derived column data task that creates derived columns and changes the data type to reflect that of the destination. The derived columns is then connected to the SQL Server destinationation table that matches the excel header columns to the destination table columns.

What should I do with the connection string that I mentioned in my first post? I have provided it here for you or anyone else to review.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

Can you or anyone else tell me what I am doing wrong here?

Thanks!!

Lee

|||

Can you actually import at least one single file? forget about the foreach loop and the expression in the connection manager.

I just did a search on 'Could not find installable ISAM ' and found this:

http://support.microsoft.com/kb/209805

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=477902&SiteID=1

So, before going deeper, try to connect to a single execel file.

BTW, I just found a test package I created long time ago, and I see some differences with your approach:

I have only one variable in my for each loop container to hold the file name; and since I configured the loop container to retrive 'Fully Qualified" file name; the variable contains the path and the file name. In the excel connection manager I applied an expression to the ExcelFilePath property (not to the Connection String). The expression is just: @.{User::VarFileName] ; which is the variable I use in the loop container. I don't mess with the extended properties.sql

Receive "Acquiring a connection requires a valid Task name" when

Hi All,
I'm creating "DTSExecuteSQLTask" Task in DDQ ActiveX script code. Then
I'm trying to execute it from within this code with command:
oCustomerTask.execute oPkg, Nothing, Nothing,
DTSTaskExecResult_Success
but I receive error message:
"Acquiring a connection requires a valid Task name".
What's the matter?
Following is my activx script code
'***************************************
*******************************
' Visual Basic ActiveX Script
'***************************************
*********************************
Function Main()
Dim oPkg, startDate, oTask, oConnection, oCustomerTask
Dim oResult
set oPkg = DTSGlobalVariables.parent
startDate = DTSGlobalVariables("startDate")
Set oConnection = oPkg.Connections("db_conn2")
Set oTask = oPkg.tasks.new("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
Set oCustomerTask = oTask.CustomTask
oCustomerTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomerTask.description = "test task"
oCustomerTask.ConnectionID=oConnection.id
oCustomerTask.CommandTimeout=0
oCustomerTask.OutputAsRecordset = False
oCustomerTask.SQLStatement = "delete from tmp_table"
oCustomerTask.execute oPkg, Nothing, Nothing,
DTSTaskExecResult_Success
Set oCustomerTask = Nothing
set oTask=Nothing
Set oConnection=Nothing
set oPkg=nothing
Main = DTSTaskExecResult_Success
End Function
What's wrong?
Thanks in advance.I have found the resolution, after you create the task, you need
create a step object whcih will refers this new created task, you also
need add the task to current package. And then execute the step, you
can get the result. After the step complets, you need to remove the
task from package.
This solution works for me, but I do not know this is the best
solution or not. If anybody has more better solution, please post it.
Thanks a lot.
Following is the code which can run on my SQL Server 2000 Enterprise
Edition
'***************************************
*******************************
' Visual Basic ActiveX Script
'***************************************
*********************************
Function Main()
Dim oPkg, startDate, oTask, oConnection, oCustomerTask
Dim oResult
set oPkg = DTSGlobalVariables.parent
startDate = DTSGlobalVariables("startDate")
Set oConnection = oPkg.Connections("db_conn2")
'create task itself
Set oTask = oPkg.tasks.new("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
Set oCustomerTask = oTask.CustomTask
oCustomerTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomerTask.description = "test task"
oCustomerTask.ConnectionID=oConnection.id
oCustomerTask.CommandTimeout=0
oCustomerTask.OutputAsRecordset = False
oCustomerTask.SQLStatement = "select * from test_table"
oPkg.tasks.add oTask
'associate step with a task
Dim oStep
Set oStep = oPkg.Steps.New
oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
oStep.Description = "Execute SQL Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSExecuteSQLTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
'run step
oStep.execute
'remove the added customer task
Dim index
For index=1 To oPkg.tasks.count
If (oPkg.tasks.item(index).name = "DTSTask_DTSExecuteSQLTask_1")
Then
oPkg.tasks.remove index
Exit For
End If
Next
Set oStep = Nothing
Set oCustomerTask = Nothing
set oTask=Nothing
Set oConnection=Nothing
set oPkg=nothing
Main = DTSTaskExecResult_Success
End Function

Receive "Acquiring a connection requires a valid Task name" when

Hi All,
I'm creating "DTSExecuteSQLTask" Task in DDQ ActiveX script code. Then
I'm trying to execute it from within this code with command:
oCustomerTask.execute oPkg, Nothing, Nothing,
DTSTaskExecResult_Success
but I receive error message:
"Acquiring a connection requires a valid Task name".
What's the matter?
Following is my activx script code
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oPkg, startDate, oTask, oConnection, oCustomerTask
Dim oResult
set oPkg = DTSGlobalVariables.parent
startDate = DTSGlobalVariables("startDate")
Set oConnection = oPkg.Connections("db_conn2")
Set oTask = oPkg.tasks.new("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
Set oCustomerTask = oTask.CustomTask
oCustomerTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomerTask.description = "test task"
oCustomerTask.ConnectionID=oConnection.id
oCustomerTask.CommandTimeout=0
oCustomerTask.OutputAsRecordset = False
oCustomerTask.SQLStatement = "delete from tmp_table"
oCustomerTask.execute oPkg, Nothing, Nothing,
DTSTaskExecResult_Success
Set oCustomerTask = Nothing
set oTask=Nothing
Set oConnection=Nothing
set oPkg=nothing
Main = DTSTaskExecResult_Success
End Function
What's wrong?
Thanks in advance.I have found the resolution, after you create the task, you need
create a step object whcih will refers this new created task, you also
need add the task to current package. And then execute the step, you
can get the result. After the step complets, you need to remove the
task from package.
This solution works for me, but I do not know this is the best
solution or not. If anybody has more better solution, please post it.
Thanks a lot.
Following is the code which can run on my SQL Server 2000 Enterprise
Edition
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oPkg, startDate, oTask, oConnection, oCustomerTask
Dim oResult
set oPkg = DTSGlobalVariables.parent
startDate = DTSGlobalVariables("startDate")
Set oConnection = oPkg.Connections("db_conn2")
'create task itself
Set oTask = oPkg.tasks.new("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
Set oCustomerTask = oTask.CustomTask
oCustomerTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomerTask.description = "test task"
oCustomerTask.ConnectionID=oConnection.id
oCustomerTask.CommandTimeout=0
oCustomerTask.OutputAsRecordset = False
oCustomerTask.SQLStatement = "select * from test_table"
oPkg.tasks.add oTask
'associate step with a task
Dim oStep
Set oStep = oPkg.Steps.New
oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
oStep.Description = "Execute SQL Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSExecuteSQLTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
'run step
oStep.execute
'remove the added customer task
Dim index
For index=1 To oPkg.tasks.count
If (oPkg.tasks.item(index).name = "DTSTask_DTSExecuteSQLTask_1")
Then
oPkg.tasks.remove index
Exit For
End If
Next
Set oStep = Nothing
Set oCustomerTask = Nothing
set oTask=Nothing
Set oConnection=Nothing
set oPkg=nothing
Main = DTSTaskExecResult_Success
End Function

Really really slow cursor

I'm stumped on this.
My developers proc left to run all night consumes tons of cpu but does no
updates at all and I have to kill the connection in the morning.
The select in the cursor declaration returns 357 rows so not an outrageous
result set. When I run the select in Qry Analyser it runs in under 6 seconds
.
When I run the stored proc in QA it takes forever. Debug print statements
appear up until the initial fetch and then nothing.
Can a cursor loop indefinitely?
CREATE PROCEDURE dbo.stc_Insert_Instrument AS
set nocount on
declare
@.rc int -- returncode
, @.errmsg varchar(250)
, @.msg varchar(50)
select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
-- create temp tables
select
<Snip>
into #moodystaging
from moodystaging
select
<Snip>
into #spstaging
from spstaging
declare ins_cursor cursor read_only for
select
iss.IssuerId,
st.SECURITY_DES,
case isdate(st.maturity)
when 1 then st.maturity
else null
end as maturity,
case isdate(st.issue_dt)
when 1 then st.issue_dt
else null
end as issue_dt,
case isnumeric(st.cpn)
when 1 then st.cpn
else null
end as cpn,
cp.CouponTypeId,
st.CRNCY,
case isnumeric(st.AMT_ISSUED)
when 1 then st.AMT_ISSUED
else null
end as amt_issued,
case isnumeric(st.AMT_OUTSTANDING)
when 1 then st.AMT_OUTSTANDING
else null
end as amt_outstanding,
r.RatingId,
sprt.RatingId as spRatingId,
st.id_bb_unique,
st.id_isin,
st.id_cusip
from
staging st
left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
left join external e on st.id_bb_unique = e.externalid and
e.externaltype='BB'
left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
left join rating r on mt.rtg = r.rating and r.type = 'Moody'
left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
where
e.externalid is null
and iss.sourceid is not null
open ins_cursor
declare
@.issuerid int,
@.instrument_name varchar(50),
@.maturitydate datetime,
@.issuedate datetime,
@.coupon float(8),
@.coupontypeid int,
@.currency varchar(3),
@.amountissued float(8),
@.amountoutstanding float(8),
@.moodyratingid int,
@.spratingid int,
@.bb_id varchar(50),
@.isin varchar(50),
@.cusip varchar(50),
@.instrumentid int
fetch next from ins_cursor into
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid,
@.bb_id,
@.isin,
@.cusip
print 'after first fetch'
while @.@.fetch_status = 0
begin
print 'processing'
insert into instrument (
issuerid,
instrumentname,
maturitydate,
issuedate,
coupon,
coupontypeid,
currency,
amountissued,
amountoutstanding,
moodyratingid,
spratingid)
values (
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into Instrument failed.'
goto errhandler
end
set @.instrumentid = @.@.identity
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'BB', @.bb_id)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for BB type.'
goto errhandler
end
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'ISIN', @.isin)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for ISIN type.'
goto errhandler
end
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'Cusip', @.cusip)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for Cusip type.'
goto errhandler
end
fetch next from ins_cursor into
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid,
@.bb_id,
@.isin,
@.cusip
end
close ins_cursor
deallocate ins_cursor
drop table #moodystaging
drop table #spstaging
return 0 -- success
errhandler:
raiserror ('%s %s',16,1,@.errmsg,@.msg)
if @.@.trancount > 0
rollback transaction
drop table #moodystaging
drop table #spstaging
return @.rc
GOMaybe one of the tables that you are updating is locked and the stored
procedure is waiting for the lock to free up?
"Si" <Si@.discussions.microsoft.com> wrote in message
news:4A962893-F4D1-4BDA-814E-9DB0E891EA6F@.microsoft.com...
> I'm stumped on this.
> My developers proc left to run all night consumes tons of cpu but does no
> updates at all and I have to kill the connection in the morning.
> The select in the cursor declaration returns 357 rows so not an outrageous
> result set. When I run the select in Qry Analyser it runs in under 6
seconds.
> When I run the stored proc in QA it takes forever. Debug print statements
> appear up until the initial fetch and then nothing.
> Can a cursor loop indefinitely?
>
>
> CREATE PROCEDURE dbo.stc_Insert_Instrument AS
> set nocount on
> declare
> @.rc int -- returncode
> , @.errmsg varchar(250)
> , @.msg varchar(50)
>
> select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
> -- create temp tables
> select
> <Snip>
> into #moodystaging
> from moodystaging
>
> select
> <Snip>
> into #spstaging
> from spstaging
>
> declare ins_cursor cursor read_only for
> select
> iss.IssuerId,
> st.SECURITY_DES,
> case isdate(st.maturity)
> when 1 then st.maturity
> else null
> end as maturity,
> case isdate(st.issue_dt)
> when 1 then st.issue_dt
> else null
> end as issue_dt,
> case isnumeric(st.cpn)
> when 1 then st.cpn
> else null
> end as cpn,
> cp.CouponTypeId,
> st.CRNCY,
> case isnumeric(st.AMT_ISSUED)
> when 1 then st.AMT_ISSUED
> else null
> end as amt_issued,
> case isnumeric(st.AMT_OUTSTANDING)
> when 1 then st.AMT_OUTSTANDING
> else null
> end as amt_outstanding,
> r.RatingId,
> sprt.RatingId as spRatingId,
> st.id_bb_unique,
> st.id_isin,
> st.id_cusip
> from
> staging st
> left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
> INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
> left join external e on st.id_bb_unique = e.externalid and
> e.externaltype='BB'
> left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
> left join rating r on mt.rtg = r.rating and r.type = 'Moody'
> left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
> left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
> where
> e.externalid is null
> and iss.sourceid is not null
>
> open ins_cursor
> declare
> @.issuerid int,
> @.instrument_name varchar(50),
> @.maturitydate datetime,
> @.issuedate datetime,
> @.coupon float(8),
> @.coupontypeid int,
> @.currency varchar(3),
> @.amountissued float(8),
> @.amountoutstanding float(8),
> @.moodyratingid int,
> @.spratingid int,
> @.bb_id varchar(50),
> @.isin varchar(50),
> @.cusip varchar(50),
> @.instrumentid int
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> print 'after first fetch'
> while @.@.fetch_status = 0
> begin
> print 'processing'
> insert into instrument (
> issuerid,
> instrumentname,
> maturitydate,
> issuedate,
> coupon,
> coupontypeid,
> currency,
> amountissued,
> amountoutstanding,
> moodyratingid,
> spratingid)
> values (
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into Instrument failed.'
> goto errhandler
> end
> set @.instrumentid = @.@.identity
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'BB', @.bb_id)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for BB type.'
> goto errhandler
> end
>
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'ISIN', @.isin)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for ISIN type.'
> goto errhandler
> end
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'Cusip', @.cusip)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for Cusip type.'
> goto errhandler
> end
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> end
> close ins_cursor
> deallocate ins_cursor
> drop table #moodystaging
> drop table #spstaging
> return 0 -- success
> errhandler:
> raiserror ('%s %s',16,1,@.errmsg,@.msg)
> if @.@.trancount > 0
> rollback transaction
> drop table #moodystaging
> drop table #spstaging
> return @.rc
> GO
>|||One thing is clear - this can be done without a cursor. Or have I missed
something?
What else is going on in there during the night?
ML
http://milambda.blogspot.com/|||The reason for the cursor was to extract the identity value from the
instrument table to enter it into the external table. (Sorry, I didn't
include table definitions)
I'd be very happy if there isanother way to achieve this and get rid of the
curse, I mean cursor!
There is nothing else going on overnight apart from backups.
"ML" wrote:

> One thing is clear - this can be done without a cursor. Or have I missed
> something?
> What else is going on in there during the night?
>
> ML
> --
> http://milambda.blogspot.com/|||Thanks Jim,
I don't think this is the case but I'll double check.
Progress seems to halt before then, right after the initial fetch.
Simon
"Jim Underwood" wrote:

> Maybe one of the tables that you are updating is locked and the stored
> procedure is waiting for the lock to free up?
> "Si" <Si@.discussions.microsoft.com> wrote in message
> news:4A962893-F4D1-4BDA-814E-9DB0E891EA6F@.microsoft.com...
> seconds.
>
>|||So the 'after first fetch'
is never reached?
"Si" <Si@.discussions.microsoft.com> wrote in message
news:E15851B6-2879-453D-8F01-C96375BF7A1C@.microsoft.com...
> Thanks Jim,
> I don't think this is the case but I'll double check.
> Progress seems to halt before then, right after the initial fetch.
> Simon
>
> "Jim Underwood" wrote:
>
no
outrageous
statements|||Si wrote:

> The reason for the cursor was to extract the identity value from the
> instrument table to enter it into the external table. (Sorry, I didn't
> include table definitions)
> I'd be very happy if there isanother way to achieve this and get rid of th
e
> curse, I mean cursor!
>
At least three possible solutions that don't need a cursor.
1. Create an insert trigger on the Instrument table to populate
External.
2. Use a table variable or temp table (SQL Server 2000):
DECLARE @.instrument TABLE ...;
INSERT INTO @.instrument (...)
SELECT ...
FROM staging ...;
INSERT INTO instrument (...)
SELECT ...
FROM @.instrument;
INSERT INTO external
(instrumentid, ...)
SELECT I.instrumentid, ...
FROM instrument AS T
LEFT JOIN @.instrument AS I
ON ... etc
3. Use a table variable and INSERT ... OUTPUT (SQL Server 2005):
DECLARE @.instrument (instrumentid INTEGER);
INSERT INTO instrument (...)
OUTPUT Inserted.instrumentid INTO @.instrument
SELECT ...
FROM staging ...;
INSERT INTO external
(instrumentid, ...)
SELECT instrumentid, ...
FROM @.instrument;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||curse (n) - an unnecessary cursor
I like that. :) Maybe it should be added:
http://www.webster.com/dictionary/curse
ML
http://milambda.blogspot.com/|||You don't need a cursor for a select.. insert..
insert into TableA( a, b, c )
select a, b, c from TableB
Meanwhile, the next time it seems to freeze, use the procedure sp_who2 to
determine if the process is blocked.
"Si" <Si@.discussions.microsoft.com> wrote in message
news:717A26A2-8862-49D0-A78D-44879CD2FA3B@.microsoft.com...
> The reason for the cursor was to extract the identity value from the
> instrument table to enter it into the external table. (Sorry, I didn't
> include table definitions)
> I'd be very happy if there isanother way to achieve this and get rid of
> the
> curse, I mean cursor!
>
> There is nothing else going on overnight apart from backups.
> "ML" wrote:
>|||Some re-thinking on the design and cursor no longer needed.
Thanks very much everyone for your time and replies.
Simon.
"Si" wrote:

> I'm stumped on this.
> My developers proc left to run all night consumes tons of cpu but does no
> updates at all and I have to kill the connection in the morning.
> The select in the cursor declaration returns 357 rows so not an outrageous
> result set. When I run the select in Qry Analyser it runs in under 6 secon
ds.
> When I run the stored proc in QA it takes forever. Debug print statements
> appear up until the initial fetch and then nothing.
> Can a cursor loop indefinitely?
>
>
> CREATE PROCEDURE dbo.stc_Insert_Instrument AS
> set nocount on
> declare
> @.rc int -- returncode
> , @.errmsg varchar(250)
> , @.msg varchar(50)
>
> select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
> -- create temp tables
> select
> <Snip>
> into #moodystaging
> from moodystaging
>
> select
> <Snip>
> into #spstaging
> from spstaging
>
> declare ins_cursor cursor read_only for
> select
> iss.IssuerId,
> st.SECURITY_DES,
> case isdate(st.maturity)
> when 1 then st.maturity
> else null
> end as maturity,
> case isdate(st.issue_dt)
> when 1 then st.issue_dt
> else null
> end as issue_dt,
> case isnumeric(st.cpn)
> when 1 then st.cpn
> else null
> end as cpn,
> cp.CouponTypeId,
> st.CRNCY,
> case isnumeric(st.AMT_ISSUED)
> when 1 then st.AMT_ISSUED
> else null
> end as amt_issued,
> case isnumeric(st.AMT_OUTSTANDING)
> when 1 then st.AMT_OUTSTANDING
> else null
> end as amt_outstanding,
> r.RatingId,
> sprt.RatingId as spRatingId,
> st.id_bb_unique,
> st.id_isin,
> st.id_cusip
> from
> staging st
> left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
> INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
> left join external e on st.id_bb_unique = e.externalid and
> e.externaltype='BB'
> left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
> left join rating r on mt.rtg = r.rating and r.type = 'Moody'
> left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
> left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
> where
> e.externalid is null
> and iss.sourceid is not null
>
> open ins_cursor
> declare
> @.issuerid int,
> @.instrument_name varchar(50),
> @.maturitydate datetime,
> @.issuedate datetime,
> @.coupon float(8),
> @.coupontypeid int,
> @.currency varchar(3),
> @.amountissued float(8),
> @.amountoutstanding float(8),
> @.moodyratingid int,
> @.spratingid int,
> @.bb_id varchar(50),
> @.isin varchar(50),
> @.cusip varchar(50),
> @.instrumentid int
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> print 'after first fetch'
> while @.@.fetch_status = 0
> begin
> print 'processing'
> insert into instrument (
> issuerid,
> instrumentname,
> maturitydate,
> issuedate,
> coupon,
> coupontypeid,
> currency,
> amountissued,
> amountoutstanding,
> moodyratingid,
> spratingid)
> values (
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into Instrument failed.'
> goto errhandler
> end
> set @.instrumentid = @.@.identity
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'BB', @.bb_id)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for BB type.'
> goto errhandler
> end
>
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'ISIN', @.isin)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for ISIN type.'
> goto errhandler
> end
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'Cusip', @.cusip)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for Cusip type.'
> goto errhandler
> end
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> end
> close ins_cursor
> deallocate ins_cursor
> drop table #moodystaging
> drop table #spstaging
> return 0 -- success
> errhandler:
> raiserror ('%s %s',16,1,@.errmsg,@.msg)
> if @.@.trancount > 0
> rollback transaction
> drop table #moodystaging
> drop table #spstaging
> return @.rc
> GO
>

Really need help w/ connection manager problem in SSIS

Hello,

I am attempting to set up a Connection Manager for a Flat File Source.

Data in my flat file is like this, for example:

"038188306","03/02/2007"
"038C88328","9A9990846","INFY-SW ","L","Z98ZVL97R","2006-06-02"

Row 1 has two columns, Row 2 has six columns (as delimited by the double quotes).

Format: Delimited

Text Qualifer: {""}

Header Row Delimiter: {CR}{LF}

But when I click on the Columns button, it only shows 2 columns. For example, it shows the first row correctly, but Row 2 shows as:

Column 1

"038C88328"

Column 2

"9A9990846","INFY-SW ","L","Z98ZVL97R","2006-06-02"

which is NOT what I want.

I really need help here.

THANKS

Nevermind, figured it out :-)

Need to skip first row

|||

Hey K108,

I just wanted to know how did yo figured ti out.

Thanks,

Vikt

|||The answer is, all the rows in the text file must have the exact same number of columns. In my text file, the first row is garbage data with only 2 columns. So I just needed to select the option to skip 1 row.

Really need help w/ connection manager problem in SSIS

Hello,

I am attempting to set up a Connection Manager for a Flat File Source.

Data in my flat file is like this, for example:

"038188306","03/02/2007"
"038C88328","9A9990846","INFY-SW ","L","Z98ZVL97R","2006-06-02"

Row 1 has two columns, Row 2 has six columns (as delimited by the double quotes).

Format: Delimited

Text Qualifer: {""}

Header Row Delimiter: {CR}{LF}

But when I click on the Columns button, it only shows 2 columns. For example, it shows the first row correctly, but Row 2 shows as:

Column 1

"038C88328"

Column 2

"9A9990846","INFY-SW ","L","Z98ZVL97R","2006-06-02"

which is NOT what I want.

I really need help here.

THANKS

Nevermind, figured it out :-)

Need to skip first row

|||

Hey K108,

I just wanted to know how did yo figured ti out.

Thanks,

Vikt

|||The answer is, all the rows in the text file must have the exact same number of columns. In my text file, the first row is garbage data with only 2 columns. So I just needed to select the option to skip 1 row.

Reality of remote connection

I'm trying to connect to my sql database across the internet from a C#
application. I am attempting to login using the administrator account that
has been added to the SQL server database and given all the roles. The auth
mode is mixed (it was windows, but I changed it to mixed after reading
around).
So, as a test I try to get Visual Studio 2003 Pro to connect to the
database, figuring it will at least do the connection string correctly. I put
in the IP of the server and the username/password into the new connection
dialog and hit Test Connection.
I get:
Test connection failed because of an error in initialising provider. Login
failed for user 'Administrator'.
So basically, I would like to know:
a) Is this possible? Should I instead be writing a server application that I
connect to on the SQL server machine that connects to SQL Server on my behalf?
b) Is there something simple I am missing? I would prefer to make a direct
connection to SQL Server if possible.
Thankyou for reading. Any help would be appreciated.
Hi
That will work, but either your username 'Administrator' or password are
wrong.
Check those and try again.
Cheers
Mike
"BLiTZWiNG" wrote:

> I'm trying to connect to my sql database across the internet from a C#
> application. I am attempting to login using the administrator account that
> has been added to the SQL server database and given all the roles. The auth
> mode is mixed (it was windows, but I changed it to mixed after reading
> around).
> So, as a test I try to get Visual Studio 2003 Pro to connect to the
> database, figuring it will at least do the connection string correctly. I put
> in the IP of the server and the username/password into the new connection
> dialog and hit Test Connection.
> I get:
> Test connection failed because of an error in initialising provider. Login
> failed for user 'Administrator'.
> So basically, I would like to know:
> a) Is this possible? Should I instead be writing a server application that I
> connect to on the SQL server machine that connects to SQL Server on my behalf?
> b) Is there something simple I am missing? I would prefer to make a direct
> connection to SQL Server if possible.
> Thankyou for reading. Any help would be appreciated.
|||Thanks Mike, I'm happy to know it can work. I even realised that I was inside
the same subnet (live proxy ip was same sub as server) so it shouldn't have
even been domain issue.
I know I have the password correct because I was remote desktoped to the
server as admin and playing with the enterprise manager. My C# / ASP.NET app
running on the same server can interact with my database, I just can't access
it from a different machine directly. The only thing I can think of is that
maybe our proxy server is not behaving, in which case I'm going to install
SQL Server on an internal server so I don't have proxy issues and try it that
way.
Thanks for your help.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> That will work, but either your username 'Administrator' or password are
> wrong.
> Check those and try again.
> Cheers
> Mike
> "BLiTZWiNG" wrote:
|||Well I got it to work. It seems that you cannot log in with a user that has
an assosciated with a windows active directory account even though the server
is in mixed mode authentication. If I log in as an SQL server user only, like
sa, it logs in fine.
Odd.
"BLiTZWiNG" wrote:
[vbcol=seagreen]
> Thanks Mike, I'm happy to know it can work. I even realised that I was inside
> the same subnet (live proxy ip was same sub as server) so it shouldn't have
> even been domain issue.
> I know I have the password correct because I was remote desktoped to the
> server as admin and playing with the enterprise manager. My C# / ASP.NET app
> running on the same server can interact with my database, I just can't access
> it from a different machine directly. The only thing I can think of is that
> maybe our proxy server is not behaving, in which case I'm going to install
> SQL Server on an internal server so I don't have proxy issues and try it that
> way.
> Thanks for your help.
> "Mike Epprecht (SQL MVP)" wrote:
|||it could possible depending on your connection-layer
(ADO,...)
that you need to set a property Use Trusted Connection to
False

>--Original Message--
>Well I got it to work. It seems that you cannot log in
with a user that has
>an assosciated with a windows active directory account
even though the server
>is in mixed mode authentication. If I log in as an SQL
server user only, like[vbcol=seagreen]
>sa, it logs in fine.
>Odd.
>"BLiTZWiNG" wrote:
realised that I was inside[vbcol=seagreen]
so it shouldn't have[vbcol=seagreen]
desktoped to the[vbcol=seagreen]
manager. My C# / ASP.NET app[vbcol=seagreen]
database, I just can't access[vbcol=seagreen]
can think of is that[vbcol=seagreen]
I'm going to install[vbcol=seagreen]
issues and try it that[vbcol=seagreen]
username 'Administrator' or password are[vbcol=seagreen]
internet from a C#[vbcol=seagreen]
administrator account that[vbcol=seagreen]
all the roles. The auth[vbcol=seagreen]
mixed after reading[vbcol=seagreen]
to connect to the[vbcol=seagreen]
connection string correctly. I put[vbcol=seagreen]
into the new connection[vbcol=seagreen]
initialising provider. Login[vbcol=seagreen]
server application that I[vbcol=seagreen]
to SQL Server on my behalf?[vbcol=seagreen]
prefer to make a direct
>.
>
|||Tried using trusted connection, but I'm not a member of the domain (as the
client using the release software will not be either).
I can live with it being in SQL native mode because it works, but I hate not
knowing why these things don't work, or if they ever can. Does my workstation
have to be a member of the domain to be able to authenticate using a windows
AD account?
"gandalf" wrote:

> it could possible depending on your connection-layer
> (ADO,...)
> that you need to set a property Use Trusted Connection to
> False
>
> with a user that has
> even though the server
> server user only, like
> realised that I was inside
> so it shouldn't have
> desktoped to the
> manager. My C# / ASP.NET app
> database, I just can't access
> can think of is that
> I'm going to install
> issues and try it that
> username 'Administrator' or password are
> internet from a C#
> administrator account that
> all the roles. The auth
> mixed after reading
> to connect to the
> connection string correctly. I put
> into the new connection
> initialising provider. Login
> server application that I
> to SQL Server on my behalf?
> prefer to make a direct
>