Iterator Table and rand() function

A couple of people asked that I post my iterator table and rand() function. The iterator table is basically a list of numbers 1-32767 and serves the same use as a "table of numbers." You can find good information about tables of numbers here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

To create an iterator table:

create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
go

/* -- */
/* This routine is used to populate the small_iterator table. */
/* This query ran in 1 second in development and should run at */
/* a similar speed in production. */
/* -- */

truncate table SMALL_ITERATOR

insert into small_iterator
select number from master.dbo.spt_values (nolock)
where name is null
and number <= 255

insert into small_iterator
select 256 * j.iter + i.iter
from small_iterator i
inner join small_iterator j
on j.iter > 0
and j.iter <= 127
order by 256 * j.iter + i.iter

delete from small_iterator where iter = 0

select count(*) [count],
min (iter) [min iterator],
max (iter) [max iterator]
from SMALL_ITERATOR

go

dbcc dbreindex (small_iterator, '', 100)
go

update statistics small_iterator
go

exec sp_recompile small_iterator
go

Here is my RAND scalar UDF; it comes in handy at times for generating mock data:

create view dbo.vRand
as
select rand () as vRand
go

create function dbo.rand ()
returns float
as
begin

return (select vRand from dbo.vRand)

end

go

create function dbo.randList
( @.pm_listSize integer
)
returns @.randList table
( rid integer,
iRand float
)
as
begin

declare @.upperBound integer

set @.upperBound = ceiling (convert(float, (@.pm_listSize+1))
/ convert (float, 32767))

insert into @.randList
select 32767*(j.iter-1) + i.iter - 1 as rid,
dbo.rand() as iRand
from small_iterator i (nolock)
inner join small_iterator j (nolock)
on j.iter <= @.upperBound
and 32767*(j.iter-1) + i.iter - 1 <= @.pm_listSize
and 32767*(j.iter-1) + i.iter - 1 > 0

return

end

Item Count 0; Gathering hung?

Douglas,
Basically, the FT Catalog is not being properly populated for some reason
and we need to discover that reason, correct it and then you should be good
to go... There may be a post-SP3a issue here, depending upon if this server
was upgraded from SQL Server 7.0 or this SQL 2000 server is a named instance
with a SQL Server 7.0 default installation. Checkout KB article - "814035
(Q814035) FIX: A Full-Text Population Fails After You Apply SQL Server 2000
Service Pack 3" at:
http://support.microsoft.com/default...b;en-us;814035
When you changed the account under which SQL is running, did you do this
via the Enterprise Manager's server security tab? If not, then you must
"re-change" the changed the account under which SQL is running via the EM.
Checkout KB article - "277549 (Q277549) PRB: Unable to Build Full-Text
Catalog After You Modify MSSQLServer Logon Account Through [NT4.0) Control
Panel [or Win2K Component Services]" at
http://support.microsoft.com/default...B;EN-US;277549
Regards,
John
"Douglas Steen" <dug@.pobox.com> wrote in message
news:#lzgWozFEHA.3372@.TK2MSFTNGP09.phx.gbl...
> I use a simple test server with SQL and MSSearch for development work and
up
> until recently it was handling full-text searching just fine. I updated
the
> box to Win2K3, and still no problems. But then I modified noise.eng, and
in
> the process rebuilt the catalog. Now whenever I try to populate the
catalog
> I am permanently stuck "in progress" with an item count of 0, a catalog
size
> of 1 MB, and a unique key count of 1.
> Here's what I've tried:
> - I have tried full & incemental populations, starting, stopping services
&
> rebooting
> - I've waited for hours for any sort of progress beyond item count 0, but
to
> no avail
> - the Application event log shows nothing but the standard start/stop
> messages. If I stop the population myself, I do get warnings, but they're
> of the "user stopped process" variety
> - similarly, the gather logs show only start/stop and "user stopped
process"
> messages
> - I've installed SQL Server SP3a
> - I've changed the account under which SQL is running, moving it to a
system
> account and then back to the original account, starting & stopping SQL and
> MSSearch as described in
> http://support.microsoft.com/default...b;en-us;295051 (I've done
> this twice now)
> I'm at the end of the line here; any help would be appreciated.
> Sincerely,
> Douglas R. Steen
>
John -
Thanks for the response.
Unfortunately, we're using SQL 2000, so I'm not sure this applies. And
yes, I did use Enterprise Manager to change the account, so I don't think
that's it either.
Here's some more information that might affect what happened:
The change that was made was to update the noise.eng file with a noise.eng
file that had been manually edited & emailed around for a while. Is it
possible that permissions on that file are confusing the service? I tried
setting correct permissions by clicking the checkbox on the security tab for
the containing folder that says "Apply security settings to all children",
so now the permissions are the same for all files in the Config folder, but
still no luck.
Also, we're now seeing this same problem on another sql server installation.
We have two identical databases (one is a test database) on that sql server:
in one case, we didn't delete and rebuild the catalog and it's working fine;
in the other, we did have to delete and rebuild the catalog and it's broken.
And it was the case on both that server and my own that we had not rebuilt
the catalog after upgrading to Win2003 (from Win2000) until just yesterday,
when this problem started occurring.
Unfortunately, the new sql server installation & database where we're
seeing this problem is our production server/database, so the problem has
moved to urgent priority. Any more thoughts you might have would be
welcome; and thanks for the help you've given already.
Douglas R. Steen
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OEXzSy0FEHA.2208@.TK2MSFTNGP09.phx.gbl...
> Douglas,
> Basically, the FT Catalog is not being properly populated for some reason
> and we need to discover that reason, correct it and then you should be
good
> to go... There may be a post-SP3a issue here, depending upon if this
server
> was upgraded from SQL Server 7.0 or this SQL 2000 server is a named
instance
> with a SQL Server 7.0 default installation. Checkout KB article - "814035
> (Q814035) FIX: A Full-Text Population Fails After You Apply SQL Server
2000
> Service Pack 3" at:
> http://support.microsoft.com/default...b;en-us;814035
> When you changed the account under which SQL is running, did you do this
> via the Enterprise Manager's server security tab? If not, then you must
> "re-change" the changed the account under which SQL is running via the
EM.
> Checkout KB article - "277549 (Q277549) PRB: Unable to Build Full-Text
> Catalog After You Modify MSSQLServer Logon Account Through [NT4.0) Control
> Panel [or Win2K Component Services]" at
> http://support.microsoft.com/default...B;EN-US;277549
> Regards,
> John
>
> "Douglas Steen" <dug@.pobox.com> wrote in message
> news:#lzgWozFEHA.3372@.TK2MSFTNGP09.phx.gbl...
and
> up
> the
and
> in
> catalog
> size
services
> &
but
> to
they're
> process"
> system
and
done
>
|||You're welcome, Douglas,
First of all, both KB articles apply to SQL Server 2000 as well. Secondly,
the noise.* files are just text files and there should not be any permission
issues. Although, changing the file permissions and "clicking the checkbox
on the security tab for the containing folder that says "Apply security
settings to all children" has me a bit concerned. On another server where
SQL Server 2000 is installed, could you confirm the modified folder has the
same permissions (and owner) as the un-modified folder?
I now *believe* that there might be some issue caused by the upgrading to
Win2003 from Win2000 as this upgrade also affects the MSSearch service as
well as the OS-supplied wordbreaker dll's. If so, then the complexity of
this, has just gone up a notch or two..
Were any other changes made to the SQL Servers? Specifically, any changes to
BUILTIN\Administrator ? If so, what were those changes?
As for your production server/database, you might want to consider calling
Microsoft PSS SQL Server support as and get their help in resolving this
more urgent issue as newsgroups are not the best place to get
production-level support.
Regards,
John
"Douglas Steen" <dug@.pobox.com> wrote in message
news:efN7P$0FEHA.4084@.TK2MSFTNGP11.phx.gbl...
> John -
> Thanks for the response.
> Unfortunately, we're using SQL 2000, so I'm not sure this applies. And
> yes, I did use Enterprise Manager to change the account, so I don't think
> that's it either.
> Here's some more information that might affect what happened:
> The change that was made was to update the noise.eng file with a noise.eng
> file that had been manually edited & emailed around for a while. Is it
> possible that permissions on that file are confusing the service? I tried
> setting correct permissions by clicking the checkbox on the security tab
for
> the containing folder that says "Apply security settings to all children",
> so now the permissions are the same for all files in the Config folder,
but
> still no luck.
> Also, we're now seeing this same problem on another sql server
installation.
> We have two identical databases (one is a test database) on that sql
server:
> in one case, we didn't delete and rebuild the catalog and it's working
fine;
> in the other, we did have to delete and rebuild the catalog and it's
broken.
> And it was the case on both that server and my own that we had not rebuilt
> the catalog after upgrading to Win2003 (from Win2000) until just
yesterday,
> when this problem started occurring.
> Unfortunately, the new sql server installation & database where we're
> seeing this problem is our production server/database, so the problem has
> moved to urgent priority. Any more thoughts you might have would be
> welcome; and thanks for the help you've given already.
> Douglas R. Steen
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OEXzSy0FEHA.2208@.TK2MSFTNGP09.phx.gbl...
reason
> good
> server
> instance
"814035
> 2000
this
> EM.
Control
> and
updated
> and
catalog
> services
> but
> they're
> and
> done
>
|||John -
As it turns out, our production issues were solved by using QueryAnalyzer
instead of the Enterprise Manager to do the creation/population/etc.
Unfortunately, I wasn't so lucky: it hasn't worked for me. No idea why one
would work and not the other, but it is feeling like it has something to do
with the Win2K3 upgrade and permissions.
This is just a dev machine, so if all else fails (though I'd hate to do
it), I can reinstall everything (starting with SQL server). I'm going to
let it stew for a while & see if any other ideas come up. In the meantime,
I've gotta get back to work. Thanks for your efforts.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:eVo7QR1FEHA.3568@.tk2msftngp13.phx.gbl...
> You're welcome, Douglas,
> First of all, both KB articles apply to SQL Server 2000 as well. Secondly,
> the noise.* files are just text files and there should not be any
permission
> issues. Although, changing the file permissions and "clicking the checkbox
> on the security tab for the containing folder that says "Apply security
> settings to all children" has me a bit concerned. On another server where
> SQL Server 2000 is installed, could you confirm the modified folder has
the
> same permissions (and owner) as the un-modified folder?
> I now *believe* that there might be some issue caused by the upgrading to
> Win2003 from Win2000 as this upgrade also affects the MSSearch service as
> well as the OS-supplied wordbreaker dll's. If so, then the complexity of
> this, has just gone up a notch or two..
> Were any other changes made to the SQL Servers? Specifically, any changes
to
> BUILTIN\Administrator ? If so, what were those changes?
> As for your production server/database, you might want to consider calling
> Microsoft PSS SQL Server support as and get their help in resolving this
> more urgent issue as newsgroups are not the best place to get
> production-level support.
> Regards,
> John
>
>
> "Douglas Steen" <dug@.pobox.com> wrote in message
> news:efN7P$0FEHA.4084@.TK2MSFTNGP11.phx.gbl...
And
think
noise.eng
tried
> for
children",
> but
> installation.
> server:
> fine;
> broken.
rebuilt
> yesterday,
has
> reason
> "814035
> this
must
the
> Control
work
> updated
noise.eng,
> catalog
0,
start/stop
a
SQL
(I've
>
|||Douglas,
If you can manage the FT Catalog via the system stored procs, then this is
NOT a Win2003 upgrade issue, but a specific SQL Server FTS issue.
If your production and dev machines are configured similarly, and you can
manage the FT Catalog via the system stored proc, but not via the EM, you
might be using "local accounts" and checkout the following KB article -
"270671 (Q270671) PRB: Full Text Search Menus Are Not Enabled for Local
Windows NT Accounts" at:
http://support.microsoft.com/default...;en-us;q270671
Regards,
John
"Douglas Steen" <dug@.pobox.com> wrote in message
news:OYTvY01FEHA.2732@.tk2msftngp13.phx.gbl...
> John -
> As it turns out, our production issues were solved by using
QueryAnalyzer
> instead of the Enterprise Manager to do the creation/population/etc.
> Unfortunately, I wasn't so lucky: it hasn't worked for me. No idea why
one
> would work and not the other, but it is feeling like it has something to
do
> with the Win2K3 upgrade and permissions.
> This is just a dev machine, so if all else fails (though I'd hate to do
> it), I can reinstall everything (starting with SQL server). I'm going to
> let it stew for a while & see if any other ideas come up. In the
meantime,
> I've gotta get back to work. Thanks for your efforts.
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eVo7QR1FEHA.3568@.tk2msftngp13.phx.gbl...
Secondly,
> permission
checkbox
where
> the
to
as
changes
> to
calling
> And
> think
> noise.eng
it
> tried
tab
> children",
folder,
> rebuilt
we're
> has
be
Server
> must
> the
Full-Text
> work
> noise.eng,
the
> 0,
> start/stop
stopped
to
> a
> SQL
> (I've
>
sql

Issues copying data to linked server

I'm having an issue with a job that calls a stored procedure. The SP is fairly complicated, but basically creates a temp table, then inserts the records from the temp table into a table on a linked server (via VPN). The basic premise here is that I'm trying to update a web-accessible server from the production server. The funny thing is that the process works correctly for 4 out of 5 companies (company is the only parameter fed to the SP). The company that fails is by far the largest, but the process has worked in the past.

I'm not strong with all the tools available in SQL Server that might help me figure out what's going wrong. In the SQL Server Agent, it simply tells me that the job failed. If I use Query Analyzer and run the SP in Debug mode (if I'm stating that correctly), I get this error for any company I try it for, even companies that run fine (line 51 is the DELETE line):

Server: Msg 7391, Level 16, State 1, Procedure procStmtDataToReno, Line 51
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

So I suppose my long-winded question is, how can I determine why this SP fails for the one company? If relevant, the job is simply:

EXEC procStmtDataToReno 'B'

and some snippets of the SP:

CREATE PROCEDURE [dbo].[procStmtDataToReno]

@.CoCode as varchar(2)

AS

--various declarations and such

DELETE
FROM [10.1.1.4].OnlineAR.dbo.StatementData
WHERE CompanyID = @.CoCode AND (Line1Date < Convert(varchar(10), GETDATE() - 60, 101) OR Line1Date >= Convert(varchar(10), GETDATE() - 5, 101) )

--code to build temp table

INSERT INTO [10.1.1.4].OnlineAR.dbo.StatementData
SELECT * FROM #Statement
GO

TIAI normally would create a stored procedure on the remote server side and call it via openquery() or sp_executesql. That is the *fastest* way to do and DML.

Anyway, I suggest you take a look at the following article for guidance on resolving 7391 error.

http://support.microsoft.com/kb/306212|||Thanks for that. Because some companies worked, my suspicion all along was that it was "data" related rather than "process" related. I finally stumbled into the answer late last night. I had tried changing the SP to simply select the records, and it ran fine from Query Analyzer. I changed the SP back to an INSERT and executed it again from QA, and it failed, but I finally got a message that made sense:

Server: Msg 8152, Level 16, State 4, Procedure procStmtDataToReno, Line 389
String or binary data would be truncated.
The statement has been terminated.

My problem was as simple as tracking down the field that was wider on the source side than it was on the destination side. The process now works fine. I just didnt know what tool to use to get better info on what was going wrong.

I am interested in your recommendation to set it up on the remote side. I would have thought it was better to have the SP on the source side, so it would only have to transfer the results of the process over the wire. If the SP were on the destination side, wouldnt it have to bring all the data across to work with?|||yes, you have to pass the data to the remote server in order for it to be processed.

e.g.

@.sql='exec my_sp ' + @.inputvar

exec linked.db..sp_executesql @.sql

Issue with dataset from stored procedure

I am having an issue with a dataset in SSRS2005. Basically, this is a
simple dataset from a stored procedure, with two char parameters.
I just picked the stored procedure name and let the BI Studio pick all
the default settings. Then I try to preview the data. The BI studio
locks up. This happens every time.
The stored procedure works fine in SSMS. It returns one simple small
data set. It takes 6 seconds to run in SSMS. So I decided to set the
timeout to 30 seconds in the dataset. It times out and gives me the
expected timeout error message. If I set the timeout to 60 seconds, it
locks up - no timeout error message.
If I choose any other similarly structured stored procedure, no
problems occur. So I tried slightly modifying this procedure, but no
luck.
If I watch the procedure in sql profiler, the procedure is started, but
it doesn't complete until I kill BI studio.
Any ideas?hey, scott
when you say you "try to preview the data", do you mean you execute the
query from the data tab in the Report Designer, or do you actually go
to the Preview tab and run the report?
If you mean the latter, then I suggest you take a look at the report
layout, and make sure you're not creating some kind of bottleneck by
doing heavy grouping or formatting.
If you mean the former, I'd encourage you to take a look at the
properties of you data source (shared or in-report) and make sure you
are giving it the correct connection string, and authentication method
(windows/sql/none) and credentials.
Regards,
Thiago Silva
Scott.Stonehouse@.gmail.com wrote:
> I am having an issue with a dataset in SSRS2005. Basically, this is a
> simple dataset from a stored procedure, with two char parameters.
> I just picked the stored procedure name and let the BI Studio pick all
> the default settings. Then I try to preview the data. The BI studio
> locks up. This happens every time.
> The stored procedure works fine in SSMS. It returns one simple small
> data set. It takes 6 seconds to run in SSMS. So I decided to set the
> timeout to 30 seconds in the dataset. It times out and gives me the
> expected timeout error message. If I set the timeout to 60 seconds, it
> locks up - no timeout error message.
> If I choose any other similarly structured stored procedure, no
> problems occur. So I tried slightly modifying this procedure, but no
> luck.
> If I watch the procedure in sql profiler, the procedure is started, but
> it doesn't complete until I kill BI studio.
> Any ideas?|||Sorry, I mean the former. I am doing the preview from the data tab.
I know there is nothing wrong with the connection strings or anything.
I started commenting out fields in the stored procedure, and there are
three fields in particular that appear to be causing the problem.
Again, these look fine when I run it in SSMS. In each of those fields,
I did a coalesce, so I thought maybe that was related, but if I remove
the coalesce, nothing changes.
Then I tried putting the full stored procedure query into the dataset
as text instead of a stored procedure. Everything works. It was cut &
paste, so I know it's the same.
I would just leave it like this, but I have several reports running
from the same procedure. I don't want to have to edit every report
when it's time to make a change to the query.