SQL,tables in a DB: files

Showing posts with label files. Show all posts

Thursday, March 22, 2012

"Wrap" replication files

Is there a way to distribute merge and transactional replication commands/files such that a slight hiccup in the connection does not cause replication to fail? Maybe like wrapping them in a zip or other type of file?No, nothing built in, but even if that were the case that won't prevent problems, what if the connection failed during the transfer of the zip file? There's built-in retry logic in the agents for network failures, is this not working correctly for you?|||What do you mean by built-in? Is this supposed to be automatic? Are there settings or distributor properties/parameters I have to set for this? I am not talking about a total loss of connection - just a slight hiccup in communications. Maybe the files could be buffered somehow during the transfer. Is there a way for a custom application to interact with this process?|||distribution agent should retry 10 times before completely stopping, you can increase this interval. For merge agent, since it's scheduled it should just pick up again where it left off on the next sync, or if you're using rmo, you can always check for a disconnect and resubmit a sync.

Sunday, March 11, 2012

"Paralellizing" file loads

I have a package that reads a table that has a list of files that I need to load into a table that arrive every night. These files range from 50mb to 1.5gb, The entire process to load and transform is taking about 50mins, which is about a 300% increase from our current production environment. However I am looking at ways to improve performance by loading all of the data into the staging table at the same time.

Is this possible, and do you guys think it would improve performance significantly?

Can you provide more details about the source target picture?

So, many files going into a single staging table?

If so, You may want to try having several threads reading from the files and inserting into the target table; perhaps using multiple dataflows.

|||

The source are about 27 files arriving from a customer to a network drive. Currently we start the package when all the files have arrived and we load 1 file at a time into a table using a simple FileConnection->OLE DB Destination.

I have a Foreach Container (files) that loops on an ADO.NET Recordset. Inside the Forloop container it starts a package which loads the file.

So my question is how could I launch that package from within my package "asynchronously"?

|||Unforntunatly, Using a For Each loop will not help you on processecing several files at the time, which I think is the only way to speed the process up.|||

There is a possibility here, Rafael and Jwelch have given a bunch of clues in previous forums to speed up, it all depends and I have clues too,

1) Incase you have a file meant for a single table you can have few parallel dataflow extracts.

2) If there are list of files populated into each table then we can group the file according to the pattern (use wild cards or expression to assign file format) and then run few Parallel extracts using ForEach loop for each of the file groups

3) If you are loading only a single table then parallel insertion wont help, because table lock might apply when rows are getting imported from a file.

Thanks

Subhash Subramanyam

Thursday, March 8, 2012

"Named Pipes Provider: Could not open a connection to SQL Server [53]", but only on th

My connection string (to a remote instance of SQL Server Exress 2005) is exactly the same when the web files are located on my local machine or my remote machine, and while it works fine when I use the local web files, I get the following error when I use the remote files:

"Named Pipes Provider: Could not open a connection to SQL Server [53]"

As you can see, since it works from the local version, I've setup SQL to accept remote connections via TCP/IP and Named Pipes. I'm using IIS7 on the local, and IIS6 on the remote, in case that makes a difference. I have other sites setup the same way, which work fine.

Any thoughts?

Open the configuration manager and set SQL Server to allow remote connections.
This is off by default on SQL Server Express Edition.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Thank you for responding, although as you can see in my question, I point out that this is clearly set up, as I am connecting successfully from my local machine.

However, I should note that I have resolved the problem by replicating the database.
|||

Oops, I didn't read that little sentence, sorry :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Friday, February 24, 2012

"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe" finished and retu

Hello,

We are running Windows Server 2003 SP 1 and trying to upgrade SQL 2000 SP 4 to SQL 2005 using the command line.

The process finishes in under ten minutes. Summary.txt file we have this information:

Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup_<ServerName>_SQL.log
Last Action : ValidateUpgrade
Error String : The installer has encountered an unexpected error. The error code is 2259. Database: Table(s) Update failed
Error Number : 2259

In the log file named SQLSetup_ServerName_Core.log I found the following:

Error: Action "LaunchLocalBootstrapAction" threw an exception during execution. Error information reported during run:
"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe" finished and returned: 1627
Aborting queue processing as nested installer has completed
Message pump returning: 1627

After receiving this info, I can navigate to the setup.bat for the SQL 2005 upgrade and complete the upgrade without error. We are planning on 500 of these, so manual updates is a very ugly concept.

I'd appreciate any and all ideas on where to go from here.

Most Sincerely.

Could you search inside the *_SQL.log for the string mentioned in summary.txt:

"The installer has encountered an unexpected error. The error code is 2259. Database: Table(s) Update failed"

When you find the string, could you copy the lines above and below so we could see the context around the error? Hopefully you could include all the logging statements associated with the ValidateUpgrade action. It should start with a line like this:

<Func Name='ValidateUpgrade'>

Thanks!

|||

Dear R.Green,

I appreciate your help. The results of your request follows. Please let me know if I can help in any other way.

Thanks,

Bill

Function=SAPasswordPolicyCheck
Skipping Action: SAPasswordPolicyCheck (Condition is false)
<EndFunc Name='LaunchFunction' Return='0' GetLastError='0'>
MSI (s) (A4:94) [11:38:44:323]: Doing action: ValidateUpgrade.D20239D7_E87C_40C9_9837_E70B8D4882C2
Action ended 11:38:44: SAPasswordPolicyCheck.D20239D7_E87C_40C9_9837_E70B8D4882C2. Return value 1.
MSI (s) (A4:2C) [11:38:44:339]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI11F.tmp, Entrypoint: ValidateUpgrade
Action start 11:38:44: ValidateUpgrade.D20239D7_E87C_40C9_9837_E70B8D4882C2.
<Func Name='LaunchFunction'>
Function=ValidateUpgrade
<Func Name='SetCAContext'>
<EndFunc Name='SetCAContext' Return='T' GetLastError='0'>
Doing Action: ValidateUpgrade
PerfTime Start: ValidateUpgrade : Tue Nov 14 11:38:44 2006
<Func Name='ValidateUpgrade'>
<Func Name='updateFeatureSellection'>
<Func Name='SqlComponentUpgrade'>
<Func Name='SetCAContext'>
<EndFunc Name='SetCAContext' Return='T' GetLastError='0'>
<Func Name='ProcessHeaderTable'>
<Func Name='ProcessScriptTable'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='ProcessScriptTable'>
<Func Name='GetFileTargetPath'>
MSI (s) (A4!B0) [11:38:44:464]: Note: 1: 2205 2: 3: _sqlSqlUpgradeSequence
MSI (s) (A4!B0) [11:38:44:464]: Note: 1: 2228 2: 3: _sqlSqlUpgradeSequence 4: CREATE TABLE `_sqlSqlUpgradeSequence` (`Action` CHAR(255) NOT NULL, `Sequence` INT NOT NULL, `Param` CHAR(0), `Retryable` INT NOT NULL, `Fatal` INT NOT NULL PRIMARY KEY `Action`, `Sequence`)
MSI (s) (A4!B0) [11:38:44:464]: Note: 1: 2262 2: _sqlSqlUpgradeSequence 3: -2147287038
MSI (s) (A4!B0) [11:38:44:480]: PROPERTY CHANGE: Adding Sql_sqlSqlUpgradeSequence property. Its value is '15000000'.
MSI (s) (A4!B0) [11:38:44:480]: PROPERTY CHANGE: Modifying Sql_sqlSqlUpgradeSequence property. Its current value is '15000000'. Its new value: '30000000'.
MSI (s) (A4!B0) [11:38:44:480]: PROPERTY CHANGE: Modifying Sql_sqlSqlUpgradeSequence property. Its current value is '30000000'. Its new value: '30040000'.
MSI (s) (A4!B0) [11:38:44:480]: PROPERTY CHANGE: Modifying Sql_sqlSqlUpgradeSequence property. Its current value is '30040000'. Its new value: '30050000'.
MSI (s) (A4!B0) [11:38:44:480]: PROPERTY CHANGE: Modifying Sql_sqlSqlUpgradeSequence property. Its current value is '30050000'. Its new value: '30150000'.
MSI (s) (A4!B0) [11:38:44:480]: PROPERTY CHANGE: Modifying Sql_sqlSqlUpgradeSequence property. Its current value is '30150000'. Its new value: '100150000'.
MSI (s) (A4!B0) [11:38:44:480]: PROPERTY CHANGE: Modifying Sql_sqlSqlUpgradeSequence property. Its current value is '100150000'. Its new value: '230871400'.
MSI (s) (A4!B0) [11:38:44:480]: PROPERTY CHANGE: Modifying Sql_sqlSqlUpgradeSequence property. Its current value is '230871400'. Its new value: '245871400'.
MSI (s) (A4!B0) [11:38:44:480]: PROPERTY CHANGE: Modifying Sql_sqlSqlUpgradeSequence property. Its current value is '245871400'. Its new value: '275871400'.
MSI (s) (A4!B0) [11:38:44:480]: PROPERTY CHANGE: Modifying Sql_sqlSqlUpgradeSequence property. Its current value is '275871400'. Its new value: '275971400'.
<Func Name='GetFileTargetPath'>
<EndFunc Name='SqlComponentUpgrade' Return='0' GetLastError='0'>
Added FTE to SqlUpgrade property
Added REPL to SqlUpgrade property
<Func Name='updateFeatureSellection'>
MSI (s) (A4!B0) [11:38:44:636]: skipping installation of assembly component: {7F618CB9-9BCE-4C1E-9E33-59E8A564E456} since the assembly already exists
To perform upgrade setting feature SQL_Replication to be indtalled locally.
To perform upgrade setting feature SQL_FullText to be indtalled locally.
Running:
CollectInstallCases
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 44.44

Complete:
CollectInstallCases
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 44.44
, returned
true

Running:
DefineFeatureActionRules
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 44.44

Complete:
DefineFeatureActionRules
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 44.44
, returned
true

Running:
DefineInstallActionRules
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 44.44

Complete:
DefineInstallActionRules
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 45.45
, returned
true

Running:
AddInstallCaseActions
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 45.45

Complete:
AddInstallCaseActions
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 45.45
, returned
true

Loaded DLL:
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlboot.dll
Version:
2005.90.1399.0

Action "
languageUpgrade_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
" will be skipped due to the
following restrictions:

Condition "differentLanguage_with_1033_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER" returned false.

Loaded DLL:
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlboot.dll
Version:
2005.90.1399.0

Action "
maintenance_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
" will be skipped due to the
following restrictions:

Condition "sameVersion_as_9.0.139906_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER" returned false.

Loaded DLL:
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlboot.dll
Version:
2005.90.1399.0

Action "
skuUpgrade_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
" will be skipped due to the
following restrictions:

Condition "NOT_sameSKU_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER" returned false.

Loaded DLL:
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlboot.dll
Version:
2005.90.1399.0

Action "
virtualization_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
" will be skipped due to the
following restrictions:

Condition "virtualization_NotSupported_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER" returned false.

Action "
warningPatchLevel_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
" will be skipped due to the
following restrictions:

Condition "diffInstancePatchLevel_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER" returned false.

Action "
warningSPLevel_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
" will be skipped due to the
following restrictions:

Condition "diffInstanceSPLevel_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER" returned false.

Action "
warningUpgrade_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
" will be skipped due to the
following restrictions:

Condition "warningUpgrade_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER" returned false.

Running:
versionUpgrade_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 45.45

Complete:
versionUpgrade_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 45.45
, returned
true

Running:
AddFeatureCase_versionUpgrade_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 45.45

Complete:
AddFeatureCase_versionUpgrade_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 45.45
, returned
true

Running:
FeaturePhaseAction
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 45.45

Complete:
FeaturePhaseAction
at:
Info 2006.2006
/
10
/
14
11
:
Info 38.38
:
Info 45.45
, returned
true

Action "
add_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER_SQL_Engine
" will be skipped due to the
following restrictions:

Condition "noFeatureConflict_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER_SQL_Engine" returned false.
Condition "NOT_featureInstalled_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER_SQL_Engine" returned false.
Condition "installActionAvailable_maintenance_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER_SQL_Engine" returned false.

Action "
remove_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER_SQL_Engine
" will be skipped due to the
following restrictions:

MSI (s) (A4!B0) [11:38:45:839]: PROPERTY CHANGE: Deleting AGTACCOUNT property. Its current value is 'CEDNetLive\Roamer'.
MSI (s) (A4!B0) [11:38:45:839]: PROPERTY CHANGE: Deleting AGTPASSWORD property. Its current value is '**********'.
Condition "installActionAvailable_maintenance_MSSQLSERVER SQL 8.0 SQL Server Standalone Product MSSQLSERVER_SQL_Engine" returned false.

Attempt to start service when it is already running
SQL service MSSQLSERVER started successfully waiting for SQL service to accept client connections
Loaded DLL:
C:\WINDOWS\system32\Odbc32.dll
Version:
3.526.1830.0

SQL_SUCCESS_WITH_INFO (1) in OdbcConnection::connect
sqlstate=01000, level=-1, state=-1, native_error=5701, msg=[Microsoft][SQL Native Client][SQL Server]Changed database context to 'master'.
sqlstate=01000, level=-1, state=-1, native_error=5703, msg=[Microsoft][SQL Native Client][SQL Server]Changed language setting to us_english.

Executing External Command
Message type: Progress
10
Message type: Component
Database Engine
Message type: Status
Checking for SQL Connectivity...
Message type: Status
Starting analysis...
Message type: Status
Analyzing 1%
Message type: Status
Analyzing 5%
Message type: Status
Analyzing 15%
Message type: Status
Analyzing 16%
Message type: Status
Analyzing 17%
Message type: Status
Analyzing 18%
Message type: Status
Analyzing 20%
Message type: Status
Analyzing 21%
Message type: Status
Analyzing 30%
Message type: Status
Analyzing 33%
Message type: Status
Analyzing 34%
Message type: Status
Analyzing 35%
Message type: Status
Analyzing 38%
Message type: Status
Analyzing 50%
Message type: Status
Analyzing 51%
Message type: Status
Analyzing 55%
Message type: Status
Analyzing 66%
Message type: Status
Analyzing 67%
Message type: Status
Analyzing 68%
Message type: Status
Analyzing 71%
Message type: Status
Analyzing 83%
Message type: Status
Analyzing 84%
Message type: Status
Analyzing 85%
Message type: Status
Analyzing 88%
Message type: Status
Analyzing 90%
Message type: Status
Analyzing 100%
Message type: Status
Creating report
Message type: Status
Creating report
Message type: Progress
Info 100.100
<Func Name='SqlComponentUpgrade'>
<Func Name='SetCAContext'>
<EndFunc Name='SetCAContext' Return='T' GetLastError='0'>
<Func Name='ProcessHeaderTable'>
<Func Name='ProcessScriptTable'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='ProcessScriptTable'>
<Func Name='GetFileTargetPath'>
MSI (s) (A4!B0) [11:39:18:558]: Note: 1: 2259 2: 3: 4:
<Func Name='GetFileTargetPath'>
Error Code: 0x8007065b (1627)
Windows Error Text: Function failed during execution.
Source File Name: darlib\viewinstaller.cpp
Compiler Timestamp: Mon Jun 13 14:19:43 2005
Function Name: sqls::ViewInstaller::modify
Source Line Number: 137

- Context --

Setting status of unmanaged components and removing unmanaged resources
Failed to modify installer view
1: 2259 2: 3: 4:

Error Code: 1627
MSI (s) (A4!B0) [11:39:18:745]: Product: Microsoft SQL Server 2005 -- Error 2259. The installer has encountered an unexpected error. The error code is 2259. Database: Table(s) Update failed

Error 2259. The installer has encountered an unexpected error. The error code is 2259. Database: Table(s) Update failed
<Failure Type='Fatal' Error='1627'>
<EndFunc Name='LaunchFunction' Return='1627' GetLastError='0'>
Action ended 11:39:18: ValidateUpgrade.D20239D7_E87C_40C9_9837_E70B8D4882C2. Return value 3.
Action ended 11:39:18: INSTALL. Return value 3.
Property(S): ProductCode = {130A3BE1-85CC-4135-8EA7-5A724EE6CE2C}
Property(S): ProductLanguage = 1033
Property(S): Manufacturer = Microsoft Corporation
Property(S): ProductVersion = 9.00.1399.06
Property(S): MEDIAPACKAGEPATH = \Setup\

|||

Thank you for the follow up, we are still looking into this internally to see if we have any ideas. I'm wondering how you are installing SQL Server 2005. Is it from removable media, a network location, or from the hard drive? Did the media come from a download or was it burned from ISO?

The error message is strange because it should list the query that was attempted, but it looks to be an empty string. I'm just wondering if there is a possibility that the point of installation has a corrupt MSI database in some way.

|||

Hi,

Thanks for your efforts. We have a volume license agreement with Microsoft. I copied the two CDs we received under that agreement to an IDE drive on the server that is being upgraded. My template.ini file is pointing to that drive. We are running from the hard drive because we will push this upgrade to the field using LanDesk. To say it another way, we are not sending media to our hundred plus sites.

Please let me know if I didn't answer the correct question. (Something I do more and more as I get older.)

Thanks,

Bill

|||

Hi,

Any word on this? I need to keep this moving.

Thanks,

Bill

|||

Hi BaldManDBA

Any resolution? This is also a SQL Server 2005 Express edition issue too

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

Extract of my log:

- Context --

Setting status of unmanaged components and removing unmanaged resources
Failed to modify installer view
1: 2259 2: 3: 4:

Error Code: 1627
MSI (s) (9C!2C) [21:36:21:171]: Product: Microsoft SQL Server 2005 Express Edition -- Error 2259. The installer has encountered an unexpected error. The error code is 2259. Database: Table(s) Update failed

Error 2259. The installer has encountered an unexpected error. The error code is 2259. Database: Table(s) Update failed
<Func Name='SetCAContext'>
<EndFunc Name='SetCAContext' Return='T' GetLastError='203'>
Doing Action: ValidateUpgrade
PerfTime Start: ValidateUpgrade : Tue May 29 21:36:21 2007
<Func Name='ValidateUpgrade'>
<Func Name='updateFeatureSellection'>
<Func Name='SqlComponentUpgrade'>
<Func Name='SetCAContext'>
<EndFunc Name='SetCAContext' Return='T' GetLastError='203'>
<Func Name='ProcessHeaderTable'>
<Func Name='ProcessScriptTable'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>
<Func Name='GetFileTargetPath'>

In my case, it was simply a case of leave all the defaults during the SS2005 SP2 install, except the instance where (_) default instance had to be ticked instead of a named instance

Please let us know ... (as your solution may be related to all other SQL Server 2005 version/situations)

Alain

|||

Hi,

The server that generated that error was the only one to encounter the error. So, for that one server, we reinstalled SQL Server. All was OK after that. I hope this helps.

Thanks,

BaldManDBA.

"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe" finished and