I've been doing some VB6 app development against a SqlExpress database, but have confusion with issues of deployment and configuration. It's my intention to distribute the app and db to client installations, both as stand-alone and network-ready shared access. Here's basically what's occurred thus far:
The database originated as an Access mdb file.
Downloaded and installed SqlExpress, Tools, Manager, and sample database(s).
Used the Upsizing Wizard in Access to create a SqlExpress db.
Attached to the database in Express Manager so I could examine and access the table data.
Hit the data through the application with the following connection string:
Now, some points of my confusion thus far with regard to deployment:
1. Named Instance and User Instance. I assume I'm using a Named instance with the preceding connection string. Honestly, I don't recall doing any kind of adminstative setup/configuration of the db except attaching to it in Express Manager, so I'm confused over why I would need a User Instance for a local copy of the database. Are there setup steps I've missed or did the Upsizing Wizard do them for me? I've read the white paper on user instance usage, but it raises as many questions to me as it answers. I do understand User instance is intended for local copy of the db, even though I'm not using it that way in development.
2. Do I distribute both the MDF and LDF files? I saw mention somewhere not to distribute the LDF log file, but the app doesn't work without it being there.
3. What has to be done for deployment to a server for shared acccess. Aside from installing SqlExpress, and possibly Manager, I'll need to configure for remote connection usage using Configuration Manager or Surface Area Configuation tools. Does this need an administrator to create and establish users accounts, rights, and such?
4. Are there any reference books out on this stuff yet, besides books online, which I don't find satisfactory.
5. Can SqlExpress coexist on Sql 2000 server? I realize SqlExpress is essentially Sql 2005, but our existing server is Sql 2000. Should it go on a different server?
Sorry for the myriad of newbie questions, but I'm a programmer and not a db admin.
I'm going to write an application for a friend's business using VS .NET andSQL Server 2000. I'm confused though as to how I can write everything on mylaptop, then copy to their server. I have an MSDN subscription and did notrealize that I cannot install the Enterprise Edition of SQL Server on XPPro. I can install the development edition, but am I then going to run intoproblems trying to copy it over to their server?My experience lies in Informix / DB2 and Java / JDBC, so this is newterritory for me and I appreciate the help.Derrick Lathem
I'm slowing coming up to speed on configurations and deployment.
I have 2 questions for this thread.
Question 1:
When I create the deployment manifest (the file that gets created when I build) and then run it on the destination machine, there's a step in the wizard that asks for a folder location. The exact page on the wizard is called "Select Installation Folder", just to make it clear what I'm talking about.
What significance does this folder have? I noticed that when I was using the XML config option, the config file appeared there (and nothing else). When I use the SQL Server config option, I didnt see anything going into that folder. Are these the expected results in each case?
The description on that wizard page says, "The installer will install the SSIS package dependencies in the following folder." I'm not sure what this means and if I should expect more than the XML config file to appear in there (in the case when I used XLM config option). What are the "dependencies", other than the config file, that the wizard is saying that the folder will have?
Question 2:
I've been playing with deployment and there's a bunch of "play" packages that are on my test server. How do I clean these up?
I have a SSIS package on my local machine, and would like to deploy it to DEV server. Which files should i be moving to the SQL DEV Server? and where? How do I modify connection managers or is there a way to do that from the Management Studio or some other way? Thanks and I would definitely appreciate some prompt advise.
I've read quite a few KB articles on deploying SQL Express silently using the command line and a .ini file. Unfortunately I still have some questions before I can make my own .ini file. This is for a local sql server.
What is the INSTANCENAME attribute meant to be? If it is different from the server installed on my local machine which I have coded the application on, would it be an issue? What should USERNAME/SQLACCOUNT and SQLPASSWORD be? I can't remember having to setup these for the local database so I can't imagine what it's meant to be set as. What is the PIDKEY?Thank you in advance.
Well I have some queries as a newbie to SRSS 2000 reports deployment.
1) I have to build a VB .NET application with a web interface to display reports in that program. Is it possible? Is there any example codeing floating around which I can take a look? Or how to do it?
2) I have to pass the parameters for the report via a VB text box into the report and the report should be displayed. So the parameter bar in the report as it comes in IE7 should not come. How can I do that?
Or in simpler terms
- How to turn the SQL 2000 reporting features off to allow a simpler report display - How to display the report in a Microsoft Windows Form application.
I've been searching for an answer to my question quite some time now and I've not been able to figure it out yet.
Situation: - I've created a SSIS package containing a bulk insert task. - I've added a package configuration containing the appropriate connection manager (i.e. dev, beta or live) - CreateDeploymentUtility = true - I've copied the deployment folder to our beta server and I started the manifest file to install the package to the sql 2005 server, after that I specified the config file location and changed the value so the approriate connection manager is used. - When I execute the package from the sql server the package doesn't read the value from the xml config file, it uses the connection which was originally specified in the package, whereas when I run the package from my BIDS it is reading the value from the xml config file?
I can't seem to figure out why this is happening? am I missing something here?
Currently within the Configuration manager you are able to set the Target Deployment parameters such as serverurl , folder etc.. However is there a way to set the Datasource within these configuration settings. For example we have Dev,Demo, and Production sql servers that ideally would be set to to switch with their corresponding deployment target areas DEV,Prod,Demo Reporting service servers. However as it stands I find myself having to manually change the datasets to point to different Servers for testing and then back to the production when its ready to roll out.
did I miss some feature that allows me to do this within the vs ide or does this have to be done programatically?
I have 4 packages in one solution. I have created one configuration table in SQL server for all the packages. For each package, I have a different configuration filter in the same table. Everything works fine in the development environment.But when I create a deployment utility and deploy the packages, the values are not picked from the configuration table although I have re-created the configuration table in the new database.It tries to pick up the values from the database used in the development environment and thus it fails. I have changed all the connection strings in the package after deployment but still of no use.
I am not getting where I am going wrong. Any help would be highly appreciated.
We are trying to create a deployment utility for a solution. The issue we are facing is, we are using a single package configuration file and when we try to build the solution to create the deployment utility, the build process fails saying that the package configuration file already exists. THe reason for this is while trying to build, the utility copies the configation fiel for the packages, it copies for one, but for the second onward, when it tries to copies, it fails saying the file already exists.
Any idea how to overcome this, or else any suggestions how to perform the similar steps to create a deployment utility for a solution in which the packages share a single package configuration file.
We deployed the ssis package to SQL server and now trying to configure but it only allow use to change environmental variables there is no option to browse and select XML configuration file. Does this mean when you  are using SQL server deployment mode u can only use environmental variable ?
I have a two questions that hopefully someone can assist.
Question 1
We use the XML file option to store configuration information for our SSIS packages. It seems that the XML configuration files are not being used in all instances even though we specify the file in SQL Agent. From what I€™ve seen, properties like server name and SQL user id are being pulled from the XML configuration file. Other types of references in the configuration file like email addresses for a send email task are not being pulled.
The way I determined this is that I manually edited the configuration XML file on SQL Server. Changes done to email addresses in the configuration file are not used when I run the package via SQL Agent. But changes to server name and/or SQL user name are used.
If we run the deployment wizard and update the email addresses via the Wizard, the changes are reflected in the configuration file and the SQL Agent job picks up the changes. It seems a waste to have to rerun the SSIS migration wizard just to update the configuration XML file.
We are using SQL Server 2005 SP 1. When we deploy SSIS packages, we run the deployment wizard and choose the following options:
SQL Server deployment and check the Validate packages after installation. Specify SQL Server, Use Windows Authentication, and check the Rely on server storage for encryption Update information in the configuration file within the wizard Question 2
When we migrate SSIS packages on the production server, we get the following error message during the Package Validate step: The SSIS logging provider has failed to open the log. Error code: 0x80070005. Could this be caused by differences in the SQL directory structure between the Development and Production Servers? On the Development workstation and the Development server, a directory called C:Program FilesMicrosoft SQL Server90DTSLogProviders exists. On our production server, this folder is located on D:Program FilesMicrosoft SQL Server90DTSLogProviders.
I am getting following error when "CreateDeploymentUtility" is set to true and I try building the solution. It tries to copy a file that already exits in inDeployment folder. I am using Sept. CTP.
I have just finished installing SQL 2005 Ent Edition on Win 2000 Adv Server, SQL2005 SP2, and SP2 Hotfix KB934458. After the installation, I could see and configure all services via SQL Configuration Manager and SQL Server Surface Area Configuration tools. This worked for a couple of days and now both configuration tools no longer detect SQL2005 components. SQL Server Surface Area Configuration issued an error that said "No SQL Server 2005 components were found on specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLAC)". SQL Configuration Manager did not list any installed services. I don€™t know what caused this. Anyone has any idea? Please help! Below is the Installation Report which shows installed components.
===================================
The following components are installed on this server
Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do
thanks in advance Dave
Background:
SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go
USE [ETLConfiguration] GO /****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SSIS Configurations]( [ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL, [PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]
It seems to me, that the best way is to have one Environment Varible containing the name of the SQL Server, so that you can look up the configuration in the SSIS Configuration Table when you run the package.
Is this the preferable way of doing it ? I would like to hear some positive/negative comment of why chosing a configuration type instead of another.
It seems to me that putting all of the configuration in the Environment variable is harder work but most secure (server breakdown vs table corruption/database error...)
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
I am creating a windows application to manage my ssis packages.
My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.
However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.
The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.
Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.
I'm trying to test my ASP.NET application. Everything is working to the point of getting to my login page to open. When I try to login I receive and error that the database does not exist or access is denied. I used the developer version of SQL Server to create the database and installed the MSDE on my test PC. Then I copied the mdf and ldf files to the new PC. Is there something else I should be doing? Or any suggestions?
I created a sql database on my development machine for my .net app. There are whole brunch of tables and stored procedures. Now I'm at the point to test my app on integration environment, instead of recreating each table and stored procedure, is there an efficient way to promote them. (note I use both MSDE version on both machine)
Hi all,I am doing a project that involved with the big volume of data to beuploaded to the SQL server. Due to the limitation at my employer'ssite, I couldn't have the bulk admin right. I am looking at thecommand BCP and wonder if there is any copyright issues if I were touse BCP and to deploy the command?Thanx.
I want install the project in client side .. In installation time i want to access the my server .. How i can make the setup file to access the internet.. please help me.....
I tried to deploy a package to production and failed miserably. The error is pretty simple "Format of the initialization string does not conform to specification". Trouble is, I opened my source on the production machine, generated a new connection string from a new connection manager and guess what, the string comes out EXACTLY the same as the one my package is set to point to at runtime. so, where do I start?
some general stuff. There are ADO and OLEDB connection in the package. Each gets its connection string from a variable. the variable is set in a script task which pulls the data out of an encryped sql server column. when the connection string is displayed via a simple message box, it is exactly what I want i to be, e.g. Data Source=a.b.c.d;User ID=A_USER;password=password;Initial Catalog=a_database;Provider=SQLNCLI.1;Auto Translate=False;
I am guessing that it must be a setting or option on the SQL Servers themselfs, something in its configuration as otherwise the strings are identical so there must be something in the way that one SQl server is not set up to allow connections from another?
I'm working through Ch 3 of "SQL Server 2005 Reporting Services Step by Step" by Stacia Misner, published by Microsoft Press, but I can't get the example report in Ch3 to deploy. I get a long error message, which says this:
TITLE: Microsoft Report Designer ------------------------------
A connection could not be made to the report server http://localhost/ReportServer.
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <html> <head> <title> SQL Server Reporting Services </title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.2047.00" /> <meta name="HTTP Status" content="500" /> <meta name="ProductLocaleID" content="9" /> <meta name="CountryLocaleID" content="1033" /> <meta name="StackTrace" content=" at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load() at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Construct(String configFileName) at Microsoft.ReportingServices.Diagnostics.RSConfiguration..ctor(String configFileName, String location) at Microsoft.ReportingServices.Diagnostics.RSConfigurationManager..ctor(String configFileName, String configLocation) at Microsoft.ReportingServices.Library.Global.get_ConfigurationManager() at Microsoft.ReportingServices.WebServer.Global.StartApp() at Microsoft.ReportingServices.WebServer.Global.Application_BeginRequest(Object sender, EventArgs e)" /> <style> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black} H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt} LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline} .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray} A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none} A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline} A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none} A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline}
</style> </head><body bgcolor="white"> <h1> Reporting Services Error<hr width="100%" size="1" color="silver" /> </h1><ul> <li>The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) </li><ul> <li>Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerRSReportServer.config' is denied.</li> </ul> </ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span> </body> </html> --. (Microsoft.ReportingServices.Designer)
I'm attempting to deploy CLR Stored Procedures from Visual Studio 2005.
I have a VS Database project with stored procedures for all of the tables in our database. (I would really like to keep everything together in 1 DLL), everything seems fine except...
I keep receiving the following error when attempting to deploy to SQL Server:
Error 1 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
After some experimentation I have determined that its because the DLL is too large: 1.8 mb. (I also have a larger one).
I have looked through various documentation sources but have found none concerning this problem other than specifing something aboug a 100ms execution timeout (not sure if this is the same).
Deployment works if I trim down the size of the DLL (i.e., remove procedures from the solution)
Is there anyway to deploy large DLLs to SQL Server without having to break them up? If so, where is the setting to allow a longer timeout period for deployment?
Have my first .NET app deployed to remote host and configured successfully, except for one thing: can't figure out how to export my SSE tables to the SQL server database on the remote server. I tried SQL Server Management Studio Express ... with that, I can access the remote database, but can't access the local database file I produced with VSWDE. What is the appropriate tool to use to export tables from a local SSE database file to a remote SQL Server 2005?
I am trying to add membership to my site. i use visual web developer and the set controls. i.e. login etc. I also used the asp web config tool which is where my test users are. Everything works locally. but when i upload it and try to log on I get this error message: Server Error in '/' Application.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735043 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102 Can anyone out ther please help me with this. thanksNick
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
I have a dev machine with VS2005Pro, WinXPSP2. I create a web site with authentication built with the ASP.Net Configuration Tool in VS2005.
The site, login and all other functionality are fine when tested on the dev machine.
I use copy web site to 'deploy' the site to a W2003R2 server. The authentication process doesn't recognize the password. All other web functionality is fine. With SSMSE I can see that the request is actually being processed by SS2005E.
Is this likely to be an encryption problem due to differing machine keys or something similar? What is the recommended approach to moving ASPNETDB from the dev machine to a server?
Hi. I created an app on my laptop and wish to deploy it to a server. I followed the steps to release the project and I copied the files over to the server. I'm getting an access denied or server does not exist error message. I went into the code behind pages to change the connection string but it still doesn't work. I think it has something to do with the SQL Server name. It's just local. The person that installed it did not change the name. In enterprise manager, it looks like this. (local) (Windows NT) Should the connection string be SERVER/(local) ? Is there a default name I'm not aware of? Any help would be greatly appreciated. Thanks!
Here is the error message:
Server Error in '/' Application. --------------------------------------------------------------------------------
SQL Server does not exist or access denied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
