How To Assign OPENQUERY Results from Linked Servers to local variables..

Hi All,

I have a problem about assigning the results of an OPENQUERY. Please check the code below:

DECLARE @.sqlString nvarchar(4000)
DECLARE @.sqlString1 nvarchar(4000)
DECLARE @.custName nvarchar(100)

SET @.custID = 2
SET @.sqlString1 = 'SELECT * FROM myTable WHERE CustID = ' + CAST( @.custID as varchar(6))
SELECT @.sqlString = 'SELECT CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
EXECUTE(@.sqlString)

--
The above code works fine but i need something like
SELECT @.sqlString = 'SELECT @.custname=CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
EXECUTE(@.sqlString)

By doing this i want to assign the CustName to the local variable @.custName.
I tried to use a temp table but in that case i was not able to specify a where clause in @.sqlString1. (i'd rather use linked_server.databasename.owner.tablename , but i read that this does not provide good performance )

Any suggestions about the problem are welcomed!
Thanks in advance,

Bahtiyar KARANLIKdeclare @.sqlString nvarchar(1000)
SELECT @.sqlString = 'SELECT @.custname=CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
EXEC sp_executesql @.sqlString, N'@.custname varchar(20) out', @.custname out

or

SELECT @.sqlString = 'SELECT CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
create table #a (s varchar(20))
insert #a
exec (@.sqlstring)

select @.custname = s from #a
drop table #a

Using the 4 part name in the query should be just s good. If you are joining with strings then make sure the servers are collation compatible so that the filter is performed on the remote server and check the query plan.
This sort of thing can give bad performance if used as a join to a local table but if you are just selecting using constants for a filter it should be OK.

How to alter the data type of a field

I want to change the filed type form nvarchar(5) to varchar(10)
There is no need to reserve the data in the filed.
How can I do with SQL command?
Use the ALTER TABLE command:
ALTER TABLE my_table
ALTER COLUMN my_column varchar(10)
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>I want to change the filed type form nvarchar(5) to varchar(10)
> There is no need to reserve the data in the filed.
> How can I do with SQL command?
>
|||Thank,
Can I determinate if the data type varchar before ater it.
Like
if type(my_table.my_column) <>varchar(10)
ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.g bl...
> Use the ALTER TABLE command:
> ALTER TABLE my_table
> ALTER COLUMN my_column varchar(10)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>
|||What version?
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Thank,
> Can I determinate if the data type varchar before ater it.
> Like
> if type(my_table.my_column) <>varchar(10)
> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com>
> glsD:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.g bl...
>
|||my version is SQLServer 2005 Express
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.g bl...
> What version?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
>
|||This query returns all the datatypes and lengths for columns in a given
table. You should be able to figure out how to write your IF statement from
this information:
SELECT object_name(object_id) as [object_name], c.name as column_name,
t.name as type_name, c.max_length
FROM sys.types t join sys.columns c
ON t.user_type_id = c.system_type_id
WHERE object_name(object_id) = 'my_table'
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eoktgyCGHHA.2456@.TK2MSFTNGP06.phx.gbl...
> my version is SQLServer 2005 Express
> "Kalen Delaney" <replies@.public_newsgroups.com>
> glsD:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.g bl...
>

How to alter the data type of a field

I want to change the filed type form nvarchar(5) to varchar(10)
There is no need to reserve the data in the filed.
How can I do with SQL command?Use the ALTER TABLE command:
ALTER TABLE my_table
ALTER COLUMN my_column varchar(10)
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>I want to change the filed type form nvarchar(5) to varchar(10)
> There is no need to reserve the data in the filed.
> How can I do with SQL command?
>|||Thank,
Can I determinate if the data type varchar before ater it.
Like
if type(my_table.my_column) <>varchar(10)
ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:O30DznAGHHA.2128@.TK2MSFTNGP03
.phx.gbl...
> Use the ALTER TABLE command:
> ALTER TABLE my_table
> ALTER COLUMN my_column varchar(10)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>|||What version?
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Thank,
> Can I determinate if the data type varchar before ater it.
> Like
> if type(my_table.my_column) <>varchar(10)
> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com>
> glsD:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>|||my version is SQLServer 2005 Express
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:ORvLXDCGHHA.4804@.TK2MSFTNGP03
.phx.gbl...
> What version?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
>|||This query returns all the datatypes and lengths for columns in a given
table. You should be able to figure out how to write your IF statement from
this information:
SELECT object_name(object_id) as [object_name], c.name as column_name,
t.name as type_name, c.max_length
FROM sys.types t join sys.columns c
ON t.user_type_id = c.system_type_id
WHERE object_name(object_id) = 'my_table'
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eoktgyCGHHA.2456@.TK2MSFTNGP06.phx.gbl...
> my version is SQLServer 2005 Express
> "Kalen Delaney" <replies@.public_newsgroups.com>
> glsD:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.gbl...
>

How to alter the data type of a field

I want to change the filed type form nvarchar(5) to varchar(10)
There is no need to reserve the data in the filed.
How can I do with SQL command?Use the ALTER TABLE command:
ALTER TABLE my_table
ALTER COLUMN my_column varchar(10)
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>I want to change the filed type form nvarchar(5) to varchar(10)
> There is no need to reserve the data in the filed.
> How can I do with SQL command?
>|||Thank,
Can I determinate if the data type varchar before ater it.
Like
if type(my_table.my_column) <>varchar(10)
ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
> Use the ALTER TABLE command:
> ALTER TABLE my_table
> ALTER COLUMN my_column varchar(10)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>|||What version?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Thank,
> Can I determinate if the data type varchar before ater it.
> Like
> if type(my_table.my_column) <>varchar(10)
> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com>
> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>> Use the ALTER TABLE command:
>> ALTER TABLE my_table
>> ALTER COLUMN my_column varchar(10)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>>
>|||my version is SQLServer 2005 Express
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.gbl...
> What version?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
>> Thank,
>> Can I determinate if the data type varchar before ater it.
>> Like
>> if type(my_table.my_column) <>varchar(10)
>> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>>
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>> Use the ALTER TABLE command:
>> ALTER TABLE my_table
>> ALTER COLUMN my_column varchar(10)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>>
>>
>|||This query returns all the datatypes and lengths for columns in a given
table. You should be able to figure out how to write your IF statement from
this information:
SELECT object_name(object_id) as [object_name], c.name as column_name,
t.name as type_name, c.max_length
FROM sys.types t join sys.columns c
ON t.user_type_id = c.system_type_id
WHERE object_name(object_id) = 'my_table'
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eoktgyCGHHA.2456@.TK2MSFTNGP06.phx.gbl...
> my version is SQLServer 2005 Express
> "Kalen Delaney" <replies@.public_newsgroups.com>
> ¼¶¼g©ó¶l¥ó·s»D:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.gbl...
>> What version?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
>> Thank,
>> Can I determinate if the data type varchar before ater it.
>> Like
>> if type(my_table.my_column) <>varchar(10)
>> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>>
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com>
>> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>> Use the ALTER TABLE command:
>> ALTER TABLE my_table
>> ALTER COLUMN my_column varchar(10)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>>
>>
>>
>

How to add newline in column of type nvarchar

Hi,
I want to add a newline in a content of sql column (using t-sql and not asp.net textbox) so when content is being rendered in a .net textbox I get separate rows, so instead
col1 col2 col3
I woule like to have
col1
col2
col3
Thanks

You can use CHAR(13) to insert a carriage return into the string. e.g.

INSERT INTO MyTable (MyStringColumn) VALUES('My name is'+CHAR(13)+'Colin Mackay')
See also SQL Server Books-On-Line:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_6xte.asp
Does this help?

|||Yes, that's it, thanks

how to add a nvarchar parameter to ntext or text types?

please help >>

how to add a nvarchar parameter to ntext or text types like :

set @.ntext_param = @.ntext_param + @.nvarchar_param

You cannot declare variables of text/ntext/image data type in SQL Server and manipulate them. You can only have parameters of SPs or functions with these data types. You can however use UPDATETEXT to append a varchar/char/nvarchar/char/text/ntext/varbinary/binary value to text/ntext/image value.

In SQL Server 2005, you can use the new nvarchar(max) and nvarbinary(max) data types similar to regular character data types. This will allow you to use concatenation operators. These data types are replacement for text/ntext/image data types.

|||

Concatenation is not supported on ntext, but is supported on nvarchar(max). You can do the following:

set @.ntext_param = convert(nvarchar(max), @.ntext_param) + @.nvarchar_param