String Variable

Jun 5, 2000

Hi,

Does anyone know what is the maximum size for a string variable that can be used by Execute statement in SQL 7 and SQL 6.5? If I really need to execute
a long statement around 9000 characters long, how can I do it?

declare lcsql varchar(x) && what is the maximum size for x in SQL 7 and SQL 6.5

select lcsql = 'select ...'

execute (lcsql)

View 1 Replies


ADVERTISEMENT

Transact SQL :: How To Get String Array In String Variable

Jul 28, 2015

I have a string variable and following data.

Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'

when I assign values in @ServiceID  in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.

View 8 Replies View Related

Using A Variable In A Sql String

Mar 26, 2002

Hi there,

Following is the code I have, which I select and execute as one batch.

declare @intData integer
Declare @strSQL varchar(8000)
Set @strSql = ''
set @strSql = 'select @intData = Age from MasterRecords'
exec (@strSql)

I get the following error in SQL 2000's query analyzer.

Must declare the variable '@intdata'.

Any and all help is highly appreciated.

View 1 Replies View Related

String Variable

Aug 17, 2007

Hi

I have a problem with a string variable which looks something like this

@VARIABLE = 'RTY,YTUK,GHJ'

I need to be able to select (RTY) the first individual values seperated by the commas to be used as a variable elsewhere in my SQL script.

Following this I need to use the second value (YTUK) to be run in the same piece of code. Followed by the 3rd value (GHJ)

Can anyone tell me how to do this?

Thanks

View 2 Replies View Related

Passing A String Variable To Sql

May 11, 2008

hi there,
i am trying to pass a string which contains a string,
here is the code which is wrong :
{string sqlcommand = "select pnia.pnia_number, pnia.user_name, pnia.date_pnia, pnia.user_pnia, problem.problem, gormim.gorem_name, status.status_name from pnia,gormim,problem,status where (pnia.status='@p1' and status.status='@p1' and pnia.problem=problem.problem_num and pnia.gorem=gormim.gorem)";
OleDbCommand cmd = new OleDbCommand(sqlcommand,con);OleDbParameter p1 = new OleDbParameter("@p1",this.DropDownList4.SelectedItem.Value.ToString());
cmd.Parameters.Add(p1);
}
the problem is that the sql compailer doesnt take the parameter (@p1) as a string
if someone could help me with that it would be great !  tnx

View 2 Replies View Related

Using Variable String In Sql Command

Dec 5, 1999

Using SQL Server 7.0, I am trying to use a variable string called from a form to perform a simple query.

Here's what it looks like:

[after submitting a form on the previous page with a drop down list box titled "fiscal"]

dim strYear

strYear = Request.Form("fiscal")

sql = "SELECT * FROM VENDOR WHERE STATUS = 'P' AND '"& strYear &"';"

[where strYear is the following: PYMNT_DT > ''12/31/98'']

Records exist that meet this criteria. If the PYMNT_DT > '12/31/98' is hardcoded into the sql statement, 12 records are returned. However, when the variable string is used instead, no records are found. Any ideas on how this problem might be fixed?

Thanks in advance.

Sincerely,

Chad Massie

View 1 Replies View Related

Text String As Variable?

May 9, 2014

I want to run the following code:

Declare@Testnames as Varchar(500)
Set@Testnames = ('CT,MRI')-- List all radiology type
SELECT*
FROMRPT_OUTPATIENT_IMAGING
WHEREservice_level2 In (@Testnames)

Nothing comes back.

When I run:

SELECT*
FROMRPT_OUTPATIENT_IMAGING
WHEREservice_level2 In ('CT','MRI')

Then I get results.

View 5 Replies View Related

Variable String Limit

Jun 29, 2007

Is there a way around the character limit for a variable? I'm throwing the following in a variable and evaulating it as an expression and having an execute sql task do it, but I can clearly see it's being cut off.



"insert work.dbo.data_run select publisher,publisher_db,subscriber,subscriber_db,article,null,getdate(),null

from msdb.dbo.sysreplicationalerts

where error_id<>0

and alert_error_code=20574

and [time] between '" +(DT_STR,50,1252)@[System::ContainerStartTime] +"' and '"+(DT_STR,50,1252)GETDATE()+"'"





Thanks,

Phil

View 7 Replies View Related

How To Asssign String Variable To SqlDbType

Nov 17, 2007

i have string variable as,
String str="Int";
now, while assigning sql parameters, i want
param.SqlDBType=SqlDBType.Int;
but, value of Int is dynamic. it may be string or double so,
i want it to be as,
param.SqlDBType=(SqlDBType)str;
but its not acceptable(its invalid cast).
in any way can i do it and how?
regards---

View 1 Replies View Related

String Variable From CodeBehind To Html

Dec 3, 2007

Hi Folks,
How can i pass a string variable from code behind page to html page on event fire. i want to pass sql qerry to SelectCommand (SELECT * FROM [Tickets] WHERE ([TicketNo] = @TicketNo)) in some variable from codebehind on some event fire.
<asp:SqlDataSource ID="sqldsTickets" runat="server" ConnectionString="<%$ ConnectionStrings:IMTicketingConnectionString %>" SelectCommand="" >
<SelectParameters>
<asp:SessionParameter Name="TicketNo" SessionField="test" Type="Int64" />
</SelectParameters>
</asp:SqlDataSource>
 Thanks in advance

View 4 Replies View Related

SQL Select Statement With A 'string' Variable?

Apr 16, 2008

I'm trying to add a 'change password' control to my site and seem to be having some issues.  I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly.    Error:System.Data.SqlClient.SqlException: The column prefix
'System.Security.Principal' does not match with a table name or alias name used
in the query. Here's a piece of the code that is supposed to detect the current logged in user.  However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell)  public void InitPage()    {            IPrincipal p = HttpContext.Current.User;            String myid = HttpContext.Current.User.ToString();            SqlServer sqlServer = new SqlServer(Util.SqlConnectionString());            DataTable dt;            SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString);            SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn);            DataTable UIDtable = new DataTable();            cmd1.Fill(UIDtable);            User_Id.Value = UIDtable.Rows[0]["ID"].ToString();            dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value); 

View 1 Replies View Related

Variable To Store String For Where Clause

Mar 26, 2001

I want to declare a variable that will serve as my where clause. The variable will be passed in from our website. Does anyone have any information or can guide me in the right direction to do this?

example:

@variable varchar(1000)

SELECT *
FROM table
WHERE @variable

(The @variable would reflect the where clause string)


Thanks!

View 1 Replies View Related

Concatenating Filename And Variable/string

Mar 31, 2008

I'm trying to write a stored procedure in SQL that will make a copy of a file and then append the current date to the end of the copy's filename. I'm storing the date in a variable called @currentdate which is defined as:

declare
@currentdate varchar(10)
set @currentdate='' + 'select datepart(month,getdate())' + '-' + 'select datepart(day,getdate())' + '-' + 'select datepart(year,getdate())'

Here is the SQL code I'm using:

exec xp_cmdshell 'copy "C:DevelopmentParticipant Limits ReportParticipant Limits Report template.xls" "C:DevelopmentParticipant Limits ReportParticipant Limits Report ' + @currentdate + '.xls"'
GO

The resulting file should have a filename something like "Participant Limits Report 3-31-2008". I get an "Incorrect syntax near '+'." error.

View 20 Replies View Related

Problem With Setting A Variable In SQL String

Jul 20, 2005

Hi,I am having problems setting the value of a variable in a SQL Stringthat I have to create dynamically in my procedure. The code that Icurrently have is as follows:set @sqlStatement='Set @compare_string=' + '(Select ' +@group_column_list_mod + ' from ' + @Tbl_Name + '_Sorted' + ' whereIdentity_Column=' + ltrim(rtrim(str(@loop_counter))) + ')'exec(@sqlStatement)The error message that I get is as follows:Must declare the variable '@compare_string'.Here @compare_string has already been declared in the procedure and Idon't have a problem using the variable anywhere else but this SQLStatement (when called using the EXEC function).I am not sure why SQL Server can't see the variable declared when usedin a string in conjunction with EXEC. Is this a syntax issue? Any helpon this issue would be greatly appreciated!Thanks in advance.

View 5 Replies View Related

String Variable Size Issue

Nov 4, 2006

I have to send an email using a variable as message body... the variable gets its value from database varchar(8000). Due to limited size of string variable in SSIS the value gets truncated.

Any workaround?

Thanks

View 13 Replies View Related

Store Connection String In Variable

May 10, 2007

Hi,



Does anyone know a way to store the ConnectionString of an OLEDB connection in a variable? I want to see exactly what values are in there because the step is failing with an error at that point. Here is what I'm looking for:



Data Source=;Initial Catalog=;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;





Thanks,

Phil

View 5 Replies View Related

How To Use /Set Option To Set A Variable To Empty String

May 19, 2008



Hi,

I developed an SSIS package and I'm using the dtexec utility to run it. The package has a variable RunDate (datetime) and i want to set this value to "" (empty string) when i try to run this package using the dtexec.

Currently I'm getting an error Argument option not valid.
Thanks in advance

View 8 Replies View Related

Applying A Carriage Return To A String Variable

May 15, 2001

I have a stored proc that builds a character string from a number of rows returned from a select query. Is there anyway to insert a carriage return after I append the value of each row to the string variable?

Thanks in advance,

Chris

View 2 Replies View Related

Using A Variable That Stores The Query String For WHERE Condition

Feb 20, 2001

Hi, I was wondering if someone could help out. I need to create a stored procedure, but before i do so, I need to know if I can store a conditional expression in a string and just use that variable as my condition for the WHERE clause. The reason I ask this is because I am trying to create a stored procedure that queries different things depending on what the inputs are.

View 2 Replies View Related

Assining String Variable To A Datetime Column

May 7, 2007

HI

can i assign a string column to a date column
string column contain date data.

UPDATE TABLE ENT SET EXPIRE_DATE = PREVIOUS_TRN_VALUE

where EXPIRE_DATE is a datetime column
PREVIOUS_TRN_VALUE is a string column having the value '2007-05-07'

thanks

View 2 Replies View Related

Passing Variable To String Compare In Function

Dec 8, 2007

I have created a function with:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @Output varchar(100)

SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity

RETURN @Output


END

how can I pass the variable @item correctly for the string comparison

WHERE item = @item AND week = @week

to work correctly please?

WHERE item = '@item' AND week = @week

won't work and

WHERE item = @item AND week = @week

won't work.

View 2 Replies View Related

String / Variable Problem - Dynamic Table Name

Jul 20, 2005

HiI'm grateful for any light you can shed on this!!I've to admit, it's an unusual design but I've multiple contact tables namede.g. i2b_ash_contact or i2b_ted_contact.'i2b_' and '_contact' are static but the middle part is dynamic.Storing all contacts in one table with an identifier of e.g. 'ash' or 'ted'for each record is not possible.Returning the value from the dynamic Query is no problem but I don't knowhow to assign it to a variable.When I try doing this it either runs into problems with evaluating thevariables or doesn't retuen anything at all e.g. if I say at the end 'Print@AddressID'. The variable remains empty.How can I do something like:DECLARE@AddressID int,@ProgClient (varchar(10),@Table varchar(10)Note: @Prog is a string e.g. 'ash' or 'ted'SET @Table = 'i2b_ + @ProgClient + '_contactSET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)

View 2 Replies View Related

SSIS String Variable Is Truncating Data

Sep 25, 2006

Greetings,


I have written a SSIS package which does the following:


- An 'Execute SQL Task' uses an OLEDB connection to execute a Stored Procedure in SQL Server 2005
A SSIS user defined variable named @SourceSQLStatement is passed to the Stored procedure as an OUTPUT
This variable is a string Data Type

- The Stored Procedure generates an SQL command and saves it in the variable
This variable is defined as @SourceSQLStatement nvarchar(4000) OUTPUT
The varible is then returned to the SSIS package


When the package is executed the variable does not contain the entire SQL command.
When I check in the Locals window during Debug, the contents seem to have been truncated:


+ User::SourceSQLstatement {SELECT a.FailureID, a.ExceptionHandlerID, a.FailureTypeCd, a.AccountNbr, a.AccountNm, a.CaseNbr, a.CustNm, a.AssociateNm, a.TargetSysNm, a.FailureDt, a.FailurePointTxt, a.SubmittedByNm, a.ErrorMsgTxt, a.CreateId, a.CreateDt, a.UpdateId, a.UpdateDt FROM GSPIntegrationException a WHERE a.CreateDt >= (select max(ETLC.RunDt) from X141572_ETLSTAGING.dbo.ETL ETL inner join X141572_ETLSTAGING.dbo.ETLControl ETLC on ETL.ETLID = ETLC.ETLID inner join X141572_ETLSTAGING.dbo.ETLRun ETLR on ETLC.ETLID = ETLR.ETLID an} String


The fully generated SQL command does not exceed the 4000 character length provided in the stored procedure.
Has anyone encountered this issue before?

Any help would be greatly appreciated

Thank You

View 4 Replies View Related

Inserting Data Into A Table From A String Variable

Apr 25, 2008

How to insert data into a table from a string variable? Like below:


DECLARE @Data AS NVARCHAR(MAX)

SET @Data = 'bla|bla|bla
lab|lab|lab
abl|abl|abl'


BULK INSERT tablename

FROM @Data

WITH

(


DATAFILETYPE = 'char',

FIELDTERMINATOR = '|',

ROWTERMINATOR = ''

);

View 3 Replies View Related

Maximum Size Of The SSIS String Variable?

Aug 30, 2007

In SSIS, what is the maximum size of the String variable (one that you would define in the Variables dialog box)?

View 13 Replies View Related

Help Required For Splitting Up String Variable Using Comma Separator

Jul 9, 2007

I need a help in SQL Server 2000.
I am having a string variable in the format like -- (1,23,445,5,12)
I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop.
Can anyone help me out in splitting the variable using the comma separator...

View 4 Replies View Related

How Can I Concatenate A String To A Int Variable? Need Correct Combination Of Quotes!

May 26, 2008

Hi,
I need to concatenate a string with an int variable on a stored procedure; however, i looks like i am lost in single and double quotes. Does any one know the right comination of quotes for this please? My Code is below:
 1 @Price int
2
3 DECLARE @SqlPrice varchar(50)
4
5 if (@Price is not null)
6
7 set @sqlPrice = 'AND price' + '' > '' + '' + @Price + ''
8

 

View 4 Replies View Related

T-SQL (SS2K8) :: Replacing String Values With Contents Of Variable

Dec 19, 2014

So I have the following column named String in a table:

<key>Children</key><integer>2</integer>

This of course can vary for the different records. What's the best way to replace the 2 with the contents of my variable with TSQL?

declare @children int
set @children = 4

I want to do something like this:

SELECT <key>Children</key><integer>@children</integer>

View 4 Replies View Related

T-SQL (SS2K8) :: Extract String - Variable Sizes With Breaks?

Jun 18, 2015

Row Value :

Fiscal Year: 2016(
)Budget Scenario: Main Budget (0+12)(
)Forecast Scenario: Jun (0+12) Forecast(
)Department: 400 - New York(
)YTD Period: Jun

and I need to extract 400 only... substring fails because the length of the Scenario, Forecast etc differs.

Should I just go for charindex on break() or is there a another way ?

View 3 Replies View Related

Data Flow Source That Will Take A String Variable With Csv Structure?

Dec 21, 2006

I am able to use a custom script task to receive a MSMQ package and save the package contents to a flat file.

I can also use the bulk load task to push the flat file contents into a SQL table.

However, I would like to save the package contents to a variable (done, it works), and then pass that string variable to a data flow task for SQL upload. In other words, I don't see any reason to persist the msmq package contents to disk.

My question is: Which data flow source can I use that will accept a string variable? The string variable will then need to be processed with bulk load or an execute sql task.

Btw, the content of the string variable is a csv style string:

"01001","11/21/2006",15
"01001","11/21/2006",1
"01001","11/21/2006",25
"01001","11/21/2006",3


Thanks,

Trey

View 3 Replies View Related

Assigning A Particular Flat File Record To A String Variable

Jun 12, 2007

Hey all!

Okay, can I assign line x of a flat file to a variable, parse that line, do my data transformations, and then move on to line x+1?

Any thoughts?

In other words, I'm looking at using a for loop to cycle through a flat file. I have the for loop set up, and the counter's iterating correctly. How do I point at a particular line in a flat file?

Thanks for any suggestions!

Jim Work

View 5 Replies View Related

How To Create Trigger Which CREAT TABLE From A Variable String?

Feb 23, 2006



I have a Table Name "Forums". I want to ceate an AFTER-Trigger on it. It will execute when ever a new row is inserted to "Froums" Table.

Here is what I did but It needs to be corrected:

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

ALTER TRIGGER CreateTopicsTableTrigger

ON dbo.Forums

AFTER INSERT

AS

SET NOCOUNT OFF

DECLARE @myNewForum varchar

CAST(@@ROWCOUNT as varchar) /*Is it OK???*/

SET @myNewForum=@myNewForum+@@ROWCOUNT /*Here I dont know how assigments work in SQL*/

GO

CREATE Table @myNewForum /*Will this work some how???*/

( TopicID int IDENTITY NOT NULL, TopicTitle varchar(50) , CreatedBy varchar(50) ,

DateCreated DateTime , DateLastUpdate DateTime , LastUpdateBy varchar(50) )



::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

View 5 Replies View Related

How Do I Use A @variable To Hold On Value Return From An Exec (&#39;string Command&#39;) Statement.

Sep 19, 2000

How do I use a @variable to hold on value return from an exec ('string command') statement.

Example for:

declare @OldID int
declare @cmd varchar(255)
declare @db varchar(25)
declare @OldOwner varchar(25)

set @db = 'DBNAME'
set @OldOwner = 'USERNAME'

select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
exec (@cmd)

How can I use @OldID to hold on UID return from this statement.

When I try use:
select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
then I'm getting a error message: @OldID not declare.

Thanks.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved