sql nulls slow: store

Showing posts with label store. Show all posts

Thursday, March 8, 2012

AS2005 September CTP Memory Error

I get "Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed." while executing query set with cube browser in SQL Server BI Dev Studio. The same error I get while executing query with VB code (via ADOMD). The source of the error is AS2005 OLEDB Provider.

One important information: I have installed Sept CTP on clean Windows XP Pro, there were no previous versions of SQL 2005 installed.

Any ideas what's up? Maybe some missing parameter in connection string?I'm having the same problem after I uninstalled the June CTP and installed Sept CTP. I'm getting the error in BIDS and SSMS, I can browse dimensions but when I try to drag a dimension or measure onto cube browser I get the error. I tried deleting the AS database, redeploying, and reprocessing and I'm still getting the error. Any help would be appreciated.|||

From another message board:

This is a known issue with the September CTP and it has been fixed in
post-CTP builds. The problem surfaces with client-side record sizes greater
than 1k, so a possible workaround might be to rework the cube structure to
reduce record sizes.

--
-Raman Iyer
[This posting is provided "AS IS" with no warranties, and confers no
rights.]

|||Trying to work thru the as2005 tutorial and am stopped by said memory issue.

Any solutions to this? Any measure,dimension that i use causes the problem.
Reworking the cube structure to reduce record sizes? Anyone been successfull w/ that?

Are there any post-CTP (september) builds available
thanks

|||ttt

Same problem here.

There seems, however, to be some users on the forum who have gotten through the AS2005 tutorial without a hitch. I would assume these people did not have to reduce record sizes or anything like that.

Has anyone had any luck yet? Has anyone tried building a cube from non-AdventureWorks data? I tried but had a problem deploying so I didn't get to even test the memory error.

|||I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?
|||I currently use April CTP and everything works fine. I encountered two minor problems: slow first initialization of metadata during reading it with ADOMD (not NET) and there is no way to get value of unary operator property of member.

Best regards!|||Yup. Same here. Any chance of a hotfix or something? This basically halts SSAS development for my team on Sept CTP (we will have to roll back :P)
|||

Luther Smith wrote:
I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?

Sorry, I wasn't specific enough - I just meant that I had deployed non-AdventureWorks cubes sucessfully on SeptCTP. I don't know how to find out the string size, but if you tell me, I'll check it out and let y'all know.

disclaimer - I'm using SQL2005 Sept CTP x64|||

Same problem here.

I've been trying to track this down for a few days now. The problem is reportably fixed in the Nov release, but no hot fix is available.

Based on some configuration testing, the problem appears to be in either the PTS layer or specifically in the OLE DB drivers. I managed to recreate the issue in the June CTP (CTP 15) configuration, where the Sept drivers were installed. I could access the server fine from a remote PC, but localling, the error poped up.

|||

Is there a download for the hotfix? It seems ridiculous to have to back out the CTP16 or to have to wait until the RTM is released. I need to start developing on SSAS 2005 immediately. Help?

|||I agree. I have gone through all sorts of uninstalls and reinstalls between the June and September CTP's. Nothing worked to get past the memory error. I need to start learning 2005 right away!

AS2005 September CTP Memory Error

One important information: I have installed Sept CTP on clean Windows XP Pro, there were no previous versions of SQL 2005 installed.

From another message board:

--
-Raman Iyer
[This posting is provided "AS IS" with no warranties, and confers no
rights.]

|||Trying to work thru the as2005 tutorial and am stopped by said memory issue.

Any solutions to this? Any measure,dimension that i use causes the problem.
Reworking the cube structure to reduce record sizes? Anyone been successfull w/ that?

Are there any post-CTP (september) builds available
thanks|||ttt

Same problem here.

There seems, however, to be some users on the forum who have gotten through the AS2005 tutorial without a hitch. I would assume these people did not have to reduce record sizes or anything like that.

Has anyone had any luck yet? Has anyone tried building a cube from non-AdventureWorks data? I tried but had a problem deploying so I didn't get to even test the memory error.

Anyone from MS have a solution or workaround?|||I encountered this very same error. Can you tell me where I could find a post-CTP build?|||Same problem here. Was this functionality available in earlier betas, and did it work properly? I suppose that would be far easier than trying to get a NEWER build...|||I have built and depolyed a non-Adventureworks cube on September CTP with no problems at all.|||I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?|||I currently use April CTP and everything works fine. I encountered two minor problems: slow first initialization of metadata during reading it with ADOMD (not NET) and there is no way to get value of unary operator property of member.

Best regards!|||Yup. Same here. Any chance of a hotfix or something? This basically halts SSAS development for my team on Sept CTP (we will have to roll back :P)|||

Luther Smith wrote:
I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?

disclaimer - I'm using SQL2005 Sept CTP x64|||

Same problem here.

I've been trying to track this down for a few days now. The problem is reportably fixed in the Nov release, but no hot fix is available.

|||

Is there a download for the hotfix? It seems ridiculous to have to back out the CTP16 or to have to wait until the RTM is released. I need to start developing on SSAS 2005 immediately. Help?

|||I agree. I have gone through all sorts of uninstalls and reinstalls between the June and September CTP's. Nothing worked to get past the memory error. I need to start learning 2005 right away!

AS2005 September CTP Memory Error

One important information: I have installed Sept CTP on clean Windows XP Pro, there were no previous versions of SQL 2005 installed.

From another message board:

--
-Raman Iyer
[This posting is provided "AS IS" with no warranties, and confers no
rights.]

|||Trying to work thru the as2005 tutorial and am stopped by said memory issue.

Any solutions to this? Any measure,dimension that i use causes the problem.
Reworking the cube structure to reduce record sizes? Anyone been successfull w/ that?

Are there any post-CTP (september) builds available
thanks

|||ttt

Same problem here.

There seems, however, to be some users on the forum who have gotten through the AS2005 tutorial without a hitch. I would assume these people did not have to reduce record sizes or anything like that.

Has anyone had any luck yet? Has anyone tried building a cube from non-AdventureWorks data? I tried but had a problem deploying so I didn't get to even test the memory error.

Best regards!|||Yup. Same here. Any chance of a hotfix or something? This basically halts SSAS development for my team on Sept CTP (we will have to roll back :P)
|||

Luther Smith wrote:
I tried deploying a cube from a SQL 2000 data source using the September CTP, but ran into the same problem. Do you have any idea whether your successful deployment had a smaller string size and if so how it got one?

disclaimer - I'm using SQL2005 Sept CTP x64|||

Same problem here.

I've been trying to track this down for a few days now. The problem is reportably fixed in the Nov release, but no hot fix is available.

|||

Is there a download for the hotfix? It seems ridiculous to have to back out the CTP16 or to have to wait until the RTM is released. I need to start developing on SSAS 2005 immediately. Help?

|||I agree. I have gone through all sorts of uninstalls and reinstalls between the June and September CTP's. Nothing worked to get past the memory error. I need to start learning 2005 right away!

Friday, February 24, 2012

Arrays in a SQL2k DB

Hi,
I need to store a 2D Array in a Table.
What is the easiest way to to this. the array would only contain integer
values..
anyone a better idea than "implode" the Array in a string and "explode" it
again in the program code?
Nilshttp://www.aspfaq.com/2248
"Nils Wolf" <nisse@.gmx.at> wrote in message
news:e9bT6j4yFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need to store a 2D Array in a Table.
> What is the easiest way to to this. the array would only contain integer
> values..
> anyone a better idea than "implode" the Array in a string and "explode" it
> again in the program code?
> Nils|||"Nils Wolf" <nisse@.gmx.at> wrote in message
news:e9bT6j4yFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need to store a 2D Array in a Table.
> What is the easiest way to to this. the array would only contain integer
> values..
> anyone a better idea than "implode" the Array in a string and "explode" it
> again in the program code?
>
Well, storing it in a binary IMAGE column, or VARBINARY if it's small is a
whole lot better than using a string. Most programming environments know
how to convert integer arrays back and forth to byte arrays, and SQL Server
knows how to store and retrieve byte arrays.
But consider whether it's really best to hide the structure of your data
from SQL Server. Sometimes there's really no alternative, but if you can
find a way to model your data with tables and rows, it might be easier to
store, retrieve and manipulate, and if your arrays are sparse it may even be
more efficient.
David|||the Array is not so small.
it would be at least (100 x 100) up to (400 x 300)
the point is, that i need one array per row in a table with about 1000
rows...
Nils
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> schrieb im
Newsbeitrag news:uqw%23Wq4yFHA.2424@.TK2MSFTNGP12.phx.gbl...
> "Nils Wolf" <nisse@.gmx.at> wrote in message
> news:e9bT6j4yFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Well, storing it in a binary IMAGE column, or VARBINARY if it's small is a
> whole lot better than using a string. Most programming environments know
> how to convert integer arrays back and forth to byte arrays, and SQL
> Server knows how to store and retrieve byte arrays.
> But consider whether it's really best to hide the structure of your data
> from SQL Server. Sometimes there's really no alternative, but if you can
> find a way to model your data with tables and rows, it might be easier to
> store, retrieve and manipulate, and if your arrays are sparse it may even
> be more efficient.
> David
>|||Then IMAGE, not VARBINARY.
David
"Nils Wolf" <nisse@.gmx.at> wrote in message
news:uyqrCv4yFHA.3860@.TK2MSFTNGP09.phx.gbl...
> the Array is not so small.
> it would be at least (100 x 100) up to (400 x 300)
> the point is, that i need one array per row in a table with about 1000
> rows...
> Nils
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> schrieb im
> Newsbeitrag news:uqw%23Wq4yFHA.2424@.TK2MSFTNGP12.phx.gbl...
>

array with sql server 2000

hello
I have created one store procedures that return a table variable

'CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
DECLARE @.tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @.colA nvarchar(50)
DECLARE @.MyCursor CURSOR
/*declare @.mycursor1 cursor*/

SET @.MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@.immobile_id
order by posizione
OPEN @.MyCursor
FETCH NEXT FROM @.MyCursor
INTO @.ColA
WHILE @.@.FETCH_STATUS = 0
BEGIN

Insert @.tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @.ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@.immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @.MyCursor
INTO @.ColA
END

CLOSE @.MyCursor
DEALLOCATE @.MyCursor

select * from @.tbl

/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
Dim rs as new adodb.recordset
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you

Could you please provide more details?

Thanks

|||

CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
SET NOCOUNT ON

-- Original text followed

|||I guess using SET NOCOUNT ON resolved the problem. Several TSQL statements can produce results or messages. So to suppress some of the unwanted messages and read just the SELECT statement output for example you need to SET NOCOUNT ON in the SP. This will eliminate the DONE messages send to the client after the SELECT @.d = ... statement for example. See Books Online for more details on SET NOCOUNT ON effect on returning resultsets.

array with sql server 2000

hello
I have created one store procedures that return a table variable

CLOSE @.MyCursor
DEALLOCATE @.MyCursor

select * from @.tbl

Could you please provide more details?

Thanks

|||

CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
SET NOCOUNT ON

-- Original text followed

Sunday, February 19, 2012

array in store procedure

How to send an array list as an input variable into store procedure?
I have a list of UserName witch I would like to store into table through
store procedure.
HrckoYou can passed it as a string and get the parts out of the string in the SP.
There is no array or such type in TSQL. (AFAIK)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Hrvoje Voda" <hrvoje.voda@.luatech.com> schrieb im Newsbeitrag
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||http://www.sommarskog.se/arrays-in-sql.html
Jacco Schalkwijk
SQL Server MVP
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||Hrcko,
Here is an example that may help. ListTable() is an efficient
table-valued function that can take your list of UserName values
and return a table, so you can do something like
insert into X(UserName)
select Item from ListTable('Hrvoje, Kresimir, Josipa, Barbara, Vladen,
Vilko, Ksenija')
-- Definition and notes for ListTable()
/*
A table-valued function with one parameter, a delimited list,
that returns the separate distinct items of the list.
Steve Kass, Drew University
Thanks to MVPs Linda Wierzbicki and Umachandar Jayachandran
for help and helpful discussions on this.
*/
--A table of integers is needed
create table Seq (
Nbr int not null
)
insert into Seq
select top 4001 0
from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X
declare @.i int
set @.i = -1
update Seq
set @.i = Nbr = @.i + 1
alter table Seq add constraint pk_Seq primary key (Nbr)
--table Seq created
go
--This makes things more readable. The list is easier
--to process if it begins and ends with a single comma
--As it turns out also, list items cannot
--have leading or trailing spaces (here any leading spaces
--in the first item or trailing spaces in the last are
--eliminated)
create function RegularizedList (@.List varchar(8000))
returns varchar(8000) as begin
return replace(rtrim(','+ltrim(@.List))+',', ',,', ',')
end
go
--This function returns a table containing one column, commaPos,
--of integers, the positions of each comma in @.List, except the last
--This function returns a table containing the items in the list.
--The items are extracted by selecting those substrings of
--the list that begin immediately after a comma and end
--immediately before the next comma, then trimming spaces on
--both sides.
create function ListTable (@.List varchar(8000))
returns table as return
select
ltrim(rtrim(
substring(regL,
commaPos+1,
charindex(',', regL, commaPos+1) - (commaPos+1))))
as Item
from (
select Nbr as commaPos
from Seq, (
select dbo.RegularizedList(@.List) as regL
) R
where substring(regL,Nbr,1) = ','
and Nbr < len(regL)
) L, (
select dbo.RegularizedList(@.List) as regL
) R
go
--examples
declare @.x varchar(1000)
set @.x = 'Hrvoje, Kresimir, Josipa, Barbara, Vladen, Vilko, Ksenija'
select * from ListTable(@.x)
set @.x = 'Hrvoje|Kresimir|Josipa|Barbara|Vladen|V
ilko|Ksenija'
declare @.s varchar(1000)
set @.s = replace(@.x,'|',',')
select * from ListTable(@.s)
--Note, if a list contains a non-comma delimiter, and contains no
--commas within items, this replacement allows the function to
--handle it. If a comma appears in an item, but some other non-
--delimiter is absent from the list, a three-step replacement can
--be made:
-- replace all commas with new character not in list
-- replace all delimiters with comma
-- Use (select replace(Item,<new>,<comma> ) from ListTable(@.List)) LT
-- where the list table is used.
go
--Since this is a repro script, delete everything!
--Keep them around if they are helpful, though.
DROP FUNCTION RegularizedList
DROP TABLE Seq
DROP FUNCTION ListTable
-- Steve Kass
-- Drew University
Hrvoje Voda wrote:

>How to send an array list as an input variable into store procedure?
>I have a list of UserName witch I would like to store into table through
>store procedure.
>Hrcko
>
>|||You can pass the list as a comma separated list of values in a string, and
use a split technique to break them apart using a function similar to this
one:
-- Populate an auxiliary table of numbers
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('Nums') IS NOT NULL
DROP TABLE Nums
GO
CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY)
DECLARE @.max AS INT, @.rc AS INT
SET @.max = 8000
SET @.rc = 1
BEGIN TRAN
INSERT INTO Nums VALUES(1)
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO Nums
SELECT n + @.rc FROM Nums
SET @.rc = @.rc * 2
END
INSERT INTO Nums
SELECT n + @.rc FROM Nums
WHERE n + @.rc <= @.max
COMMIT TRAN
GO
-- Create function
CREATE FUNCTION fn_SeparateElements
(@.arr AS VARCHAR(7999)) RETURNS TABLE
AS
RETURN
SELECT n - LEN(REPLACE(LEFT(arr, n), ',', '')) + 1 AS pos,
SUBSTRING(@.arr, n, CHARINDEX(',', @.arr + ',', n) - n) AS element
FROM (SELECT @.arr AS arr) AS A JOIN Nums
ON n <= LEN(@.arr) AND SUBSTRING(',' + @.arr, n, 1) = ','
GO
-- Test
SELECT * FROM fn_SeparateElements('user1,user2,user3')
pos element
-- --
1 user1
2 user2
3 user3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||
"Hrvoje Voda" wrote:

> How to send an array list as an input variable into store procedure?
>
Definitely more than one way here to skin that cat. :-)
Yet another solution not using a UDF utilizing SQL Server's own
master..spt_values. You can easily modify it to use your own numbers table.
DECLARE @.strComma VARCHAR(1000)
SET @.strComma = 'Hrvoje, Kresimir, Josipa, Barbara, Vladen, Vilko, Ksenija'
SET @.strComma = REPLACE(@.strComma,' ', '')
SELECT
CAST(RIGHT(LEFT(@.strComma,Number-1)
, CHARINDEX(',',REVERSE(LEFT(','+@.strComma
,Number-1)))) AS CHAR(30)) as
User__Name
FROM
master..spt_values
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(@.strComma)+1
AND
(SUBSTRING(@.strComma,Number,1) = ',' OR SUBSTRING(@.strComma,Number,1) = '')
User__Name
--
Hrvoje
Kresimir
Josipa
Barbara
Vladen
Vilko
Ksenija
(7 row(s) affected)
--
Frank Kalis
SQL Server MVP
http://www.insidesql.de

Array in Store procedure

Dear all,

Sometimes I happened the require that the number of input parameters
of SP is not fixed, Can sql T-SQL handle the array (dynamic array)?
Does anybody ever used an array name as the input parameter to call
the SP?

thanks,

RobertSQL doesn't have arrays but see this article for some alternatives:

http://www.sommarskog.se/arrays-in-sql.html

--
David Portas
SQL Server MVP
--|||Robert Song wrote:

> Dear all,
> Sometimes I happened the require that the number of input parameters
> of SP is not fixed, Can sql T-SQL handle the array (dynamic array)?
> Does anybody ever used an array name as the input parameter to call
> the SP?
> thanks,
> Robert
I had this problem a year ago and solved it using a udf named fn_Split
I found it pretty fast.
You give it a list of parameters as '1,2,3,4,5,6' and it returns a temp
table
so you can select from fn_Split() and have something like
1
2
3
4
5
6

i found a version of this udf at
http://www.umachandar.com/technical...tyFns/Main7.htm

hth

f.|||Hi David,

Thank you so much for your reply, i will read the article later on.

Cheers,

Robert Song

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thursday, March 8, 2012

AS2005 September CTP Memory Error

AS2005 September CTP Memory Error

AS2005 September CTP Memory Error

Friday, February 24, 2012

Arrays in a SQL2k DB

array with sql server 2000

array with sql server 2000

Sunday, February 19, 2012

array in store procedure

Array in Store procedure

sql nulls slow

Blog Archive

About Me

About Me

Followers

Blog Archive