Transact SQL :: Get Windows Environment Variables
Aug 31, 2015
I am migrating the BE of an Access app. to SQL server 2012. I need to get the user's login name (Windows Authentication login). This can be done using xp_cmdshell, but, xp_cmdshell is considered dangerous and I wouldn't be able to run it once I deploy the app. to the company servers (currently I have SQL server on my computer and as an admin I can enable xp_cmdshell to run, but IT doesn't allow it in company servers for security reasons).
Another question, is it possible to send data from the logged in user from Access to SQL server? What I need to do is let SQL know the username of the logged in user, then, use it to filter the data on SQL. Idea is that user can only run queries for his data (he can't view other user's data unless he is a manager or an admin (currently the app. in Access logs the user in automatically if his Windows Domain username is found in the user's table, and set's his role found in the Roles table). It is this functionality that is giving me some problems to migrate to SQL.
I created a function that uses the System_User SQL built-in function, this retrieves the SQL login username, but, the app. uses 1 SQL local account to connect to the server, so in essence it doesn't work as I need the Windows Domain account username.
View 10 Replies
ADVERTISEMENT
Nov 21, 2005
I 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
View 1 Replies
View Related
Jul 6, 2007
Hi all,
does anybody know if it is possible to use environment variables when calling dtexec utility?
I'd like to run packages stored on server's file system from directory that I've had specified in an environment variable called SSIS_PackagesPath.
Now, I'am trying to write dtexec command, where path to the actual SSIS package would be concatenation of environment variable (i. e. path to package directory) and name of package itself (written explicitly). Is this syntactically possible?
The reason behind is to be able to easily modify package storage directory for multiple scheduled jobs that run SSIS packages.
Any other ideas are hapilly welcomed.
Thanks,
Marek
View 3 Replies
View Related
Feb 27, 2006
when using a raw file destination it would be nice to be able to use an environment variable for the filename property.
like
%my_extract%data.txt
instead of
c:my_extractdata.txt
View 23 Replies
View Related
Sep 9, 2015
Is it possible to export the environment variables for an SSIS 2012 project? And if so how is it achieved?
View 2 Replies
View Related
May 5, 2015
I noticed accidentally today on my PATH and discovered that I have several variables for different sql versions. I can understand this as I have upgraded from 2008, to 2012 then 2014. Here are the list of variables
C:Program FilesMicrosoft SQL Server100ToolsBinn;
C:Program FilesMicrosoft SQL Server110ToolsBinn;
C:Program FilesMicrosoft SQL Server120DTSBinn;
C:Program Files (x86)Microsoft SQL Server120DTSBinn;
[Code] ....
Can I delete all variables related to older versions safely without any issue.
View 13 Replies
View Related
Feb 29, 2008
Hi all,
We're just in the middle of performing our first release of SSIS packages through various environments.
The way we are set up currently is the developer will check the package(s) and related config files out of source control, develop on their own machine and check everything in again. Then we deploy the packages consecutively to the Dev, Tst and Prd servers.
We are going down the path of using one environment variable for every config file. some packages share config files (e.g. we've only one config file for each database or ftp connection etc.) and some config files are package specific (error log file connections and success/failure e-mail sources etc.).
What we want ideally is a script that we can check into source control that will create the environment variables on a server at deployment. The "set" command at the command line can be used to change the value of an environment variable, or to create a session-specific variable, but not to create environment variables.
So far the only method that we're using is manually typing in the environment variable names via control panel and copying and pasting the paths into the value fields. Given that we're deploying potentially hundreds of config files, it's obvious that this new-fangled GUI point-and-click and copy-and-paste method of deployment is absolutely foolproof and totaly not prone to any error whatsoever.
Please tell me there's a way to create and set environment variables without going through control panel. running a script or something to do it automatically will: Ensure that each environment is set up accurately and identically, eliminating human error. Ensure that when a developer checks out a package to their local drive, although they may have to change the variable values, he can at least create the relevant variables without having to type them in. Enable efficient migration to another new server (for example during Disaster Recovery).
Can anyone point me to some example scripts at all?
Kind Regards,
Andrew.
View 4 Replies
View Related
May 27, 2014
I have deployed a project with multiple packages to SSIS 2012 db. I am able to configure the project parameters fine. But, I am not able to replace the package variable values with the 'Environment' variables.
View 3 Replies
View Related
Sep 25, 2014
Any fix for the seemingly random sort order of the variables in the dropdown list when configuring parameters and connection managers in the SSISDB catalog?
I imported all of our connection strings into an environment (about 200 of them). They were inserted in alpha order and the ID values within the internal.environment_variables table shows them in order as well, by ID and by name.
When I run profiler and capture the command that retrieves them and run it in ssms they are in order but in the dropdown they seem random.
There are no values within any of the tables that accounts for the order they are in.
If a package has 5 connections you need to go through the unsorted list 5 times to find them.
Sometimes you get lucky and they are in the first 20 or so.
I know I can write a script, just wondering if there is a fix for the sorting.
View 2 Replies
View Related
Feb 10, 2014
I have SSIS 2012 Enterprise, using catalog deployment and have more that 50 environment variables for connection to databases across my enterprise.
The problem when i go to configure the packages after deployment and pick the proper env variables, that are not sorted, so i have to browse all entries in order to find the proper entry in environment variables.
View 1 Replies
View Related
Jan 29, 2008
Hi,
I have a small requirement because of which i am facing some migration problems.
For changing the variables say InitialCatalog and Custom logging destination path, i am making use of XML configuration files. Till here i have no issues.
But problem starts here. Basically DTS package is called by calling a stored procedure from a front end application.
Within the stored procedure the package location say (D:Packages<packagename.dtsx>) is being hard coded due to which i need to change the package
location paths each time when i am migrating the stored procedures to Testing and Production environments.
Is there any efficient way of avoiding the hard coding of the package paths using Windows Environment vars or something else.
If so please help me out.
Any help would be greatly appreciated.
create procedure spexecDTS_Pkg
@g_p1 varchar(50),
@g_p2 varchar(50)
AS
declare @jid uniqueidentifier
declare @cmd varchar(4000)
SET @cmd = '"C:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /F "D:Packages est.dtsx" '
SET @cmd = @cmd + ' /SET "Package.Variables[User::userid].Properties[Value]";'+'"'+@g_p1+'"'
SET @cmd = @cmd + ' /SET "Package.Variables[User::cname].Properties[Value]";'+'"'+@g_p2+'"'
DECLARE @jname varchar(128)
SET @jname = cast(newid() as char(36))
-- Create job
exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = 'Samples',
@delete_level = 1,
@job_id = @jid OUTPUT
exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = '(local)'
exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'Execute DTS',
@subsystem = 'CMDEXEC',
@command = @cmd
-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid
Thanks in advance.
View 5 Replies
View Related
Feb 28, 2006
I have a mobile device application using mobile sql 2005 replicating with sql 2000 in a x86 environment. This works fine!
I'm having issues getting this to work under Windows Server 2003 X64.
I've got all the components installed under the X64 environment including CLR 2.0 X64 and the mobile sql tools. the but when I run the Configure Web Synchronization Wizard I get the following error. SQL Server 2005 Mobile Edition Server Tools were not found on the IIS server. Run the SQL Server 2005 Mobile Edition Server Tools installer....
My question is: Were do I get the X64 version of these tools?
sqlce30setupen.msi
sql2Ken@P4.msi
The SQL environment is X86 as follows: SQL2000 SP4
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Any help would be much appreciated!
View 6 Replies
View Related
Oct 8, 2015
I'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 Related
Dec 6, 2006
Hi,
We are trying to move our application from SQL2005 to SQL2005, we are using 2005 jdbc driver 1.1.
The problem we have is, when a date is retrieved from the database, only the year part got correctly, but the date is automatically set to January 1 of that year. The interesting thing is this problem doesn't occur on local development environment (developer's windows platform), it only happens when we deploy the application to production server (unix).
Does anyone know what is going on, how to fix it?
Thanks.
Cathie
View 12 Replies
View Related
Jul 23, 2015
How do I set a variable to represent all of the data. For example using SELECT * will pull all of the data. Is there any symbol or way to declare and set a variable to do the same exact concept. In my query I have set many different variables which are used later on in my where clause but depending on what information I'm pulling from the data I don't wan the variable to have a specific value and instead pull all the data.
View 2 Replies
View Related
Nov 16, 2005
I 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 Related
Jul 18, 2015
I am trying to insert different number of columns into variables. This is what it does If I use a static columns.
declare @AccountType nvarchar(10)
declare @Total numerical(15,2)
declare @1 numerical (15,2)
declare @2 numerical (15,2)
declare @3 numerical (15,2)
#MonthtoDate temp table is created using a dynamic pivot query.
Data looks like this :
Account Type 1 2
3 Total
Type 1 3
0 4 7
Type 2 5
7 1 13
Select @AccountType = AcctType , @Total = MonthToDate, @1 = [1], @2 = [2], @3 = [3] from #MonthtoDate
However the issue is with [1],[2],[3] columns. Those are the number of days of the month. If today is the 3rd day of the month, we only need to show 3 days. So the final table has column [1],[2],[3] and @AccountType and @Total .
We want to run this query everyday to get the moth to date values.If we run this tomorrow, it will have 4 date columns [1], [2],[3],[4] and @AccountType and @Total .
View 6 Replies
View Related
Jun 16, 2015
SQL Sever 2012, I have a table where I store data related with a Log, the columns of the table are three, IDTable, DateLog and CountLog, the column CountLog is a cumulative value, each new rows can increment this value, my query will receive two parameters related with the Date, @dateFrom and @dateTo, with this information is necesary to get the diff between two months, for example:
CREATE TABLE MyTable
(
IDTable INT IDENTITY NOT NULL,
DateLog DATE NOT NULL,
CountLog INT NOT NULL
)
[code]....
In this case I need to apply a filter with the Dates:
@dateFrom ='2014-01-01',
@dateTo ='2014-04-28'
But according with this filter I need to limit the results only for this period between the date, but I need that the first month (January 2014) take the information of the max value from previous month even not included in the filter, in this case the results will be the next:
Id MonthYear Diff
1 Jan2014 5
2 Feb2014 3
3 March2014 0
4 April2014 5
With the results, for the first mont (January) is, according to the range of times, January is the lower limit,the last cumulative value of CountLog before the first month of the range was 2 (December 2013), the max value of the CountLog for this first month was 7, the diff is 7-2 equal to 5 , in the case of the next month (February) the max value of the CountLog was 10, the diff is 10 - 7 equal to 3 and this way.
[URL]
View 16 Replies
View Related
Sep 24, 2015
I have set up two SQL server (e.g. Server1 and Server2) which are set up to use Windows Authentication with my login (e.g. login1).On Server1 I have created a linked server to Server2, I have then selected the Security tab and added my local login and selected impersonate. I have then selected ‘Be Made using the login’s current security context’.I then receive the error message ‘The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?’
View 9 Replies
View Related
Jul 1, 2015
I have a table with the next structure:
DECLARE @MaxCountHistogram TABLE
(
MaxId INT IDENTITY PRIMARY KEY NOT NULL,
PublicationId INT NOT NULL,
ProviderId INT NOT NULL,
DateLog DATETIME NOT NULL,
Amount FLOAT NOT NULL
)
INSERT INTO @MaxCountHistogram
VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)
I need to take for each Publication and Provider and getting the diferential between two different months, for example:
Period Delta Amount Provider PublicationId
20150530 10.2564 3 432
20150630 3 Result of (13.2564- 10.2564 ) 3 432
View 4 Replies
View Related
Dec 5, 2015
How can i subtract two columns:
I have table:
ID COL 1 COL 2
-------------------------
1 200.00 70.00
2 200.00 30.00
3 200.00 90.00
4 200.00 110.00Col1 - COL2
But to continue for each row and value is reduced by the previously value.
My output should be like as:
ID COL 1 COL 2 [COL3 AS RESULT]
-------------------------
1 200.00 70.00 130
2 200.00 30.00 100
3 200.00 90.00 10
4 200.00 110.00 -100
View 3 Replies
View Related
Jun 9, 2015
I have a non-partitioned table (TableToPartition) and I want to apply an existing partition scheme (PartSch) to it using a query. I didn't find any option so I used the StorageCreate Partition wizard to generate the script.why this clustering magic needed if it is dropped at the end? Isn't there another way without indexing to partition a table, say something with ALTER TABLE? (SQL Server 2012)
BEGIN TRANSACTION
CREATE CLUSTERED INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
(
[ID]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartSch]([ID])
DROP INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
COMMIT TRANSACTION
View 2 Replies
View Related
Nov 23, 2015
I have a the following date variables that's being set around the current date. how to adjust it to work around a date variable @Date instead of the current date? Lets the @Date = 2015-06-30 then the it would adjust the variable below accordingly. I'm assuming the getdate()) needs to be replaced with @Date but I can't seem to get it to work.
Declare @EndOfLMPriorYear Date = DateAdd(yyyy,-1,EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0)))
Declare @EndOfPriorMonth Date = EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-2,0))
DECLARE @EndOfLastMonth Date = EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0))
View 3 Replies
View Related
Sep 4, 2006
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
View 8 Replies
View Related
Jan 24, 2006
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
View 2 Replies
View Related
Dec 12, 2007
Is it Possible adding Windows (activi directry users) from SQL Server which is running Windows authotication.?
View 4 Replies
View Related
Jun 23, 2006
I am attempting to write a Windows service that watches a database for uploaded files to import. When a new file is found, the corresponding SSIS package is run from the file system with variables passed through. I started development as a Windows app and copied the functionality to a service.
The app runs fine. The service does not. I get a "Failure" each time a package is executed. Everything is identical behind the scenes with the obvious exceptions that OnStart and OnStop handlers are buttons in the app. I added a script task at the beginning of one of the SSIS packages to notify me that it is even running at all. It doesn't even hit that initial task.
Again, the app will run all packages just fine. The data is imported and the results return as "Success."
The following is the code executing the package. Any help is appreciated. I've been banging my head on this one for a few days now. (Is there a tag to format a code sample?)
Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
pkgLocation = sPackageFolder & PackageName & ".dtsx"
pkg = app.LoadPackage(pkgLocation, Nothing)
Dim vars As Variables = pkg.Variables
vars("ImportId").Value = ImportId
vars("ProductionServer").Value = ProductionServer
vars("ProductionDatabase").Value = ProductionDatabase
vars("SourceFileName").Value = FileName
vars("SourceFilePath").Value = FilePath
pkgResults = pkg.Execute()
View 3 Replies
View Related
Apr 14, 2008
Long story short -
Have two C# applications - one WinForms desktop app, the other a WinForms smart device app. Both use SQL Server Compact CE 3.5 to store data locally. Both work just fine.
Now the desktop app needs to open and update the .SDF file that is stored on the Windows Mobile device. (Device will be docked via USB.)
What format would the path to the .SDF look like that the WinForms application would need to specify in order to open it?
Thanks for reading.
View 3 Replies
View Related
Mar 26, 2008
Hallo there,
I just upgraded from Windows XP Pro to Windows Vista Bussiness and tried to reinstall SQL Server 2005 Developers Edition. After the installation i downloaded (using microsoft windows update) and installed all the service packs for sql and vista available.
My problem is when i open sql server management studio and try to connect to my default instance using windows authentication and database engine, an 18456 error occurs.
I enabled all the protocols and all the ports
I disabled windows firewall and antivirus (eset nod32)
I installed all service packs available
I have also installed Visual Studio 2005 without installing sqlexpress
But nothing happens!
Please i am very desperate, any information will be gratefully accepted.
This is my installation Information
Code Snippet
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.6001.18000
Microsoft .NET Framework 2.0.50727.1434
Operating System 6.0.6001
Thank you in advance,
Patonios
View 3 Replies
View Related
Jul 20, 2005
Hi all,I have a Windows 2003 server, which is also a terminal server forapplication, with sql 2000 installed. My company has developed anapplication that uses SQL 2000 as its database. The application is aclient/server one. In each client computer there's a link to theapplication on the server. There is no problem with Windows 98,Windows 2000 pro, Windows xp pro clients, but the windows 95 onescannot log in to the database. The log of the application shows thefollowing error:connection error -2147467259. Cannot open database requested in login'database name'. Login fails.Till a week ago the application was running on a Windows 2000 serverwith SQL 2000 install and the W95 clients had no problem connecting tothe database, so my guess is the error has something to do withWindows 2003 server, but what'causing the error?I tried to install a newer version of MDAC (MDAC 2.5, the last versionof MDAC you can install on W95)but with no success. By the way W95clients have no problem accessing shared folder on the Windows 2003server.Any idea?ThanksMarino
View 1 Replies
View Related
Apr 17, 2008
Does any one come across any difficulty in the following migration?
OS: Wondows Server 2K
DB: SQL Server 2K Standard Edition
Migrating to
OS: Wondows Server 2K3
DB: SQL Server 2K Standard Edition
Thank you,
Gish
View 4 Replies
View Related
Jul 12, 2006
How can I Port Driver and OAL code of Windows CE 5.0 to Windows CE 4.2?
If anybody have idea about this, kindly give me suggestions.
View 1 Replies
View Related
Jun 12, 2007
Good 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.
View 3 Replies
View Related
