I thought a stored procedure was taking much too long to complete. So, I moved the main query to Query Analyzer and found that when I substituted actual values for variables that my SELECT statement ran in seconds. Just to test, I created DECLARE statements, set the variables equal to the same values, re-ran the SELECT statement and it took over a minute. Even the Execution Plan was much different. Any suggestions?
View 4 Repliesdeclare @StartTime nvarchar(10)= '12:00'
declare @EndTime nvarchar(10)= '12:45'
declare @Diff time(1) = cast(@EndTime as datetime) - cast(@StartTime as datetime)
How to I use Column names instead of Hard coding variables - e.g. '12:00'
Is it possible to force parameters into the reports so enabling me to force a user id value into every report that is picked up from the list. The user ID is a system value and I don't want end users having any knowledge of it?
Cheers
Darren
Me.SqlConnection1.ConnectionString = "workstation id=""XXXXXXXX"";packet size=4096;user id=XX;data source=""XXXXXXX"";persi" & _
"st security info=True;initial catalog=manufacturing;password=XXXXX"
What excatly is this statement doing it looks to be creating a trusted connection to a certain workstation?
Hi there,
The postage and packing scheme being used at the site I'm working on depends on the customer's location.
If they're in the UK they get once scheme and if they're in Ireland they get another. Furthermore, if they're anywhere else they get another scheme.
A customer's country is indicated by a 'countryID' stored in the main customer row in the database. (This ID references a country in the Countries table.)
Thus, I was wondering if it is acceptable to hard code the country pk of the UK and Ireland into the formular which works out the postage and packing?
At present, for a similar issue, I've even hard coded the the pk of UK and Ireland into some Javascript running at the client.
Is it fair design to work with a hard coded pk like this?
Cheers,
Hello I was hoping somebody out there could help me …..
We have a hard-coded application which uses the Sa account with no password. We want to add password to Sa – but when we do get users/DBAs calling us saying the application does not work.
How can we add password to Sa and get the application to work - unfortunately we do not have scripts for the application or know of the whereabouts of the developers.
Any suggestions/ideas – will be greatly appreciated
Cheers
Khalid
Dear Reader ,There are some details <facts>, which can be stored (Hard coded) in exe.example: Measurement Units and their relations.Now I want to print the list of those Measurement Units and Relationships between them.Can we print them directly , without bringing them into Database?Can we use them and make reports merging details from the Database?Am using SQL 2000 and VB.Net 2003SuryaPrakash Patel--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedI want to be able to return the rows from a table that have been updated since a specific time. My query returns results in less than 1 minute if I hard code the reference timestamp, but it keeps spinning if I load the reference timestamp in a table. See examples below (the "Reference" table has only one row with a value 2014-09-30 00:00:00.000)
select * from A where ReceiptTS > '2014-09-30 00:00:00.000'
select * from A where ReceiptTS > (select ReferenceTS from Reference)
Hi. I've looked all over MSDN, newsgroups and the web but I can't find the answer to a problem that I am having.
The application that I am working on used both transactional and merge replication. I want to avoid hard coding passwords into an application that kicks off the pull replication on the client machine.
The client machines are all using SQL Server 2005 Express. The other machine is running SQL Server Standard. The passwords and login details are specified in the subscription properties in the Management Studio.
A fragment of the code is posted below. The transactional sychronization works fine without having to specify any passwords - however the merge replication does not work if both of the passwords are not specified.
private void SynchButton_Click(object sender, EventArgs e) { // Set up the subscriber connection details. subscriberConnection = new ServerConnection(subscriberName); try { // Connect to the Subscriber. subscriberConnection.Connect(); // Do the transactional subscription synchronisation independantly of the // merge subscription replication. try { transPullSubscription = new TransPullSubscription(subscriptionDbName, publisherName, publicationDbName, transPublicationName, subscriberConnection); // If the pull subscription and the job exists, start the agent job. if (transPullSubscription.LoadProperties() && transPullSubscription.AgentJobId != null) { TransSynchronizationAgent transSyncAgent = transPullSubscription.SynchronizationAgent; transSyncAgent.Synchronize(); } else { } } catch (Exception ex) { } // Do the merge subscription synchronisation independantly of the // transactional subscription replication. try { // Set up the subscription details for the merge subscription (bi-directional data) mergePullSubscription = new MergePullSubscription(subscriptionDbName, publisherName, publicationDbName, mergePublicationName, subscriberConnection); // If the pull subscription and the job exists, start the agent job. if (mergePullSubscription.LoadProperties() && mergePullSubscription.AgentJobId != null) { MergeSynchronizationAgent mergeSyncAgent = mergePullSubscription.SynchronizationAgent; mergeSyncAgent.DistributorPassword = "<<password>>"; mergeSyncAgent.PublisherPassword = "<<password>>"; mergeSyncAgent.Synchronize(); }etc etc..
Any help or suggestions will be greatly appeciated. Thanks.
Hi There,
Our company deals with financial education and typically has 9 different databases which have some cross referenced stored procedures. Every time we replicate Production database into TEST and DEV environments, we had to manually update the database references in Stored procedures. and it usually takes atleast a week and until then all the dev and test work has to wait.
Hence, I wanted to write a script, Here the code below.
-- These two variables must contain a valid database name.
DECLARE @vchSearch VarChar(15),
@vchReplacement VarChar(15)
SET @vchSearch = 'Search'
SET @vchReplacement = 'Replacement'
/*
-- Select the Kaplan Database Names in the Current Server
*/
DECLARE @tblDBNames TABLE (vchDBName VarChar(30))
INSERT INTO
@tblDBNames
SELECT
Name
FROM
MASTER.DBO.SYSDATABASES
WHERE
Has_DBAccess(Name)=1
And Name IN ( 'DB_DEV', 'DB_TEST', 'DB_PROD', 'WEBDB_DEV', 'WEBDB_TEST', 'WEBDB_PROD' , 'FINDB_DEV', 'FINDB_TEST', 'FINDB_PROD')
--SELECT * FROM @DBNames
IF @vchSearch NOT IN (SELECT vchDBName FROM @tblDBNames)
BEGIN
PRINT 'Not a Valid Search DB Name'
GOTO Terminate
END
IF @vchReplacement NOT IN (SELECT vchDBNAME FROM @tblDBNames)
BEGIN
PRINT 'Not a Valid Replacement DB Name'
GOTO Terminate
END
-- We have Valid DB Names, lets proceed...
--USE @vchReplacement
SET @vchSearch = '%' + @vchSearch + '..%'
SET @vchReplacement = '%' + @vchReplacement + '..%'
-- Get Names of Stored Procedures to be altered
DECLARE @tblSProcNames TABLE (vchSPName VarChar(100))
INSERT INTO
@tblSProcNames
SELECT
DISTINCT so.Name
FROM
SYSOBJECTS so
INNER JOIN SYSCOMMENTS sc
ON sc.Id = so.Id
WHERE
so.XType='P'
AND sc.Text LIKE @vchSearch
ORDER BY
so.name
-- Now, the table @tblSprocNames has the names of stored procedures to be updated.
-- And we have to Some HOW ?!! grab the stored proc definition and use REPLACE() to
-- update the database reference
-- Then, use cursors to loop through each stored proc and upate the reference
Now, I have got stuck how to extract the body of a stored procedure into a variable.
Please Help.... I dont want spend weeks of time in the future to do this work manually.
Madhu
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Do I need to use a cursor?
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
See DDL and sample data below. What would be the easiest way to get the desired output without hard coding the values? Data in both tables may change over time.
DECLARE @num AS TABLE (
Id INT IDENTITY(1, 1)
,Price MONEY
)
DECLARE @range AS TABLE (
Id INT IDENTITY(1, 1)
,Rng MONEY
[code]....
Hi Everyone,
I have 2 variables say "@one" and "@two"
Can I populate these 2 variables in just one SQL statement
normally we do this
select @one = one from mytable where three = 3
select @two = two from mytable where three = 3
can I have one statement which can put the values in the 2 variables.
something like
select @one,@two = one,two from mytable where three = 3
(this does not work)
Awaiting a reply
Thanks,
saad.
Hello.
I need to store 2 values that i retrive from a databse into variables so i can check them towards what is typed into the textboxes for my login script iv built. How can i do this?1 string myConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersalhoDocumentsIntrapointWebApp_DataIntrapoint.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
2
3 SqlConnection myConnection = new SqlConnection(myConnectionString);
4 SqlCommand cmd = new SqlCommand("SELECT [username], [password] FROM [company] WHERE (([username] = @username) AND ([password] = @password))");
5 cmd.Parameters.AddWithValue("username", TextBoxUsername.Text);
6 cmd.Parameters.AddWithValue("password", TextBoxPassword.Text);
7
8 myConnection.Open();
9
10 //how do i pass the values retrived into variables, like string usrname and string password?
11
12 myConnection.Close();
can u give me some idea how to make Sp who having two variables as a parameter having values seperated by ","
now thses vaues have to insert in to two tables tbColor .... colorname,product_id
and tbSize.......sizename,product_id
thanksss
Hello,
I am looking into a new project and I assume that I can end-up in a situation where the same package could be called at the same time (or close enough) by different jobs passing different values to the package's variables.
Is it going to work? Will each instance of the package run in a separate thread?
I would think so because I already have a package similar to this.
The different is that the new package will run for a long time as opposed to the older one which runs for a second or so only.
Just wanted to make sure and gather your feedback.
Thanks
I have variables and values stored in a table in this format
process_id | t_variable | t_value
-----------------------------------------------------
1 | Remote_Log_Server | AUSCPSQL01
...
many such rows
how to assign values to variables in SSIS?
basically i'm looking for SQL equivalent of the following query i currently use to assign values to multiple variables (in a single query)
SELECT
@varRemoteLogServer=MAX(CASE WHEN [t_variable] = 'Remote_Log_Server' THEN [t_value] END)
,@varVariable2=MAX(CASE WHEN [t_variable] = 'variable2_name' THEN [t_value] END)
FROM Ref_Table
WHERE process_id=1
Can someone show how to do this?I have a SqlDataSource1, and i have a SELECT * FROM Table1How would i get@ProdName@ProdNumber Into the following local variablesString ProductNameInt ProductNumber I’m using C# and ASP 2.0 VWDThanks for Help1
View 2 Replies View RelatedHi
Does anybody know how to pass values from asp dot net to SSIS package variables ?
Currently I have an SSIS package for monitoring windows service... for that...
I have to pass the Server-IP Addrress, UserName, Password, Service Name as Parameter.
I would like to pass these parameters through an Interface from RUN TIME.
Please help this problem
Regards
Deepu M.I
From the microsoft technet How to: run a package using a SQL server agent job (http://technet.microsoft.com/en-us/library/ms139805.aspx):
Click the Set Values tab to map properties and variables to values.
Note:
The property path uses this syntax: Package<container name>.<property name>. Depending on the package structure, a container may include other containers, in which case nested containers are separated by a back slash (). For example, PackageMyForeachLoopMySequenceMyExecuteSQLTask.Description.
If this is the syntax for writing the container.property value, how do you write the variables?
how can i put multiple values in the variables.
for eg:
Declare @w_man as varchar
set @w_man = ('julial','BEVERLEYB', 'Lucy') and few more names.
I am getting syntax error(,)
Hi, figured out where I was going wrong in my post just prior, but isthere ANY way I can assign several variables to then use them in anUpdate statement, for example (this does not work):ALTER PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50))ASDECLARE @varCust VARCHAR(50)SELECT @varCust = (SELECT Customer FROM tblWorkOrdersWHERE WorkOrder=@varWO)DECLARE @varAssy VARCHAR(50)SELECT @varAssy=(SELECT Assy FROM tblWorkOrdersWHERE WorkOrder=@varWO)UPDATE statement here using declared variables...I can set one @variable but not multiple. Any clues? kinda new tothis.Thanks,Kathy
View 2 Replies View RelatedI've got an SSIS package that works fine. It does extracts from a foreign ODBC source and moves it to SQL Server. It has 3 variables, a customer ID, a fromDate and a toDate. Those variables are used to complete a SQL statement expression. So far so good.
I now want to provide a web interface in a web page that will enable the user to provide values for those variables. How can I execute my package passing the user's input?
Thanks for any advice.
Lou
Hi,
I need a resolution of the following issue:
Following SQL is to be inserted in an audit table :
INSERT INTO GLOBAL_VARIABLE_LOAD
([LOAD_ID]
,[LOAD_STATUS]
,[START_TIME]
,[END_TIME])
VALUES
(@P_LOAD_ID
,@P_LOAD_STATUS
,@P_START_TIME
,@P_END_TIME)
@P_LOAD_ID, @P_LOAD_STATUS, @P_START_TIME, @P_END_TIME are the 4 parameters mapped to global variables (At package Level) which store values mapped in a previous SQL Task in my control flow.
Following Error Message is thrown on executing the SQL Task:
Invalid object name 'GLOBAL_VARIABLE_LOAD'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Can anyone guide me as to how should I go about saving values in global variables and inserting them in a table.
Thanks in advance.
Regards,
Aman
Hi,
My scenario:
I am using a FTP Connection Manager and the configuration setting for it is being set in the package configuration xml file. So the xml file contains the Ftpserver, FTp server username and password. The package is picking up the values from the xml file and is executing successfully. I have to do this because I was not able to provide an expression to the Connection Manager Server Password property.
Now, I want to pick up the ftp details from a database table and set it in the xml file during runtime. Is this possible? OR something like using the
<Configuration ConfiguredType="Property" Path="Package.Connections[FTP Connection Manager].Properties[ServerPassword]" ValueType="Variable"><ConfiguredValue>@[user::FtpPassword]</ConfiguredValue></Configuration>
Kindly look at the items in bold. Is this possible? Then I can set the value of the variable in the package before the FTP connection manager task is executed.
Thanks for all the help.
$wapnil
HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice!
View 9 Replies View RelatedI 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 .
Can I assign values to variables in 2012 using below command? I have used the same command in 2008 and it works fine.
DTEXEC
/SERVER"XXXXXXXXSQLSERVER2012"/SQL"Mypackage.dtsx"/SETPackage.Variables[FilePath].Value;"C:Test estvariable.csv"
Wondering is there a different way in 2012 to pass values to variables dynamically.
I would like to have a stored procedure to run a shared VB file, which will check a refrence value from
a SQL Server table every hour, then redirect the web page to an error page if the reference value
is equal to e.g. 'E'.
My question is: a S.P. can update a SQL Server table, but can a S.P. 'run' a shared VB file to redicret
a web page automatically? How?
TIA,
Jeffrey
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
View 1 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 need to use a top and a join in the same sql. To get 10 top refnr from orders_refnr. That works fine to I use this:
SQL = "SELECT TOP 10 refnr, antal = COUNT(refnr) FROM orders_refnr INNER JOIN produkter ON (orders_refnr.refnr = produkter.referensnummer) GROUP BY refnr ORDER BY antal DESC"
But I need to be able to get information from more fields than the field refnr. How can I specify more fields? I need to get other fields from produkter.
Please helt I´m really stucked.