I have three variables that are stored in an SQL Server configuration table. When I execute the package, they do not seem to be getting updated. At what point do the configurations get loaded?
View 4 Replies
Hi,
How to access "Package Configuration" variables values inside the VSA.
For Ex:
When I create the Package, at the Package Configuration Level I have created 2 variables. One is for MDB file Location and another is for SQL Server configuration details.
Now, When I move from Dev. server to Prod Server, the environment would change therefore I want to access these variables inside the design script (VSA).
Anyone could advice in this.
Regards,
Hi ..
I need some information on the package configuration files. i.e how to set the configuration parameters from outside the packages. Pls provide me some detailed information along with examples. I want to make my packages to read paramaters from the configuration files as we have in .NET(web.config)////
Secondly I want to know how to play well with variables.. i.e setting a variable.. reading a variable.. passing a variable in the SSIS package dataflows and datatasks.. respectively. Please provide some infomation and a good exam[ple on it I need
Hi,
We can set the connection parameters using Variables as well as thru Package Configuration. My question is, if we use both to pass value for a connection string, which one will take precedence or in other words, which value will be used?
Advance thanks for your help.
It is pretty easy to create the package configuration to the shared, never-changing path to XML file. But to get this to work, I have to add a bunch of package variables which are driven from the configuration. I then use expressions to compute actual paths to source files and packages.
This business of copying a block of variables from one package to another is extremely tedious, and at least to me, something of a maintenance issue. I have found if I get ahead of myself and forget to create the variables first, then Visual Studio is NOT very forgiving, and will complain repeatedly just prior to crashing without the offer to save anything. If I add a new variable, it is like an easter egg hunt. This does NOT feel very single point of maintenance when variables must be used in this way to really effectively apply configuration, and it is so tedious and error prone, and unforgiving. I seem to spend a great percentage of time copying the names and datatypes and values of variables ONE AT A TIME. What is that all about?
I hear people telling the praises of configurations, but I must be missing something.
With DTS I used a system where global scope settings were "injected" into packages as they ran, and I never had to worry about anything, and it was damn hard to goof it.
Please someone, evangelize me!! Are people bypassing VS and using NOTEPAD on the packages or something like that to save time?
Hello,
We have an existing OLD System in SQL Server 2000 DTS Packages.
The Whole application runs on DTS.
There are several Packages which are called from a Master Package. Each Child packages have their own Global Variables. Most of them are the File Location variables to have the Source Location of the Input Data, mainly from the Excel Files.
Now, even if the Global Variables are there to change whenever they want to change the Locations of the Files, they have to goto each child package and change the variable themselves.
To resolve this issue, they want a configuration File (INI) / Table which would store those Variables. My thought is to read from that File/Table and Update all the packages' global variables through an ActiveX Script as the First Step of the Master package. That would eliminate the need of changing anything in the existing System.
But the Problem is the management (PM / DBAs / Team members) have different views to store the Configuration data.
1. Some wants it into a Different Database, having one table for this application so in future they can also add another table for some other application.
2. Some wants to store it in a Table in the Same Database of this Application.
3. Some wants to save it as a INI file.
As i'm the one who's going to really implement it, they have asked me to research for a best solution out there.
so I request to help me to decide which is a good solution and why.
Best Regards,
Deepak
I am getting following error when trying to install SQL express 2005 on XPSP2.
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine SIGMA-805539A79 Error:2147944122 (0x800706ba).
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=70342
I tied re-installing WMI using http://blogs.msdn.com/jpapiez/archive/2004/12/09/279041.aspx link but could not get it working.
Do i need IIS installed? Its not installed on this box...
please suggest something... i am stuck...
Thanks,
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
DEV
Analysis Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Database Engine
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Reporting Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
QA
Analysis Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Database Engine
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Common components
Integration Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Notification Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Workstation Components
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
===================================
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View 3 Replies View RelatedI been asked recently in the interview thatHow can I detect changes automatically in the SQL Server Database when anything is updated, deleted or inserted?if anyone can help me in this that will be really great.
I dont actually know whether I can ask this here but I wanted to know the answer for this and thought this might be right place to ask?
sorry if I am wrong?
Thanks in Advance
Client issues query which sends out individual requests to the 2 OLAP servers that are load balanced. The client evaluates the versions of the returned record sets to ensure the consist data being returned for one single query. Otherwise, this error will be seen:
The cube has been updated by the server the data is now obsolete.
These servers sit behind a cisco 11506 CSS with load balancing based on balance type: least busy server, also persistence based on cookies.
My developer says this worked fine for a long time then just 'started happening'.
any suggestions are appreciated.
We are getting data feed from Oracle database in our project. Everyday we will need to track if any rows got inserted/updated/deleted in the source and get that update right into our data warehouse.
Currently we are taking a dump of the required table (as it is) to our staging DB and comparing it with previous day data to track the changes (column by column comparison). This approach is working currently but has performance bottleneck. There is no tracking column (eg. last modified date or time) in source that will give us any idea of what got changed. Also there is no identity key or primary key in the source data.
Is there a way in SQL Server to get that inserted/updated records only instead of comparing column by column to track the changes?
currently i m developing a web application with using sql server 2005
and i was testing yesterday a sql update query with sql server management studio.
in my update query i forgot to put where condition and now all the rows of table are updated. is there any solution to undo this and retrieve all rows back?
Regards Selena
Dear friends, We have a server put at US side and we are trying to load the data into another related database in India site, now the thing is that we want to update India data at an interval of 5 mins?
Is there any way to do it without effecting the performance of the US server?
thanks,
Hello all,
I am trying to change values of two tables in my sql server 2000 database. When one of the tables is modified in some way like adding/updating a record, I need a trigger procedure to copy this new data from the first table to the second. Problem is, how do I get the newly inserted or updated data from the first table? How do I specify that I only want the data which caused the trigger to execute? Anyone know?
Thanks,
Bob
I am trying to create an aggregate table where the value is a rolling sum. Type a on date 1 is the sum of the values in the main tbl. Type a on date 2 is the sum of values for type a on date 1 and date 2. Is this possible? I have been trying update t sql with sum(case where date <= date) statements but can't get it to run.
create table main_table (type nvarchar(10), date int, datavalues int);
insert into main_table
values('a', '1',3);
insert into main_table
values('b', '1',4)
[Code] .....
I have a table with constantly changing data - stock.
I want to start monitoring the value of stock at the end of each month. I can do this with a simple query, export the results to Excel and store on the network.
My question is:
Are there better ways to do this with SQL Server?
I thought of a monthly "job" that does the query and outputs to a file. (Need to be able to look at each month separately though for trend monitoring.)
Then wondered, if I should have an extra table to store the data and write queries on that in the future?
Hi,
I was wondering, is it really true that in Sql Server 2005, there is no updated date -property for stored procedures? There is a created date -property, but it's not useful, because stored procedure's get updated often. This is a huge disadvantage for me, and I was really waiting for ms to fix this problem after sql server 2000, but no. Does anyone figured out any solution to this problem?
Br,
Riika
Account getting wrongly updated due to concurrencyWe are using Sql 2014 and storing data in customer_account table for customer account details however we are experiencing wrong value insertion during concurrency ..pls find the code
Declare @date datetime2(7)
Declare @InsDate datetime2(7)
SELECT TOP 1 @Amountremaining =Remaining ,@Date=Datetime                                                           Â
 FROM <customertaccounttable>                                                             Â
Â
[code]....
How can i find the users of the table who updated it last (or) Owner of the table not the schema
View 4 Replies View RelatedHi --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
http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/24/6843.aspx
Wizard results:
Name:
ETL
Type:
SQL Server
Connection name:
ETLConfiguration
Any existing configuration information for selected configuration filter will be overwritten with new configuration settings.
Configuration table name:
[dbo].[SSIS Configurations]
Configuration filter:
PT_CUST_ABR
Target Property:
Package.Variables[User::gsPreLoad].Properties[Value]
Package.Variables[User::gsPostLoad].Properties[Value]
Package.Variables[User::gsLoad].Properties[Value]
Package.Variables[User::gsFlatFilename].Properties[Value]
Package.Variables[User::gsFileName].Properties[Value]
Package.Variables[User::gsCDOMailTo].Properties[Value]
Package.Variables[User::gsCDOMailSubject].Properties[Value]
Package.Variables[User::giRecordCount].Properties[Value]
Package.Variables[User::giFileSize].Properties[Value]
Package.Variables[User::giBatchID].Properties[Value]
Package.Variables[User::gdFileDateCreated].Properties[Value]
Package.Connections[MyDatabase].Properties[ServerName]
Package.Connections[MyDatabase].Properties[InitialCatalog]
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]
Initially I had designed ETL using Dataflow task in SSIS , No I have converted into Store procedure using merge statement.
I am getting new records inserted, source records and deleted row count when I am running sqp manually.
In ssis simple I have used the rowcount transformation to capture the records.
below variable i have declared in SSIS Package.
User::etlArchiveLogId
User::sourcerecords
User::newrecords
User::changerecord
now I am incremental loading using Stored procedure below is the sp which is executing when task success and logs the records.
ALTER PROCEDURE [dbo].[usp_LogArchiveBBxEndTime]
@EtlArchiveLog_Id int,
@RowsSource int,
@RowsNew int,
@RowsChanged int,
@Sucessful bit,
@Description varchar(500)
[code]....
Now I want to log the variable records which I have declared in my stored procedure .how can I log it in table?
I wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'
View 2 Replies View Related Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
I'm working on databases where statistics of some indexes (tables) are changing too frequently. Once I update them manually, one minute after they get 10-20% change, and five minutes after they get over 100% change. Tables get updated very frequently (multiple times in a second).
When I run a query to read from sys.stats, sys.dm_db_stats_properties and other dynamic views, I see that they were last updated when I did it manually, but the change rate overpassed the 500+20% (tables have multiples of 10K rows). Auto create and update statistics are set to true on all databases, and I don't know why sql server does not do that automatically.
I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
At present I have:
CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)
[Code] ...
However this doesn't appear to bring back the procedure that triggered the update...
The value the trigger should update on is document_status = 0
DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]
[Code] ....
We installed SP1 for SQL Server 2014 this past weekend and got this error message in the logs. I found that if you set the db to read-write, it updates the system objects, even after SP1 has completed. Then you can set it back to read-only. I'm just posting this so other people can find it on the internet, as I wasn't able to find it specifically.
Error Log Entry:System objects could not be updated in database 'x' because it is read-only.
Problem: After installing SP1 for SQL Server 2014 you will find this message in the error logs saying read-only databases could not be updated.
Solution: Simply set the db to read-write and the system objects will get updated, long after SP1 was installed.
ALTER DATABASE [x] SET READ_WRITE WITH NO_WAIT
Then set it back to read-only:
ALTER DATABASE [x] SET READ_ONLY WITH NO_WAIT
You should then see these log entries:
System objects could not be updated in database 'x' because it is read-only.
Setting database option READ_WRITE to ON for database 'x'.
Starting up database 'x'.
CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:28.143 (local time). This is an informational message only; no user action is required.
Synchronize Database 'x' (129) with Resource Database.
Setting database option READ_ONLY to ON for database 'x'.
Starting up database 'x'.
CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:29.888 (local time). This is an informational message only; no user action is required.
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...)
Let's have some comments
hi ; everybody
i have almost installed sql server 2005 enterprise edition on windows xp sp2 and i havent connected to any server .i have taken error:40 with my all attempts.tcp/ip protocol have been enabled on sql server configuration manager.i have tried to start sql server surface area configuration but every my attempt have been resulted a fault which is below.
[No SQL Server 2005 components were found on the specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLSAC)]
i dont suppose that i did something wrong when i setupped.because i read every article on the internet about how to make setup.also; i setupped numbers of times on my pc.
but i didnt accomplished.
i thank for any help.please; give your solution explicitly.
I'd like to capture the change event when it happens and have both a record posted to a table and an email notification to the DBA.
View 7 Replies View Relatedhi all
i get the following message error when installing sql server 2005 express :
<< sql server setup could not connect to the database service for server configuration the error was :[microsoft][gestionnaire de pilotes odbc] source de données introuvable et nom de pilote non spécifié >>
is there any help
thanks and best regards
OS: XP-SP2
Not able to install SQL Express 2005.
------------------------------
SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.
Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29515&EvtType=lib%5codbc_connection.cpp%40Do_sqlScript%40OdbcConnection%3a%3aconnect%40connect%40x80090325
Needles to say, above link is useless..
I had managed to install previously, but ran into problems while trying to upsize an MS-Access database, I've tried everything from creating certificate to hacking away at the registry...Log-on with a different account, etc...Anybody knows how to get around this?
Here's part of the log file:
Microsoft SQL Server 2005 9.00.1399.06
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sat Jun 03 20:09:53 2006
Machine : YANICK
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install : Successful
Log File : e:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0005_YANICK_SQLSupport_1.log
--------------------------------------------------------------------------------
Machine : YANICK
Product : Microsoft SQL Server Native Client
Product Version : 9.00.1399.06
Install : Successful
Log File : e:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0005_YANICK_SQLNCLI_1.log
--------------------------------------------------------------------------------
Machine : YANICK
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.1399.06
Install : Successful
Log File : e:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0005_YANICK_SqlWriter_1.log
--------------------------------------------------------------------------------
Machine : YANICK
Product : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Install : Successful
Log File : e:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0005_YANICK_MSXML6_1.log
--------------------------------------------------------------------------------
Machine : YANICK
Product : SQL Server Database Services
Error : SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.
Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
--------------------------------------------------------------------------------
Machine : YANICK
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.00.1399.06
Install : Failed
Log File : e:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0005_YANICK_SQL.log
Last Action : InstallFinalize
Error String : SQL Server Setup could not connect to the database service for server configuration. The error was: {Microsoft}{SQL Native Client}SSL Provider: The certificate chain was issued by an authority that is not trusted.
Error Number : 29515
--------------------------------------------------------------------------------