MS SQL Server :: Employing XML Formatted String Data Rather Than Normal String(char(), Nchar() Or Varchar() Values

What Is The Difference Between Updating Null Value Vs Empty String To Varchar/char Field?
Hi,
What is the difference updating a null value to char/varchar type column

versus empty string to char/varchar type column?Which is the best to do and why?
Could anyone explain about this?

Example:

Table 1 : tCountry - Name varchar(80) nullable
Table 2 :tState - Name char(2) nullable
Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state
So,when the state is not present for the country ,i have two options may be - null,''
tCountryDetails.State = '' or tCountryDetails.State = null?

View Replies !

Converting CHAR/VARCHAR/TEXT Into NCHAR/NVARCHAR/NTEXT!!!
Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.

After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.

PLEASE HELP ME!!!

Thanks,

Firoz Ansari

View Replies !

What's Difference About Datatype &"Char,VarChar,NChar,NVarChar In Sql 2000 ?
Hi All:
         I am new to Sql 2000 database,Now I'm planing to create a table in my databse,my table included below fields like this :
       PoNo(the length is 15 characters) ,Supplier Name(the length is 50 characters).etc
      but I don't how to select the datatype for them. should I select Char or VarChar ?
      which one is the best slection ?
thans in advanced!




View Replies !

Insert Date As Formatted String
Hello,I thought this would be easy, and I've read a lot of posts, but Ican't seem to find exactly what I'm trying to do.

Quote:

View Replies !

Input String Was Not In A Correct Format For Formatted Currency
Ok, when I bind a textbox in my FormView I'm changing the format to
currency (<%# Bind("TotalValue","{0:c}") %>). When I click
Update I get "Input string was not in a correct format" since
TotalValue is Int32 data type.

Shouldn't it convert it back to Int32 when it updates the db? Or do I have to do it manually, and if so how?

View Replies !

Select Qry: 1 Real Value To Formatted Decimal, 2. Replace NULL With String, How Do I?
Hi.

1.
Have a query that fetches a real value (e.g. 4.3345643)

Let say the field is called TheReal.

How can I format the value in the select statement so I get a thousand separator(s) and two decimals in the resultset.

2. In the same query I have a left join as well.
Table 2 retur (sometimes) <NULL>.
Is it possible to force this <NULL> value to be a fixed string value instead in the select statement.

View Replies !

How Do I Get Bold And Normal Formatted Text In A Single Field?
In SSRS I am trying to get a textbox value to hold text with a mixture of formatting, along the lines of "name (country)" where the "name" part is bold and the "(country)" is normally formated.

Can anyone help?

Thanks in advance.

View Replies !

String Or Binary Data Would Be Truncated. (only For 1700 Character String?)
I am trying to insert a row into a table of Microsoft SQL Server 2000.

There are various columns.

[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,

The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-

StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset

at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?

Can anyone please tell me what's wrong?

View Replies !

Adding String To Database, But Name Of String Is Added, Not Data
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!

Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page

Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try

End Sub
End Class

View Replies !

Replace Char At End Of String
Hi,

I have a table where this one column(varchar) has rows of data with a period at the end of the string. Is there any function I could use to remove all the periods?

For eg:
bhjio.
shtpl.

should become:
bhjio
shtpl

Thanks

View Replies !

Search Char In String
Hi

Is there any function to seperate a sting (VARCHAR) into two based on a character in the string (e.g. '-').

In short is there "InStr" or "split" type function in T-SQL ? If not what is the alternative?

I have a column called "Range" (VarChar) in the table which has ranges like 1-14 or 15-49 etc.

I need to select records by compare a variable (numeric) against these ranges.

So for instace if the variable has 15 its in range 15-49.

I was thinking of spliting the range field into two so that comparison is possible.

Any suggestions?

thanks

View Replies !

How To Compare Nvarchar(15), Char(5), And String
I have used a query statement with the following WHERE string to 'Fill' a dataset.
"AND (A.ApptsDate > '" & strApptPreDate & "' OR (A.ApptsDate = '" & strApptPreDate & "' AND A.ApptsTime >= '" & strApptPreTime & "' ))" & _ and strApptPreTime is defined as:Dim strApptPreTime As String = SomaShared.strPadTime(CStr(dApptCalcNewDate.Hour) + ":" + CStr(dApptCalcNewDate.Minute))

Somehow, the dataset showed only the ApptsTime after 10 am. After more than 2 weeks of debugging, I still can see a dataset watch for > 10 amAppsTime only.   Now I am guessting, the problem is 9 is different from 10 - 16, it all because 9 is single digit. Then I check the data type settings for these variables. Here are what I found:
In SQL Server Agent job, the ApptsTime data was 'inserted' by @NewApptTimes, which is declared as char(5).In SQL Server database, the ApptsTime was defined as nvarchar(15).
My question are:     1. The reason why there were no 9 am data for the dataset, is becasue 9 am of nvarchar(15) is not > 8:30 of strApptPreTime?     2. If the answer to the quation 1 is yes, how do I define AppsTime and/or strApptPreTime?
TIA,Jeffrey

View Replies !

How To Drop Intro Char In A String?
Hi guys,

From my DTS is like this:

DTSDestination("Tex") = replace(DTSSource("Tex"),Chr(13) & Chr(10), "<br>")

And now I try unsuccesfully this one on a Derived Column task

REPLACE([Tex], Chr(13), " " )

TIA

View Replies !

Turn Hex Into Char String And Concatenate
I've been trying to return hex data in a way that can be concatenated.
I need the actual hex info (e.g. 0x6E3C070) as displayed
since it contains info about the path to a file.
So I can turn it into D:6E3C7 to get the path to the file.
In searching around I have come across a way to do this but can't figure out how to get it to run through a column and either display or insert into a table multiple results.

-Here's the user function that converts an integer into a hex string-

CREATE FUNCTION udf_hex_string (@i int)
RETURNS varchar(30) AS
BEGIN
DECLARE @vb varbinary(8)
SET @vb = CONVERT(varbinary(8),@i)
DECLARE @hx varchar(30)
EXEC master..xp_varbintohexstr @vb, @hx OUT
RETURN @hx
END
GO

-Using this table-

create table
XPages
(PageStoreId int null,
HexString varchar (30) null,
VolumePath varchar (60) null, )

--'PageStoreId' contains the data that needs to be converted into the editable hex string
--'HexString' is where I'd like it to go so I can parse it later.

--I can run the below select and get the hex string. But am stuck on how to run a select or update that would run through the 'XPages.PagestoreId'
column and insert the hex string into the 'XPages.Hexstring' column. 'XPages.PagestoreId' could have 100's of entries that need to be converted and placed in the relevant the 'XPages.Hexstring' column.

SELECT dbo.udf_hex_string(1234)

Thanks

View Replies !

Conversion From Char To Nchar
Hello,

I am trying to convert a single code page MS Server database into a unicode database, using the unicode data types,NCHAR, NVARCHAR, NTEXT. The problem is that in the original database, indexes and constraints have been defined on the tables whose configurations need to be changed. As a result, the ALTER TABLE command fails. Are there any other alternative solutions?
Also, data from the old database needs to be preserved. The objective is to create a unicode database which keeps the old data intact as well as accepts the new data in unicode.
It would be great if you could help!
Thanks,
Sheetal.

View Replies !

Built In Function To Search The Occurence Of Char In String

Hi Everyone-
i woder if someone know Built in function to search the occurence of char in string in T-SQL

and iam not talking about the Index of the first occurence (e.g CHARINDEX )
but it is more generic

i want to send to the function to ask about the index the second or the third or N occurence of the char.
and i wonder if it is built in for performance reasons

example
getseachIndex("M|d|d","|",2)==> the result is 4

View Replies !

Char Nchar Vchar Nvchar !?
Hello
in MS SQL 2000 for text it is possible ti use
char
nchar
varchar
nvarchar

what must be used and for what ?
I want to store normal text with occidental char (accents)

thank you

View Replies !

Changing Char/NChar Column From NOTNULL To NULL
Hi I have a table, which contains Char and NChar NOT NULL columns
Now I need to change it to NULL, when I use the following command, it fails for the following error,

The command I used,
ALTER TABLE <TableName>
ALTER COLUMN <ColName> CHAR NULL
ALTER TABLE <TableName>
ALTER COLUMN <ColName> NCHAR NULL

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.

But for the same table, the below command executes fine,
ALTER TABLE <TableName>
ALTER COLUMN <ColName> SMALLINT NULL

Also I can change the NULLABILITY from NOTNULL to NULL using Enterprise Manger, editing the table using Table Design and selecting Allow Nulls option.

I need a script to accomplish this task.

Any help would be greatly appreciated.

-Senthil

View Replies !

How To Convert Float To Varchar Or String..
I've three columns:
Length          Width            Height
1.5                  2.5              10
2                   3.7                19

in Query I want to display Like 1.5 X 2.5 X 10 (Length, Width, Height).....

???

View Replies !

Retriving An Xml String Stored In Varchar(max)
I try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code dr = cmd.ExecuteReader();
_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();
Label1.Text = _xmlFile; and this is what I get "12"Maybe I missed something to get the whole XML String

View Replies !

Evaluating Varchar String With Math
In reference to this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50392

madhivanan asks for sample table data to avoid using a cursor. I could really use this, so here is the table

CREATE TABLE #T (
FORMULA VARCHAR(15),
ANSWER decimal(15,8)
)
INSERT INTO #T SELECT '1', NULL
INSERT INTO #T SELECT '1.0 + 2', NULL
INSERT INTO #T SELECT '1/2', NULL
INSERT INTO #T SELECT '1/2+2', NULL
INSERT INTO #T SELECT '1/(2+2)',NULL
INSERT INTO #T SELECT '3*7.5', NULL
INSERT INTO #T SELECT '3/2*.6-2', NULL
INSERT INTO #T SELECT '(1-1)', NULL

SELECT * FROM #T

-- RUN THE MATH FUNCTIONS, UPDATE THE TABLE'S ANSWER COLUMN WITH THE ANSWER

SELECT * FROM #T

DROP TABLE #T

I would expect this output:

FORMULA ANSWER
====================
1 NULL
1.0 + 2 NULL
1/2 NULL
1/2+2 NULL
1/(2+2) NULL
3*7.5 NULL
3/2*.6-2 NULL

FORMULA ANSWER
====================
1 1.00000000
1.0 + 2 3.00000000
1/2 0.50000000
1/2+2 2.50000000
1/(2+2) 0.25000000
3*7.5 22.50000000
3/2*.6-2 -1.10000000
(1-1) 0.00000000

Any help appreciated, thanks,

View Replies !

This Stored Procedure Can Be Used To Search And Replace Substring In The Char, Nchar,
#1 This stored procedure can be used to search and replace substring in the char, nchar, varchar and nvarchar columns in all tables in the current database. You should pass the text value to search and the text value to replace. So, to replace all char, nchar, varchar and nvarchar columns which contain the substring 'John' with the substring 'Bill', you can use the following (in comparison with the SetTbColValues stored procedure, this stored procedure replace only substring, not the entire column's value):

EXEC replace_substring @search_value = 'John', @replace_value = 'Bill'

View Replies !

Incompatible Datatype?? Varchar(50) Not Recognised As String?
here is my code snippet

Session("matricN") = Trim(TextBox1.Text)
Dim txtValue As String
txtValue = Session("matricN")
da = New SqlDataAdapter("select MatricNumber,Name, Roles from Register where MatricNumber = " & txtValue, addRoleConn1)

MatricNumber is in varchar(50) datatype,, the errror says there is a syntax near "="

View Replies !

Default Empty String Value For Varchar Columns
Hi guys,
Is there a way to declare a default value of empty string '' for a varchar table column?
Thanks,Kevin

View Replies !

String To Varchar Conversion - Sybase And SSRS
Hi,

I am having an issue with a Report in SSRS against a Sybase db.

The dataset for the report is a Sybase stored proc which is passed a parameter (varchar). When I run the proc in Sybase it runs fine, when I run it in SSRS in the data tab it runs fine, but when I try to preview the report, or run from IE after deploying it I get the following error:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Query execution failed for data set 'Table_1'. (rsErrorExecutingCommand) Get Online Help
The given type name was unrecognized

Looks like it is an error converting the string in SSRS to a varchar in Sybase, but I don't know how to fix it.

If I create a table with an identity (int) column and the varchar column, join this to the proc and pass the int as the parameter the report works fine!! (This is not an option in production though.)

I am using SybaseASE OLE DB Provider Version 02.70.0032

Any ideas?

TIA,
Guytz

View Replies !

Returning VARCHAR(MAX) Result Set To A String Variable
Hi,

I have a stored procedure that returns a value of data type VARCHAR(MAX) and inserts this into a package variable of type System.Object called XMLA_PartitionScript.

XMLA_PartitionScript contains XMLA code to create a partition in an AS 2005 database. I want to pass the variable to an Analysis Services Execute DDL Task object as the Source in the task, but before I do this I have to convert it back to a string.

Has anyone any ideas on how to do this, as the size of XMLA_PartitionScript is too large to be held in a string (hence I had to pass the code into a System.Object variable in the first place).

I found the following blog that suggested this approach, but I can't manage to convert the variable from an Object datatype to a string datatype:

http://blogs.conchango.com/stevewright/archive/2006/01/20/2686.aspx

Any help appreciated...

PaulOR

View Replies !

Converting Timestamp To Varchar Or Concatenating It With A String
Hello,

I apologise if this question has been asked before but I have searched forums and the web and have not found a solution. I am current creating a script that has a cursor that builds a sql statement to be executed e.g.

--code within cursor

SELECT          '
DECLARE @Result INT
EXEC @Result = DELETE_DOCUMENT
            @DocumentID = ' + STR(DocumentID) + ',
            @TimeStamp =' + CAST([Timestamp] as varchar) + ',

            -- CHECK RESULT AND STATUS
    -- IF OK LOG IN META_BATCH ELSE LOG ERROR' AS SQL
            FROM Document

The problem I am having is trying to join the timestamp column into the sql string. I have tried to cast the time stamp to a varchar but I end up with the following output for the timestamp column values

    T
    T€‘
    T
    xnÃž
    T!
    T"
    T#
    T$
    T%
    T&
    T'
    T(
    T)
    T*
    T+
    T,

instead of

0x0000000013540F1C
0x0000000013540F1E
0x0000000013540F1F
0x0000000013786EDE
0x0000000013540F21
0x0000000013540F22
0x0000000013540F23
0x0000000013540F24
0x0000000013540F25
0x0000000013540F26
0x0000000013540F27
0x0000000013540F28
0x0000000013540F29
0x0000000013540F2A
0x0000000013540F2B
0x0000000013540F2C

which would not allow my delete script to work correctly. So I would really appreciate some advice to a pointer to where I might find out how to convert the timestamp.

Thanks
Sam

View Replies !

How To Pass A GUID String To A Varchar In SQL Stored Procedure
In my .NET app I have a search user control. The search control allows the user to pick a series of different data elements in order to further refine your display results. Typically I store the values select in a VarChar(5000) field in the DB. One of the items I recently incorporated into the search tool is a userID (GUID) of the person handling the customer. When I go to pass the selected value to the stored proc

objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = strCriteria;
I get: Failed to convert parameter value from a String to a Guid.
Ugh! It's a string, dummy!!! I have even tried:
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString();
and

objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString("B");
but to no avail. How can I pass this to the stored proc without regard for it being a GUID in my app?

View Replies !

Script To Search For A String In All Varchar Columns In All Tables In A Database?
I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.

View Replies !

How To Use String Values? I Really Need Help For This. Thanks.
Hello,

I am working with ASP.NET/VB and Microsoft SQL 2000 database.

I have a search form where keywords are submitted.
Consider I write the the keywords 'asp' and 'book'. The results page is called as follows: results.aspx?search=asp%20book

Then I use this script in results.aspx to put the keywords in a string:

Sub Page_Load(sender As Object, e As System.EventArgs)
Dim keywords() As String = Request.QueryString("search").Split(CChar(""))
End Sub

My table is set for FULL TEXT SEARCH.
Consider the SQL when I look for records containing 'asp' and 'book':

SELECT *
FROM dbo.documents
WHERE CONTAINS (*, 'ASP or BOOK')

This SQL looks only for these words. What I need is to look for records that contain the Keywords included in the string keywords().

Can you tell me how to access the string values in the SQL and use it?

Thanks,
Miguel

View Replies !

Averaging String Values?
SQL neophyte hoping this is an easy answer...

I'm trying to obtain an average for a set of string values. "Huh?" (In an investment portfolio, some securities have ratings, e.g., AAA or Aa2 or BBB, etc., and to obtain an overall rating for the portfolio you have to find a way to average the string values from each individual security.) I figured I'd assign a numerical value to each, average, and convert back to string. My first attempts have been a little futile...can anyone help?

View Replies !

Format Values Into One String
HI!

I do a select and concatinating the answers into a one column table @tab(string).
Is it possible to format these values like this:
GENAV     DELAV             TOTIP                                RES
  12            myvalues          www.notech.com                 1
   1             starthere          192.168.0.2                         1
125            or here             www.hereistomanychar.s      0

max 3       max 10             max 22                               max 1

the first column max 3 characters the second column max 10 char...
I want to do this to get a good view of the data i the table @tab

Here is the select:

INSERT INTO @tab

SELECT Convert( varchar, GENAV)+ ' ' + Convert( varchar, DELAV)+ ' ' + Convert( varchar, TOTIP) ' ' + Convert( varchar, RES)

from dbo.MOVE

where DATE between @starttime and @theEndTime

With this select it looks like:

  12 myvalues www.notech.com 1
1 starthere 192.168.0.2 1
125 or here www.hereistomanychar.s 0

This is very complicated to read!

View Replies !

Order By With String Values
Hi all,
I have table with id column of nvarchar..

Now,WHEN I RUN THIS QUERY,
select ID from <TABLE>,
IT RETURNS

erfq-1
erfq-10
erfq-100
erfq-1000
erfq-10000
erfq-1001
erfq-101
erfq-11
erfq-12
erfq-19
erfq-2
erfq-3
erfq-30
erfq-4
erfq-5
erfq-50
erfq-6
erfq-9
erfq-99
erfq-999

WHERE AS I NEED IT AS BELOW

erfq-1
erfq-2
erfq-3
erfq-4
erfq-5
erfq-6
erfq-9
erfq-10
erfq-11
erfq-12
erfq-19
erfq-30
erfq-50
erfq-99
erfq-100
erfq-101
erfq-999
erfq-1000
erfq-1001
erfq-10000

How Can I achive this one ?

Thanks in Advance
Dana

View Replies !

Conditionally Grouping Different String Values Together
I have a sql server table column that can contain the following possible values. 1. 766/IT 2. 777/HR3. 890/EG4. 012/AS5. Trainee6. Contractor 7. Others I want to write an SQL query grouped by this column, Trainee should be one group, Contractor should be another different group, Others should be another different group and then every thing else ( 766/IT ,777/HR ,890/EG, 012/AS) should be grouped together as one group. Think of it in terms of a pie chart with those groups.

View Replies !

Possible To Return A String Of Table Values?
This is a portion of my stored procedure in T-SQL

CREATE PROCEDURE GetPoll
(
@PollID INT,
@PollQuestion VARCHAR(1000) Output,
@PollOptions VARCHAR(1000) Output,
@PollPercentages DECIMAL Output
)

AS

SELECT @PollQuestion = (SELECT PollQuestion FROM Polls WHERE PollID = @PollID)

SELECT @PollOptions = (SELECT [Option] FROM PollOPtions WHERE PollID = @PollID)

*SELECT @PollPercentages = (SELECT [Option], COUNT([Option]) As Num_Votes FROM Votes WHERE PollID = 1 GROUP BY [Option])

The final part(*) of this SQL will return more than one value, so is it possible for me to return all that information in one varaible with one SELECT query?? Or would I need to use a loop and query a value at a time, and store it into a delimited string?

thx in advance...

View Replies !

Rowset Values In A Single String
I am not close to an sql server today and this question was posed to me. can someone hook me up?

" I want to query a column of values and place them into a single string seperated by commas" (as a function)

Table a
123
456
789
321
654
987

output
123,456,789,321,654,987

thanks in advance

View Replies !

Capturing Values From Executing A String
I currently have a need to dynamically build an sql statement that always returns a single value when executed. The sql statement is always the same except the database name reference in the statment.

What I need is to be able to capture that value for later use in the procedure. Since this is a stored procedure, I can't use the "USE" statment to switch databases and I haven't been able to figure this out using the Execute statement. I can execute the string, but I can't capture the value.

I'm simply trying to execute the same set of sql statements in a stored procedure without hardcoding database names or build an identical stored procedure in all our databases.

Any help is appreciated.

View Replies !

Datatype Question Varchar(max), Varchar(250), Or Char(250)

I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.

How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?

Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?

Should I just go with char(250) and watch my db size explode?

Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.

Any insight to this would be appreciated.

View Replies !

NULL Values Vs Empty String Vs Space
How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?

View Replies !

Problem Concatenating Column Values Into String...
I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!

View Replies !

Converting Hexadecimal String Values To Int In SQL Server.
Hi All,In continuation to previous posting, I have one more query :A column with String containing hex values say, CA02B. I have to access thevalue in a int variable, any hints on how we should proceed on this?Thanks & Regards,Chandra Mohan

View Replies !

Concatinating String Values From Multiple Rows
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 Replies !

Employing XML Formatted String Data Rather Than Normal String(char(), Nchar() Or Varchar() Values

Hello, Is there a way of passing in an xml formatted string or text to the report through a data set and have the textbox or table in which it displays keep the formatting specified in the xml string rather than in the textbox properties? Thanks.

Hello,
Is there a way of passing in an xml formatted string or text to the report through a data set and have the textbox or table in which it displays keep the formatting specified in the xml string rather than in the textbox properties?

Thanks.