Prev: Index Rebuild with ONLINE = ON causes major blocking
Next: How to PIVOT this?
From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 1 Apr 2010 02:49
Hi Sylvain,

Thanks for your reply.

1. First of all for SQLNCLI10 it require Windows Xp Service Pack 3, please
inform me if I will install Windows XP Service Pack 3 & use SQLNCLI10 then
will nVarChar(Max) will work as output parameter using ADO parameters ?
Because I tried with SQLNCLN as a provider but it generate same error
message.

2. Following is my code in Stored Procedure SSP_ItemMaster_Select

-- START PROCEDURE =========================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SSP_ItemMaster_Select]
@pItemCode AS nVarCahr(15) = '',
@pItemDescription AS nVarChar(Max) = '' OUTPUT,
@pSalesRate AS Float = 0 OUTPUT
AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT
@pItemDescription = ItemDescription,
@pSalesRate = SalesRate
FROM dbo.FaItemMaster
WHERE ItemCode = @pItemCode

END
-- End of Procedure
=========================================================================

3. Following is my code of procedure assume

'**********************************************************************************************************
Private Sub Command1_Click()
Dim cnn as new adodb.connection
Dim cmd as New adodb.command
Dim prm as New adodb.parameters

' Opening Connection
cnn.open "Provider = SQLOLEDB; Server = 127.0.0.1; Database=MyDatabase; User
ID=sa; Password=mypass; "

' Initialise Command
set cmd.ActiveConnection = cnn

' Append ItemCode Paremeters
Set prm = cmd.CreateParameters("@pItemCode", advarWChar, adParamInput, 15)
cmd.Parameters.Append Prm

' Append Rate Paremeters
Set prm = cmd.CreateParameters("@pSalesRate", adDouble , adParamOutput)
cmd.Parameters.Append Prm

' Assign Command Text
cmd.CommandText = "dbo.SSP_ItemMaster_Select"
cmd.CommandType = adCmdStoredProc
cmd.Parameters("@pItemCode").Value = "0101001"
cmd.Execute , , adExecuteNoRecords

' Display Description
MsgBox "Item Description " & vbcrlf &
cmd.Paremeters("@pItemDescription").Value
End Sub
'***************************************************************************************************************************

4. Now please explain me what it wrong in above code.
5. If I use nText will it return LongText how many characters long ?

Please reply.

Sanjay Shah


"Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message
news:OSMS05S0KHA.752(a)TK2MSFTNGP04.phx.gbl...
> SQLNCLI10 is the native client that comes with SQL-Server 2008; you can
> download it separately from the following page:
> http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
> and it should work as well against SQL-Server 2005.
>
> SQLNCLI is the native client that comes with SQL-Server 2005 and if you
> have the later installed on your system, you shouldn't have see an error
> message about this missing provider. However, as you didn't provide any
> example of your code, we cannot exclude that you have made an error there.
> You can download it from:
> http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
>
> Finally, if you have so much trouble with nvarchar(max), try using nText
> instead.
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "Scott Morris" <bogus(a)bogus.com> wrote in message
> news:e%23teRUN0KHA.3708(a)TK2MSFTNGP02.phx.gbl...
>> "SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." <sanjay(a)microbrain.in> wrote
>> in message news:%23CrlUBN0KHA.6104(a)TK2MSFTNGP06.phx.gbl...
>>> Hi Dan,
>>>
>>> If I use provider SQLNCLI10 then it display error message provider not
>>> found.
>>>
>>> I tried to use SQLNCLI as a provider & ADO 2.8 but same error is coming.
>>
>> Have you tried a simple web search?
>>
>> http://www.google.com/search?q=ado+2.6+varchar%28max%29&sourceid=ie7&rls=com.microsoft:en-US&ie=utf8&oe=utf8
>> http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic62664.aspx
>> http://msdn.microsoft.com/en-us/library/bb399384.aspx
>>
>> Note the last link has a specific example of this. You might also try
>> posting to a newsgroup specific to ADO or VB (e.g..,
>> microsoft.public.vb.database.ado)
>>
>
>


From: Sylvain Lafontaine on 1 Apr 2010 11:49
I don't know. ADO and Visual Basic 6 are very old stuff here and their
selling as well as their support have been ceased since many years now. If
you want to go with this very old stuff, you will have to make a lot of
tests by yourself.

Also, Dan has provided you with a valuable reference that I'm repeating
here:
http://msdn.microsoft.com/en-us/library/ms130978.aspx

You should give a great deal of attention to this article but as you are
working with VB6, my first reflex would be to suggest you to remain with
Text and nText and to not use varchar(max) or nvarchar(max) at first; at
least until you got it right with Text and nText first.

Also, you should get them through recordsets returned with a Select
statement and not through parameters. Thinking that returning complex
values such as Text and nText through parameters will go faster is an error.
Before trying to go faster, you should try to get it right first. (And no,
returning a Text through a parameter won't really run faster.).

Just make sure that all the Text and nText columns are listed last in your
Select query and that you retrieve them in order - first, the non-ntext
fields then each ntext, one by one - as this is often required with older
driver (don't know/remember with VB6); where feed_name, url

Select A, B, C, ..., Text1, Text2, Text3 from MyTbl ...

A = my_rs ("A")
C = my_rs ("C")
B = my_rs ("B")
....
Text1 = my_rs ("Text1")
Text2 = my_rs ("Text2")
Text3 = my_rs ("Text3")

The order of retrieval is not important for the fields A, B and C but it is
for the text fields Text1, Text2 and Text3 on older drivers.

Finally, don't use automatic instantiation with ADO; so replace the
following:

Dim cnn as new adodb.connection
Dim cmd as New adodb.command
Dim prm as New adodb.parameters

with:

Dim cnn as adodb.connection
Dim cmd as adodb.command
Dim prm as adodb.parameters

Set cnn = new ADODB.Connection
Set cmd = new ADODB.Connection
Set prm = new ADODB.Connection

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." <sanjay(a)microbrain.in> wrote in
message news:u9vY6eW0KHA.220(a)TK2MSFTNGP06.phx.gbl...
> Hi Sylvain,
>
> Thanks for your reply.
>
> 1. First of all for SQLNCLI10 it require Windows Xp Service Pack 3, please
> inform me if I will install Windows XP Service Pack 3 & use SQLNCLI10 then
> will nVarChar(Max) will work as output parameter using ADO parameters ?
> Because I tried with SQLNCLN as a provider but it generate same error
> message.
>
> 2. Following is my code in Stored Procedure SSP_ItemMaster_Select
>
> -- START PROCEDURE =========================================
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> ALTER PROCEDURE [dbo].[SSP_ItemMaster_Select]
> @pItemCode AS nVarCahr(15) = '',
> @pItemDescription AS nVarChar(Max) = '' OUTPUT,
> @pSalesRate AS Float = 0 OUTPUT
> AS
>
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
>
> -- Insert statements for procedure here
> SELECT
> @pItemDescription = ItemDescription,
> @pSalesRate = SalesRate
> FROM dbo.FaItemMaster
> WHERE ItemCode = @pItemCode
>
> END
> -- End of Procedure
> =========================================================================
>
> 3. Following is my code of procedure assume
>
> '**********************************************************************************************************
> Private Sub Command1_Click()
> Dim cnn as new adodb.connection
> Dim cmd as New adodb.command
> Dim prm as New adodb.parameters
>
> ' Opening Connection
> cnn.open "Provider = SQLOLEDB; Server = 127.0.0.1; Database=MyDatabase;
> User ID=sa; Password=mypass; "
>
> ' Initialise Command
> set cmd.ActiveConnection = cnn
>
> ' Append ItemCode Paremeters
> Set prm = cmd.CreateParameters("@pItemCode", advarWChar, adParamInput, 15)
> cmd.Parameters.Append Prm
>
> ' Append Rate Paremeters
> Set prm = cmd.CreateParameters("@pSalesRate", adDouble , adParamOutput)
> cmd.Parameters.Append Prm
>
> ' Assign Command Text
> cmd.CommandText = "dbo.SSP_ItemMaster_Select"
> cmd.CommandType = adCmdStoredProc
> cmd.Parameters("@pItemCode").Value = "0101001"
> cmd.Execute , , adExecuteNoRecords
>
> ' Display Description
> MsgBox "Item Description " & vbcrlf &
> cmd.Paremeters("@pItemDescription").Value
> End Sub
> '***************************************************************************************************************************
>
> 4. Now please explain me what it wrong in above code.
> 5. If I use nText will it return LongText how many characters long ?
>
> Please reply.
>
> Sanjay Shah
>
>
> "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message
> news:OSMS05S0KHA.752(a)TK2MSFTNGP04.phx.gbl...
>> SQLNCLI10 is the native client that comes with SQL-Server 2008; you can
>> download it separately from the following page:
>> http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
>> and it should work as well against SQL-Server 2005.
>>
>> SQLNCLI is the native client that comes with SQL-Server 2005 and if you
>> have the later installed on your system, you shouldn't have see an error
>> message about this missing provider. However, as you didn't provide any
>> example of your code, we cannot exclude that you have made an error
>> there. You can download it from:
>> http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
>>
>> Finally, if you have so much trouble with nvarchar(max), try using nText
>> instead.
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server
>> (French)
>>
>>
>> "Scott Morris" <bogus(a)bogus.com> wrote in message
>> news:e%23teRUN0KHA.3708(a)TK2MSFTNGP02.phx.gbl...
>>> "SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." <sanjay(a)microbrain.in>
>>> wrote in message news:%23CrlUBN0KHA.6104(a)TK2MSFTNGP06.phx.gbl...
>>>> Hi Dan,
>>>>
>>>> If I use provider SQLNCLI10 then it display error message provider not
>>>> found.
>>>>
>>>> I tried to use SQLNCLI as a provider & ADO 2.8 but same error is
>>>> coming.
>>>
>>> Have you tried a simple web search?
>>>
>>> http://www.google.com/search?q=ado+2.6+varchar%28max%29&sourceid=ie7&rls=com.microsoft:en-US&ie=utf8&oe=utf8
>>> http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic62664.aspx
>>> http://msdn.microsoft.com/en-us/library/bb399384.aspx
>>>
>>> Note the last link has a specific example of this. You might also try
>>> posting to a newsgroup specific to ADO or VB (e.g..,
>>> microsoft.public.vb.database.ado)
>>>
>>
>>
>
>


From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 1 Apr 2010 11:57
Hi Sylvain,

You are suggested me to use Text & nText data type, but

1. In SQL Server 2005 Books suggest not to use Text & nText data type
because it will be removed in future version.
2. Text & nText is Fixed Length string consume more bytes & it slow.
3. It can store maximum 8000 characters but my requirement is upto 64000
characters.
4. I had checked that through parameters all data is coming properly only
this adLongxxxxx data types.
5. Are you sure if I use VB.Net & ADO.Net then this problem will not there,
so I try sample application in vb.net &
ado.net then port my full application.

Since last 2 days, I am trying to solve this problem and searched so many
forums and found that this is problem with ADO.

Please help me.

With Regards,

Sanjay Shah


"Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message
news:%235dFmLb0KHA.3652(a)TK2MSFTNGP04.phx.gbl...
>I don't know. ADO and Visual Basic 6 are very old stuff here and their
>selling as well as their support have been ceased since many years now. If
>you want to go with this very old stuff, you will have to make a lot of
>tests by yourself.
>
> Also, Dan has provided you with a valuable reference that I'm repeating
> here:
> http://msdn.microsoft.com/en-us/library/ms130978.aspx
>
> You should give a great deal of attention to this article but as you are
> working with VB6, my first reflex would be to suggest you to remain with
> Text and nText and to not use varchar(max) or nvarchar(max) at first; at
> least until you got it right with Text and nText first.
>
> Also, you should get them through recordsets returned with a Select
> statement and not through parameters. Thinking that returning complex
> values such as Text and nText through parameters will go faster is an
> error. Before trying to go faster, you should try to get it right first.
> (And no, returning a Text through a parameter won't really run faster.).
>
> Just make sure that all the Text and nText columns are listed last in your
> Select query and that you retrieve them in order - first, the non-ntext
> fields then each ntext, one by one - as this is often required with older
> driver (don't know/remember with VB6); where feed_name, url
>
> Select A, B, C, ..., Text1, Text2, Text3 from MyTbl ...
>
> A = my_rs ("A")
> C = my_rs ("C")
> B = my_rs ("B")
> ...
> Text1 = my_rs ("Text1")
> Text2 = my_rs ("Text2")
> Text3 = my_rs ("Text3")
>
> The order of retrieval is not important for the fields A, B and C but it
> is for the text fields Text1, Text2 and Text3 on older drivers.
>
> Finally, don't use automatic instantiation with ADO; so replace the
> following:
>
> Dim cnn as new adodb.connection
> Dim cmd as New adodb.command
> Dim prm as New adodb.parameters
>
> with:
>
> Dim cnn as adodb.connection
> Dim cmd as adodb.command
> Dim prm as adodb.parameters
>
> Set cnn = new ADODB.Connection
> Set cmd = new ADODB.Connection
> Set prm = new ADODB.Connection
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." <sanjay(a)microbrain.in> wrote
> in message news:u9vY6eW0KHA.220(a)TK2MSFTNGP06.phx.gbl...
>> Hi Sylvain,
>>
>> Thanks for your reply.
>>
>> 1. First of all for SQLNCLI10 it require Windows Xp Service Pack 3,
>> please inform me if I will install Windows XP Service Pack 3 & use
>> SQLNCLI10 then will nVarChar(Max) will work as output parameter using ADO
>> parameters ? Because I tried with SQLNCLN as a provider but it generate
>> same error message.
>>
>> 2. Following is my code in Stored Procedure SSP_ItemMaster_Select
>>
>> -- START PROCEDURE =========================================
>> SET ANSI_NULLS ON
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>>
>> ALTER PROCEDURE [dbo].[SSP_ItemMaster_Select]
>> @pItemCode AS nVarCahr(15) = '',
>> @pItemDescription AS nVarChar(Max) = '' OUTPUT,
>> @pSalesRate AS Float = 0 OUTPUT
>> AS
>>
>> BEGIN
>> -- SET NOCOUNT ON added to prevent extra result sets from
>> -- interfering with SELECT statements.
>> SET NOCOUNT ON;
>>
>> -- Insert statements for procedure here
>> SELECT
>> @pItemDescription = ItemDescription,
>> @pSalesRate = SalesRate
>> FROM dbo.FaItemMaster
>> WHERE ItemCode = @pItemCode
>>
>> END
>> -- End of Procedure
>> =========================================================================
>>
>> 3. Following is my code of procedure assume
>>
>> '**********************************************************************************************************
>> Private Sub Command1_Click()
>> Dim cnn as new adodb.connection
>> Dim cmd as New adodb.command
>> Dim prm as New adodb.parameters
>>
>> ' Opening Connection
>> cnn.open "Provider = SQLOLEDB; Server = 127.0.0.1; Database=MyDatabase;
>> User ID=sa; Password=mypass; "
>>
>> ' Initialise Command
>> set cmd.ActiveConnection = cnn
>>
>> ' Append ItemCode Paremeters
>> Set prm = cmd.CreateParameters("@pItemCode", advarWChar, adParamInput,
>> 15)
>> cmd.Parameters.Append Prm
>>
>> ' Append Rate Paremeters
>> Set prm = cmd.CreateParameters("@pSalesRate", adDouble , adParamOutput)
>> cmd.Parameters.Append Prm
>>
>> ' Assign Command Text
>> cmd.CommandText = "dbo.SSP_ItemMaster_Select"
>> cmd.CommandType = adCmdStoredProc
>> cmd.Parameters("@pItemCode").Value = "0101001"
>> cmd.Execute , , adExecuteNoRecords
>>
>> ' Display Description
>> MsgBox "Item Description " & vbcrlf &
>> cmd.Paremeters("@pItemDescription").Value
>> End Sub
>> '***************************************************************************************************************************
>>
>> 4. Now please explain me what it wrong in above code.
>> 5. If I use nText will it return LongText how many characters long ?
>>
>> Please reply.
>>
>> Sanjay Shah
>>
>>
>> "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message
>> news:OSMS05S0KHA.752(a)TK2MSFTNGP04.phx.gbl...
>>> SQLNCLI10 is the native client that comes with SQL-Server 2008; you can
>>> download it separately from the following page:
>>> http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
>>> and it should work as well against SQL-Server 2005.
>>>
>>> SQLNCLI is the native client that comes with SQL-Server 2005 and if you
>>> have the later installed on your system, you shouldn't have see an error
>>> message about this missing provider. However, as you didn't provide any
>>> example of your code, we cannot exclude that you have made an error
>>> there. You can download it from:
>>> http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
>>>
>>> Finally, if you have so much trouble with nvarchar(max), try using nText
>>> instead.
>>> --
>>> Sylvain Lafontaine, ing.
>>> MVP - Windows Live Platform
>>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>>> Independent consultant and remote programming for Access and SQL-Server
>>> (French)
>>>
>>>
>>> "Scott Morris" <bogus(a)bogus.com> wrote in message
>>> news:e%23teRUN0KHA.3708(a)TK2MSFTNGP02.phx.gbl...
>>>> "SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." <sanjay(a)microbrain.in>
>>>> wrote in message news:%23CrlUBN0KHA.6104(a)TK2MSFTNGP06.phx.gbl...
>>>>> Hi Dan,
>>>>>
>>>>> If I use provider SQLNCLI10 then it display error message provider not
>>>>> found.
>>>>>
>>>>> I tried to use SQLNCLI as a provider & ADO 2.8 but same error is
>>>>> coming.
>>>>
>>>> Have you tried a simple web search?
>>>>
>>>> http://www.google.com/search?q=ado+2.6+varchar%28max%29&sourceid=ie7&rls=com.microsoft:en-US&ie=utf8&oe=utf8
>>>> http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic62664.aspx
>>>> http://msdn.microsoft.com/en-us/library/bb399384.aspx
>>>>
>>>> Note the last link has a specific example of this. You might also try
>>>> posting to a newsgroup specific to ADO or VB (e.g..,
>>>> microsoft.public.vb.database.ado)
>>>>
>>>
>>>
>>
>>
>
>


From: Sylvain Lafontaine on 1 Apr 2010 20:53
"SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." <sanjay(a)microbrain.in> wrote in
message news:eVxGVRb0KHA.4832(a)TK2MSFTNGP04.phx.gbl...
> Hi Sylvain,
>
> You are suggested me to use Text & nText data type, but
>
> 1. In SQL Server 2005 Books suggest not to use Text & nText data type
> because it will be removed in future version.

They don't suggest to use VB6 neither.

> 2. Text & nText is Fixed Length string consume more bytes & it slow.

No difference between Text, nText, varchar(max) or nvarchar(max) excerpt for
very small strings and this behavior is option dependant.

> 3. It can store maximum 8000 characters but my requirement is upto 64000
> characters.

Don't know from where you have got this but this is false. You can store
64000 characters easily in a Text or nText field. You might have some
configuration problems with older drivers and it's also possible that you
might have to use some special technic but if you want to use something like
VB6, you will have to make your own testing and research as this is very old
stuff no longer in use in many places.

> 4. I had checked that through parameters all data is coming properly only
> this adLongxxxxx data types.

I'm sorry but I don't really understand what you are trying to say here.

> 5. Are you sure if I use VB.Net & ADO.Net then this problem will not
> there, so I try sample application in vb.net &

There is no problem neither with VB6 and ADO, it's just that you don't seem
to want to follow the suggestions that have been made here.

> ado.net then port my full application.
>
> Since last 2 days, I am trying to solve this problem and searched so many
> forums and found that this is problem with ADO.

We have given you the answer: don't try to return a Text, nText,
varchar(max) or nvarchar(max) through a parameter to ADO and use a recordset
instead and also, use Text or nText instead of varchar(max) or nvarchar(max)
but it looks like that you don't want to listen. I'm sorry but if you want
to use some old stuff like VB6, you will have to put some water into your
wine and start reading the suggestions and answers that have been posted in
response to your initial question.

Also, your question has more to do with ADO and VB6 than with SQL-Server.
You should post in a more appropriate newsgroup about ADO or VB6 if you want
more specific answers.

> Sanjay Shah

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


 | 
Pages: 1
Prev: Index Rebuild with ONLINE = ON causes major blocking
Next: How to PIVOT this?