SQL HELP......IF's With Variables
Jun 8, 2005
Anyone know how to write the portion in red in a stored procedure?LEFT OUTER JOIN TITLE AS T ON (POS.TITLE_ID = T.TITLE_ID)JOIN DISTRICT_LOCATIONS AS DL ON (POS.DISTRICT_LOCATION_ID = DL.DISTRICT_LOCATION_ID)WHERE POS.PRIMARY_IND = 1 IF @DISTRICT_LOCATION_ID != 'All' BEGIN and DL.DISTRICT_LOCATION_ID = @DISTRICT_LOCATION_ID ENDIF @ATTENDANCE_STATUS_ID!= 'All' BEGIN and AST.ATTENDANCE_STATUS_ID= @ATTENDANCE_STATUS_ID ENDUNION ALLSELECT DISTINCT 5 AS TAG ,3 AS PARENT ,convert(varchar,getdate(),101) as [ACTIVITY_REPORT!1!REPORT_DATE] ,AX.ACTIVITY_CLASS_ID AS [ACTIVITY!2!ACTIVITY_CLASS_ID] ,NULL AS [ACTIVITY!2!ACTIVITY_NAME]
View 2 Replies
ADVERTISEMENT
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
Jul 13, 2007
What is @@variables means in sql server?
View 3 Replies
View Related
Jan 21, 2004
Is there a way to use the LIKE keyword with variables like below?
DECLARE @Name CHAR(10)
SET @Name = 'MyName'
SELECT * FROM table
WHERE my_name LIKE @Name
This won't work, but you get the idea of what I want. Any thoughts?
Thanks,
View 1 Replies
View Related
Jun 4, 2004
Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?
View 7 Replies
View Related
Feb 16, 2000
Hi,
I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename.
I get the error that it can't open a database @databasename.
Can I declare the database object in T-SQL?
Thanks
View 1 Replies
View Related
Nov 2, 2000
I have this script:
.....
USE master
go
/* Get Name of Server & declare variables */
declare @sname varchar(30)
declare @db1 varchar(30)
declare @db2 varchar(30)
declare @db3 varchar(30)
declare @dbf1 varchar(30)
declare @dbf2 varchar(30)
declare @dbf3 varchar(30)
select @sname = rtrim(substring(srvname,5,30)) from sysservers
print 'The name of this server is: ' + @sname
Set @db1 = @sname + 'database1'
Set @db2 = @sname + 'database2'
Set @db3 = @sname + 'database3'
Set @dbf1 = @db1 + 'RL_log'
Set @dbf2 = @db2 + 'RL_log'
Set @dbf3 = @db3 + 'RL_log'
print @db1
print @dbf1
go
ALTER DATABASE @db1 MODIFY FILE (NAME = @dbf1, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db2 MODIFY FILE (NAME = @dbf2, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db3 MODIFY FILE (NAME = @dbf3, MAXSIZE = UNLIMITED)
go
.....
When I run it, I get the following errors:
.....
The name of this server is: KANSASCITY
KANSASCITYdatabase1
KANSASCITYdatabase1RL_log
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@db1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db2'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db3'.
.....
Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.
WHAT I AM TRYING TO ACCOMPLISH:
I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.
Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?
Thank you (in advance)
View 2 Replies
View Related
Mar 28, 2003
Hi
I think this is an easy question.
Is it possible to use a variable in a create database statement
i.e.
declare @db_name varchar(20)
select @db_name='new_db'
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
DROP DATABASE @db_name
CREATE DATABASE [@db_name] ON (NAME = @db_name, FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataew_DB_Data.MDF' , SIZE = 300, FILEGROWTH = 10%) LOG ON (NAME = N'new_DB_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataImport_Utility_DB_Log.LDF' , SIZE = 30, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
Also, if the above is possible, how can i pass a database name to the script if I am calling it from a batch file (using osql).
Thanks,
Jim
View 2 Replies
View Related
Jul 26, 2000
I want to change database in my SQL-script like this:
DECLARE @DB_NAME varchar(30)
SELECT * FROM @DB_NAME.dbo.TableName,
but it is syntactically incorrect.
I could use
USE (@DB_NAME)
but I write Stored Procedure, it is not allowed using
USE statement in any Stored Procedures.
View 2 Replies
View Related
Oct 31, 2005
Hey folks,
i'm trying to make a variable inside of a loop do this.
@a = 1
@b = Variable + @a
and then inside of the loop @a incriments by 1, so i'd get
variable1
variable2
...etc
but I can't get the variable to work right...
Any help would be great!
Caden
View 2 Replies
View Related
Apr 14, 2006
I have a MS SQL server DB which stores tables and queries and a MS Access DB which acts as an interface and stores Forms.
I am a MS Access Developre and this is my first MS Sql Server DB. I would like to know how can i pass a variable which is an object in a Form (In MS Access DB) to a query in MS SQL?
In MS Access( i mean mdb file which all tables, queries and forms are in one mdb file)it is very easy, for example if you would like to pass idfld from Form of frminfo to a query you can say: Where idname like " & Forms!frminfo!idfld , but it does not work on MS SQL.
Would you please tell me how can i use a variable in MS SQL query?
Thank
Ebi
View 8 Replies
View Related
Jan 29, 2007
I hope this is the place for questions about DTS.
I'm working on a DTS package that runs against development databases. At some point I'll move it to qual for testing, and I'll have to change names of servers and credentials for some of the connections. Can I do that with global variables? BOL aren't very helpful about this.
To be more specific. I'll have one "master" package that will be kicking off other packages. I've found in help that I can use dtsrun to pass variables - so I'm assuming that DBAs moving packages from one environment to another will adjust parameters passed by dtsrun to the "master" package. But how does the "worker" package reference global variable from the "master" package? And how do I reference global variable inside the "worker" package so that it's value is used for any of the properties I see when clicking on "disconnected properties" (like server name or user name)?
Is there some sort of tutorial out there that explains use of global variables?
Thanks in advance for any and all leads
View 9 Replies
View Related
Jun 5, 2007
Basically I am creating a wizard(using forms) within a database to import client files and reformat them into the format that we need.
I have a form that list all 26 column headings that I need and tied to each column headings is a combo box that that lists the column headings from the imported client file.
I need to create a query that either appends it to an already formatted file or creates a new table with the column headings.
The problem is the field headings from the imported file change.
This is the sql that I used that doesn't work:
INSERT INTO Formatted_Data ( Field X )
SELECT Imported_Data.Forms![Import Data-3]![Combo33]
FROM Imported_Data;
Because Imported_Data.Forms![Import Data-3]![Combo33] is not recognized. I think if I could create a variable to replace the Forms! part then it would work but I do not know how to do this,
Thanks in advance to anyon that can help
View 3 Replies
View Related
Nov 14, 2007
Hello,
I have a recordset that only has 1 record. I want to use this record to initialize the package variables. How do I do this? Or where do I do this?
View 2 Replies
View Related
Mar 14, 2007
Hi
I have a simple task flow which takes data from an external table and puts it into a SQL server table. In the SQL server table I have a column which flags the data source. I have set up a variable in the variable window to hold a constant which was the appropriate value for the column. I cannot see how to map the variable I have created to the column in my output table.
Not sure if variables do what I think they do, can anyone give me a simple overview, and point me in the right direction to add my constant into the output table.
Thanks in advance
ADG
View 1 Replies
View Related
Apr 25, 2007
Hi everyone,
Is the only way to get a SSIS variable to change values via a configuration file or table? I thought that at one point in developing some SSIS packages that if I changed the value of a variable in a script that value was then retained.
Thanks
View 7 Replies
View Related
Sep 7, 2007
Is there a way to manipulate the value of a variable with out using a skripttask?
I just want to var1 = var2.Substring(5)
View 5 Replies
View Related
Jan 21, 2008
I have around 40-50 packages.
I need to use a Variable do some control.
Let says it's DateFormat
My Question is
From BIDS i can change the value before i run the package. I might change it according to user's requirement
How if i plan to install the package to SQL server and it will run/called using SQL AGENT.
View 5 Replies
View Related
Jul 23, 2007
bla...well, i don't know if my syntax is right but what im trying todo is...
set a varialbe for my timestamps so i cant change them when needed...thus my sp can run automatically...
DECLARE @startTime AS varchar(100)
SET @startTime = '2007-07-09 00:00:00.000'
From Memberaccountdetails M1 where M1.Participantid = AL4.ParticipantID) AND
AL6.WorkDate={ts '2007-07-17 00:00:00.000'} AND
AL1.IntendedSettlementDate BETWEEN {ts @startTime} AND {ts '2007-07-16 00:00:00.000'} AND
AL1.TransactionType='DEL')
the highlighted red timestamp is what i need to achieve from a variable...
When i add this variable to the query i get the following message
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
please help!
View 7 Replies
View Related
Oct 22, 2007
Hi there,
Is it possible to store a server name in a variable?
Thanks
View 3 Replies
View Related
Oct 18, 2007
I want to return the results of a select statement and tie the results to a variable, is it possible?
DECLARE @variable datatype
SELECT @variable = 'SELECT * FROM TABLE'
SELECT @variable
If I execute this statements i would get the "select * from table" since thats what my variable is set to but I want to return the results of the select statement as the value of the variable.
Any advice?
Thanks
View 7 Replies
View Related
Nov 18, 2007
Hi all
I got an SSIS package with an OLE DB connection (Access) and SQL server connection. I'm trying to copy some tables in the OLE DB connection to the SQL server without creating individual data flow tasks. I was able to create blank tables with the same table names as the Access database using a ForEach container loop.
Now I tried executing a similar method to copy the data from the Access database to their corresponding table in the SQL server database. It gave me an error saying the SQL statement is incomplete. I tried executing a very simple SQL statement connected to the OLE DB connection: select * from @curtableName where @curtableName is the current table the ForEach container loop is looking at. Again same error occurs.
My question is, is there a way for me to execute this SQL statement? Or will I have to create a data flow task for each individual table?
Thanks in advance
CoyoteM
View 10 Replies
View Related
Feb 14, 2008
Hello,
I am trying to set this up with variables instead of the file path and table name, so if they change I do not have to change them everywhere. But when I change it from the string 'c:file.csv' to "' + @fileName + "' it errors. Is it possible to do this or am I just missing something?
Code Snippet
DECLARE @doesExist INT
DECLARE @fileName VARCHAR(200)
DECLARE @myTableName VARCHAR(20)
SET @fileName = 'c:file.csv' --variable
SET @myTableName = 'my_table_name_here' --variable
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "' + @myTableName + "') --HERE
BEGIN
CREATE TABLE temp_table
(
One int,
Two int,
Three int,
four varchar(1) ,
five int
)
END
SET NOCOUNT ON
EXEC xp_fileexist "' + @fileName + "' @doesExist OUTPUT --HERE
SET NOCOUNT OFF
print(@fileName)
print(@doesExist)
IF @doesExist = 1
BEGIN
BULK INSERT temp_table
FROM "' + @fileName + "' -- HERE
WITH
.............
Thanks again
View 18 Replies
View Related
Apr 13, 2007
Hi everyone,
Primary platform is XP Pro Sp2. Sql25k sp1.
Is it possible to see either user variables or system variables from VSA? I€™ve been looking for any option but I don€™t see nothing similar.
In fact, I am really thinking regarding old dts 2000 when you had a VbScript Task and you could see perfectly all the global variables defined in your package.
Thanks in advance and regards,
View 5 Replies
View Related
Jan 25, 2006
It it possible to define a variable that is the result of 3 other variable ?
like @[User::Total] = @[User::varA] + @[User::varB] + @[User::varC]
I know that i can set it with a scripttask but is there a way to define it when creating the variable ?
View 3 Replies
View Related
Oct 25, 2007
SQL is still relatively new to me, and I haven't been able to figure out how to really make variables work for me (unless I'm using a cursor -- but I'd really like to avoid them).
Here is an example to demonstrate my problem:
Code Block
SELECT i.Item_ID, i.Size_Height * i.Size_Length / 144 AS Sq_Ft, i.Total_Sales/(i.Size_Height * i.Size_Length / 144)
FROM Item i
(Forgive the fact that there could be a divide by zero error for now - I'd like to keep the code simple to demonstrate my problem)
You see, what I really want to do is stuff that "ic.Size_Height * ic.Size_Length / 144" into a variable and reuse it so it might look something like...
Code Block
DECLARE @sqFT float
SELECT i.Item_ID, (SELECT @sqFT = i.Size_Height * i.Size_Length / 144) AS Square_Feet, i.Total_Sales/@sqFT AS Sales_Per_FT
FROM Item i
but SQL doesn't seem to enjoy that very much - probably because there doesn't seem to be a way to set a variable and use it at the same time. This example is pretty trivial, but a lot of the stuff I need to do involved pretty complicated queries that build off of a bunch of results from existing queries.
I did all of what I needed with a cursor but it took well over 7 times the amount of time to generate my results as did a query like the one above. I absolutely need to keep the performance on this pretty solid as what I need to use this for will return the results to a web application.
Does anybody have any suggestions? Thanks a lot in advance for any help!
View 5 Replies
View Related
Nov 21, 2006
I am trying to use two variables in my SQL statement to query an access database and then pass the results to a datagrid. Nothing shows up in my datagrid. I think that the syntax on my SQL statement is wrong. I am really not sure how to embed the variables, especially since there are two. I really need help with this, it is for work. The code is posted below. Thanks.
Sub Search_Click( s as Object, e as eventArgs)
Dim conLibrary As OleDbConnectionDim Category As StringDim Search_Field As StringDim dstResults As DataSetDim dadResults As OledbDataAdapterDim dtblBooks As DataTable
conLibrary = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:ewpsychiatrylibrarydb.mdb")conLibrary.Open
Category = ddlSearch.SelectedItem.textSearch_Field = txtSearch.textdadResults = New OledbDataAdapter("Select * From rec WHERE '"& Category"' "=" '"&Search_Field"'", conLibrary)dstResults = New Dataset()dadResults.Fill(dstResults, "Table1")
dgrdResult.DataSource = dstResults.Tables(0).DefaultView
dgrdResult.DataBind()conLibrary.closeServer.Transfer("results.aspx")
end Sub
View 1 Replies
View Related
Dec 30, 2006
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
View 2 Replies
View Related
May 2, 2007
into database.Is there any other way except using parameters? and if there isn't, how can i insert 2 parameters in the same query?
View 5 Replies
View Related
May 14, 2007
Pretty simple question and I see it's been asked a few times with no answers so I thought I'd try again. :)
Is there a way to put a variable into an SQL connection string? Like: <asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:BGHelpdeskConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM MyTable WHERE name=" & response.write(MyName)
.... etc
View 7 Replies
View Related
Feb 22, 2008
I have two numeric variables in T-SQL - @Var1 and @Var2These variables can be positive or negative. I want to find the difference between the two variables, eg:@Var1 = -50@Var2 = 25Difference = 25 @Var1 = -50@Var2 = -40Difference = 10 @Var1 = 60@Var2 = 10Difference = 50 Is there a function in SQL Server to carry this out?
View 4 Replies
View Related
Nov 10, 2004
Hello,
In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.
I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?
Brian
View 3 Replies
View Related
