Getting the error "XML stored procedures are not supported in fibers mode"

Hello All
I am getting the error "XML stored procedures are not supported in fibers
mode", when I try to run the example xmlsql given in the SQL books online...
I have changed my "lightweight pooling option to 0", using sp_configure.
I have restarted the server after this.
sp_configure,'show advanced options', 1 will show the lightweight pooling
as 0
But I am still getting the error.
I am using Sql 2000 on windows 2003
Please help. thanks in advance
-rahul
Did you execute the reconfigure command?
This is needed to make the configuration change active.
Best regards
Michael
"RP" <rahulpidaparti@.yahoo.com> wrote in message
news:ODSgBqU2EHA.3244@.TK2MSFTNGP11.phx.gbl...
> Hello All
> I am getting the error "XML stored procedures are not supported in fibers
> mode", when I try to run the example xmlsql given in the SQL books
> online...
> I have changed my "lightweight pooling option to 0", using sp_configure.
> I have restarted the server after this.
> sp_configure,'show advanced options', 1 will show the lightweight
> pooling
> as 0
> But I am still getting the error.
> I am using Sql 2000 on windows 2003
> Please help. thanks in advance
> -rahul
>

Getting stored procedure output into a table

Hi,
I trying to use the system stored procedures to do some job processing.
I want to run the sp_help_job and get the output into a table/cursor
Any thoughts on how I might accomplish this.
Thanks
Doug
Use this approach:
create table #Temp (
[name] varchar(255),
[db_size] varchar(255),
[owner] varchar(255),
[dbid] varchar(255),
[created] varchar(255),
[status] varchar(255),
[compatibility_level] varchar(255))
insert #Temp
exec sp_helpdb
Andre
|||Andre
Thanks for the suggestion.
Unfortunately it didn't work.
This is the pfrag
create temp table...
insert #sys_jobs_output execute sp_help_job
and the response
Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info,
Line 67
An INSERT EXEC statement cannot be nested.
(0 row(s) affected)
Analysis
Seems that the Sp_help_job calls the sp_get_composite that creates
additional temp table, thus the nested error.
I also tried the openquery/openrowset approach and both failed.
Any other thoughts?
Thanks
|||Andre,
Thanks again for the suggestion.
I finally realized that I could cheat and create copies of the MS procedures
sp_help_job, sp_get_composite_info and comment out the final step that
deletes the temporary tables.
This way, I can use the provided functionality, and leave the data around
for the next process.
Thanks again.
|||Glad you figured it out. I've done similar "cheats" in the past myself. I
created my own copy of sp_who2 so I could check activity by a certain user.
Interestingly enough, I just saw the same thing described in a SQL Server
Mag article. Maybe we're not such hacks after all.
Andre
|||Try this link :
http://tinyurl.com/5btxz
cyclop
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message960490.html
sql

Getting stored procedure output into a table

Hi,
I trying to use the system stored procedures to do some job processing.
I want to run the sp_help_job and get the output into a table/cursor
Any thoughts on how I might accomplish this.
Thanks
--
DougUse this approach:
create table #Temp (
[name] varchar(255),
[db_size] varchar(255),
[owner] varchar(255),
[dbid] varchar(255),
[created] varchar(255),
[status] varchar(255),
[compatibility_level] varchar(255))
insert #Temp
exec sp_helpdb
Andre|||Andre
Thanks for the suggestion.
Unfortunately it didn't work.
This is the pfrag
create temp table...
insert #sys_jobs_output execute sp_help_job
and the response
Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info,
Line 67
An INSERT EXEC statement cannot be nested.
(0 row(s) affected)
Analysis
Seems that the Sp_help_job calls the sp_get_composite that creates
additional temp table, thus the nested error.
I also tried the openquery/openrowset approach and both failed.
Any other thoughts?
Thanks|||Andre,
Thanks again for the suggestion.
I finally realized that I could cheat and create copies of the MS procedures
sp_help_job, sp_get_composite_info and comment out the final step that
deletes the temporary tables.
This way, I can use the provided functionality, and leave the data around
for the next process.
Thanks again.|||Glad you figured it out. I've done similar "cheats" in the past myself. I
created my own copy of sp_who2 so I could check activity by a certain user.
Interestingly enough, I just saw the same thing described in a SQL Server
Mag article. Maybe we're not such hacks after all.
Andre|||Try this link :
http://www.databasejournal.com/feat...10894_3491201_2

Getting stored procedure output into a table

Hi,
I trying to use the system stored procedures to do some job processing.
I want to run the sp_help_job and get the output into a table/cursor
Any thoughts on how I might accomplish this.
Thanks
--
DougUse this approach:
create table #Temp (
[name] varchar(255),
[db_size] varchar(255),
[owner] varchar(255),
[dbid] varchar(255),
[created] varchar(255),
[status] varchar(255),
[compatibility_level] varchar(255))
insert #Temp
exec sp_helpdb
Andre|||Andre
Thanks for the suggestion.
Unfortunately it didn't work.
This is the pfrag
create temp table...
insert #sys_jobs_output execute sp_help_job
and the response
Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info,
Line 67
An INSERT EXEC statement cannot be nested.
(0 row(s) affected)
Analysis
Seems that the Sp_help_job calls the sp_get_composite that creates
additional temp table, thus the nested error.
I also tried the openquery/openrowset approach and both failed.
Any other thoughts?
Thanks|||Andre,
Thanks again for the suggestion.
I finally realized that I could cheat and create copies of the MS procedures
sp_help_job, sp_get_composite_info and comment out the final step that
deletes the temporary tables.
This way, I can use the provided functionality, and leave the data around
for the next process.
Thanks again.|||Glad you figured it out. I've done similar "cheats" in the past myself. I
created my own copy of sp_who2 so I could check activity by a certain user.
Interestingly enough, I just saw the same thing described in a SQL Server
Mag article. Maybe we're not such hacks after all. :)
Andre|||Try this link :
http://tinyurl.com/5btxz
cyclop
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message960490.html

Getting started with DMX

Hello,

I'm studying some articles about DMX and i have a question. DMX statements is only for prediction?

Can i create a model and run a classification/clustering/decision tree algorithm through DMX instead of prediction?Because all the examples i found are talking about prediction.

Are there any other sources i can study, so i can have a better understanding of what DMX does?

Thank you in advance!

DMX is both a data definition and data manipulation language, so yes, you can create and train models with DMX using the CREATE and INSERT statements. In SQL Server 2005, the DDL part of it is a subset of the full DDL available via XMLA. Take a look at the DMX reference here for more information: http://msdn2.microsoft.com/en-us/library/ms132025.aspx.

|||I will study the article, thank for the help.

Getting SSIS running on Web Server

I need to be able to run SSIS packages form an asp.net (win 2k3) web server. Wrox has a book out "Professional SQL Server 2005 Integration Services" where they call the dtsx package directly using the following vb.net snipette:

Imports Microsoft.SqlServer.Dts.DtsClient

Dim ssisConn As New DtsConnection
ssisConn.ConnectionString = String.Format("-f ""{0}""", strMyFilePath)
ssisConn.Open()

As you would expect this works great on a workstation that has BIDS installed on it but does not work on a web server where sql client tools are not installed. Without install sql tools on the server what needs to be done to get this functioning as coded? How about calling packages that are installed on the server?

If anyone knows of any sites or books that cover this in detail I would appreciate the info. I only seem to be able to find bits and pieces.

thanks in advance

You can't. To my knowledge, you'll need the SSIS client tools installed. The packages are executed using dtexec (or a variant, I suppose).|||Searching turned this up.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=172501&SiteID=1|||

I figured it wouldn't be possible to run packages from a web server w/out having ssis installed. I wanted to make sure that some combonation of dll's copied to the bin directory wouldn't get me what I needed. I opted to expose a web service on the sql server and consume the service from the web box. The following kb article describes how to do it via a job and a web service.

http://msdn2.microsoft.com/de-de/library/ms403355.aspx

Getting SSIS running on Web Server

I need to be able to run SSIS packages form an asp.net (win 2k3) web server. Wrox has a book out "Professional SQL Server 2005 Integration Services" where they call the dtsx package directly using the following vb.net snipette:

Imports Microsoft.SqlServer.Dts.DtsClient

Dim ssisConn As New DtsConnection
ssisConn.ConnectionString = String.Format("-f ""{0}""", strMyFilePath)
ssisConn.Open()

As you would expect this works great on a workstation that has BIDS installed on it but does not work on a web server where sql client tools are not installed. Without install sql tools on the server what needs to be done to get this functioning as coded? How about calling packages that are installed on the server?

If anyone knows of any sites or books that cover this in detail I would appreciate the info. I only seem to be able to find bits and pieces.

thanks in advance

You can't. To my knowledge, you'll need the SSIS client tools installed. The packages are executed using dtexec (or a variant, I suppose).|||Searching turned this up.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=172501&SiteID=1|||

I figured it wouldn't be possible to run packages from a web server w/out having ssis installed. I wanted to make sure that some combonation of dll's copied to the bin directory wouldn't get me what I needed. I opted to expose a web service on the sql server and consume the service from the web box. The following kb article describes how to do it via a job and a web service.

http://msdn2.microsoft.com/de-de/library/ms403355.aspx

Getting rid of 'NaN' values in the Graph

Hi folks,

I run into problems where some metrics have value associated with dim attributes Markets where some don't have... The graph shows NaN, in the reporting services. Is there a way to get of NaN. something like filter out.

--Imran

Use the dataset filters and the following filter expressions:

CStr(Fields!YourField.Value) <> "NaN" AND

Fields!YourField.Value <> Nothing

Shyam

Getting reliable information about cache hit ratio

Maybe I am just a lot better at this than I thought, but I figure that somewhere there is a mathematical rule that is being overlooked. When I run dbcc sqlperf (lrustats) on some of my production machines, I sometimes end up with a cache hit ratio (which is defined as a percentage, mind you) that is slightly over the limit:

Statistic Value
---------- --------
Cache Hit Ratio 100.00898
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 9434.0

I suspect some counter somewhere is getting wrapped around its 4 byte limit. Is there any reliable source for getting statisics about SQL Server performance? Users tend be unreliable and say everything is slow.I'd say use the SQL Anaylser and use the Estimate Execution Plan area to start. Then, there alway the current activity under Management to look at. Then there the Profiler. I also suggest checking out a software called BMC. Maybe get a test copy.|||Those are good suggestions for tuning individual queries, but I am looking more for server level statistics. Suppose I have one query that fills my buffer cache with tables A through G. Then after that query, there is another query that brings in tables J through Q. Unless the buffer is big enough to hold them all in RAM, then some or all of tables A though G are going to be paged out. This is what I am looking for. The interaction of multiple queries as reflected by a cache hit ratio (and other waittime statistics as I find them). BMC would also have to depend on the statistics gathered from SQL Server, so I should be able to get those statistics that BMC is reading. Clear as mud?

Getting permission On Remote Server

After i add the server as remote, Set the Data access option to be True
I run the current sql:
SELECT *
FROM ServerName.DatabaseName.dbo.Client
It gave me an error: Server does not exist or access denined
Where can be the problem?
--
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilRoy
Make sure that the login had been granted to this database
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eR8elxa9FHA.1420@.TK2MSFTNGP09.phx.gbl...
> After i add the server as remote, Set the Data access option to be True
> I run the current sql:
> SELECT *
> FROM ServerName.DatabaseName.dbo.Client
> It gave me an error: Server does not exist or access denined
> Where can be the problem?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||Hi,
here are some potential causes for reason /error :
http://support.microsoft.com/defaul...kb;en-us;328306
and ensure proper connection has been done using
sp_addlinkedserver
sp_addlinkedsrvlogin
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Roy Goldhammer" wrote:

> After i add the server as remote, Set the Data access option to be True
> I run the current sql:
> SELECT *
> FROM ServerName.DatabaseName.dbo.Client
> It gave me an error: Server does not exist or access denined
> Where can be the problem?
> --
> ??òé a?ì????
> òúé? ?e??ú ú??e?
> èì' 03-5611606
> ?ì_' 050-7709399
> _é?ééì: roy@.atidsm.co.il
>
>

Getting only first letter of the column

Hi,

I am using an OLEDB source to run a stored procedure, which returns records from a temp table. The destination table is exactly same as the temp table in the stored procedure. I've some collation settings on both the destination table and temp table, but both are exactly same.

I got Unicode to Non-unicode conversion error first. I dont know why it happened as there is no difference in source and destination table. I solved that issue using the data conversion component. Then I got the truncation error. I set the Ignore on Truncation on error output to get rid of that issue. Then the package executed without any problem. But all the nvarchar and varchar fields in the destination table got populated with only the first letter of data.

Any Idea?

hi

check the limits of the varchar values in the both tables and match them

|||

Hi Ali Naqvi,

Thanks for your reply. Actually the problem was with a dummy SELECT statement that I put on top of the SP for getting the column metadata. Once I made the type casting to proper type and length inside the SELECT query, both unicode conversion error and truncation error are gone. Now the package is running fine without any error.

Getting HelloWorld_CLR sample to work

Hello,

I have been trying to get the app installed and running; I've updated the install script to point to the right server and it worked. I've run the

ALTER DATABASE ssb_HelloWorld SET ENABLE_BROKER; and the

EXECsp_configure'clr enabled', 1;

and the CLR seemed to have problems while getting replies back from the server. However after repeated retries it worked well. Sporadically I would see an error. After uninstalling the sample and installing it again - to document the steps previously taken - the CLR application is no longer receiving messages from the server. Any idea about what setting might be needed?

Thanks,

Running the app - after a while - just for kicks, it worked. I do not understand why? Any help greatly appreciated.|||

Could you please clarify what you mean by "works" and "not works". For example, when you say "does not work" do you mean:

a) target queue is not getting the messages (i.e. select * from TargetQueue has no messages)

b) activation procedure is not getting activated at all (i.e. you don't see any tasks activated in select * from sys.dm_broker_activated_tasks but select * from TargetQueue is non-empty)

c) activation procedure gets launched but is throwing unhandled exceptions (which are reported to eventlog).

Thanks,
Rushi

|||

Hi Rushi,

After successfully sending a message from HelloWorldClient to HelloWorldService, the client app attempts to get a conversation from the service. It is here that no conversation is returned.

Also, as I am learning about this SQL Server Service Broker, and reading the online documentation, would there be:

1. A good book that would comprehensively cover Service Broker and CLR

2. Additional HelloWorld_CLR sample documentation. This sample touches - for instance message looping - that I could not find explicit related documentation.

Thanks in advance

|||

Rushi,

here is more info, from the event application log. The problem seems to have come back:

An error occurred in the service broker message dispatcher, Error: 15404 State: 19.

|||

See this previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1

Typically, this problem occurs because the dbo account is mapped to an invalid Windows login. Random failures indicate that the login is randomly valid/invalid, like for instance when you work on a laptop and sometimes you're connected to the domain sometimes you're not. Changing dbo to a valid login (like [sa]) resolves the issue:

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [SA];

HTH,
~ Remus

Getting HelloWorld_CLR sample to work

Hello,

I have been trying to get the app installed and running; I've updated the install script to point to the right server and it worked. I've run the

ALTER DATABASE ssb_HelloWorld SET ENABLE_BROKER; and the

EXEC sp_configure 'clr enabled', 1;

and the CLR seemed to have problems while getting replies back from the server. However after repeated retries it worked well. Sporadically I would see an error. After uninstalling the sample and installing it again - to document the steps previously taken - the CLR application is no longer receiving messages from the server. Any idea about what setting might be needed?

Thanks,

Running the app - after a while - just for kicks, it worked. I do not understand why? Any help greatly appreciated.|||

Could you please clarify what you mean by "works" and "not works". For example, when you say "does not work" do you mean:

a) target queue is not getting the messages (i.e. select * from TargetQueue has no messages)

b) activation procedure is not getting activated at all (i.e. you don't see any tasks activated in select * from sys.dm_broker_activated_tasks but select * from TargetQueue is non-empty)

c) activation procedure gets launched but is throwing unhandled exceptions (which are reported to eventlog).

Thanks,
Rushi

|||

Hi Rushi,

After successfully sending a message from HelloWorldClient to HelloWorldService, the client app attempts to get a conversation from the service. It is here that no conversation is returned.

Also, as I am learning about this SQL Server Service Broker, and reading the online documentation, would there be:

1. A good book that would comprehensively cover Service Broker and CLR

2. Additional HelloWorld_CLR sample documentation. This sample touches - for instance message looping - that I could not find explicit related documentation.

Thanks in advance

|||

Rushi,

here is more info, from the event application log. The problem seems to have come back:

An error occurred in the service broker message dispatcher, Error: 15404 State: 19.

|||

See this previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1

Typically, this problem occurs because the dbo account is mapped to an invalid Windows login. Random failures indicate that the login is randomly valid/invalid, like for instance when you work on a laptop and sometimes you're connected to the domain sometimes you're not. Changing dbo to a valid login (like [sa]) resolves the issue:

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [SA];

HTH,
~ Remus

Getting error MS SQL2000

Hi ,

In one of our vendor's prog [VB], we always got this error when it try to assign seq number.

The whole process will run around 5 hrs and the last 5 minutes is the seq number assigning part.

[Microsoft][ODBC SQL Server Driver]Timeout expired

But using the same program etc..., when we re run the seq no assiigning only, it is fine.

Can some body help me to clear this.

thanks

rgs
ananth [Microsoft][ODBC SQL Server Driver]Timeout expiredHI ,

what did you set you connection time to , e.g. for the program to connect to the sql server?did you try setting it higher e.g. from 30 seconds to 60 seconds?