Environment Variable
Jul 17, 2006hi,
can you show me how to get the value of an environment variable from a script task?
thanks!
ADVERTISEMENT
Passing Variable Value Through Environment Variable
Feb 15, 2008
Hi All!
I have a parent package that contains two children... The second child depends on the succes of the first child.
THe first child generates a variable value and stores it in an Environment variable ( Visibility - All ) ...After the first succeeds, the second will start executing and will pick up the variable value from environment variable( through package configuration setting )...
Unfortunately, this doesn't work...As the second child picks the stale value of the environment variables...Essentially it assigns variable value not after the first child is finished, but right at the beginning of parent execution...
I tried to execute coth children as Out Of Proc as well as In Proc...The same
Would anybody have an idea how to resolve this problem?
Thanks in advance for any help!
Vladimir
Package Configuration With Environment Variable
Aug 9, 2007Hi,
I have issues with the Connection Manager in the SSIS package when using package configs thru environment variable.
Here goes..
SSIS package1:
Connections used: devcon1, devcon2 - Dev Env and testcon1,testcon2 - Test Env. Now using all four. Ideally either devcons or testcons should reside at a time.
Environment variable:
Pckg_config = <location of config file which has testcon1 and testcon2>
I need to use only devcon1 and devcon2 in Dev env. In test i need to use only testcon1 and testcon2
Hence i set the values of devcons in devEnv.dtsconfig and testcons in testEnv.dtsconfig
Now i remove both testcons from ssis package. If i try to run the Test Env and my testcons which are marked in testenv.dtsconfig are not found as connections in ssis package then the ssis gives error wanting for those connections.
SSIS maintains the connections in the Connection Manager per package. Although internally it is a pool of connections.
Ideally i should be able to play around with the connection at run time. My package now works, if it is deployed with all the devcons and testcons together. However, ideally it should be either devcons or testcons. I am trying to be more explicable to reach to the masses here.
Am i doing something wrong? All your efforts in solving this puzzle will be greatly appreciated. Please participate.
Thanx,Tushar
.dtsConfig ConfiguredValue Contains Environment Variable?
Mar 7, 2006is this possible?
<Configuration ConfiguredType="Property" Path="Package.Variables[User::varFolderName].Properties[Value]" ValueType="String">
<ConfiguredValue>%enviroment variable%</ConfiguredValue>
</Configuration>
it would be really useful. it looks like the .dtsconfig file needs to be maintained on each install independently. This makes maintenance a nightmare. it would be a lot nicer if the .dtsconfig files were more like templates rather than hard coded values to specific system resources.
Package Configuration Using Environment Variable
Jun 28, 2007I am doing SSIS package configuration using environment variable.
I have created a system environment variable that points to the dtsConfig file.
I opened the package and choosen the configuration type as environment variable and specified the environment variable
When I click the next button , it doesn't allow me to choose the configurable property.
Please suggest
Package Configuration + Environment Variable
Jul 17, 2006We are using Package configuration with environment variables. The problem we are having that if we try to open project from other PC (PC 2) it gives the error:
Error 1 Error loading F0005.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. z:visual studio 2005projectssales data martextract to staging areaF0005.dtsx 1 1
We are using environment variable named DWConfig and have configured correct path in each PC. If we edit package configuration in PC 2 and go thru the same procedures without any amendments the errors is removed for that PC and if, again we OPEN that project in PC 1 it gives same error and if we go thru package configuration wizard again error is removed.
Can any one tell me is there any solution of that problem?
Note: Our project is saved on server (neither PC 1 nor PC 2)
regards,
Anas.
Environment Variable Package Configuration
Dec 17, 2007
Okay - this one is driving me batty.
I have a package that uses an environment variable package configuration of value X for a connection string. I close BIDS. I change the value of the environment variable to value Y. I open BIDS and the package, and the value of my connection string is Y. I save my package with the new configuration. if I look at the dtsx file, I see connection string with value Y. All as expected.
I move the package to my server (I've tried Import package from SSMS, using the deployment manifest, and save copy as). On the server, the environment variable is set to value Y. If I run the package or export it; however, the value of my connection string is X!
Does anyone have any suggestions of things to try or some reason that this is not working?
Thanks,
Jessica
Transact SQL :: How To Check For Existence Of Environment Variable
Oct 8, 2015I'm trying to figure out the best way to write a script to deploy environment variables to different servers. To create the variable I'm currently using catalog. create_environment_variable. I want to wrap that in an if not exist statement.I had thought about just blowing away all the variables and recreating them but I thought that wouldn't go over well in prod. I wasn't sure if by deleting the variable, references to the variable would be lost.
View 3 Replies View RelatedPackage Configuration Type - Environment Variable
Feb 19, 2008Hello All -
Have you ever seen the error message below?
Description: The package path referenced an object that cannot be found: "Package.Variables[User::<variable_name>].Properties[Value]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning Could not load package "<package_name>" because of error 0xC0010014.
Basically, I create a package variable under my User Namespace and this variable will tell what server the SSIS is running at. We first create a system variable locally and the SQL Server will have a variable with exactly the same name so that the server name will be evaluated through the package variable/package configurations when the SSIS is executing from a SQL Server job.
This way we do not hard code the server name... We always succeeded on doing that with DTS as well as SSIS packages but just now my package is running into this issue...
Since I did not change ANYTHING in the package, I am guessing this is not programming related and that something was changed in the server. However, the DBA was helpless over here and I have no clue of what this error means.
Any help would be appreciated.
Thanks, Gabriel.
Package Ignores Environment Variable For ConnectionString
Mar 19, 2008We're attempting set the ConnectionString for our configuration database connection manager from an environment variable, but SSIS seems to ignore the environment variable value. Deployment process:
Create the Connection Manager
Create an Environment Variable type configuration with the Target Property: Package.Connections[acConfigDBManager].Properties[ConnectionString]
Build the package
Copy the package and the manifest from the project's inDeployment folder to a folder on the server
From the SSIS server's console, Import the package from the File System.
Run the package, after first inspecting the ConnectionString in the Connection Managers collection
In all cases, the ConnectionString variable for our configuration database is the value in the package at build time.
So far we've tried the following variations:
confirmed that the spelling and case of the environment variable is identical on the XP development computer and the Win2003 server.
restarted SQL Server and SSIS
rebooted the Win2003 server.
built the package with a blank ConnectionString value in the connection manager
re-imported the package, overwriting the old one
deleted the old package before reimporting
renamed the package and imported
run imports from SQL Management Studio from the server console
assign the connection string from an arbitrary system environment variable
assign the ServerName and use an expression to build ConnectionString
What now? Has anyone been able to set a Connection Manager property from an environment variable?
Integration Services :: Environment Variable In SSIS Not Being Recognized In JOB
Jun 16, 2015I've created a SSIS Package and it's connection is based on Environment Variable(please seeprocedure).
Now, I'm trying to create a job that calls this package and it seems that when you view Data Sources, it still pointing to the old server.
But when you open-up the package through BIDS in the same server, it's using the new reference that I have specified in the environment variable (please refer to the first image).
I came across this blog with the same issues as mine. He suggested to re-start the SSIS Service which I already did  but nothing happens. I even re-started the SQL Agent but still no luck.
I'm not sure what else is missing except for re-starting the machine which is the last thing I want to do as this is PRODUCTION server.
Integration Services :: The Configuration Environment Variable Was Not Found
Jul 28, 2008I have a Master Package that executes a number a child packages.
Â
In my SSIS Package Configuration:
Â
1. Â I have an SSIS Configuation table that holds the connection string.
Â
2. An XML Configuration File with a setting of configuration location stored in an enviornmental variable.
Â
3. And finally, an Eveniornmental variable with the setting of ProjectFolderAbsolutePath value, where it is the full path of the project folder.
Â
The project functions normally but everytime I open it I get the following error.
" Warning loading MasterPackage.dtsx: The configuration environment variable was not found. The environment variable was: "EnviorVariable". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid."
SSIS Dynamic Configuration - Environment Variable Problem
Aug 24, 2007Dear all,
I have a problem with SSIS reading an environment variable after deploying the packages to a server. I explain.
I have an Parent Packages ETL_MAIN_PACKAGE.dtsx that reads the child packages from a record set and loops on it to execute them with the Execute Package Task task. The first child package to be executed is called DIM_PERIODIC.dtsx.
On my local machine, the Parent Package is configured to read its database connections from an XML file SSIS_configfile.config located on my C: drive. The path (C:SSIS_configfile.config) to this file is stored in the environment variable BI_ETL.
When I run the Parent Package inside SSIS only machine, the connections are read and the package executes perfectly. Now, I want to deploy the packages on our server.
I copied the XML configuration file to the server C drive, I created the same environment variable BI_ETL and set its value to C:SSIS_configfile.config and I rebooted the machine (in case).
The execution of the Parent package is managed by a stored procedure. I use xp_cmdshell command. The command line generated is :
cmd.exe /c dtexec /file "C:ETL_DeploymentETL_MAIN_PACKAGE.dtsx" /CHECKPOINTING OFF /MAXCONCURRENT " -1 " /SET Package.Variables["P_PACKAGE_PATH"].Value;"C:ETL_Deployment" /SET Package.Variables["P_LOOKUP_PATH"].Value;"C:ETL_DeploymentETL_LOGS" /SET Package.Variables["P_SCHOOL_CODE"].Value;"007"
This command generates an error telling that the Environment variable is not found and it throws this error:
Error : 2007-08-23 18:59:10.25
Code : 0x80019003
Sourse : The configuration environment variable was not found. The environment variable was: BI_ETL. This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid.
End Error
Error: 2007-08-23 18:59:10.25
Code: 0xC001401E
Source: ETL_MAIN_PACKAGE Connection manager "Package Path Execute"
Description: The file name "C:ETL_Deployment" /SET Package.Variables[P_LOOKUP_PATH].Value;C:ETL_DeploymentETL_LOGSDIM_PERIODIC.dtsx" specified in the connection was not valid.
End Error
I run the package on the same server with a command line directly in a DOS window:
C:>cmd.exe /c dtexec /file "C:ETL_DeploymentETL_MAIN_PACKAGE.dtsx" /CHECKPOINTING OFF /MAXCONCURRENT " -1 " /SET Package.Variables["P_PACKAGE_PATH"].Value;"
C:ETL_Deployment" /SET Package.Variables["P_LOOKUP_PATH"].Value;"C:ETL_Deplo
ymentETL_LOGS" /SET Package.Variables["P_SCHOOL_CODE"].Value;"007"
I don't have anymore the error saying that the Environment variable is not found, but I still have the same second error :
Error: 2007-08-23 18:59:10.25
Code: 0xC001401E
Source: ETL_MAIN_PACKAGE Connection manager "Package Path Execute"
Description: The file name "C:ETL_Deployment" /SET Package.Variables[P_LOOKUP_PATH].Value;C:ETL_DeploymentETL_LOGSDIM_PERIODIC.dtsx" specified in the connection was not valid.
End Error
I conclude that the environment variable is not read at all.
Does anybody have an idea on how to solve this problem ?
Many thanks.
Sami
Accessing Environment Variable In Flat File Connection Manager
Apr 11, 2008I'm doing a simple ETL that reads a database table and dumps the content to a text file. The text file will be named Employee.txt. This file name will remain the same across my environments, but I may want to vary the directory location to where I want this file dumped.
So, I defined an environment variable called "DataTargetDir" in all my environments. Now, I want to utilize this variable in the "File name:" box within the Flat File Connection Manager Editor. How do I do this? I'm thinking I can write something like "%DataTargetDir%Employee.txt" in the "File name:" box, but it's not working.
Am I approaching this the right way?
How To Deploy Updated Database From Development Environment To Live Environment?
Nov 16, 2005I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
View 3 Replies View RelatedSSIS Script Task Alters Package Variable, But Variable Does Not Change.
Oct 25, 2006I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task
Mar 6, 2008I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.
Feb 27, 2008I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes
Oct 15, 2007I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO
Scripting: Dumb Q.. How Can I Store A DTS Variable Inside A Script Variable?
Nov 1, 2005Hi
View 7 Replies View RelatedQA Environment?
Jun 12, 2007Good morning,
I am a DBA and my boss recently recommended that we change our QA environment so that we don't have any databases on it unless they are actively being tested. This would be to save hardware costs.
The only issue I can think of at this point is that promotion to our QA environment would take up to 2 hours for some of our larger databases if we have to restore production databases to qa for every QA run.
Is this a good idea? How do you run your QA (full or partial copy of prod)? Any input appreciated.
Thank you.
SQL EE And SQL SE In The Same Environment
Dec 5, 2007Hi!
I work on a company where we today have one SQL Server Standard Edition and Reporting Services (SSRS) and Analysis Services (SSAS) installed on the same server.
Now we want to install SQL Server Enterprise Edition, but still have the Standard Edition installed.
We are gonna invest in one more server.
I wonder if its possible to install the database engine for both versions on one server and then install instances of SSRS and SSAS on the other server?
Can Standars Edition have SSRS and SSAS on another server than the Database Engine?
And is it different versions of SSRS and SSAS between SQL SE and SQL EE, or does SSRS and SSAS get the SQL EE benefits if the database engine is SQL EE?
Thanks in advance!
Debug Error - Object Variable Or With Block Variable Not Set -
Feb 15, 2006I keep getting this debug error, see my code below, I have gone thru it time and time agian and do not see where the problem is. I have checked and have no NULL values that I'm trying to write back.
~~~~~~~~~~~
Error:
System.NullReferenceException was unhandled by user code Message="Object variable or With block variable not set." Source="Microsoft.VisualBasic"
~~~~~~~~~~~~
My Code
Dim DBConn As SqlConnection
Dim DBAdd As New SqlCommand
Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString
DBConn = New SqlConnection(strConnect)
DBAdd.CommandText = "INSERT INTO D12_MIS (" _
& "CSJ, EST_DATE, RECORD_LOCK_FLAG, EST_CREATE_BY_NAME, EST_REVIEW_BY_NAME, m2_1, m2_2_date, m2_3_date, m2_4_date, m2_5, m3_1a, m3_1b, m3_2a, m3_2b, m3_3a, m3_3b" _
& ") values (" _
& "'" & Replace(vbCSJ.Text, "'", "''") _
& "', " _
& "'" & Replace(tmp1Date, "'", "''") _
& "', " _
& "'" & Replace(tmpRecordLock, "'", "''") _
& "', " _
& "'" & Replace(CheckedCreator, "'", "''") _
& "', " _
& "'" & Replace(CheckedReviewer, "'", "''") _
& "', " _
& "'" & Replace(vb2_1, "'", "''") _
& "', " _
& "'" & Replace(tmp2Date, "'", "''") _
& "', " _
& "'" & Replace(tmp3Date, "'", "''") _
& "', " _
& "'" & Replace(tmp4Date, "'", "''") _
& "', " _
& "'" & Replace(vb2_5, "'", "''") _
& "', " _
& "'" & Replace(vb3_1a, "'", "''") _
& "', " _
& "'" & Replace(vb3_1b, "'", "''") _
& "', " _
& "'" & Replace(vb3_2a, "'", "''") _
& "', " _
& "'" & Replace(vb3_2b, "'", "''") _
& "', " _
& "'" & Replace(vb3_3a, "'", "''") _
& "', " _
& "'" & Replace(vb3_3b, "'", "''") _
& "')"
DBAdd.Connection = DBConn
DBAdd.Connection.Open()
DBAdd.ExecuteNonQuery()
DBAdd.Connection.Close()
Transact SQL :: Insert Values From Variable Into Table Variable
Nov 4, 2015CREATE TABLE #T(branchnumber VARCHAR(4000))
insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
24 Hour Environment
Sep 29, 1999I have about 23 SQL servers running 6.5 SP3 or SP5a in a 24 hour environment. Most of the activity takes place between 6am and 11pm, with few transaction after 11pm. What is best to do with the main DB's transaction log, have the truncate at checkpoint option checked OR back up the transaction log a couple times a week? The Database is backed up every 6-8 hours.
Thanks in advance for your opinions/help.
LN
DTS Environment Variables
Nov 21, 2005I have about 40 DTS packages that I want to run against three different databases on the same server. Can someone suggest an easy way to run these jobs and differentiate each time which SQL or INI file to use.
Thanks,
John Shaening
The Database Environment
Jan 14, 2004Hello, everyone:
What is difference between testing environment and product environments for MS SQL Server? Thanks a lot.
YTZ
SQL In A Wireless Environment
Mar 12, 2007Jimmy writes "We use a software that stores its database in SQL. We currently have a server box running Windows 2000 Server with SP4 and SQL Server 2005 with SP2 which holds the database. The server is wired in but we have several other stations that connect to the database wirelessly.
The problem is with our wireless stations.
When a wireless client travels from one access point to a repeater, it'll drop the connection for that moment. Once it picks up the wireless connection again, it requires closing out of the software and going back into it so it reestablishes the SQL connection to the server.
Is there a solution for a seamless reconnection with SQL for our wireless clients? Is this a problem with our software or SQL? Please advise. Thank you."
SQL Developer Environment
Nov 6, 2007Hi Guys,
Im new to SQL Server 2005.
In our organization, I was tasked to implement and maintain
MS SQL 2005 database.
We have several system developer and they are using MS SQL 2005
as a backend database.
Currenty, they are responsible for designing their own databases required by their application.
My concern is that I want to restrict access to my SQL server that only the DB administrator can access the
database server.
My question is that, how they can design their databases without accessing the database server. Is it
possible for them to access SQL Server with some restriction? I mean, I want to impose some security measures
that they can only access objects within their scope.
Our current scenario is that they have MS SQL Express version installed in their pc and from that terminal
they can design and test their application and after everything is in place, Its me who will migrate their
respective databases for production in MS SQL 2005 server.
Hoping you can provide me the best alterntive to this query.
Thanks a lot.
Development Environment Needs?
Jul 20, 2005Trying to figure out what development enviroment we need in order todo the following:- develop a non-native SQL server stored procedure;- call a web service or java program from the stored procedure;- return static values;- call the stored procedure from a view.How do I get a hold of the right tools and what do I need to put thepieces together?Obviously, I've not used SQL server and I'm looking for the basicstarting point.Thanks!
View 5 Replies View RelatedRDA Setup Environment
Aug 23, 2007
Hi,
I'm currently using the setup below for my RDA testing. Is there any problem with this setup?
connected connected
Client Apps (SQLCE 3.0) -----------------> Windows Server 2003 (Web server) ----------------> Windows XP (SQL2005)
previously was using windows XP to act as web server and have SQL server reside inside the same machine but having 10 connection limitation from windows xp where only 10 client can connect to the machine. The current setup moves the web server out to windows server 2003 and i believe it solves the 10 concurrent connection limitation but how about the SQL server? is it limited to 10 connection from windows server 2003 also? (Windows XP is my testing server, Windows Server 2003 contains important test data for the whole company) . I tried with concurrent RDA pull for 40 client concurrently with 1 pc having 10 client apps but obtain not really satisfying results. Pulling one table (20Mb) needs 1 to 2 mins through LAN networks but pulling concurrently with 40 clients takes up to 20 - 30 mins.
Any advice? Thank you and have a nice day.
Regards,
Ben
SQL Express Environment
May 5, 2006Novice SQL Express User...
Now that I've installed SQL 2005 Express Advanced, shouldn't there be a DB Environment I can work with? There must be a configuration mistake because I can't see the "container?" Prior to SP1 I had SQL 2005 Express installed and I could open and use SQL; however, once I loaded SP1 that stopped. I thought if I load the "Advanced" I would be "back in business."
Thanks,
Tony
SQL Hospital Environment
Sep 25, 2007i am working in hospital environment. i am a novice in SQL. i have an urgent issue to answer which is around numbering the order of theatre event for the same patient based on the into theatre time. Patients can go to theatres several times in the single hospitalisation. we need to order as 1, 2, 3 and so on based on the into theatre time which is date/time format. your help is much appreciated. thanks.
View 5 Replies View Related