SQL Server Q&A

Symptoms
When you restore a database, recovery of logical file names of the database can fail, and the logical file names that correspond to the database in the master..sysaltfiles and the <Database_name>..sysfiles tables may differ.
Resolution
When you perform a backup and restore operation with a database that is in full recovery mode, this problem occurs if you modify the logical file names in the database after you back up the database but before you back up the transaction log.

Symptoms
For a database with two or more file groups, if a clustered index is created on a table in each filegroup, an attempt to back up any individual file group may fail with the following 3029 error message:

Server: Msg 3029, Level 16, State 1, Line 2
Database file ‘DB_DataA’ is subject to logical recovery and must be among the files to be backed up as part of the file or filegroup backup.
Server: Msg 3029, Level 16, State 1, Line 2
Database file ‘DB_DataB’ is subject to logical recovery and must be among the files to be backed up as part of the file or filegroup backup.
Server: Msg 3013, Level 16, State 1, Line 2
Backup or restore operation terminating abnormally.
Resolution
Perform a full database backup before you attempt to make file or file group backups. After the full backup, you can back up individual file groups.

Symptoms
When you try to restore a snapshot-based database backup from a file or from a file group in Microsoft SQL Server 2005, the restore process may fail. Additionally, you may receive one or more of the following error messages:
Error message 1

Successfully completed data transfer.
Error message 2

Msg 3283, SevLevel 16, State 1The file “MyDBLog” failed to initialize correctly. Examine the error logs for more details.
Error message 3

Msg 3013, SevLevel 16, State 1RESTORE DATABASE is terminating abnormally.
Error message 4

Errors resulted in failure of the command
Resolution
This issue occurs because of a change in SQL Server 2005.
SQL Server 2000 required the following multiple-step restore process: Restore the file or the file group from the full database backup.The database remains in a restoring state. Restore the transaction log or logs from the log backup chain. SQL Server 2005 tries to restore the log files and the data files in a single step. Some third-party snapshot backup utilities do not support the single-step approach.

Symptoms
Learn about how to use the My.Computer.Network object and its methods to download and upload files across a network in Microsoft Visual Basic 2005. This article contains sample steps and sample code that demonstrate how to use the My.Computer.Network object for these tasks.
Resolution
This article describes how to use the My.Computer.Network object to upload and download files across a network in Visual Basic 2005. By using this object, you can transfer files from a local computer to a remote network resource. Additionally, you can transfer files from a remote network resource to a local computer. To do this, use the following methods in a Visual Basic 2005 application: My.Computer.Network.DownloadFileMy.Computer.Network.UploadFileThe My.Computer.Network.UploadFile method sends the specified file from the local computer to the specified remote host address. The My.Computer.Network.DownloadFile method downloads the specified remote file and then saves the file in the specified location on the local computer.

Symptoms
Adovb.exe is a self-extracting compressed file containing sample code thatdemonstrates how to use ActiveX Data Objects (ADO) within Visual Basic.
Resolution
The following file is available for download from the Microsoft Download Center:
http://download.microsoft.com/download/vb60pro/demo/1/WIN98/EN-US/AdoVB.EXE(http://download.microsoft.com/download/vb60pro/demo/1/win98/en-us/adovb.exe)For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591?(http://support.microsoft.com/kb/119591/EN-US/) How to Obtain Microsoft Support Files from Online ServicesMicrosoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.
Collapse this tableExpand this table
FileNameSizeDateAdoDemo.mdb332KB7/28/97AdoVB18KB8/5/97AdoVB.vbp1KB8/5/97AdoVbEx6KB7/28/97
What Adovb DemonstratesAdovb demonstrates proper use of output and return parameters (for SQLServer), and opening a parameterized recordset for both a Microsoft Access and SQL Server data sources. Also included is a generic template for error handling with ADO code:

Private Sub cmdTemplate_Click()Dim Conn1 As adodb.ConnectionOn Error GoTo VbError’ Trap (non-ADO) error/exceptions’ Create Connection Object (using early binding)Set Conn1 = new ADODB.ConnectionOn Error GoTo AdoError’ Trap any error/exceptionConn1.ConnectionString = AccessConnectConn1.Open’———————-’ YOUR CODE GOES HERE!’———————-’ Successful ShutdownConn1.CloseDone:’ Miscellaneous (graceful) CleanupOn Error Resume NextSet Conn1 = NothingExit Sub’ ADO Error/Exception HandlerAdoError:’ Save Error Information!ErrNumber = Err.NumberErrSource = Err.SourceErrDescription = Err.DescriptionAdoErrorEx List1, Conn1′ Non-ADO Native error/exception handlerVbError:VbErrorEx List1, ErrNumber, ErrSource, ErrDescriptionGoTo DoneEnd Sub NOTES:When using ADOVB with ADO 2.0 or later, you should remove the Reference to Microsoft OLE DB ActiveX Data Objects 1.0, and set a Reference to the latest version of Microsoft ActiveX Data Objects available.When using ADO 2.0 or later, referencing the OriginalValue property of an ADO Field object will give the following error, if the LockType of the ADO Recordset is Read Only:

Run-time error ‘3251′:The operation requested by the application is not supported by the provider.To avoid this error in ADOVB, comment out or remove the following line of code, in the Click event procedure for cmdAccess in the code window for frmADOVB:

List1.AddItem vbTab & “OriginalValue= ” & rs1.Fields(i).OriginalValue

Symptoms
This article describes how to create a shared package configuration file so that you can make configuration changes easily in Microsoft SQL Server 2008 Integration Services (SSIS).
Resolution
Description of a shared package configuration fileWhen you deploy sets of related SSIS packages, it is more efficient to reconfigure these packages by including shared settings. For example, a connection string might be shared across multiple packages to point to a frequently used database server. The connection string may have to be changed when the system is deployed from a test environment to a production environment or in other scenarios.
You can apply new settings to a package at runtime by using package configurations. Package configurations are created by using the Package Configurations tool in Business Intelligence Development Studio (BI Development Studio). By using the Package Configuration Organizer dialog box and the Package Configuration Wizard under Package Configurations, you can configure how XML and database configurations are applied to a package before it runs. Additionally, you can edit these configurations when the system is migrated. This mechanism is designed for use with individual packages. However, you can share a single package configuration file across multiple packages. This enables you to change package configurations in one place.
How to create a shared package configuration fileBefore you create a shared package configuration file, you must first uniformly design the set of packages. You must name every object consistently in the set of related packages that have to be configured together. For example, if you want the shared package configuration file to configure the ConnectionString property of a connection manager that is named “ReportsDB” in one package, every other package that shares this configuration file must also contain a ‘ReportsDB’ connection manager. If packages do not contain objects that are specified in the shared package configuration file, the packages cannot use the shared package configuration file. After you have verified that the packages contain consistent object names, you can create the shared package configuration file. To do this, follow these steps:In BI Development Studio, open the first package in Solution Explorer.In SSIS Designer, click the Package Explorer tab, and then on the SSIS menu, click Package Configurations.In the Package Configuration Organizer dialog box, click Enable package configurations, and then click Add.On the Welcome page of the Package Configuration Wizard, click Next.On the Select Configuration Type page, specify the configuration type, and then set the properties that are relevant to the configuration type.On the Select Properties to Export page, select only the properties that you want to configure across multiple packages.
Note If the configuration type supports only one property, the title of this wizard page is Select Target Property.On the Completing the Wizard page, type the name of the shared package configuration file, and then click Finish.Test this first package to make sure that it works correctly.Open each additional package in BI Development Studio, and then in the Package Configuration Organizer dialog box, enable package configurations.Add the file that you created in step 7. When you are prompted to reuse or overwrite the existing file, click Reuse Existing.