restrict access to database: string

Showing posts with label string. Show all posts

Friday, March 23, 2012

How to run multiple store procedures in SQL Reporting Service

Can we run two store procedures in the Query String under the Dataset section
of SQL Reporting Service 2005? It seems like the Query String only allow one
store procedure. Ww want to use a separate store procedure to open a
symmetric key before we run the second store procedure to retrieve the
sensitive data.What about writing a thrid procedure calling the two others ?
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--sql

Monday, March 19, 2012

How to return varchar(MAX) from a CLR function?

Hi,

I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters.

How can I solve this problem and return varchar(MAX)?

Thanks

? The Visual Studio deployment tool maps SqlString to VARCHAR(8000). Use SqlChars instead -- it gets mapped to VARCHAR(MAX). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Bill YU@.discussions.microsoft.com> wrote in message news:2a21db0a-f54b-42e2-88b9-fb4cab26d356@.discussions.microsoft.com... Hi, I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters. How can I solve this problem and return varchar(MAX)? Thanks|||Thanks Adam, it works.|||Came here to ask a similar question and saw your response to this question and it fixed my problem also. When deploying my CLR SP, studio was mapping the SP'S string parameters to varchar(4000) and my xml data was being truncated. Changed the data type of the sp parameter to SqlChars and everything worked fine after that, thanks!
|||? Visual Studio deployment unfortunately has a lot of quirks. In case you're interested, I discuss another one in the following blog post: http://www.amazon.com/gp/plog/post.html/ref=cm_blog_pl/104-6385614-0075127?%5Fencoding=UTF8&pt=personalBlog&aid=PlogMyCustomersAgent&ot=customer&pd=1147803274.225&pid=PMCAALIINL2LPJELat1147801908&iid=AALIINL2LPJEL -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Bo416@.discussions.microsoft.com> wrote in message news:2343e9db-ce1c-4f2a-9b47-2d471a0ddb44@.discussions.microsoft.com...Came here to ask a similar question and saw your response to this question and it fixed my problem also. When deploying my CLR SP, studio was mapping the SP'S string parameters to varchar(4000) and my xml data was being truncated. Changed the data type of the sp parameter to SqlChars and everything worked fine after that, thanks!

how to return UTF 8 string from nvarchar field

hi
i have connected my ms sql 2000 with C using ODBC
can u help me to return the utf 8 string from nvarchar field ??
how should i do it
please help!!!!!!!http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q232/5/80.ASP&NoWebContent=1 information about storing UTF8 data.|||hi

thanx for u r help but it did not solve my problem
i want to retrive the utf 8 stirngs stored in nvarchar
but this does not help

thanx hope u can help me|||In SQL you can use READTEXt/WRITETEXT/UPDATETEXT to retrieve such data and refre to books online for more information.|||hi
thanx a lot
can u tell me which book to look for and

what i am saying is that the UTF 8 data that i have stored in the nvarchar field and retrieved it from C and store in a txt file
it should give ?? it only gives me ? which means it is not reading the whole data
so can u help in that plzzz
thanx a lot
looking forward to u r reply

thanx|||IN which language you're trying to retrieve C or SQL?
Ensure the windows locale does match to the collation set on SQL server.

Books online is the utility installed alongwith SQL server.
If not get the latest from here (http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp)|||i am retrieving the data from MS SQL(back end) through ODBC using C language(front end)
in win2k envoironment and storing the data in a text file
the field is a nvarchar field which has UTF 8 chars in it
and i want to retrieve the data from it and but it is not working accordingly
what should i do ??

Monday, March 12, 2012

How to return Integer as a string in stored procedure?

In a table, I have a 10 digit account number that is stored as a string
(Example Acct number: 0601600030). However, in the stored procedure, when I
try to store the account number in a local variable, it appears to be
implicity converted to an integer because the leading zero is dropped...this
stored procedure returns 601600030.
Here the stored procedure. How can I return the column exactly as it is in
the table?
ALTER PROCEDURE [dbo].[test001]
AS
BEGIN
DECLARE @.InterfaceAcctNbr nvarchar(10)
---
SELECT
@.InterfaceAcctNbr = cast(vAcctNbr as nvarchar(10))
FROM tbl_Visit
WHERE VID = 95799
return @.InterfaceAcctNbr
---
END
-Kevinis it stored as a VARCHAR ?
what about storing as a nvarchar?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/remotedba.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in message
news:E59CAC14-8538-4B21-A0A0-4ACE0525CE2C@.microsoft.com...
> In a table, I have a 10 digit account number that is stored as a string
> (Example Acct number: 0601600030). However, in the stored procedure, when
I
> try to store the account number in a local variable, it appears to be
> implicity converted to an integer because the leading zero is
dropped...this
> stored procedure returns 601600030.
> Here the stored procedure. How can I return the column exactly as it is
in
> the table?
>
> ALTER PROCEDURE [dbo].[test001]
> AS
> BEGIN
> DECLARE @.InterfaceAcctNbr nvarchar(10)
> ---
> SELECT
> @.InterfaceAcctNbr = cast(vAcctNbr as nvarchar(10))
> FROM tbl_Visit
> WHERE VID = 95799
> return @.InterfaceAcctNbr
> ---
> END
>
> --
> -Kevin|||Hi Kevin,
The return from the stored procedure is only ever int, if you want to pass a
value out of the stored procedure then you need to use a SELECT or OUTPUT
...
create proc test01
@.AcctNbr nvarchar(10) output
as
begin
set @.AcctNbr = '0123123123'
return( 0 )
end
Call using...
declare @.Acct nvarchar(10)
exec test01 @.AcctNbr = @.Acct output
print @.Acct
or
create proc test01
@.AcctNbr nvarchar(10) output
as
begin
select @.AcctNbr = '0123123123'
return( 0 )
end
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in message
news:E59CAC14-8538-4B21-A0A0-4ACE0525CE2C@.microsoft.com...
> In a table, I have a 10 digit account number that is stored as a string
> (Example Acct number: 0601600030). However, in the stored procedure, when
> I
> try to store the account number in a local variable, it appears to be
> implicity converted to an integer because the leading zero is
> dropped...this
> stored procedure returns 601600030.
> Here the stored procedure. How can I return the column exactly as it is
> in
> the table?
>
> ALTER PROCEDURE [dbo].[test001]
> AS
> BEGIN
> DECLARE @.InterfaceAcctNbr nvarchar(10)
> ---
> SELECT
> @.InterfaceAcctNbr = cast(vAcctNbr as nvarchar(10))
> FROM tbl_Visit
> WHERE VID = 95799
> return @.InterfaceAcctNbr
> ---
> END
>
> --
> -Kevin|||Kevin Buchanan wrote:
> In a table, I have a 10 digit account number that is stored as a
> string (Example Acct number: 0601600030). However, in the stored
> procedure, when I try to store the account number in a local
> variable, it appears to be implicity converted to an integer because
> the leading zero is dropped...this stored procedure returns 601600030.
> Here the stored procedure. How can I return the column exactly as it
> is in the table?
>
> ALTER PROCEDURE [dbo].[test001]
> AS
> BEGIN
> DECLARE @.InterfaceAcctNbr nvarchar(10)
> ---
> SELECT
> @.InterfaceAcctNbr = cast(vAcctNbr as nvarchar(10))
> FROM tbl_Visit
> WHERE VID = 95799
> return @.InterfaceAcctNbr
> ---
> END
There are 3 ways to return values from a SQL Server stored procedure:
1. a Select statement that returns a resultset
--run this script in Query Analyzer (QA):
Create Procedure SelectValue
(@.input int)
AS
Select @.input + 5
go
exec SelectValue 10
go
drop procedure SelectValue
2. a Return parameter:
--run this script in QA:
create procedure ReturnValue
(@.input int)
AS
Return @.input + 5
go
declare @.returnvalue int
exec @.returnvalue = ReturnValue 10
select @.returnvalue
go
drop procedure ReturnValue
3. an Output Parameter:
--run this script in QA:
create procedure OutputValue
(@.input int output)
AS
SET @.input = @.input + 5
go
declare @.outputvalue int
SET @.outputvalue = 10
exec OutputValue @.outputvalue output
select @.outputvalue
go
drop procedure OutputValue
I do not recommend method 1 for returning a single value. A resultset is
expensive to build, in that it must contain metadata in addition to data. So
more network traffic is created, and the client app needs to expend more
resources in order to retrieve and expose the resultset to the calling
procedure.
Most developers use the Return parameter to return status codes instead of
data. This is for the sake of consistency: there is no technical reason not
to use RETURN to return data, except that RETURN can only be used to return
integers. If you need to return other datatypes, you need to use an output
parameter.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||It is stored as varchar.
-Kevin
"Jack Vamvas" wrote:

> is it stored as a VARCHAR ?
> what about storing as a nvarchar?
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/remotedba.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in messag
e
> news:E59CAC14-8538-4B21-A0A0-4ACE0525CE2C@.microsoft.com...
> I
> dropped...this
> in
>
>|||Tony:
Thanks for the reply. Actually, here is the "complete" stored procedure. I
am doing a "lookup" of the acctnumber and storing it in another record (base
d
on the intput keys).
The issue is that I can read it, but it appears to implicity convert it to
an integer - even though it is declared as nvarchar. (I have tried both
varchar and nvarchar - both give the same result.) I don't need to return
the acctnumber from the proc, but I do need to update another record with th
e
string value of the column - not the integer conversion!
ALTER PROCEDURE [dbo].[MergeDowntimeRecords]
@.DownTimePtID int,
@.DownTimeVID int,
@.InterfacePtID int,
@.InterfaceVID int
AS
BEGIN
DECLARE @.DownTimePtMRNbr int
DECLARE @.InterfaceAcctNbr nvarchar(10)
DECLARE @.InterfacePtType nvarchar(5)
DECLARE @.InterfaceAdmDt DateTime
DECLARE @.InterfaceDschDt DateTime
DECLARE @.InterfaceHippa nvarchar(5)
DECLARE @.InterfaceLOS nvarchar(20)
---
SELECT
@.InterfaceAcctNbr = vAcctNbr,
@.InterfacePtType = vPtType,
@.InterfaceAdmDt = vAdmDtTm,
@.InterfaceDschDt = vDschDtTm,
@.InterfaceHippa = vHippa,
@.InterfaceLOS = vLOS
FROM tbl_Visit
WHERE VID = @.InterfaceVID
return @.InterfaceAcctNbr
---
UPDATE tbl_Visit
SET
vPtID = @.InterfacePtID,
vPtType = @.InterfacePtType,
vAdmDtTm = @.InterfaceAdmDt,
vDschDtTm = @.InterfaceDschDt,
vHippa = @.InterfaceHippa,
vLOS = @.InterfaceLOS
WHERE VID = @.DownTimeVID
---
DELETE FROM tbl_Visit WHERE VID = @.InterfaceVID
---
SELECT @.DownTimePtMRNbr = NULL
SELECT @.DownTimePtMRNbr = PtMrNbr FROM vw_All_Visits WHERE
VID=@.DownTimePtID
IF (@.DownTimePtMRNbr IS NULL)
BEGIN
-- If the MRNbr is Null, then it was manually entered, delete it -
-- Else the Demog record was looked up. ...so, don't delete it!!
DELETE FROM tbl_Demog WHERE PtID = @.DownTimePtID
END
ELSE
BEGIN
-- Since the patient was "looked up", reset the PtDownTime bit.
UPDATE tbl_Demog SET PtDownTime = 0
END
---
RETURN 0
END
-Kevin
"Tony Rogerson" wrote:

> Hi Kevin,
> The return from the stored procedure is only ever int, if you want to pass
a
> value out of the stored procedure then you need to use a SELECT or OUTPUT
> ...
> create proc test01
> @.AcctNbr nvarchar(10) output
> as
> begin
> set @.AcctNbr = '0123123123'
> return( 0 )
> end
> Call using...
> declare @.Acct nvarchar(10)
> exec test01 @.AcctNbr = @.Acct output
> print @.Acct
> or
> create proc test01
> @.AcctNbr nvarchar(10) output
> as
> begin
> select @.AcctNbr = '0123123123'
> return( 0 )
> end
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in messag
e
> news:E59CAC14-8538-4B21-A0A0-4ACE0525CE2C@.microsoft.com...
>
>|||Hi Kevin,
Yes, the problem is the line...

> return @.InterfaceAcctNbr
The above will stop the proc and the update will not happen anyway; if you
are debugging try this...
print @.InterfaceAcctNbr
return
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in message
news:76C046DD-06CC-41AF-9777-90290ACB8DDE@.microsoft.com...
> Tony:
> Thanks for the reply. Actually, here is the "complete" stored procedure.
> I
> am doing a "lookup" of the acctnumber and storing it in another record
> (based
> on the intput keys).
> The issue is that I can read it, but it appears to implicity convert it to
> an integer - even though it is declared as nvarchar. (I have tried both
> varchar and nvarchar - both give the same result.) I don't need to return
> the acctnumber from the proc, but I do need to update another record with
> the
> string value of the column - not the integer conversion!
>
>
> ALTER PROCEDURE [dbo].[MergeDowntimeRecords]
> @.DownTimePtID int,
> @.DownTimeVID int,
> @.InterfacePtID int,
> @.InterfaceVID int
> AS
> BEGIN
> DECLARE @.DownTimePtMRNbr int
> DECLARE @.InterfaceAcctNbr nvarchar(10)
> DECLARE @.InterfacePtType nvarchar(5)
> DECLARE @.InterfaceAdmDt DateTime
> DECLARE @.InterfaceDschDt DateTime
> DECLARE @.InterfaceHippa nvarchar(5)
> DECLARE @.InterfaceLOS nvarchar(20)
> ---
> SELECT
> @.InterfaceAcctNbr = vAcctNbr,
> @.InterfacePtType = vPtType,
> @.InterfaceAdmDt = vAdmDtTm,
> @.InterfaceDschDt = vDschDtTm,
> @.InterfaceHippa = vHippa,
> @.InterfaceLOS = vLOS
> FROM tbl_Visit
> WHERE VID = @.InterfaceVID
> return @.InterfaceAcctNbr
> ---
> UPDATE tbl_Visit
> SET
> vPtID = @.InterfacePtID,
> vPtType = @.InterfacePtType,
> vAdmDtTm = @.InterfaceAdmDt,
> vDschDtTm = @.InterfaceDschDt,
> vHippa = @.InterfaceHippa,
> vLOS = @.InterfaceLOS
> WHERE VID = @.DownTimeVID
> ---
> DELETE FROM tbl_Visit WHERE VID = @.InterfaceVID
> ---
> SELECT @.DownTimePtMRNbr = NULL
> SELECT @.DownTimePtMRNbr = PtMrNbr FROM vw_All_Visits WHERE
> VID=@.DownTimePtID
>
> IF (@.DownTimePtMRNbr IS NULL)
> BEGIN
> -- If the MRNbr is Null, then it was manually entered, delete it -
> -- Else the Demog record was looked up. ...so, don't delete it!!
> DELETE FROM tbl_Demog WHERE PtID = @.DownTimePtID
> END
> ELSE
> BEGIN
> -- Since the patient was "looked up", reset the PtDownTime bit.
> UPDATE tbl_Demog SET PtDownTime = 0
> END
> ---
> RETURN 0
>
> END
>
> --
> -Kevin
>
> "Tony Rogerson" wrote:
>|||Thanks - that was my problem.
--
-Kevin
"Bob Barrows [MVP]" wrote:

> Kevin Buchanan wrote:
> There are 3 ways to return values from a SQL Server stored procedure:
> 1. a Select statement that returns a resultset
> --run this script in Query Analyzer (QA):
> Create Procedure SelectValue
> (@.input int)
> AS
> Select @.input + 5
> go
> exec SelectValue 10
> go
> drop procedure SelectValue
> 2. a Return parameter:
> --run this script in QA:
> create procedure ReturnValue
> (@.input int)
> AS
> Return @.input + 5
> go
> declare @.returnvalue int
> exec @.returnvalue = ReturnValue 10
> select @.returnvalue
> go
> drop procedure ReturnValue
>
> 3. an Output Parameter:
> --run this script in QA:
> create procedure OutputValue
> (@.input int output)
> AS
> SET @.input = @.input + 5
> go
> declare @.outputvalue int
> SET @.outputvalue = 10
> exec OutputValue @.outputvalue output
> select @.outputvalue
> go
> drop procedure OutputValue
>
> I do not recommend method 1 for returning a single value. A resultset is
> expensive to build, in that it must contain metadata in addition to data.
So
> more network traffic is created, and the client app needs to expend more
> resources in order to retrieve and expose the resultset to the calling
> procedure.
>
> Most developers use the Return parameter to return status codes instead of
> data. This is for the sake of consistency: there is no technical reason no
t
> to use RETURN to return data, except that RETURN can only be used to retur
n
> integers. If you need to return other datatypes, you need to use an output
> parameter.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||Yeah - I had the return to stop the proc and return the value (for debug).
Thanks.
-Kevin
"Tony Rogerson" wrote:

> Hi Kevin,
> Yes, the problem is the line...
>
> The above will stop the proc and the update will not happen anyway; if you
> are debugging try this...
> print @.InterfaceAcctNbr
> return
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in messag
e
> news:76C046DD-06CC-41AF-9777-90290ACB8DDE@.microsoft.com...
>
>

How to return a string from a Stored Procedure

Up till now I've used SP's for updates and only ever needed to return error messages.

Now I have an SP that checks and validates something and has to return a string containing the result, (always a string/varchar!)

It works fine in Query Analyzer, I just need a demo of how to incorporate it into a VB app.

Hope that makes sense.

Thanks
Markuse OUTPUT Parameters...check BOL for more info..

hth|||Thanks, I already have that.

My question was how to return it into VB.Net|||if you mean how to catch the returned value from vb.net


...
'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.varchar
mycommand.Parameters.Add(myParam)mycommand.ExecuteNonQuery()
return convert.todouble((mycommand.Parameters("@.result").Value))
objcon.close
...

hth|||OK, I got what I wanted.

I hadn't thought of using ExecuteScalar with a SP, just needed to broaden my thinking a bit.

so...abbreviated...here's what I did.

Dim con as New SqlConnection("myConnectionString")
Dim cmd = New SqlCommand("mySPname",con)
cmd.CommandType=CommandType.StoredProcedure
cmd.Parameters.Add("@.ItemID",12345)
" " "
" " "
Try
cmd.Connection = con
cmd.Connection.Open()
x = cmd.ExecuteScalar()
Catch exp As SqlException
x = exp.Message
Finally
cmd.Collnection.Close()
End Try

This way I either get the string value I was looking for or the error message.

Friday, March 9, 2012

How to return a partial string based on a particular character?

Hi,
I am looking through books on-line but an not finding what I am looking for.
In my stored proc, I am being passed a varchar field, 20 long. It looks
something like, '103098-1'
I need to split the characters on the left side of the '-' into one field,
and the characters on the right side of the '-' into another field.
How do I do this?
Thanks,
Steve
This is how I did it, does this make sense, or is there an easier way?
Declare @.strOrder varchar(20)
set @.strOrder = '38372-1'
set @.charIndex = CHARINDEX('-', @.strOrder)
set @.Orderin = CONVERT(int, LEFT(@.strOrder, @.charIndex - 1))
Set @.linein = CONVERT(int, SUBSTRING(@.strOrder, @.charIndex + 1, 20 -
@.charIndex))
Thanks again.
"SteveInBeloit" wrote:

> Hi,
> I am looking through books on-line but an not finding what I am looking for.
> In my stored proc, I am being passed a varchar field, 20 long. It looks
> something like, '103098-1'
> I need to split the characters on the left side of the '-' into one field,
> and the characters on the right side of the '-' into another field.
> How do I do this?
> Thanks,
> Steve
|||yes, it could be done in a single line though.
Declare @.strOrder varchar(20)
declare @.left varchar(10)
declare @.right varchar(10)
set @.strOrder = '38372-1'
select @.left = left(@.strOrder, CHARINDEX('-', @.strOrder)-1),
@.right=substring(@.strOrder,
charindex('-',@.strOrder)+1,len(@.strOrder)-charindex('-',@.strOrder)+1)
print @.left
print @.right
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:A9247AFD-686A-46BE-AE7B-225E813FBCA1@.microsoft.com...[vbcol=seagreen]
> This is how I did it, does this make sense, or is there an easier way?
> Declare @.strOrder varchar(20)
> set @.strOrder = '38372-1'
> set @.charIndex = CHARINDEX('-', @.strOrder)
> set @.Orderin = CONVERT(int, LEFT(@.strOrder, @.charIndex - 1))
> Set @.linein = CONVERT(int, SUBSTRING(@.strOrder, @.charIndex + 1, 20 -
> @.charIndex))
> Thanks again.
> "SteveInBeloit" wrote:
for.[vbcol=seagreen]
looks[vbcol=seagreen]
field,[vbcol=seagreen]

How to return a long string

Hi,
I have a SQL script it returns a long string (more than 600 characters),
however I can only catch 256 characters. How to get whole characters?
thanks in advanceTools | Options
Results tab
Maximum characters per column
Keith
"Li Pang" <LiPang@.discussions.microsoft.com> wrote in message
news:CB8B888B-4406-46B2-8EE6-53B8839F258D@.microsoft.com...
> Hi,
> I have a SQL script it returns a long string (more than 600 characters),
> however I can only catch 256 characters. How to get whole characters?
> thanks in advance|||You didn't write where you are trying to get the string, so I guess
that you run the SQL statement in the Query Analyzer. If I'm correct,
then you need to modify the Query Analyzer's configuration. You can
do it Tools-->Option-->Results. Then just modify the number of
characters per column.
Adi|||Thanks
"Keith Kratochvil" wrote:

> Tools | Options
> Results tab
> Maximum characters per column
> --
> Keith
>
> "Li Pang" <LiPang@.discussions.microsoft.com> wrote in message
> news:CB8B888B-4406-46B2-8EE6-53B8839F258D@.microsoft.com...
>|||FYI, max in SQL Server 7.0/2000 is 8,192.
http://www.aspfaq.com/2272
http://www.aspfaq.com/
(Reverse address to reply.)
"Adi" <adico@.clalit.org.il> wrote in message
news:1106573590.672085.19500@.f14g2000cwb.googlegroups.com...
> You didn't write where you are trying to get the string, so I guess
> that you run the SQL statement in the Query Analyzer. If I'm correct,
> then you need to modify the Query Analyzer's configuration. You can
> do it Tools-->Option-->Results. Then just modify the number of
> characters per column.
> Adi
>

How to return a integer as a string?

How to retrive a value using a query string?

Hello,

I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.

This is what I have:

<script runat="server"> Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Dim conn As SqlConnection Dim cmd As SqlCommand Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @.Password) AND (users.username = @.Username)" conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance") cmd = New SqlCommand(cmdString, conn) cmd.Parameters.Add("@.Username", SqlDbType.VarChar, 50) cmd.Parameters("@.Username").Value = Me.Login1.UserName cmd.Parameters.Add("@.Password", SqlDbType.VarChar, 50) cmd.Parameters("@.Password").Value = Me.Login1.Password conn.Open() Dim myReader As SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) If myReader.Read() Then FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False) Else 'Response.Write("Invalid credentials") End If myReader.Close() End Sub</script>

I would like to know how can I get now the "user.FirstName" and pass it to a session variable?
how should I code it?

thanks,

Inside your read have

Session["FirstName"]=myReader.getString(2)

Is there any reason that you're getting 7(I think) fields from the database and throwing them away?

Also, it's poor form to store unencrypted passwords in the database. At least use xor or something.

Wednesday, March 7, 2012

How to retrieve large string from stored procedure?

Hello! This is my scenario...

Development - Visual Studio 2005
Database - MS SQL 2005

I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.

This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.

So... Does anyone here can help me out? Thanks in advance.
Have you tried using a C# string datatype?|||Hello! Thanks for the reply. But for do you mean C# string data type here? Hmm... Let me show some codes here to clarify the question. :-)

OleDbParameter prm_Xml = new OleDbParameter("@.Xml", OleDbType.VarWChar, -1);

prm_Xml.Direction = ParameterDirection.Output;

OleDbParameter's constructor needs a OleDbType enumerator for second parameter. VarWChar has a limitation for 4K characters. If I change that to LongVarWchar, then will have a runtime error...
|||

Take a look at this link - http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

Hope this helps

|||Raj, thanks for your prompt reply. Sorry for the late reply.

The link you sent does give me a clear picture of how to store and retrieve data that larger than 8K. But I still wonder whether there is a way to get the data as a output parameter of a stored procedure in OLEDB way.
|||

I think there is an example in the link that uses stored procedure with output parameter and gives VB/C# sample to retrieve it - Was that not helpful?

Take a look at - http://www.dotnet247.com/247reference/a.aspx?u=http://support.microsoft.com/?kbid=317016 and

http://www.dotnet247.com/247reference/a.aspx?u=http://www.kbalertz.com/Feedback_308049.aspx

Hope this helps

How to retrieve large string from stored procedure?

Hello! This is my scenario...

Development - Visual Studio 2005
Database - MS SQL 2005

OleDbParameter prm_Xml = new OleDbParameter("@.Xml", OleDbType.VarWChar, -1);

prm_Xml.Direction = ParameterDirection.Output;

OleDbParameter's constructor needs a OleDbType enumerator for second parameter. VarWChar has a limitation for 4K characters. If I change that to LongVarWchar, then will have a runtime error...
|||

Take a look at this link - http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

Hope this helps

|||Raj, thanks for your prompt reply. Sorry for the late reply.

I think there is an example in the link that uses stored procedure with output parameter and gives VB/C# sample to retrieve it - Was that not helpful?

Take a look at - http://www.dotnet247.com/247reference/a.aspx?u=http://support.microsoft.com/?kbid=317016 and

http://www.dotnet247.com/247reference/a.aspx?u=http://www.kbalertz.com/Feedback_308049.aspx

Hope this helps

Friday, March 23, 2012

How to run multiple store procedures in SQL Reporting Service

Monday, March 19, 2012

How to return varchar(MAX) from a CLR function?

how to return UTF 8 string from nvarchar field

Monday, March 12, 2012

How to return Integer as a string in stored procedure?

How to return a string from a Stored Procedure

Friday, March 9, 2012

How to return a partial string based on a particular character?

How to return a long string

How to return a integer as a string?

How to return a integer as a string?

How to return a integer as a string?

How to retrive a value using a query string?

Wednesday, March 7, 2012

How to retrieve large string from stored procedure?

How to retrieve large string from stored procedure?

restrict access to database

Blog Archive

About Me