Concatinating Variables

Jul 20, 2005

Hello,

How do I concatinate a variable. Here's the scenarios:

declare @var1 varchar(20)
declare @var2 varchar(20)
declare @var3 varchar(20)
declare @var4 varchar(20)
..
..
declare @var32 varchar(20)

set @var1 = 'Something 1'
set @var2 = 'Something 2'
....
set @var32 = 'Something 3'

/* I have to store the values of these individual variables. I wish to
have a "While" routine which iterates through the above variables. I
wish to have the variable name concatinated as that I do not have to
write numerous lines of code setting up individual 32 variables. How
could I use the '+' operator to join 'var' + @count . Where count is
from 1 through 32. I am having some trouble with the syntax.*/

Regards,
VS

View 7 Replies

Concatinating With Cursor

May 6, 2008

frenz:
Can any one send me the code for
concatinating cells values of column in to one cell of that column using cursors.

Ex:

hobbyID

h1

h2

h3

hobbyId
h1,h2,h3

View 1 Replies View Related

Concatinating Columns

Aug 8, 2006

Hello all.

I am taking details from a linked server file (.XLS file). Two of the fields in this file are STARTTIME and STARTDATE.

The problem I have is that these have to be imported into a SQL table, but only into one column of type DATETIME. The column name is STARTDATE. I really have no idea on how to go about this. Any ideas would be greatly appreciated.

Thanks.

View 4 Replies View Related

Concatinating Select Results

Jan 18, 2006

Hey everyone,

I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.

The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.

Here is what I am wanting to accomplish:

UPDATE tblWorkOrdersSET Comments = (SELECT Comments
FROM tblComments
WHERE tblWorkOrders.ReqNum =
tblComments.ReqNum)

I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.

I would appreciate any suggestions.

Thanks,

Lee.

View 2 Replies View Related

Concatinating SmallInt Data Type

Aug 20, 2007

Newbie question regarding a db I have inherited. A table FullDocuments has a DocNo column with smallint data type and a SequenceNo column also with smallint data type.DocNo
has numbers that represent persons. SequenceNo has numbers that
represent specific documents associated with each person (DocNo).So DocNo 5 and Sequence 3 represents the 3rd document associated with person 5.My SELECT statement looks like this:SELECT ReadingNo, SequenceNoThis returns data like this: 5 3I would like to concatenate the SELECT statement to return like this: 5-3So I made Sql like this:SELECT ReadingNo + '-" + SequenceNo Which
returns a alias ('No Column Named') result value of 8 which is an
arithmetic result instead of a string concatination that I want.So my questions are:1.
Should the original database designer have used string data types for
these columns since they will never be used for math purposes?2.
Do I need to cast them to string data type (like nchar(4) - since
neither column will ever exceed 4 digits) to get the result I desire?3. Or can I keep them as smallint and modify my SELECT statement to allow concatination yielding a string result?

View 4 Replies View Related

Concatinating Two Field And Insert The Result

Aug 20, 2005

Hii,I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?use northwind
select city, region,([city]+ +[region]) as uniqefrom customerswhere region is not nullThe resulting records in Quary Anchorage AK AnchorageAKTsawassen BC TsawassenBCVancouver BC VancouverBCSan Francisco CA San FranciscoCA

View 1 Replies View Related

Problem In Concatinating Two Paramters To Update One Column

Sep 20, 2007

Using gridview to display the data and sql server 2000 I have
a column in the database say departtime of datetime datatype that
cntains the date and time resp(09/19/2007 9:00 PM). I am separating the
date and time parts to display in two different textboxes say
txt1(09/19/2007) contaons date and txt2(9:00 PM) contains time by using
the convert in sqldatasource. Now i need to update the column in the
database and i am using Updatecommand with parameters in aspx lke
updatecommand = "Update table set departtime = @departtime" . How can
i update my column as datetime by getting the data from 2 texboxes as
now i have 2 textboxes displaying data for single column means if user
edit the data in txt1 as(10/19/2007) then on click of update i need to
populate the column daparttime as (10/19/2007 9:00 PM).Please let me know if you have any questions.

View 1 Replies View Related

Concatinating String Values From Multiple Rows

Nov 4, 2006

I currently have some SQL code that is used to build a string that is a concatination of string values across multiple rows. The subqueries in the script sometimes return NULL values so I use the following statement to change the default behavior of the concatination operator which prevents my query from returning NULL:

SET CONCAT_NULL_YIELDS_NULL ON

Here's the code snippet:

select DISTINCT

(SELECT CASE WHEN (t1.MaskValue & HDR.TranTypeID)=1 THEN ' ' + t1.description ELSE '' END FROM transactiontypes t1 WHERE (t1.MaskValue & HDR.TranTypeID)=1) +

(SELECT CASE WHEN (t2.MaskValue & HDR.TranTypeID)=2 THEN ' ' + t2.description ELSE '' END FROM transactiontypes t2 WHERE (t2.MaskValue & HDR.TranTypeID)=2) +

(SELECT CASE WHEN (t3.MaskValue & HDR.TranTypeID)=4 THEN ' ' + t3.description ELSE '' END FROM transactiontypes t3 WHERE (t3.MaskValue & HDR.TranTypeID)=4) +

(SELECT CASE WHEN (t4.MaskValue & HDR.TranTypeID)=8 THEN ' ' + t4.description ELSE '' END FROM transactiontypes t4 WHERE (t4.MaskValue & HDR.TranTypeID)=8) +

(SELECT CASE WHEN (t5.MaskValue & HDR.TranTypeID)=16 THEN ' ' + t5.description ELSE '' END FROM transactiontypes t5 WHERE (t5.MaskValue & HDR.TranTypeID)=16)) as 'Transaction Type'

FROM HDResponse HDR

Here's the underlying table structure:
CREATE TABLE [dbo].[TransactionTypes](
             [ID] [int] IDENTITY(1,1) NOT NULL,
             [Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,        [MaskValue] [int] NOT NULL) ON [PRIMARY]
CREATE TABLE [dbo].[HDResponse](
             [ResponseID] [int] IDENTITY(1,1) NOT NULL,
             [LoggedDateTime] [datetime] NULL,
             [ResponseTypeTripID] [int] NULL,
             [ResponseTypeID] [int] NULL,
             [ResponseTypeObjectID] [int] NULL,
             [ObjectID] [int] NULL,
             [IDHolderID] [int] NULL,
             [TransportCode] [int] NULL,
             [CardID] [int] NULL,
             [IssueCode] [smallint] NULL,
             [EventDateTime] [datetime] NULL,
             [Response] [bit] NULL,
             [TranTypeID] [int] NULL)
             ON [PRIMARY]

The problem I am having is I need to be able to use the query above in a view used for reporting. Unfortunately, you cannot use SET CONCAT_NULL_YIELDS_NULL ON in a view. This causes my query to return NULL if any of the subqueries return NULL. I could create a function to do something similar and reference the function in the query but I can't help but think there must be a way to get this done in a single query.

Any thoughts or ideas would be greatly appreciated.

Thanks!!!!!

View 1 Replies View Related

Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

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

How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

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

@@ Variables

Jul 13, 2007

What is @@variables means in sql server?

View 3 Replies View Related

Using LIKE With Variables

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

DTS Variables..?

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

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 View Related

T-SQL Variables

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

Using Variables In T-SQL

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

Using Variables In DDL

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

Using Variables In T-SQL

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

Variables

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

Variables In MS SQL

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

DTS And Variables

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

Variables In SQL?

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

Variables

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

Using Variables

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

Variables

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

Using Variables

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

Using Variables

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

Variables In Sql

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

Using Variables

Oct 22, 2007

Hi there,

Is it possible to store a server name in a variable?

Thanks

View 3 Replies View Related

Using Variables

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

OLE DB And Variables

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

Help With Variables

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

Variables From VSA

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