SQL 2012 :: Environment Variables Not Sorted In Dropdown When Configuring Parameters / Datasources
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
ADVERTISEMENT
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
Dec 17, 2007
We are having our SSRS (Sql Server Reporting Services) Servers deployed in Domain1 to connect to the Domain1 data sources. Now, when we configure the SSRS datasources (which is in Domain1) to run under €œDomain2/User€? credentials , it€™s taking 60 minutes to render a report whereas when we configure the SSRS datasources to run under €œDomain1/User€? account it€™s taking only 5 seconds.
I would like to know €œWhy there is a latency difference while configuring the SSRS data sources with different domain credentials (Domain1Domain2)?€?
Any pointers to Support KB or any other related articles would be really great.
View 2 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
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
Feb 8, 2006
Each time I start of SQL Server Management Studio, I get the following message "Microsoft SQL Server Management Studio configuring environment for first time".
I had a simiiliar message with Visual Studio 2005 and fixed it by setting the setting to look on local disk drive. I cannot find a similiar setting in SQL Server 2005.
Any suggestions on how to resolve?
View 4 Replies
View Related
Feb 14, 2008
Hi,
We are developing a windows application (Distributed via ClickOnce) and hosting the
SqlServer 2005 at our company. Now, we need to add reporting services to our application
and I've stumbled upon some problems.
First off, each customer has their own database on the same SqlServer instance. What
I would want, and need guidance for, is the following..
1. I want each customer to be able to use the Report Builder to login and access a
model only targeted against their database (maybe using some sort of sql user account?).
2. I want each customer to be able to login to Report Manager and save/edit their
own reports targeted solely for their database.
3. If possible, is there a way to script this for easy creation when we acquire a new customer.
4. If this is not at all possible, what other solutions are there out there?
Kind Regards,
Robert
View 2 Replies
View Related
Feb 1, 2006
I have discovered some shortcomings in the way inline table valued function parameters are treated in the OLEDB datasource. You can select the user designed function ine the Generic Query Builder and test it with the required parameters. However when you attempt to set up the parameters for the result ing SQL Command Text you get and error message to the effect that the parameters cannot be retrieved from the datasource. Once again this is disappointing because Report Services seems to deal with the parameters perfectly well.
Dick Campbell
View 2 Replies
View Related
Jul 24, 2007
I'm trying to mimic the rowcount transform's custom property called VariableName in a custom pipeline transform, with the same drop-down and list of variables in the default property grid as that of "RowCount".
Should a UITypeEditor or a TypeConverter be used for such a custom property? More importantly, in either case, how do you get from the UITypeEditor or TypeConverter to the TaskHost which contains the Variables to enumerate?
I'm not that familiar with Windows Forms, but it appears in both cases (editor or type converter), an ITypeDescriptorContext parameter named "context" is passed, but I'm certain how to get from A to B, that is, from the context variable to the TaskHost.
P.S. There's no need to add a new variable, just select from those that already exist.
View 5 Replies
View Related
Nov 2, 2007
Is there any way to configure a SSIS package variable using a SQL Statement?
The "Package Configurations" does not accept SQL statement.
I have about 5 package variables that need to be initialized using queries.
I don't want to end up writing 5 execute sql tasks to initialize them.
In DTS we could do that at one place using Dynamic Properties Task.
View 6 Replies
View Related
Jan 10, 2007
Hello,
I have a report with a multi-value parameter. In its' drop drop box, I can select ALL or I can individually select single items, but it doesn't appear that I can select a range of items using the shift key. The problem is; occasionally the user will want to select everything, and them eliminate an item or two. The list it too long to select every one individually. It would be an easy solution if hey could select the entire list, then deselect what they don't want. Is there a way?
Thanks.
View 1 Replies
View Related
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
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
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
Oct 8, 2015
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
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
Nov 20, 2015
I run these stored procedure to build the report and I am able successfully to build the report but I need some prompts to in the report to get the specific data .
ALTER
PROCEDURE [dbo].[Sharepoint_Ticket_Report]
@StartDate
DATETIME,
@EndDate
[Code] ....
And for prompts how to create created_by and message_type dropdowns as shown in picture.
View 7 Replies
View Related
Oct 1, 2015
configuring sql alert for low sql memory.
I have configured the memory for sql server as below
min : 512 MB
Max :13500 MB
Total RAM on the server is 16GB
I want to receive email from sql server informing that sql server is running on low memory ( less than 200 MB free) so that I do not get out of memory issues and the sql server continues to run without fail.
View 9 Replies
View Related
Jun 7, 2015
We are going to install a SQL Server 2012 Ent. Edition two node (Active/Passive) cluster. Only one instance. Issue is, a separate shared storage is not provision for MSDTC.
1. Is it mandatory to configure MSDTC for a single SQL 2012 instance ?
2. Can we use one of the shared drives (Data/log/bkp/temp) for configuring MSDTC ?
View 2 Replies
View Related
Jan 8, 2008
Hi all,
I'm trying to make a stored procedure which runs a different stored procedure.
exec sp_addsubscription @publication = N'update setup tables', @subscriber = 'SERVER', @destination_db = N'DB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
this is the stored procedure I want to run, but I wanted to use a parameter for 'SERVER'.
if my parameter is called @serverName, how would you use it for subscriber?
exec sp_addsubscription @publication = N'update setup tables', @subscriber = @serverName, @destination_db = N'DB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
kind of like that??
thank you
View 1 Replies
View Related
Nov 26, 2014
I've read lots about why you shouldn't normally shrink databases in posts such as this:
[URL]
But we have a situation where we are required to copy the live db to various non production environments for testing. Part of this process involves truncating a number of tables with masses of blob data. So we're freeing up quite a lot of space. The question is how to reclaim this? The database is peculiar in that it's got no clustered indexes so I can't rebuild indexes on another filegroup with drop to move and rebuild.
I've tried dbcc shrinkfile specifying a size. I've tried to shrink the file in increments. The problem is I'm just not getting much space released. I get maybe 2-3%. I suspect this is because we're dealing with heaps with some tables that have sparsely populated blob / image data.
Is there an alternative to shrinking? Should I recreate all the db objects in a new database? It doesn't matter if the process takes a while or if it has to be done manually.
View 7 Replies
View Related
May 10, 2015
We are working in a Merge replication environment where we have SQL Server 2005, 11 publications and 2 subscribers.We used to get lot of incidents from the Application owner for blockings, recently we faced a situation where the lead blocker is in sleeping state and the session was used by the merge agent.Checked the query that the session was running, it was sys.sp_MSenumgenerations90;1.
View 1 Replies
View Related
Aug 26, 2013
I don't know the difference between parameters and variables in a stored procedure. How can I tell?
ALTER PROCEDURE [dbo].[spEdgeSalesDashBoad]
@x_strSessionGUIDVARCHAR(50),
@x_strStartofWeekVARCHAR(10),
@x_StartofMonthVARCHAR(10),
@x_strEndDateVARCHAR(10)
[Code] ......
View 2 Replies
View Related
Feb 3, 2006
As mentioned in a previous posting, I have an in-line table valued UDF with three input parameters. I can set this up as an OLEDB Datasource SQL Command Text with parameter markers (i.e. "?") and test it successfully in the Generic Query Builder. The parameter markers are correctly associated with the input parameters of the UDF and the parameters can be entered at execution time into a parameters table.
So near and yet so far. When I attempt to map the parameter markers with Package Variables there is an error message saying the the parameter details cannot be retrieved from the function. If the function was in a foriegn (e.g. Oracle) database I might accept this as just one of those things but this is a SQL 2005 database and compatability should be complete. Add to this that the Generic Query Builder has no problem with the same UDF and nor does Reporting Services and I have to assume that this is a bug plain and simple.
The only solution that I have seen suggested is to embed the SQL Command text in a Package Variable and change it at execution time but I regard this as a second rate solution.
View 2 Replies
View Related
Jan 23, 2013
We have 4 Servers which have SQL SERVER 2012 and "AlwaysOn" have been enabled on all 4 servers:
Server1,Server2,Server3,Server4
Server1 is the Primary node and thr rest are secondaries. There is a Sync relation between Server1 and Server2 and also there is aSync relation between Server1 and Server3 & Server4.
Is it possible to setup log shipping from Server2 & Server3(secondaries) to two new servers?
View 9 Replies
View Related
Dec 11, 2014
I am try to put together options in regard to creating a test environment for our Dynamics NAV system. The environment will be mainly used to test new releases / changes ahead of applying them to production.The 2 options I am considering are…
1.Create a second Test instance on our Production SQL Server to host a test database
2.Purchase a set of SQL developer licences and having a totally separate server for our test environment.
My preference would be option 2. However I need to build a convincing case that this is the best way forward. I wondered if I could tap into the thoughts of the SQL Central community and see how other approach this.
View 3 Replies
View Related
Sep 10, 2015
Is it possible to use registered servers feature in clustered environment and with different versions of Sql server like 2012 & 2014.
View 0 Replies
View Related
Feb 29, 2008
I’ve got the following piece of ASP code, which updated a
table through the standard edit/update command filed options.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:CAT_SYSTEMConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="sp_diplomaViewQualifications"
UpdateCommand="UPDATE
PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and
qualId=@original_qualId"
DeleteCommand="UPDATE
PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy
WHERE rowstatus=1 and qualId=@original_qualId"
SelectCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="quantity"
/>
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="createdBy" DefaultValue="TEST"/>
</DeleteParameters>
<SelectParameters>
<asp:Parameter Name="masterKey"
/>
</SelectParameters>
</asp:SqlDataSource>
My problem is that I need to pass the contents
of the VB variable ‘createdBy’ into the DeleteParameters option.
This is defined in the code as: Dim createdBy As String = getUserLoginName(Me.Page)
How do I pass this into the update and/or delete part
of the command field on the ASP page?
View 3 Replies
View Related
Jun 3, 2006
When creating a Reporting Services report and declaring local variables as part of your query in a dataset there is sometimes a problem. When you hit run in the Data section and the €œDefine Query Parameters€? box pops up, all the variables are not there. Sometimes when you go to properties (€¦) of that dataset the parameters are gone. Is this a bug? This is happening both in RS2000 and 2005.
Thanks
View 7 Replies
View Related
Jan 27, 2015
I do have the following Extended Event session on my Dev box;
CREATE EVENT SESSION [sp_showplan] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(sqlserver.plan_handle)
WHERE ([package0].[equal_uint64]([object_type],(8272)) AND [sqlserver].[equal_i_sql_unicode_string]([object_name],N'MyStoreProcedure')))
ADD TARGET package0.event_file(SET filename=N'E:DBA_AuditSP_Exec.xel',metadatafile=N'E:DBA_AuditSP_Exec.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
The idea is being able to capture the execution plan when the program invokes the store procedure, regardless of the database.
This works on my Dev box. When I manually trigger "MyStoreProcedure" from database A , the event is saved. The same thing happens when I do that from database B. Ok ... so far, so good.
So I went to the live production environment and setup my Extended Events session. But it's saving nothing. I was able to check that the store procedure was executed on several databases but my extended events session never grabbed the plan.
What could be the reason for this? Memory starvation maybe? Is there something I am doing wrong?
View 5 Replies
View Related
Oct 27, 2015
I know how to "Create and map a Server Environment" using management studio [URL] ......
Is it possible to create the environment in DTS (2012) and have that environment created on the target server?
View 2 Replies
View Related
