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.
So I know my code is pretty far off on the variables, but I couldn't find info on how to do this anywhere. Do I need to use sub selects to set the variables? Or something other than variables?
I have a stored procedure that prompts the user for a year and a month. Based on the month selected, I need to determine the number of days in that month.
I have tried declaring variables to use to calculate number of days in month and a counter, but they are not working. When I try to run this it says I have to prompt user for these as well. How do I declare and set a variable inside a stored procedure.
Sample of my code is below...
REATE PROCEDURE crm_contact_frequency_report
@TheYear varchar(4), @TheMonth integer,
@MyCount integer, @NumDays integer
AS
SELECT
/* EMAILS (B) */ (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 1) ) AS Total_EmailOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 0) ) AS Total_EmailImconing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode IS NULL) ) AS Total_EmailNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) ) AS Total_All_Emails,
/* PHONE CALLS (C) */ (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 1) ) AS Total_CallOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 0) ) AS Total_CallIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode IS NULL) ) AS Total_CallNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) ) AS Total_All_Calls,
/* FAXES (D) */ (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 1) ) AS Total_FaxOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 0) ) AS Total_FaxIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode IS NULL) ) AS Total_FaxNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) ) AS Total_All_Faxes
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20) Set @testvar = 'noname' Select @testvar= pub_name FROM publishers WHERE pub_id= '999' Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20) Set @testvar = 'noname' Select @testvar=(Select pub_name FROM publishers WHERE pub_id= '999') Select @testvar
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as begin Declare @ret int; Declare @EnterVolumeTime int; Declare @ExitVolumeTime int; Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
Hi, I have just started with ASP, and have a problem. I have created a stored procedure which looks to a specific tablename for information, based upon the users choice from a dropdown list. The control works fine when executed from within visual web developer, and I manually enter the value that the variable expects. However I can not get the dropdown listbox value to be written to the SQL value. I have tried for days, traweled the net for answers, borrowed 3ft in height of SQL books! so either I am doing something fundamentally wrong, or I am missing something. My SP is: ALTER Procedure GenericTableSelect @tablename VarChar(20) AS Declare @SQL VarChar(1000) SELECT @SQL = 'SELECT [base model] FROM ' SELECT @SQL = @SQL + @tablename Exec ( @SQL) and from the page the command to call it is: SelectCommand=generictableselect></asp:SqlDataSource> But this fails to compile and comes back with "@tablename not defined" any pointers in the right direction would help. The object of this is for two drop down boxes - the first is populated from one database of categories, the selection of which populates the second drop down list with items from within that category. Cheers, Richard
I have theses variables @strStartDaysDateVARCHAR(10), @strStartWeekDateVARCHAR(10), @strStartMonthDateVARCHAR(10), @strEndDateVARCHAR(10),
[code]....
So with @strStartDaysDate I'm getting number of records for the day.
With @strStartWeekDate I'm getting number of records for the week.
With @strStartMonthDate I'm getting number of records for the month.
I'm using GETDATE () function for @strEndDate because I can't count records after the current date.Of course those physical dates ('08/01/2013') are not good because I would have to have new dates each week or each month.How do I properly set my variables once so that they regardless to the month or year?
This is a fun one. I have a job that runs a SSIS package. It has some variables that I set a runtime. How can I programmatically change the values for these variables using SMO? I can get to the step in the job using VB but I can't find a way to programmatically change the value of the parameters of the job. The code loops through every property of the SSIS step. There's a command string but that's an ugly beast to code against. Any help is appreciated.
I am trying to understand the relationship of setting package configurations and setting variable values during job scheduling. I understand that I can select variables that I want to manipulate at run time using package configurations. I understand that the configuration file is an xml file that the job can be told to access at run time. Here are my questions:
1. Once I create a configuration file, do I physically modify the file to change the variable that is input at runtime? 2. Do I have to select the config file and then change the value using the Set values tab? 3. What is the relationship between the config file and the set values tab?
4. When creating a package configuration, when would you use the options other than XML configuration file?
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?
I have an FTP task in a for each containter and am setting the RemotePath using an expression (works great). Thought I could use this to start learning some of the scripting funtionality in SSIS (in a script task) so found some code in this forum (thanks Original Posters!) and tried my hand at some coding... Intent was to create a variable and then dynamically overwrite the Expression in the FTP Task from the script (I know I don't need to do this, I just wanted to use it for learning purposes)....
I have a variable named varFTPDestPathFileName (string) and want to set it to the value of varFTPDestPath (string) + varFTPFileName (string). Note: all variables are scoped at the package level (could this be the problem?). I did not assign any of the variables to ReadOnly or ReadWrite on the Script Task Editor page (seems to me that doing this in the code is a whole lot cleaner [and self documenting] than on the Task Editor page)...
I keep getting the following error: "The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there."
Here is the script:
Public Sub Main() Dim vars As Variables ' Lock for Read/Write the variables we are going to use Dts.VariableDispenser.LockForRead("User::varFTPDestPath") Dts.VariableDispenser.LockForRead("User::varFTPFileName") Dts.VariableDispenser.LockForWrite("User::varSourcePathFileName") Dts.VariableDispenser.GetVariables(vars)
' Set Value of varSourcePathFileName <<--- ERROR OCCURS HERE vars("User::varSourcePathFileName").Value = _ Dts.Variables("User::varFTPDestPath").Value.ToString + _ Dts.Variables("User::varFTPFileName").Value.ToString
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub
I would also like to be able to loop through the Dts.VariableDispensor to see the contents of the variables and their values.
Somthing like
For each ??? in vars msgbox(???.Value) Next
One other question... Do we always have to preface the variable with "User::" or "System::", if so can you explain why?
I am trying to set the connection string in a connection manager at runtime. Here is what I have done:
1. Created a gv_DataSource, gv_Username and gv_Password
2. Created a ForEach Loop that reads DataSource, Username and password values from a variable (it is an For Each ADO loop Enumerator). The ADO recordset is read into by an Execute SQL task before the loop.
3. Mapped values from the recordset to variable in the ForEach loop's "Variable Mappings" page.
4. Used the variables in my Sybase OLEDB Connection Manager's "Expression" property, setting the "ConnectionString" property to:
5. I set the values in my database table for the connection-I set 2 connections for which I have Sybase OLEDB datasources setup.
When I run the package, I just get the first server's data twice, it doesn't set the second server's data during the second loop. I made sure the first one was working (i.e. the ConnectionString's property was being set by the data from the current variables) by setting the variables incorrectly in the variable properties page, and then running the package. So the first row of connection information is working, but the second loop around it doesn't seem to be working. I used a msgbox in a script task to show that the variables are mapping correctly in the loop, so it seems the second time around the connection information isn't taking from the variables.
Hi, Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.
How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.
What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?
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.
how can I dynamically set up the period between now and one year before in a query like this:
where cast(LEFT(period_start,8)as DATE)>='2014-10-01' and cast(LEFT(period_end,8)as DATE)<='2015-09-30' and  Practically in the second or third of every month I need to run the period late of one year.
#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 .
I am making use of the DtUtil tool to deploy my package to SQL Server. Following is my configuration: 32-bit machine and 32-bit named instance of Yukon.
I have some package variables which need to be set in the code.
Previously I did it as follows:
Set the package variables in the code. For example:
Here the package was successfully deployed and when i open those packages using BIDS, I am able to see that the variables are set to the values as doen in teh code.
Because of oen problem I am not using SaveToSQLServer method. So I switched to DTUtil tool. Now I am doing it this way:
Set the package variables as before. Deploy the package to SQL Server using DTUtil tool.
Now is the problem: The package is successfully deployed. But the variables are not set to the value that I have specified in the code.
I also tried DTexec utility to set the package variable. Even that does n't work. Can anyone help me out? Is there any alternate method to set package variables?
I have the below code and have two questions. 1) With the current delete control, the system breaks stating: System.Data.SqlClient.SqlException: Must declare the variable '@doc_area_id'. 2) Currently the Area Type column brings up all records set as '1'. It shows the value '1' for each column. I would like it to state the text 'Shared Area' under the Area Type Column where it is set as '1' in the database.
I am learning T-SQL syntax and I am very familiar with it, however how would I do the following:
We have a table that actually has a column that contains SQL statements. I want to build a SQL statement in Reporting Services that is going to take that column to build a "dynamic" SQL statment and then I will use the exec sp_executesql statement.
Do I need to declare a parameter, or in SQL is there such thing as a variable?
So if I have:
DECLARE @sql nvarchar(4000)
SELECT AdHocSQL from TheTable
SET @sql=AdHocSQL
Would this work? Is this syntatically correct? Or should I be doing this some other way?
The report is sort of a summary report that has about 250 different items and each item has different data to get from different tables.
I have a job that runs in sql 2008r2 that declares a value to a variable. This variable is passed to a function. I need to include more values in and am unsure how to do this. @InCo could be 4,5,6,or 7.
I am still having problems with my cursors. I am receiving the following errors when I execute my Stored Procedure: Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 27 A cursor with the name 'MyCursor' already exists. Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 31 The cursor is already open. Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 37 A cursor with the name 'vwCursor' already exists. Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 38 The cursor is already open.
Here is the code causing the problem:
CREATE PROCEDURE [dbo].[BatchNonMons2] AS
BEGIN /* Create a new hdr each time the Sys/Prin changes in the Dtl rec */
/* Define every field you want to output */ DECLARE @Batch_Type int DECLARE @Batch_Num int DECLARE @Sys varchar (4) DECLARE @Prin varchar (4) DECLARE @Account_Num varchar(16) DECLARE @Tran_Code varchar(3) DECLARE @Clerk_Code varchar(3) DECLARE @Memo_Text varchar(57) DECLARE @SubTrans varchar(2) DECLARE @SubTrans_2 varchar(1) DECLARE @Terminal_Id varchar(3) DECLARE @Op_Code varchar(2) DECLARE @Last4 varchar(6)
Open MyCursor; /*Works like an ARRAY*//*holds current record */ FETCH NEXT FROM MyCursor INTO @Batch_Type, @Batch_Num, @Sys, @Prin, @Account_Num, @Tran_Code, @Clerk_Code, @Memo_Text, @SubTrans, @SubTrans_2, @Terminal_Id, @Op_Code
Declare vwCursor cursor FOR SELECT Sys, Prin FROM dbo.vwNonMonSysPrins open vwCursor; FETCH next FROM vwcursor INTO @vwSys, @vwPrin
/* When @@Fetch_Status = 1 EOF has been reached */ WHILE @@Fetch_Status = 0 BEGIN WHILE @vwSys = @Sys BEGIN
Hi,I have a User-defined function "Concatenate_NoteTexts" which I use in aquery (SQL Server 2000). On my local development machine it is called likethis:SELECTdbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTextsFROM tblIntroducersI want to run the same code on a shared remote server where I am user "JON"instead of "dbo". I don't want to hard-code the User Name into the SQL, butwhen I tried to put the user name into a variable as here:DECLARE @USER_NAME VarChar(30)SET @USER_NAME = USER_NAME()SELECT@USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTextsFROM tblIntroducersI get the following error:Server: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '.'Any advice?TIA,JONPS First posted earlier today to AspMessageBoard - no answers yet.http://www.aspmessageboard.com/foru...=626289&F=21&P=1
I need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!
HiCan someone please shed some light as to how this can be done. With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.select * from @tableName where condition = condition
