any suggestions on having one web config that once put on the dev servers uses the dev sql server and when put on prod will use the production sql server?
would like to encrypt it and be done with it. but it needs to recognize the server it's on.
I have a connection class that does this - but i need to use sqldatasource and not objectdatasource.
does anyone know of any potential issues of using a 32 bit sql server 2005 instance used for disaster recovery of a 64 bit sql 2005 cluster ?
thanks
I am migrating from local to Dev,QA and Prod.
I created a .dtsconfig file containing database connection strings to Dev database. What is the "location" on the Dev server where this .dtsconfig file nees to be deployed to??
Thanks for your help.
I'm having an issue to restoring database from prod to report server. I'm getting following error.
When I did Manually I got first error as below.
Msg 233, Level 20, State 0, Line 0 A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
This is the second error
Msg 3044, Level 16, State 1, Line 37
Invalid zero-length device name. Reissue thestatement with a valid device name.
Msg 3013, Level 16, State 1, Line 37
RESTORE DATABASE is terminating abnormally.
Msg 5011, Level 14, State 5, Line 45
User does not have permission to alter database 'XeP', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 45
ALTER DATABASE statement failed.
Script Which I used.
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
[Code] .....
I just start my job as sql server DBA. We have disaster recovery plan. We implement Log shipping . My question after the server failiure, when production server up again ,how to point stand by server to production server?, any help will be appreciated.
View 1 Replies View RelatedHi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies View RelatedHi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
how do you design a dev box?
how do you replicate from prod to dev?
Ideas will be appreacited.
do you get a bak file and restore it?
DTS ?
linkservers?
???
=============================
http://www.sqlserverstudy.com
I have table with column values as below. I need to change the folder name from QA to PROD.
C:QADocumentspdf1
C:QADocumentspdf2
C:QADocumentspdf3
C:QADocumentspdf4
C:QADocumentspdf5
and I need to change the path as below:
C:PRODDocumentspdf1
C:PRODDocumentspdf2
C:PRODDocumentspdf3
C:PRODDocumentspdf4
C:PRODDocumentspdf5
Hi
How to move DTS packages from Dev env to Prod Env.(I know Export /Import).
Thanks in Advance.
for the first time in my long SQL DBA live I see such a behaviours. My tempdb database is growing every damn second since a this morning. Now it reached 30Gb, the log file is empty (217 Mb).
We use SQL 2000 Ent on Win 2000 Advance Server. Running Siebel Call Center (7.5 ver) with about 300 users.
Some users time to time obtain and hold a huge amount Exclusive locks on the tempdb extents
Where do I look for a leak?
Any ideas?
thx
Dim
Hi all,
I've got two SQL Server 2000 (SP ??) instances (on two separate machines; Win Server 2003 Standard) that I've inherited. I want to use one of them as a reporting instance of production for a single ~4GB database, updated nightly.
In other DBMS's I'd set up log shipping or a simple dump-and-load to keep the two in sync, but I'm not very familiar with SQL 2000 (I used to admin a SQL Server 7 back-in-the-day but have been on Sybase ASE, MySQL (blech) and 'Orable since).
Any suggestions to do this easily and (fairly) painlessly?
Would I want to set up replication between the two? If so, which flavor?
-- To me, this seems a bit overkill. Plus I hate to muck with production unless I really need to
Would I use DTS to do this?
-- Seems straightforward but as I understand it, DTS under-the-covers is a bcp-type process, which can be fairly slow.
Or a simple dump-and-load (with copy)?
-- This seems the best option as we're already doing a nightly dump. However, the data will have to be shuffled off to the other server (or some sort of network share set up that it can access) and then a script fired off when the dump is complete. This seems the most "brittle" of the three options (if the dump hasn't finished yet, then the script copy and import will fail, etc.)
Surely this has been done over and over again (searching the archives didn't tell me anything, but the site search tool isn't that great).
Thanks!!
Hello,
I have recently become a release manager for SRSS in our company.
Since then I've been swamped with requests to migrate reports, permissions and subscription lists from development environment to production.
Each time I have to do it manually with a lot of clicks. It is a real pain...
So, may be... may be there is an automation tool out there to help me? Does anybody know?
This tool or s/w package should move a report file along with its permissions and subscription lists from one server to another.
Please advise!
thanks,
I have been using the index tuning wizard to review some of my stored procs,and views. So far most of my indexes have been set up well, but I am curiousas to how they would look under a production system load. I was thinking ofrunning a profile for about 30 minutes or so on the prod system, and thenusing that profile for the index tuning wizard to see what it says.Would this be of value?Can running a profile on a prod system be dangerous?--BV.WebPorgmaster - www.IHeartMyPond.comWork at Home, Save the Environment - www.amothersdream.com
View 2 Replies View RelatedI have a package that uses configurations to override package settings based on what environment the package runs in. The package's configuration entries begins with an initial XML config entry that overrides the package's connection manager to a SQL database that holds the remaining configs in a table. Subsequent config entries then fetch their settings from the table. This package is run from a SQL job.
This all works fine in dev. When I moved everything into prod the packages are not getting configured and are using their values stored in the DTSX files. I've triple checked the XML config file, the tables with the configs, and the packages. There are no error messages. I've added some debuging steps to the package to verify that the configs in the table are not getting into the package.
I've also tried manually changing the configs in the table where the package is set to look if the initial XML config fails to adjust the config database location. The package still fails to see any configs from the table.
What could be different between dev and prod that would produce this situation? Both dev and prod have identical copies of the package and the job and are currently pointed to the same configurations database.
By the way, the other connections in the package work for both source selects and destination inserts. Only the configurations are failing, and again there is no error message.
We have two different SQLServer 2005 databases, one for development and one for prod. I'm pretty new to DTS, and even newer to SSIS, and am working on converting a bunch of DTSs to SSIS.
Our DTS packages essentially were duplicated and edited for production because of the different server names, and some different directory names for sources and destinations. So the dev package would connect to the DBDEV database, and the prod to DBPROD, for example. When I create a package in dev and then copy it to prod, I have to go in and change all of the connections to now point to prod. There are also global variables pointing to various directories that need to be modified. Worse yet, there are also variables to directories set in ActiveX Script Tasks (which are deprecated and so need to be replaced). This is kludgy and error prone. So, since I'm learning SSIS and converting the packages, I would like to make them better.
What is a good way to specify the connections in a dynamic way? That way, when the packages are moved from dev to prod, the prod database can just be specified in one place. In other words, what are some best practices that I should know about? I'm reading this forum, and checking out links I find therein, but I also do better with specific examples (because I am so new to this).
Thank you for any guidance you can provide.
-thursday's geek
For critical systems running SQL 2000 I've always believed the development, QA and production instances should all be the same edition of SQL Server. I didn't want to take a chance of something performing differently in development then in QA and production due to dev being Standard Edition and QA and production being Enterprise Edition.
For SQL Server 2005 would you agree with this approach? I'm only referring to critical systems. Non-critical I am willing to take the chance.
What are your thoughts?
Dave
So, we are about 3 weeks away from going into production, and somehow we failed to give much thought to deploying our RS project into production.
We have over 110 report models that need to be deployed into production, and until now, we just deploy into our dev and test environments using Visual Studio. But, in our production environment, our deployers will not have Visual Studio.
Is there any simply backup/restore method that can be used to move our test environment into production? Please don't suggest a copy of each file one at a time /sigh.
Appreciated,
Scott
Hi,
I currently have a 2000 Ent. production server and a stand by server ready for transaction log shipping.
Is it possible to setup transaction log shipping on a live environment without any interruptions?
I'm currently backing up the log every 1 hour, I'd like to increase to 15 minutes.
Any help would greatly be appreciated.
Thanks,
- Gary
I'm new in the DBA position recently aquired, and I'm having a few issues with MSSQL configuration and administration.
1. I have created a two maintenance plans, one for system databases and another one for user databases, this plans include things such as, Integrity Checks, Optimizations and DB Backups, however the Integrity Checks fail with the error that the DB's must be in single user mode, how can I go about to make this and what are the implications of doing so?
2. There's been a few reports of MSSQL server compromises through UDP port 1433, so I closed it at firewall level, only TCP 1433 is going through, do I really need UDP 1433?
Thank you very much.
Hi All,
I have a query. I want to use sql server authentication
for my asp.net application. I have created a seperate username and
password for the sql server. Now I want to use that username and
password to establish the connection thru web.config. In web.config, I
don't want to give the username and password of sql server. Instead, I
want to get those details in other ways. Please tell me how to handle
the connection string with more security?
Balaji
Greetings,
My connection string looks like this (in the web.config) and is used with many data controls in my application:
<connectionStrings><add name="connection_NAH" connectionString="Data Source=NAH;Initial Catalog=NAH;User ID=xxxx;Password=xxxx" providerName="System.Data.SqlClient" /></connectionStrings>
I'm using vb.net and my code looks like this:Dim myConnection As SqlConnectionDim mySqlCommand As SqlCommandmyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("connection_NAH").ConnectionString)mySqlCommand = New SqlCommand("UPDATE tbMasterOption SET ImageURL = 'test.gif' WHERE ID = 116977")Try myConnection.Open() mySqlCommand.ExecuteNonQuery()Catch ex As Exception Label3.Text = "Couldn't update database record: " + ex.ToString()Finally myConnection.Close() Label3.Text = "Closed db connection"End Try
------------------
The only output I get is from the label3 which says "Closed db connection". Doesn't look like any exceptions are thrown but when I look at the database the record is not updated. Originally I thought my SQLConnection was not constructed correctly but I also tested it by outputting the connection string to Label3 which looked fine. The only other thing I can think of is in the use of ExecuteNonQuery(). Is this the right method or do I need something else? Running the sql query manually works...
Thanks in advance..
I'm using a package configuration to set the value of a user variable which I then use inside my package to build out a certain expression. I got this to work with the XML file option, but I can't get it to work using the SQL Server option.
So this is what Im doing.
(1) I copy pasted the package that works with the XML config option.
(2) In the new package, I deleted the existing XLM config and created a new one, this time choosing the SQL Server option. After I finish, I see that the dbo.SSIS_Configurations table gets created in my database with the value of my variable in there.
(3) I run the package and I see that it works. I'm not sure at this point if it is working because it's reading the value from the config table or from the BIDs environment where I initialized the value.
(4) Anyway, I then go and update the value of my variable in dbo.SSIS_Configurations.
(5) When I run the package again, it doesn't see the new value. What am I doing wrong?
I'm trying to configure ASP.NET2.0 Application to use SQL Server 2000 and yes i did make sure all my setup in configuration manager is setup and the surface area
Whenever i ran the REGSQL .EXE CMD it works until i get to where it suppose to give you a list of data instance to choose from base on ScottGu article in step 4
I'm running SQLEXPRESS ADVANCE
VWD EXPRESS 05
Thanks
Hello, So I am just getting started with .NET, I am using Visual Web Developer 2005 Express andSQL Server 2005 Express on XP Media Center (same as XP Pro) ASP.NET version 2.0.????Anyways, I am having the time of my life figuring out how to connect to SQL Server from within my C#, as follows,(where settings.connection = ".SQLEXPRESS;Database=MyDatabase;Integrated Security=SSPI;" in my web.config)SqlConnection conn = new SqlConnection(Settings.Connection); SqlDataReader rdr = null; try { conn.Open(); SqlCommand cmd = new SqlCommand("select * from mytable", conn); rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(rdr[0]); } } the above yields unspeakable errors, namely <font color="red"><b>Cannot open database requested by login. login failed for user 'computername/SQLEXPRESS'</b></font> , etcAND<font color="red"><b>A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)</b></font> If you have ANY information on the suspect, please, please report it to me. Greatly appreciated!Thanks, Cam
View 1 Replies View RelatedWhich will perform better?
(4) 550Mhz Xeon with 1MB cache and 2GB of RAM
(2) 550Mhz Xeon with 2MB cache and 2GB of RAM
(2) 550Mhz Xeon with 1MB cache and 4GB of RAM
Thanks,
John Fletcher
Hello,
When I installed MS SQL Server 2000 on our client's computer I installed it under a local account using Windows Authentication as the server logon and am now accessing it using a network login (remoting into the same box). The result is that if I change the password for the local Windows account and reboot the server, SQL server cannot be restarted; I get an error:
A connection could not be established to (LOCAL)
Reason: SQL SERVER does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registration properties and try again.
I've changed the registration properties to use SQL Server Authenticaiton instead of Windows to no avail. Whichever connection option I use the server will not start unless I change the local account's password back to what it was when I installed. This works, but our client wants to have that local account's password changed for security/peace of mind. Any help or advice would be appreciated here :)
Thank you
Hey guys,
I have been developing a SQL based data warehouse for financial reporting purposes. I do expect the database to continue to grow over the next few years.
Right now, I am running SQL Server 2000 on an HP DL380 G3 with dual Xeon 2.8GHz, 1.5GB of ECC DDR RAM, 4x 300GB SCSI 10K RPM HDD's.
I would like to replace the server as it is now two generations old and because when there are five people running queries against the data, it starts taking minutes to return the results. When this reporting database hits production, it will be queried by up to 20 people simultaneously and I don't feel the current server will provide results in reasonable amounts of time.
I would like to know what you people think I should be doing to provide an optimal environment for this data warehouse. Should I stick with SQL Server 2000 or move to 2005? Why would you recommend that?
I am thinking of replacing the server with the following configuration: HP GL380 G5, 2x Xeon E5345 Quad-core CPU's, 4GB PC2-5300 ECC DDR2 RAM, 8x 72GB SAS 15K RPM HDD's. Do you think this is an ideal configuration? Too much? Too little?
I would love to hear what you have to say and suggest. Thank you in advance!
Chris.
Hey guys,
I have been developing a SQL based data warehouse for financial reporting purposes. I do expect the database to continue to grow over the next few years.
Right now, I am running SQL Server 2000 on an HP DL380 G3 with dual Xeon 2.8GHz, 1.5GB of ECC DDR RAM, 4x 300GB SCSI 10K RPM HDD's.
I would like to replace the server as it is now two generations old and because when there are five people running queries against the data, it starts taking minutes to return the results. When this reporting database hits production, it will be queried by up to 20 people simultaneously and I don't feel the current server will provide results in reasonable amounts of time.
I would like to know what you people think I should be doing to provide an optimal environment for this data warehouse. Should I stick with SQL Server 2000 or move to 2005? Why would you recommend that?
I am thinking of replacing the server with the following configuration: HP GL380 G5, 2x Xeon E5345 Quad-core CPU's, 4GB PC2-5300 ECC DDR2 RAM, 8x 72GB SAS 15K RPM HDD's. Do you think this is an ideal configuration? Too much? Too little?
I would love to hear what you have to say and suggest. Thank you in advance!
Chris.
I'm trying to get a SSIS package to run as a SQL Server Agent job. The package uses a config file. When I try to add the config file to the job under the Configurations tab. When I click on the 'Add' button, I get the following error:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The EXECUTE permission was denied on the object 'xp_availablemedia', database 'mssqlsystemresource', schema 'sys'.
The user does not have permission to perform this action.
The statement has been terminated. (Microsoft SQL Server, Error:229)
Any suggestions?
As we know Sql Server 2005 provides its ability to deploy clr assembly on it. However my problem is that my assembly has a configuration file. I used ConfigurationManager.GetSection to create some of the object. When I am deploying the project the trouble is that the app.config is missing. I used a class lib project type to create the assembly. Besides changing a storing method, can I still use this config file to reach my goal? Sqlserver.exe.config is not a good idea and it is not workable. The sqlserverexe host cannot find the assembly files.
Thanks very much!
Once I added a linked server on MSSQL how can I remove the linked server, or change its configuration?
tks