Saving Store procedure on table

Hello there
I have some store procedure that return query as result.
Is there a way to store the result in table?"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:em9Zi27CGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have some store procedure that return query as result.
> Is there a way to store the result in table?
Maybe this will give you the information you need:
http://www.sommarskog.se/share_data.html|||INSERT INTO YourTable (Col1, Col2...) EXEC YourSp
Andrew J. Kelly SQL MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:em9Zi27CGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have some store procedure that return query as result.
> Is there a way to store the result in table?
>
>|||You can create the table upfront & use INSERT .. EXEC statement.
INSERT tbl ( ... ) EXEC usp @.param1, @.param2, ...
Alternatively, you can create a loopback and use OPENQUERY like:
EXEC sp_serveroption 'data access', true
GO
SELECT * INTO tbl
FROM OPENQUERY ( Server, 'SET FMTONLY OFF; EXEC.. ' )
Anith

Saving Carriage Return in a nvarchar field (SQL Server 2005)

Hi,

As you can see, I'm totally new at Sql Server.

I have a problem, I store text into a nvarchar field (could be a 200 or 20,000 characters long string), inside the text there are several carriage returns which I would like to preserve to later presentation, but when retreiving the data from sql server I got the "cr" as "?", also I opened the database from Sql Managment and all cr's were saved as "?".

What can I do to preserve the cr inside each field ?

Thanks in advance.

Hi George,

To resolve this issue, you need to ensure that insert/update operations are presenting character data which includes CR/LF's in a manner that is recognisable to mssql:

create table #crTest
(
charCol nvarchar(100) not null
);

declare @.s nvarchar(100);

set @.s = 'this is a test:' + nchar(13) + 'new line';
insert into #crTest values(@.s);

select * from #crTest;

In the above example, the CRLF is represented as the ASCII code 13 - sql is able to recognise and preserve formatting during subsequent interrogations.

To correct existing data, you could use the REPLACE function as such:

update MyCharTable

set problematicColumn = replace(problematicColumn, '?', nchar(13));

However this assumes you do not have a valid "?" somewhere in the "problematicColumn" column.

Cheers,

Rob

|||

Thanks for your reply Robert,

The problem is that I could have some valids "?", so I would have to make mssql understand that the chr(13) is indeed a chr(13) and not to convert it to "?"

According to your answer, if I'm storing the data from vb.net to mssql, is it possible to do this ?:

SqlStr as string = "Insert into table (field1nvarchar) Values ('" & Var1.replace(chr(13),nchar(13)) & "')"

or how should I do it ?

Thanks in advance.

George

|||

Hi George,

Sorry for the delay in responding - I've been off.

If you're using vb, they where you would normally insert your VBCRLF (or is it Environment.Newline in vb too now?) you would insert a char(13):

sVal as string = " 'this is line 1' " & VBCRLF & " 'this is line 2' "

Should be:

sVal as string = " 'this is line 1' + NCHAR(13) + 'this is line 2' "

AdoCommand.Execute("Insert into MyTable (column1Nvarchar) values (" & sVal + ")")

The point to keep in mind is that REPLACE is a SQL function and if you're able to control how CR's are represted to sql at the time of insert/update, then you do not need it - simply use CHAR(13) to tell mssql to store a CR.

Cheers,

Rob

|||

I'm having this same problem too. However; there is no NCHAR(13) in VB.NET that I know of. It is in MSSQL though. Should I do something like use chr(13) instead of vbCrLf and then REPLACE all chr(13)'s with a NCHAR(13) on the SQL side in my stored procedure that saves my data? By doing this, will these be automatically converted back to vbCrLf's on the VB.NET side so that my data will show properly in my multiline textbox?

Thanks

|||

Hi George,

Yes, you'll have to replace the vbCrLf with NCHAR(13) in the query/parameter string. It's not obvious, but if you look closely in my previous post, the NCHAR(13) is now encolsed in a string:

sVal as string = " 'this is line 1' " & VBCRLF & " 'this is line 2' "

Should be:

sVal as string = " 'this is line 1' + NCHAR(13) + 'this is line 2' "

So in your proc, I assume you've a nchar/char parameter that accepts the string param from VB - in this case, sVal. If the @. param of the sp is a nchar, then in the above scenario, the actual CRLF will be included in the @. param - you won't need to do anything further to recognise and store the CR/LF on the mssql side.

Cheers,
Rob

Saving Carriage Return in a nvarchar field (SQL Server 2005)

Hi,

As you can see, I'm totally new at Sql Server.

I have a problem, I store text into a nvarchar field (could be a 200 or 20,000 characters long string), inside the text there are several carriage returns which I would like to preserve to later presentation, but when retreiving the data from sql server I got the "cr" as "?", also I opened the database from Sql Managment and all cr's were saved as "?".

What can I do to preserve the cr inside each field ?

Thanks in advance.

Hi George,

To resolve this issue, you need to ensure that insert/update operations are presenting character data which includes CR/LF's in a manner that is recognisable to mssql:

create table #crTest
(
charCol nvarchar(100) not null
);

declare @.s nvarchar(100);

set @.s = 'this is a test:' + nchar(13) + 'new line';
insert into #crTest values(@.s);

select * from #crTest;

In the above example, the CRLF is represented as the ASCII code 13 - sql is able to recognise and preserve formatting during subsequent interrogations.

To correct existing data, you could use the REPLACE function as such:

update MyCharTable

set problematicColumn = replace(problematicColumn, '?', nchar(13));

However this assumes you do not have a valid "?" somewhere in the "problematicColumn" column.

Cheers,

Rob

|||

Thanks for your reply Robert,

The problem is that I could have some valids "?", so I would have to make mssql understand that the chr(13) is indeed a chr(13) and not to convert it to "?"

According to your answer, if I'm storing the data from vb.net to mssql, is it possible to do this ?:

SqlStr as string = "Insert into table (field1nvarchar) Values ('" & Var1.replace(chr(13),nchar(13)) & "')"

or how should I do it ?

Thanks in advance.

George

|||

Hi George,

Sorry for the delay in responding - I've been off.

If you're using vb, they where you would normally insert your VBCRLF (or is it Environment.Newline in vb too now?) you would insert a char(13):

sVal as string = " 'this is line 1' " & VBCRLF & " 'this is line 2' "

Should be:

sVal as string = " 'this is line 1' + NCHAR(13) + 'this is line 2' "

AdoCommand.Execute("Insert into MyTable (column1Nvarchar) values (" & sVal + ")")

The point to keep in mind is that REPLACE is a SQL function and if you're able to control how CR's are represted to sql at the time of insert/update, then you do not need it - simply use CHAR(13) to tell mssql to store a CR.

Cheers,

Rob

|||

I'm having this same problem too. However; there is no NCHAR(13) in VB.NET that I know of. It is in MSSQL though. Should I do something like use chr(13) instead of vbCrLf and then REPLACE all chr(13)'s with a NCHAR(13) on the SQL side in my stored procedure that saves my data? By doing this, will these be automatically converted back to vbCrLf's on the VB.NET side so that my data will show properly in my multiline textbox?

Thanks

|||

Hi George,

Yes, you'll have to replace the vbCrLf with NCHAR(13) in the query/parameter string. It's not obvious, but if you look closely in my previous post, the NCHAR(13) is now encolsed in a string:

sVal as string = " 'this is line 1' " & VBCRLF & " 'this is line 2' "

Should be:

sVal as string = " 'this is line 1' + NCHAR(13) + 'this is line 2' "

So in your proc, I assume you've a nchar/char parameter that accepts the string param from VB - in this case, sVal. If the @. param of the sp is a nchar, then in the above scenario, the actual CRLF will be included in the @. param - you won't need to do anything further to recognise and store the CR/LF on the mssql side.

Cheers,
Rob

Save the return value into a variable

Hi
How can I save the returned XML Expression into a variable? I tried to this
a described on the next line, but with no luck...
I use SQL Server 2000 SP 4
DECLARE @.doc nvarchar(4000)
SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
auto)
Thank's
MichelHello Michel,
Can't be done in SQL Server 2000 since the XML Serialization is always done
post query there.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||If you have SQL Server 2005, the below query is valid. And you'lll have more
features in FOR XML.
"Michel" <michel_mueller@.bluewin.ch> wrote in message
news:ujy1pp69GHA.4708@.TK2MSFTNGP05.phx.gbl...
> Hi
> How can I save the returned XML Expression into a variable? I tried to
> this a described on the next line, but with no luck...
> I use SQL Server 2000 SP 4
> DECLARE @.doc nvarchar(4000)
> SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
> auto)
> Thank's
> Michel
>

Save the return value into a variable

Hi
How can I save the returned XML Expression into a variable? I tried to this
a described on the next line, but with no luck...
I use SQL Server 2000 SP 4
DECLARE @.doc nvarchar(4000)
SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
auto)
Thank's
Michel
Hello Michel,
Can't be done in SQL Server 2000 since the XML Serialization is always done
post query there.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/