Not a valid identifier while executing an Stored procedure

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

|||

First off, I'm not sure why you're constructing a dynamic select inside your procedure...the procedure should be the select statement, using any input parameters you defined.

But to solve the problem, you need to change

exec @.sqlstr

to

exec(@.sqlstr)

I'd rewrite the entire piece of code...

|||

This is a duplicate post.

Please see answer in your other posting.

|||

Use the following satement to execute the SP,

Code Snippet

Exec [GSU_Site_ReterieveActiveSitesOnSearch]' where GSUStatus.GSUStatusID=1and site.Sitename like''lakshmisite'' '

|||

Kusuma,

Instead passing the this value " where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' ", use:

' where GSUStatus.GSUStatusID=1 and site.Sitename like ''lakshmisite'''

Notice that I am using two apostrophes per each one inside the string.

As you can see, you are setting QUOTED_IDENTIFIER to on, when creating the sp, so anything enclosed by double quote will be interprete as an identifier (name of a column, table, etc.), so when you pass that value to the sp, it will look like

...

SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID +

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and there is not such identifier in your db.

you can set QUOTED_IDENTIFIER to OFF, but I prefer to leave it as ON and use the other method to escape apostrophes.

AMB

|||

If you call it from any UI, the single quote will be automatically taken care by the providers/ADO classes. (since it is a parameter)

But when you test the sp, you have to use either escape sequence or as AMB sujest use the QUOTED_IDENTIFER OFF config.

|||

Thanks Mani :-)

Now it is working.

There were two problems. One

1)setQUOTED_IDENTIFIERON should be OFF

2)exec@.sqlstr should be exec(@.sqlstr)

Kusuma

|||

Hai Dalej,

Sorry for posting two times.

I need dynamic query for a searching -sitenames,Businessunit etc......... ( searching based on columns in a table)

Now the problem is solved by giving exec(@.sqlstr) instead of exec @.sqlstr.

Thanks for your help :-)

Kusuma

NOP in sysprocesses (cmd column)

Can somebody please tell me what 'NOP' is in the cmd column of sysprocesses
(SQL2K) noticed this when a stored procedure was running . .. I could not
find anything about this cmd (relating to SQL) on the internet . . .however
there were some references to NOP in c# and .NET programming
Thanks for your help
Olu Adedeji
Baysignia Systems
In other areas NOP means "No Operation", but I do not know if that is true
here..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> Can somebody please tell me what 'NOP' is in the cmd column of
sysprocesses
> (SQL2K) noticed this when a stored procedure was running . .. I could not
> find anything about this cmd (relating to SQL) on the internet . .
..however
> there were some references to NOP in c# and .NET programming
> Thanks for your help
>
> Olu Adedeji
> Baysignia Systems
|||Thanks Wayne
"Wayne Snyder" wrote:

> In other areas NOP means "No Operation", but I do not know if that is true
> here..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
> news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> sysprocesses
> ..however
>
>

NOP in sysprocesses (cmd column)

Can somebody please tell me what 'NOP' is in the cmd column of sysprocesses
(SQL2K) noticed this when a stored procedure was running . .. I could not
find anything about this cmd (relating to SQL) on the internet . . .however
there were some references to NOP in c# and .NET programming
Thanks for your help
Olu Adedeji
Baysignia SystemsIn other areas NOP means "No Operation", but I do not know if that is true
here..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> Can somebody please tell me what 'NOP' is in the cmd column of
sysprocesses
> (SQL2K) noticed this when a stored procedure was running . .. I could not
> find anything about this cmd (relating to SQL) on the internet . .
.however
> there were some references to NOP in c# and .NET programming
> Thanks for your help
>
> Olu Adedeji
> Baysignia Systems|||Thanks Wayne
"Wayne Snyder" wrote:

> In other areas NOP means "No Operation", but I do not know if that is true
> here..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
> news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> sysprocesses
> ..however
>
>

NOP in sysprocesses (cmd column)

Can somebody please tell me what 'NOP' is in the cmd column of sysprocesses
(SQL2K) noticed this when a stored procedure was running . .. I could not
find anything about this cmd (relating to SQL) on the internet . . .however
there were some references to NOP in c# and .NET programming
Thanks for your help
Olu Adedeji
Baysignia SystemsIn other areas NOP means "No Operation", but I do not know if that is true
here..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> Can somebody please tell me what 'NOP' is in the cmd column of
sysprocesses
> (SQL2K) noticed this when a stored procedure was running . .. I could not
> find anything about this cmd (relating to SQL) on the internet . .
.however
> there were some references to NOP in c# and .NET programming
> Thanks for your help
>
> Olu Adedeji
> Baysignia Systems|||Thanks Wayne
"Wayne Snyder" wrote:
> In other areas NOP means "No Operation", but I do not know if that is true
> here..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Olu Adedeji" <OluAdedeji@.discussions.microsoft.com> wrote in message
> news:ACD1A5E9-503C-41D7-9F6D-9C8D2ABA7EFD@.microsoft.com...
> > Can somebody please tell me what 'NOP' is in the cmd column of
> sysprocesses
> > (SQL2K) noticed this when a stored procedure was running . .. I could not
> > find anything about this cmd (relating to SQL) on the internet . .
> ..however
> > there were some references to NOP in c# and .NET programming
> >
> > Thanks for your help
> >
> >
> > Olu Adedeji
> > Baysignia Systems
>
>sql

noob: How do i run pl/sql on ms sql?

Hi experts,

I'm using MS SQL 2000. The closest i could get to having pl/sql within ms sql was tru its stored procedures feature.

I created a new stored procedure within the built-in Northwind database. I pasted the following inside:

--------------------
declare

vname Employees.firstname%TYPE;

begin

SELECT firstname INTO vname FROM Employees
WHERE firstname = 'Nancy';

dbms_output.put_line ('Name is ' || vname);

EXCEPTION

when NO_DATA_FOUND then
dbms_output.put_line ('no data found');

when TOO_MANY_RECORDS then
dbms_output.put_line ('too many records');

END;

--------------------

I checked the syntax and i get some error about the employees table.
Error 155: 'Employees' is not a recognized cursor option

Any idea?

Thanks..You've got some serious reading to do...there is a major methodolgy switch you'll have to understand (not to mention sytax) between the two...

Oracle has a lot of nice "built in" features, and at the same time is extremely painful...

For example, ylucan not use %TYPE...

YOu must explicitlety declare EVERYTHING...

This is how you write what your example is trying to do...

CREATE PROC mySproc99
AS

BEGIN

DECLARE @.vname varchar(40), @.Rowcount int, @.Error int

SELECT @.vname = Lastname
FROM Employees
WHERE firstname = 'Nancy'

SELECT @.RowCount = @.@.ROWCOUNT, @.Error = @.@.Error

IF @.Error <> 0
BEGIN
PRINT 'Error Condition ' + CONVERT(varchar(5),@.Error)
Return @.Error
END

IF @.RowCount <> 1
BEGIN
PRINT 'Multiple Rows Found Error'
Return 2
END

IF @.RowCount = 1
BEGIN
PRINT 'Name is '+ @.vname
Return 0
END
END

GO

EXEC mySproc99
GO

DROP PROC mySproc99
GO

Noob need help with recursion

I created a table called MyTable with the following fields :

Id int
Name char(200)
Parent int

I also created the following stored procedure named Test, which is supposed to list all records and sub-records.

CREATE PROCEDURE Test
@.Id int
AS

DECLARE @.Name char(200)
DECLARE @.Parent int

DECLARE curLevel CURSOR LOCAL FOR
SELECT * FROM MyTable WHERE Parent = @.Id
OPEN curLevel
FETCH NEXT FROM curLevel INTO @.Id, @.Name, @.Parent
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.Id AS Id, @.Name AS Name, @.Parent AS Parent
EXEC Test @.Id
FETCH NEXT FROM curLevel INTO @.Id, @.Name, @.Parent
END

CLOSE curLevel
DEALLOCATE curLevel
GO

I added a MxDataGrid and DataSourceControl.
SelectCommand property of the DataSourceControl = EXEC Test 0

When I run the aspx page, it only shows 1 record. I tried to change the parameter to 1, 2, 3 but it always shows only 1 record (not the same tho).

Is there something wrong with the stored procedure ?Ok, I made some modifications to make it work properly but there is some limitations. I have to store the results in a temp table. Can I do something similar but without the temp table ?

(Note : I changed some field/table names)

-- ----------------------
-- Fill and select the temp table where the nodes and sub nodes id are stored
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodes
@.NodeId INT
AS
DELETE FROM TmpNodesAndSubNodes
EXEC GetNodesAndSubNodesRecursive @.NodeId
SELECT * FROM TmpNodesAndSubNodes
GO
-- ----------------------
-- Fill the temp table with the id's of the nodes and it's sub nodes
-- ----------------------
CREATE PROCEDURE GetNodesAndSubNodesRecursive
@.NodeId INT
AS
-- Store the node id into the temp table
INSERT INTO TmpNodesAndSubNodes VALUES(@.NodeId)
-- Declare a local cursor to seek in the nodes table
-- and some variables to retreive the values
DECLARE @.CurrentNodeId INT
DECLARE @.CurrentParentNodeId INT
DECLARE curLevel CURSOR LOCAL FOR
 SELECT Id FROM Nodes WHERE ParentNodeId=@.NodeId
-- Fetchs the records and call the recursive method for each node id
OPEN curLevel
FETCH NEXT FROM curLevel INTO @.CurrentNodeId
WHILE @.@.FETCH_STATUS = 0
BEGIN
 EXEC GetNodesAndSubNodesRecursive @.CurrentNodeId
 FETCH NEXT FROM curLevel INTO @.CurrentNodeId
END
-- Clean up
CLOSE curLevel
DEALLOCATE curLevel
GO

|||Aaarg :)

I decided to work on a non recursive solution.

Noob count question

I have a table of events, which stores an event ID, event Name and event
date.
I want to write a stored procedure that returns the next 4 events starting
from today, so far I have:
SELECT
[EventID],
[EventName],
[EventDate]
FROM
Events
WHERE
(DATEDIFF(dd, GETDATE(), EventDate) > 0)
ORDER BY
[EventDate] ASC
How do I add a clause to say only return a maximum of 4 (or however many is
left if fewer) events from today?Use the TOP clause using 4 as the number of records to be returned.
Optionally SET ROWCOUNT. But TOP is preferred.
HTH
Jerry
"Seefor" <invalid@.email.address> wrote in message
news:DfBXe.111419$G8.92946@.text.news.blueyonder.co.uk...
>I have a table of events, which stores an event ID, event Name and event
>date.
> I want to write a stored procedure that returns the next 4 events starting
> from today, so far I have:
> SELECT
> [EventID],
> [EventName],
> [EventDate]
> FROM
> Events
> WHERE
> (DATEDIFF(dd, GETDATE(), EventDate) > 0)
> ORDER BY
> [EventDate] ASC
>
> How do I add a clause to say only return a maximum of 4 (or however many
> is left if fewer) events from today?
>|||many thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:etg0fRTvFHA.1392@.tk2msftngp13.phx.gbl...
> Use the TOP clause using 4 as the number of records to be returned.
> Optionally SET ROWCOUNT. But TOP is preferred.
> HTH
> Jerry
> "Seefor" <invalid@.email.address> wrote in message
> news:DfBXe.111419$G8.92946@.text.news.blueyonder.co.uk...
>|||If you want to use an index on EventDate efficiently, make sure you don't
have any manipulation on the filtered column.
Instead of:
WHERE (DATEDIFF(dd, GETDATE(), EventDate) > 0)
Use:
WHERE EventDate >= CONVERT(VARCHAR(8), GETDATE(), 112)
Specify TOP 4 in the SELECT list, and sort by EventDate .
BG, SQL Server MVP
www.SolidQualityLearning.com
"Seefor" <invalid@.email.address> wrote in message
news:DfBXe.111419$G8.92946@.text.news.blueyonder.co.uk...
>I have a table of events, which stores an event ID, event Name and event
>date.
> I want to write a stored procedure that returns the next 4 events starting
> from today, so far I have:
> SELECT
> [EventID],
> [EventName],
> [EventDate]
> FROM
> Events
> WHERE
> (DATEDIFF(dd, GETDATE(), EventDate) > 0)
> ORDER BY
> [EventDate] ASC
>
> How do I add a clause to say only return a maximum of 4 (or however many
> is left if fewer) events from today?
>

Non-sync initialization

Dear friends
I read about 'NoSync' initialization.I tried out in my system.That time after executing Sp_Scriptpublicationcustomprocs,Stored procedures are propagated to subscriber.Replication was working fine.But afterwards I got some problem.So I reinstall my SQL Server I agin tried 'NoSync' initialization.This time Stored procedure is getting created on Publisher database not in subscriber.Can you tell me what may be the reason

I have two other doubts also
1)Sometimes I am getting error in subscriber.'The process couldn't connect to Distributor'.How to configure distributor in this case?
in configure publishing,distributor wizard I won't able to do anything like that change distributor login or something

2)How to delete Distributor database from a server.once it's get created if wan't also I won't able to delete it

3)If I am using SQL Server 2005 Peer to peer Replication whether it will be reliable for load balancing.My All servers will be in one office only.Or clustering may be better technology for load balancing.if it is like that can you tell me why it is?

Expecting reply
Filson

If it worked for you the first time, it should work for you the second time If you received an error, you have to tell us otherwise we won't know where to start.

Regarding "the process could not connect to Distributor" msg, you say "sometimes", or do you mean "all the time"? If it's sometimes, then you need to check your network connection or domain security. All replication does is try to make a connection, if it can't, it will fail.

If you want to remove replication, follow the steps in Books Online for remove replication. If you get into a state where you can't get out of, try running "sp_removedbreplication".

Peer to Peer is an excellent solution for load balancing, it's one of several. there are many docs on the net describing pros and cons of each.

non-required parameter

I using BI development studio to create some reports and one of the stored procedures that I have takes in 4 parameters.

ManagerID int =0
OfficeID int =0
StartDate datetime
EndDate datetime

In the report designer I use 2 other sql queries to populate ManagerID fields and OfficeID Fields.

The report will work if the customer selects both ManagerID and OfficeID fields but will not work if Manager OR OfficeID fields are select. They both have to be selected or an error message will be displayed stating that the non-selected field must have a value.

The the parameters properties section I have changed the type is integer and select allowed NULLs, I have even set the default value to 0 this didn't work. Then I tried changing the type from integer to string and selected 'allowed blanks'.

I still get the popup message stating that the non-select item must have a value.

Is there a way I can select 1 and not the other?

why not to try to add those drop down list to the web page not in the report and pass the selected parameters to the report file

Sample code

Private Sub SetReportParameters()
Dim userid As New ReportParameter("userid",
Security.GetUserID().ToString())
Dim p() As ReportParameter = {userid}
ReportViewer1.ServerReport.SetParameters(p)
End Sub

http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-1/

|||

Tthank you for the reply.

One thing I should have mentioned is that we are using ColdFusion 7 with SQL 2005.
I am still learning a few things about it.
If will try passing in those parameters but I am not sure how it will work with this structure.

|||

I don't think there is any problem but i cam through great web cast for this hope it helps you

SQL Server Reporting Services for Cold Fusion Developers
https://www119.livemeeting.com/cc/mseventsbmo/view?id=1032256029&role=attend&pw=7933C65D

|||

Thank you for the link, I have been looking for this video for awhile and all I got was a word document of the webcast.

I will be watching it soon.

I will mark this as answered.

Non-English Characters in Table/Stored Procedure Names

Could you tell me what are the limitations/concerns regarding Non-English characters being used for Table/Views/Stored Procedure Names?

I have been studying that very topic, my web site is built on 12 languages at present, and storing documents with UTF-8 encoding in SQL text fields works well. Its a bit tricky though with HTML tags embedded.|||

See the topic below for more details:

http://msdn2.microsoft.com/en-us/library/ms175874(SQL.90).aspx

|||Thanks!

NoLock vs ReadPast

I have been experiencing deadlock errors with two stored procs that I am using.

SP1 is a read query that gets all unprocessed orders (status = 0)

SP2 is an insert query that inserts new orders (Status = 0) uses a transaction.

I have a multithreaded application and once in a while the read query (SP1) will try to read a new row that has just been inserted but not committed yet hence the deadlock arises.

If i use a hint "With(NoLocks)" this will be a dirty read and still read the uncommitted insert from SP2 - is this correct?

Where as if I use hint "With(ReadPast)" this will now only read committed rows and hence the deadlock should not arise - it will not read any uncommitted rows - Correct?

So I think that it is better to use READPAST than NOLOCK. Any orders that have status = 0 not picked up will get picked up on the next round when SP1 is executed again.

Any thougths or suggestions are always appreciated.

Jawahar

I have a lot of experience with NOLOCK. The basic different in NOLOCK and READPAST is that NOLOCK will read the uncommitted data, preventing blocking because of that specific read. However, READPAST skips those rows. If you are updating a row, then NOLOCK reads the uncommitted data if the transaction is still open. If you use READPAST, it doesn't read the row at all, as I understand it.

Since I have a lot of experience with NOLOCK, I would suggest NOLOCK because I understand it a lot better.

I'm not all that convinced that your reads are causing DEADLOCKS however. I wonder if you are getting multiple inserts or updates that are blocking each other. The read locks (READ COMMITTED), by default, will just wait until the insert is done and then read the new inserted row. NOLOCK just tells it not to wait and reads UNCOMMITTED.

To test this, I would use NOLOCK and see if you still have the same problem.

|||

I would almost never suggest you use NOLOCK in a production environment. You don't want to have any chance that you have two users fetching the same row to process. So I would suggest you use readpast to read only committed rows, but skip those that are being inserted, in case that row fails.

As for deadlocks, I can't exactly fathom why this is occurring from the limited information. It is possible that you have indexing problems, causing full table scans to occur on selects... Can you post the table structure and queries.

|||I disagree about the production comment. If you are pulling reports, you want to be able to pull the same record quickly--regardless of locks. As for selecting the same record by two users at the same time, that would occur anyway whether you use NOLOCK or not--assuming that there is not an open transaction modifying the situation.|||There is a possibility for rows to show up twice when using NOLOCK because of page splitting so beware of the hint unless you know exactly how your data is used. This does not mean you should never use NOLOCK of course, there are many situations in which it is completely sane to use it but just remember the consequences.|||

Chris:

I think you have misunderstood what Louis means. I think here when he says "production" he means as part of a record-updating process -- in which case if you are reading a record for update you would never want a dirty read -- using a NOLOCK hint -- but always want a "clean" read of the data.

Dave

|||

Dave,

I think you are correct. I agree with the statement in that case :)

|||

All thank you for your insight

Here are my two sp that cause the Deadlock to occur sometims - I have poseted the queries ony not the whole SP with input paramters etc but the queries are the guts of the SP. I did tunr on Deadlock tracing and these SP were identified as the cause of the deadlock situtation - Many thanks -Jawahar

SP 1 - Read (currently there are no hints) - PLEASE note the queries are not optimized and do not follow good coding standards)

BEGIN
set rowcount @.nMaxMessages
select A.* from MsgRequest A
where ProcessingCode = 0
and RequestID = ( select Min(RequestID) from MsgRequest B where A.ClientID= B.ClientID and B.ProcessingCode = 0 )
and A.ClientID NOT IN (select ClientID from MsgRequest where ProcessingCode = 1)
order by ReceiveDT ASC
END

SP 2 Insert (currently there are no hints) - PLEASE note the queries are not optimized and do not follow good coding standards)

BEGIN
BEGIN TRAN
insert into MsgRequest ( ReceiveDT, ClientID, CommandCode, ProcessingCode, MessageLen, QueryParameterString,
MessageData, ServiceOrderID, NewServiceOrderFlag, ClientVersionNumber)
Values ( getdate(), @.ClientID, @.CommandCode, @.ProcessingCode, @.MessageLen, @.QueryParameterString,
@.MessageData, @.ServiceOrderID, @.NewServiceOrderFlag, @.ClientVersionNumber)
if (@.@.error != 0)
BEGIN
RAISERROR 20001 'Error in csp_MsgRequestInsert'
ROLLBACK TRAN
RETURN(1)
END
COMMIT
return (0)
END

|||

Jawahar:

Is the RequestID column unique in the MsgRequest table? That is, is the RequestID a key to this table? Also, is the data returned by this procedure used as a select list or is this potentially used as the basis for a record update?

Dave

|||

Dave,

Yes the RequestID is the Primary key

The data from the select is processed row by row using a multithreaded system. We run a windows service (multithreaded) to process the data (new orders).

Jawahar

|||

Jawahar:

There are a few other things that I need to know:

How many rows are in the MsgRequest table ?|||

You need to first determine the cause of the deadlock before trying to use locking hints. What version of SQL Server are you using? Did you enable trace flag # 1204? In SQL Server 2005, there is a new trace flag# 1222 and trace events that will help identify the cause. See link below for troubleshooting steps for SQL Server 2000:

http://support.microsoft.com/default.aspx/kb/832524/

|||

Jawahar:

Umachandar is right about the need to fully understand your deadlock. But I went ahead and attempted to mock up your select query.

First, I created an index on the MsgRequest table based on (1) ProcessingCode, (2) ClientID and (3) requestID. I figured that the requestID component was not completely necessary but I wanted to avoid some bookmark lookups so I included. When I got all done testing I tried recreating the index without the requestID component and found that when I removed the requestID component from the index my logical reads increased from 38 to 4007 so I left it in.

I was able to improve on the performance of your select query by eliminating one of the scans through the MsgRequest table. The query I used was:

select A.*
from msgRequest A
inner join
( select clientId,
max (processingCode) as max_processingCode,
min (requestID) as min_requestID
from msgRequest p
where processingCode between 0 and 1
group by clientId
) b
on a.requestID = b.min_requestID
and max_processingCode = 0
order by ReceiveDT ASC

This query returned for me the same rows as your original query was returning and ran with far less logical IO. I don't like the A.* syntax, but like you said ... Sometimes you got what you you got. Hopefully, this query will provide (1) a smaller lock profile and (2) a much smaller IO profile.

Dave

|||

Jawahar:

One thing that I failed to point out is that my test was based on a relatively low cardinality of the '0' and '1' processing codes compared to all other processing codes. I used a 1000000 row test table with 8000 rows in the 1 process state and 800 rows in the 0 process state because it looks to me like we are talking about a "new record" process.

Dave

|||

Dave

Thanks for your new suggestion on the Selection query - sorry for the late reply

I am using SQL Sever 2000

I did run the trace DBCC Trace 1204 and 3605 and found that the two SP I have listed are the ones that are involved in the Deadlock. The Select SP being the victim each time. Currently I only have an index on the RequestID column, but adding the ClientID and Processing code is a good Idea.

Out table at present has 600, 000 records. 99.33 % of the records have already been processed and have a Processing code of = 2 and we never touch those records. At any given time we should not have more than 15 to 20 records of the Processing code = 0 and 15 to 20 records Processing code = 1.

would the DBCC trace be helpful to you?

Thanks again for your suggestions I will try yor revised query. Another process that I might add is to archive Rows that have already been processd to an archive table to help reduce the size of the request table.

-jawahar

noise words, @@ERROR, and stop and resume indexing

Hi,
I'm FTS newbee, and have some questions
1) check noise words inside stored procedure
2) @.@.Error fails
3) The best way to stop and restart indexing
1)
Just found out that this error
Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
A clause of the query contained only ignored words.
triggered when executing
SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
can be solved by changing the language specific file with noise words ...
but that's not really an option in our server configuration. Now I wondered
is there a way to query via TSQL a list of the noisewords
... so I can exclude them before parsing the query? I could import the
noisewordfile into a tabel, but isn't there an easier way?
2)
Concerning the error above I dedected I can not catch the error in my stored
procedure with the instruction IF @.@.error ... So is it true that I can only
handle this error in my client software that calls the stored procedure?
3)
My full text index works fine (SQLSERVER2000/WIN2000). It requires to
update indexes immediately, so I use a timestamp field to enable this. Now,
I've got a stored procedures which nearly daily inserts about 10.000 rows.
When doing this while full text indexing is active, all users start
complaining about performance. In order to work around this problem I
tried doing the following ...
Create myStoredProcedure
-- begin of stored procedure
exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
-- insert 10.000 rows
-- end of stored procedure
exec sp_fulltext_table 'adsfull', 'start_change_tracking'
exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
Now, it seems this doesn't work. SQL Server keeps tracking changes and
updating indexes. Also if I cut away the stop instructions and paste them
into query analyzer before starting the stored procedure.
So, if I check the status via select fulltextcatalogproperty('FTADS',
'Populatestatus') ... it returns value 6 (incremental in progress) instead
of 0 (idle) while executing the stored procedure.
The only way I can resolve this issue is to stop the indexing via the
enterprise manager and to restart after the stored procedure is executed.
Is there a better/other way to stop and restart indexing instead of the 4
lines I used above?
Any help appreciated.
Kind regards,
Perre Van Wilrijk,
Remove capitals to get my real email address,
1) You could do something like this:
set nocount on
GO
Create table Noise
(noiseword varchar(100))
GO
insert into noise
exec master.dbo.xp_Cmdshell 'type c:\"Program Files\Microsoft SQL
Server"\mssql\ftdata\sqlserver\config\noise.enu'
GO
delete from noise where NoiseWord is null
GO
declare @.string varchar(100)
select @.string=noiseword from noise where charindex(' ',noiseword)>0
while charindex(' ',@.string)>0
begin
insert into noise (noiseword) values (left(@.string,charindex(' ',@.string)))
select @.string=substring(@.string,charindex(' ',@.string)+1,100)
end
GO
delete from noise where len(noiseword)-len(replace(noiseword,' ',''))>0
GO
select * from noise order by 1
For use US English.
2) no there is no good way of doing this. I normally check at the client,
for instance errors messages will be returned via ado saying MSSearch
service not runing,
3) Whenever you kick of change tracking a full or incremental population is
started. I can't think of a way to get around this right now. I'd try to
investigate exactly why you are experiencing locking on your table with the
insert proc. Perhaps you have having data page movement associated with
cluster index reorgs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
> Hi,
> I'm FTS newbee, and have some questions
> 1) check noise words inside stored procedure
> 2) @.@.Error fails
> 3) The best way to stop and restart indexing
> 1)
> Just found out that this error
> Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
> A clause of the query contained only ignored words.
> triggered when executing
> SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
> WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
> WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
> can be solved by changing the language specific file with noise words ...
> but that's not really an option in our server configuration. Now I
wondered
> is there a way to query via TSQL a list of the noisewords
> ... so I can exclude them before parsing the query? I could import the
> noisewordfile into a tabel, but isn't there an easier way?
> 2)
> Concerning the error above I dedected I can not catch the error in my
stored
> procedure with the instruction IF @.@.error ... So is it true that I can
only
> handle this error in my client software that calls the stored
procedure?
> 3)
> My full text index works fine (SQLSERVER2000/WIN2000). It requires to
> update indexes immediately, so I use a timestamp field to enable this.
Now,
> I've got a stored procedures which nearly daily inserts about 10.000 rows.
> When doing this while full text indexing is active, all users start
> complaining about performance. In order to work around this problem I
> tried doing the following ...
> Create myStoredProcedure
> -- begin of stored procedure
> exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
> --
> -- insert 10.000 rows
> --
> -- end of stored procedure
> exec sp_fulltext_table 'adsfull', 'start_change_tracking'
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> Now, it seems this doesn't work. SQL Server keeps tracking changes and
> updating indexes. Also if I cut away the stop instructions and paste them
> into query analyzer before starting the stored procedure.
> So, if I check the status via select fulltextcatalogproperty('FTADS',
> 'Populatestatus') ... it returns value 6 (incremental in progress) instead
> of 0 (idle) while executing the stored procedure.
> The only way I can resolve this issue is to stop the indexing via the
> enterprise manager and to restart after the stored procedure is executed.
> Is there a better/other way to stop and restart indexing instead of the 4
> lines I used above?
> Any help appreciated.
> --
> Kind regards,
> Perre Van Wilrijk,
> Remove capitals to get my real email address,
>
|||BTW - you might want to check out this kb article. It claims some of the
errors raised by incorrect searches do generate a correct @.@.error value.
http://support.microsoft.com/default...b;en-us;287167
My experience is still that it does not capture all errors.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
> Hi,
> I'm FTS newbee, and have some questions
> 1) check noise words inside stored procedure
> 2) @.@.Error fails
> 3) The best way to stop and restart indexing
> 1)
> Just found out that this error
> Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
> A clause of the query contained only ignored words.
> triggered when executing
> SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
> WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
> WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
> can be solved by changing the language specific file with noise words ...
> but that's not really an option in our server configuration. Now I
wondered
> is there a way to query via TSQL a list of the noisewords
> ... so I can exclude them before parsing the query? I could import the
> noisewordfile into a tabel, but isn't there an easier way?
> 2)
> Concerning the error above I dedected I can not catch the error in my
stored
> procedure with the instruction IF @.@.error ... So is it true that I can
only
> handle this error in my client software that calls the stored
procedure?
> 3)
> My full text index works fine (SQLSERVER2000/WIN2000). It requires to
> update indexes immediately, so I use a timestamp field to enable this.
Now,
> I've got a stored procedures which nearly daily inserts about 10.000 rows.
> When doing this while full text indexing is active, all users start
> complaining about performance. In order to work around this problem I
> tried doing the following ...
> Create myStoredProcedure
> -- begin of stored procedure
> exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
> --
> -- insert 10.000 rows
> --
> -- end of stored procedure
> exec sp_fulltext_table 'adsfull', 'start_change_tracking'
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> Now, it seems this doesn't work. SQL Server keeps tracking changes and
> updating indexes. Also if I cut away the stop instructions and paste them
> into query analyzer before starting the stored procedure.
> So, if I check the status via select fulltextcatalogproperty('FTADS',
> 'Populatestatus') ... it returns value 6 (incremental in progress) instead
> of 0 (idle) while executing the stored procedure.
> The only way I can resolve this issue is to stop the indexing via the
> enterprise manager and to restart after the stored procedure is executed.
> Is there a better/other way to stop and restart indexing instead of the 4
> lines I used above?
> Any help appreciated.
> --
> Kind regards,
> Perre Van Wilrijk,
> Remove capitals to get my real email address,
>
|||Perre,
Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
question in this newsgroup, and there are many ways of accomplishing this,
but the best one (IMHO) is one that I posted to this newsgroup back on March
21, 2003 as recorded in Google Groups via the following shortened url:
http://tinyurl.com/69kyy. Specifically:
Create Table noise_words
(
Noiseword varchar(50) Not Null
)
Go
Alter Table noise_words Add Constraint PK_noise_words PrimarXy Key Clustered
(
Noiseword
)
Go
Then you can use BULK INSERT, BCP or DTS to copy the contentXs of the file
into the database. Before you copy in the language-specific Xnoise word
file, you will need to make some changes to the initial file from Xthe end
of the file as the noise word files contain a list of "white space"X single
letters and characters at the end of the file, for example, from noiXse.enu:
a b c d e f g h i j k l m n o p q r s t u v w x y z
BULK INSERT or BCP will fail or think this is one big stringX (no CR/LF), so
you will need to separate out the row above such that each lXetter takes up
its own row in the file. Open the language specific noise woXrd file in a
text editor (notepad.exe) and change the above list to:
a
b
c
d
e
f
and so on. Be sure to eliminate any leading or trailing spacXes for each
character. Once that's done, you can use BULK INSERT, BCP orX DTS to copy
the data from the noise file to the noise_words table. Once the data is
imported correctly, you can use a standard XSQL statement such as:
select count(*) from noise_words where Noiseword = "between"
to use in a string parser function to remove the noise wordsX in your users
input string and then pass this edited string to a SQL ServeXr Full-Text
Search query.
In regards to FTS setting @.@.error, see KB article: Q287167 "FIX: Some
Full-Text Search Failures Do Not Set @.@.ERROR" at
http://support.microsoft.com/default...;en-us;Q287167
In regards to stopping and then restarting FT Indexing, try the following:
EXEC sp_fulltext_table 'adsfull', 'stop_background_updateindex'
EXEC sp_fulltext_catalog 'adsfull', 'stop'
-- insert 10,000 rows
exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
go
If that does not work, then try stopping the MSSearch service via
xp_cmdshell, inserting your 10,000 rows and then re-starting the MSSearch
service, for example:
-- Stop the MSSearch Service
exec master..xp_cmdshell 'net stop "Microsoft Search"'
go
-- Start the MSSearch Service
exec master..xp_cmdshell 'net start "Microsoft Search"'
go
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
> Hi,
> I'm FTS newbee, and have some questions
> 1) check noise words inside stored procedure
> 2) @.@.Error fails
> 3) The best way to stop and restart indexing
> 1)
> Just found out that this error
> Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
> A clause of the query contained only ignored words.
> triggered when executing
> SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
> WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
> WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
> can be solved by changing the language specific file with noise words ...
> but that's not really an option in our server configuration. Now I
wondered
> is there a way to query via TSQL a list of the noisewords
> ... so I can exclude them before parsing the query? I could import the
> noisewordfile into a tabel, but isn't there an easier way?
> 2)
> Concerning the error above I dedected I can not catch the error in my
stored
> procedure with the instruction IF @.@.error ... So is it true that I can
only
> handle this error in my client software that calls the stored
procedure?
> 3)
> My full text index works fine (SQLSERVER2000/WIN2000). It requires to
> update indexes immediately, so I use a timestamp field to enable this.
Now,
> I've got a stored procedures which nearly daily inserts about 10.000 rows.
> When doing this while full text indexing is active, all users start
> complaining about performance. In order to work around this problem I
> tried doing the following ...
> Create myStoredProcedure
> -- begin of stored procedure
> exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
> --
> -- insert 10.000 rows
> --
> -- end of stored procedure
> exec sp_fulltext_table 'adsfull', 'start_change_tracking'
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> Now, it seems this doesn't work. SQL Server keeps tracking changes and
> updating indexes. Also if I cut away the stop instructions and paste them
> into query analyzer before starting the stored procedure.
> So, if I check the status via select fulltextcatalogproperty('FTADS',
> 'Populatestatus') ... it returns value 6 (incremental in progress) instead
> of 0 (idle) while executing the stored procedure.
> The only way I can resolve this issue is to stop the indexing via the
> enterprise manager and to restart after the stored procedure is executed.
> Is there a better/other way to stop and restart indexing instead of the 4
> lines I used above?
> Any help appreciated.
> --
> Kind regards,
> Perre Van Wilrijk,
> Remove capitals to get my real email address,
>
|||Hilary, John,
Clarifying solutions,
Thanks a lot.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OeHOZmcBFHA.2572@.tk2msftngp13.phx.gbl...
> Perre,
> Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
> question in this newsgroup, and there are many ways of accomplishing this,
> but the best one (IMHO) is one that I posted to this newsgroup back on
March
> 21, 2003 as recorded in Google Groups via the following shortened url:
> http://tinyurl.com/69kyy. Specifically:
> Create Table noise_words
> (
> Noiseword varchar(50) Not Null
> )
> Go
> Alter Table noise_words Add Constraint PK_noise_words PrimarXy Key
Clustered
> (
> Noiseword
> )
> Go
> Then you can use BULK INSERT, BCP or DTS to copy the contentXs of the file
> into the database. Before you copy in the language-specific Xnoise word
> file, you will need to make some changes to the initial file from Xthe
end
> of the file as the noise word files contain a list of "white space"X
single
> letters and characters at the end of the file, for example, from
noiXse.enu:
> a b c d e f g h i j k l m n o p q r s t u v w x y z
> BULK INSERT or BCP will fail or think this is one big stringX (no CR/LF),
so
> you will need to separate out the row above such that each lXetter takes
up
> its own row in the file. Open the language specific noise woXrd file in a
> text editor (notepad.exe) and change the above list to:
> a
> b
> c
> d
> e
> f
> and so on. Be sure to eliminate any leading or trailing spacXes for each
> character. Once that's done, you can use BULK INSERT, BCP orX DTS to copy
> the data from the noise file to the noise_words table. Once the data is
> imported correctly, you can use a standard XSQL statement such as:
> select count(*) from noise_words where Noiseword = "between"
> to use in a string parser function to remove the noise wordsX in your
users[vbcol=seagreen]
> input string and then pass this edited string to a SQL ServeXr Full-Text
> Search query.
> In regards to FTS setting @.@.error, see KB article: Q287167 "FIX: Some
> Full-Text Search Failures Do Not Set @.@.ERROR" at
> http://support.microsoft.com/default...;en-us;Q287167
> In regards to stopping and then restarting FT Indexing, try the following:
> EXEC sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> EXEC sp_fulltext_catalog 'adsfull', 'stop'
> --
> -- insert 10,000 rows
> --
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> go
> If that does not work, then try stopping the MSSearch service via
> xp_cmdshell, inserting your 10,000 rows and then re-starting the MSSearch
> service, for example:
> -- Stop the MSSearch Service
> exec master..xp_cmdshell 'net stop "Microsoft Search"'
> go
> -- Start the MSSearch Service
> exec master..xp_cmdshell 'net start "Microsoft Search"'
> go
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
> news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
...[vbcol=seagreen]
> wondered
the[vbcol=seagreen]
> stored
> only
> procedure?
> Now,
rows.[vbcol=seagreen]
them[vbcol=seagreen]
instead[vbcol=seagreen]
executed.[vbcol=seagreen]
4
>
|||Hilary,
Re point 2, I am running multiple full-text searches
within a cursor (Original posting
in .sqlserver.programming, 4th Feb , Subject: "On Error
Resume Next" in SQL Server) and I want the procedure to
run through the entire cursor whatever but if there is an
ignored-words error the whole thing stops and does not
reach the end of the cursor so it's not so much that I
want to handle an error as to ignore it but I can't seem
to do this either. Any ideas?

>--Original Message--
>1) You could do something like this:
>set nocount on
>GO
>Create table Noise
>(noiseword varchar(100))
>GO
>insert into noise
>exec master.dbo.xp_Cmdshell 'type c:\"Program
Files\Microsoft SQL
>Server"\mssql\ftdata\sqlserver\config\noise.enu '
>GO
>delete from noise where NoiseWord is null
>GO
>declare @.string varchar(100)
>select @.string=noiseword from noise where charindex
(' ',noiseword)>0
>while charindex(' ',@.string)>0
>begin
>insert into noise (noiseword) values (left
(@.string,charindex(' ',@.string)))
>select @.string=substring(@.string,charindex(' ',@.string)
+1,100)
>end
>GO
>delete from noise where len(noiseword)-len(replace
(noiseword,' ',''))>0
>GO
>select * from noise order by 1
>For use US English.
>2) no there is no good way of doing this. I normally
check at the client,
>for instance errors messages will be returned via ado
saying MSSearch
>service not runing,
>3) Whenever you kick of change tracking a full or
incremental population is
>started. I can't think of a way to get around this right
now. I'd try to
>investigate exactly why you are experiencing locking on
your table with the
>insert proc. Perhaps you have having data page movement
associated with
>cluster index reorgs.
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in
message[vbcol=seagreen]
>news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
Line 2[vbcol=seagreen]
fads_adid[vbcol=seagreen]
with noise words ...[vbcol=seagreen]
configuration. Now I[vbcol=seagreen]
>wondered
noisewords[vbcol=seagreen]
I could import the[vbcol=seagreen]
way?[vbcol=seagreen]
the error in my[vbcol=seagreen]
>stored
true that I can[vbcol=seagreen]
>only
the stored[vbcol=seagreen]
>procedure?
It requires to[vbcol=seagreen]
to enable this.[vbcol=seagreen]
>Now,
about 10.000 rows.[vbcol=seagreen]
users start[vbcol=seagreen]
this problem I[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'stop_background_updateindex'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'start_change_tracking'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'start_background_updateindex'[vbcol=seagreen]
tracking changes and[vbcol=seagreen]
instructions and paste them[vbcol=seagreen]
procedure.[vbcol=seagreen]
fulltextcatalogproperty('FTADS',[vbcol=seagreen]
in progress) instead[vbcol=seagreen]
indexing via the[vbcol=seagreen]
procedure is executed.[vbcol=seagreen]
indexing instead of the 4
>
>.
>
|||Andy,
Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
question in this newsgroup, and there are many ways of accomplishing this,
but the best one (IMHO) is one that I posted to this newsgroup back on March
21, 2003 as recorded in Google Groups via the following shortened url:
http://tinyurl.com/69kyy.
In regards, to point 2, have you read KB article: Q287167 "FIX: Some
Full-Text Search Failures Do Not Set @.@.ERROR" at
http://support.microsoft.com/default...;en-us;Q287167 ?
Could you re-post your SQL script with the cursor code? Depending upon what
you are trying to do, the use of cursors, may not be the best approach.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:2b8a01c50d0b$6bdf12f0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hilary,
> Re point 2, I am running multiple full-text searches
> within a cursor (Original posting
> in .sqlserver.programming, 4th Feb , Subject: "On Error
> Resume Next" in SQL Server) and I want the procedure to
> run through the entire cursor whatever but if there is an
> ignored-words error the whole thing stops and does not
> reach the end of the cursor so it's not so much that I
> want to handle an error as to ignore it but I can't seem
> to do this either. Any ideas?
> Files\Microsoft SQL
> (' ',noiseword)>0
> (@.string,charindex(' ',@.string)))
> +1,100)
> (noiseword,' ',''))>0
> check at the client,
> saying MSSearch
> incremental population is
> now. I'd try to
> your table with the
> associated with
> message
> Line 2
> fads_adid
> with noise words ...
> configuration. Now I
> noisewords
> I could import the
> way?
> the error in my
> true that I can
> the stored
> It requires to
> to enable this.
> about 10.000 rows.
> users start
> this problem I
> sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> sp_fulltext_table 'adsfull', 'start_change_tracking'
> sp_fulltext_table 'adsfull', 'start_background_updateindex'
> tracking changes and
> instructions and paste them
> procedure.
> fulltextcatalogproperty('FTADS',
> in progress) instead
> indexing via the
> procedure is executed.
> indexing instead of the 4
|||there doesn't seem to be a clean way to handle this other than to extract
these words at the beginning before sending them to the cursor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:2b8a01c50d0b$6bdf12f0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hilary,
> Re point 2, I am running multiple full-text searches
> within a cursor (Original posting
> in .sqlserver.programming, 4th Feb , Subject: "On Error
> Resume Next" in SQL Server) and I want the procedure to
> run through the entire cursor whatever but if there is an
> ignored-words error the whole thing stops and does not
> reach the end of the cursor so it's not so much that I
> want to handle an error as to ignore it but I can't seem
> to do this either. Any ideas?
> Files\Microsoft SQL
> (' ',noiseword)>0
> (@.string,charindex(' ',@.string)))
> +1,100)
> (noiseword,' ',''))>0
> check at the client,
> saying MSSearch
> incremental population is
> now. I'd try to
> your table with the
> associated with
> message
> Line 2
> fads_adid
> with noise words ...
> configuration. Now I
> noisewords
> I could import the
> way?
> the error in my
> true that I can
> the stored
> It requires to
> to enable this.
> about 10.000 rows.
> users start
> this problem I
> sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> sp_fulltext_table 'adsfull', 'start_change_tracking'
> sp_fulltext_table 'adsfull', 'start_background_updateindex'
> tracking changes and
> instructions and paste them
> procedure.
> fulltextcatalogproperty('FTADS',
> in progress) instead
> indexing via the
> procedure is executed.
> indexing instead of the 4
|||Andy,
FYI, on KB article Q287167 as it is a "FIX" kb article, and this issue with
@.@.error was fixed in SQL Server 2000 SP1.
What is the full output of -- SELECT @.@.version -- on your server where you
are executing the below example code?
DECLARE TestCursor CURSOR FOR /*WHATEVER*/
OPEN TestCursor
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM TestCursor INTO @.SearchText
SET @.SearchText = FormatSearchText(@.SearchText)
/*
This is a UDF with output as per KB article as mentioned.
If anything goes wrong this returns '' as I am not
bothered if it cannot resolve input but note: This can
still output junk that can break the CONTAINS search.
*/
INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
/*
When run in QA, if this query causes an ignored-word error
the SP stops dead. I need it to carry on to the end of the
cursor.
*/
END, CLOSE, DEALLOCATE etc.
SELECT * FROM #TEMPTABLE /* Output of entire cursor */
That's pretty much what I'm trying to achieve. What do you reckon?
I reckon that if you enclose your @.SearchText in double quotes, i.e., a
phrase search, the noise word would be truly ignored. See SQL Server 2000
BOL title "Full-text Search Recommendations" - "Consider rewriting this
query to a phrase-based query, removing the noise word, or options offered
in Knowledge Base article Q246800, "INF: Correctly Parsing Quotation Marks
in FTS Queries". For example using the pubs database table pr_info:
select pub_id, pr_info from pub_info where CONTAINS(pr_info, '"between AND
books"')
In the above query the noise word "between" is truly ignored or you can use
FREETEXT if the search string cannot be fully placed within double quotes,
for example:
select pub_id, pr_info from pub_info where freetext(pr_info, '"between" AND
"books"')
Finally, you can remove all noise words from your language-specific noise
word file. These files are located under \FTDATA\SQLServer\Config\noise.*
where * represents the language - enu = US_English. You will need to stop
the MSSearch service, edit noise.enu (assuming US_English) with notepad.exe
and remove the words and save the file, restart the MSSearch service and run
a Full Population on all of your FT Catalogs. I'd recommend that you remove
all noise words that you may want to search on, but not empty the entire
file and at least leave a single space in the file.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Kane" <jt-kane@.comcast.net> wrote in message
news:e4tWiVTDFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Andy,
> Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
> question in this newsgroup, and there are many ways of accomplishing this,
> but the best one (IMHO) is one that I posted to this newsgroup back on
March
> 21, 2003 as recorded in Google Groups via the following shortened url:
> http://tinyurl.com/69kyy.
> In regards, to point 2, have you read KB article: Q287167 "FIX: Some
> Full-Text Search Failures Do Not Set @.@.ERROR" at
> http://support.microsoft.com/default...;en-us;Q287167 ?
> Could you re-post your SQL script with the cursor code? Depending upon
what
> you are trying to do, the use of cursors, may not be the best approach.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b8a01c50d0b$6bdf12f0$a601280a@.phx.gbl...
>
|||John,
I didn't make it clear but the UDF encloses everything in
double quotes unless it resolves to nothing in which case
an empty string is returned and the search is not
performed. It's not the noise words I have a problem with,
I just wanted to be able to skip over the ignored-words
error and carry on. Obviously the best solution would be
to make the UDF infallible but as good as I think I've got
it something else comes along e.g. one user had a search
text of a single DEL (ASCII 127) character and that broke
it. Now without going through the entire database and
reformatting all the users' search terms the only way of
proceding is to either account for absolutely every
possible ASCII/Unicode character combination or to simply
ignore examples such as the above which is what I'm trying
to do. As far as the @.@.version it's SQL 2000 SP3.
I may just have to return the initial results and do each
full-text search from the client code but i'm just a bit
miffed of having to make all those extra DB calls :-(

>--Original Message--
>Andy,
>FYI, on KB article Q287167 as it is a "FIX" kb article,
and this issue with
>@.@.error was fixed in SQL Server 2000 SP1.
>What is the full output of -- SELECT @.@.version -- on your
server where you
>are executing the below example code?
>DECLARE TestCursor CURSOR FOR /*WHATEVER*/
>OPEN TestCursor
>WHILE (1 = 1)
>BEGIN
>FETCH NEXT FROM TestCursor INTO @.SearchText
>SET @.SearchText = FormatSearchText(@.SearchText)
>/*
>This is a UDF with output as per KB article as mentioned.
>If anything goes wrong this returns '' as I am not
>bothered if it cannot resolve input but note: This can
>still output junk that can break the CONTAINS search.
>*/
>INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
>WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
>/*
>When run in QA, if this query causes an ignored-word error
>the SP stops dead. I need it to carry on to the end of the
>cursor.
>*/
>END, CLOSE, DEALLOCATE etc.
>SELECT * FROM #TEMPTABLE /* Output of entire cursor */
>That's pretty much what I'm trying to achieve. What do
you reckon?
>I reckon that if you enclose your @.SearchText in double
quotes, i.e., a
>phrase search, the noise word would be truly ignored. See
SQL Server 2000
>BOL title "Full-text Search Recommendations" - "Consider
rewriting this
>query to a phrase-based query, removing the noise word,
or options offered
>in Knowledge Base article Q246800, "INF: Correctly
Parsing Quotation Marks
>in FTS Queries". For example using the pubs database
table pr_info:
>select pub_id, pr_info from pub_info where CONTAINS
(pr_info, '"between AND
>books"')
>In the above query the noise word "between" is truly
ignored or you can use
>FREETEXT if the search string cannot be fully placed
within double quotes,
>for example:
>select pub_id, pr_info from pub_info where freetext
(pr_info, '"between" AND
>"books"')
>Finally, you can remove all noise words from your
language-specific noise
>word file. These files are located under
\FTDATA\SQLServer\Config\noise.*
>where * represents the language - enu = US_English. You
will need to stop
>the MSSearch service, edit noise.enu (assuming
US_English) with notepad.exe
>and remove the words and save the file, restart the
MSSearch service and run
>a Full Population on all of your FT Catalogs. I'd
recommend that you remove
>all noise words that you may want to search on, but not
empty the entire[vbcol=seagreen]
>file and at least leave a single space in the file.
>Regards,
>John
>--
>SQL Full Text Search Blog
>http://spaces.msn.com/members/jtkane/
>
>"John Kane" <jt-kane@.comcast.net> wrote in message
>news:e4tWiVTDFHA.2180@.TK2MSFTNGP12.phx.gbl...
frequently asked[vbcol=seagreen]
accomplishing this,[vbcol=seagreen]
newsgroup back on[vbcol=seagreen]
>March
shortened url:[vbcol=seagreen]
Q287167 "FIX: Some[vbcol=seagreen]
us;Q287167 ?[vbcol=seagreen]
Depending upon[vbcol=seagreen]
>what
the best approach.[vbcol=seagreen]
wrote in message[vbcol=seagreen]
Error[vbcol=seagreen]
to[vbcol=seagreen]
is an[vbcol=seagreen]
seem[vbcol=seagreen]
(' ',@.string)[vbcol=seagreen]
right[vbcol=seagreen]
on[vbcol=seagreen]
movement[vbcol=seagreen]
wrote in[vbcol=seagreen]
usp_ft,[vbcol=seagreen]
ads_adid =[vbcol=seagreen]
AND "astra"'))))[vbcol=seagreen]
file[vbcol=seagreen]
query?[vbcol=seagreen]
easier[vbcol=seagreen]
catch[vbcol=seagreen]
is it[vbcol=seagreen]
calls[vbcol=seagreen]
(SQLSERVER2000/WIN2000).[vbcol=seagreen]
field[vbcol=seagreen]
inserts[vbcol=seagreen]
active, all[vbcol=seagreen]
around[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'stop_background_updateindex'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'stop_change_tracking'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'start_background_updateindex'[vbcol=seagreen]
(incremental[vbcol=seagreen]
the
>
>.
>

nocount on problem

i use
set nocount on inside the stored procedure at the beginning however the
Number of Rows Counted/Affected shows up when I execute the stored
procedure in query analyzer using execute sprocName. I also tried to
add the

SET NOCOUNT ON at the beginning of the procedure and it still shows the
number of rows affected.
if i set

set nocount on
exec sprocName then the result set does not show the number of rows
affected.

Any idea why this happens? I know that NOCOUNT is set on runtime not
parse time.

Thanks!"GM" <gentian.metaj@.trustasc.com> wrote in message
news:1104772581.850237.15630@.z14g2000cwz.googlegro ups.com...
>i use
> set nocount on inside the stored procedure at the beginning however the
> Number of Rows Counted/Affected shows up when I execute the stored
> procedure in query analyzer using execute sprocName. I also tried to
> add the
> SET NOCOUNT ON at the beginning of the procedure and it still shows the
> number of rows affected.
> if i set
> set nocount on
> exec sprocName then the result set does not show the number of rows
> affected.
> Any idea why this happens? I know that NOCOUNT is set on runtime not
> parse time.
> Thanks!

I have no idea - the following trivial example works for me, ie. it does not
display the rows affected (in Query Analyzer), using MSSQL 2000 build
8.00.760:

create proc foo
as
begin
set nocount on
select * from master.dbo.sysdatabases
end
go

exec foo
go

If this doesn't help, you might want to post a minimal code example for
Query Analyzer which shows the problem you have, along with details of your
MSSQL version.

Simon|||Thanks Simon. NoCount seems to work OK. The issue was that i was using
IMCEDA SpeedSQL and i'm assuming somehow they do something weird
(Probably get @.@.rowCount or something) but when i ran it on regular QA
it seemed to work. Apparently Imceda SpeedSQL (Formerly known and
SQLExpress) must put some extra code under their interface.

While i was reading Books online it says "The setting of SET NOCOUNT is
set at execute or run time and not at parse time." What are the
implications of that statement?

Thanks

Gent|||"GM" <gentian.metaj@.trustasc.com> wrote in message
news:1104779732.275600.51970@.c13g2000cwb.googlegro ups.com...
> Thanks Simon. NoCount seems to work OK. The issue was that i was using
> IMCEDA SpeedSQL and i'm assuming somehow they do something weird
> (Probably get @.@.rowCount or something) but when i ran it on regular QA
> it seemed to work. Apparently Imceda SpeedSQL (Formerly known and
> SQLExpress) must put some extra code under their interface.
> While i was reading Books online it says "The setting of SET NOCOUNT is
> set at execute or run time and not at parse time." What are the
> implications of that statement?
> Thanks
> Gent

As I understand it, a parse-time option is set when the code is parsed, so
it will be set even if the branch of code it's in is never executed (because
of branching logic or error handling). A run-time option is set only when
that part of code really does execute. See "SET Options" in Books Online for
more details.

By the way, if you want to check up on exactly what SpeedSQL is doing, you
can use Profiler to trace all TSQL sent from it to the server.

Simon

No Views in SQL Web Data Administrator

Does anyone know how I can access Views in the SQL Web Data Administrator? All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.Hi,
I guess its by design.No UDF's too :-(
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Checkbox" <Checkbox@.discussions.microsoft.com> wrote in message
news:48BE1D32-E933-4807-B37B-38FD280017D3@.microsoft.com...
> Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.
>

No Views in SQL Web Data Administrator

Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.Hi,
I guess its by design.No UDF's too :-(
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Checkbox" <Checkbox@.discussions.microsoft.com> wrote in message
news:48BE1D32-E933-4807-B37B-38FD280017D3@.microsoft.com...
> Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.
>

No Views in SQL Web Data Administrator

Does anyone know how I can access Views in the SQL Web Data Administrator? All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.
Hi,
I guess its by design.No UDF's too :-(
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Checkbox" <Checkbox@.discussions.microsoft.com> wrote in message
news:48BE1D32-E933-4807-B37B-38FD280017D3@.microsoft.com...
> Does anyone know how I can access Views in the SQL Web Data Administrator?
All I can see is Tables, Stored Procedures, Query, Properties, Users, Roles.
>

No Temp tables in SQLDatasource???

Please tell me there's something I haven't set. I've done several tests now. If your final return in a stored proc is from a temp table (ala #mytable ) the system cannot read the schema - for that matter, it won't run at all, complaining that the object #mytable doesn't exists.

It can't possible be that temp tables aren't allowed in procs used by SQLDatasource - please tell me what I am doing wrong.

This proc, when fed to a sqldatasource, fails in the designer with #temp does not exists.

CREATE PROCEDURE dbo.repTest_Temp
AS
BEGIN

CREATE TABLE #Temp
(
[iTestID] uniqueidentifier,
[bTest] [bit],
[cTest] [varchar]
)

INSERT INTO #Temp
SELECT *
FROM tTest

SELECT *
FROM #Temp

END

try:

CREATE PROCEDURE dbo.repTest_Temp
AS
BEGIN

CREATE TABLE #Temp
(
[iTestID] uniqueidentifier,
[bTest] [bit],
[cTest] varchar(10)
)

INSERT INTO #Temp
SELECT *
FROM tTest

SELECT *
FROM #Temp

END

It still won't read the schema, but you can do whatever you want to by manually entering the fields you need. I bound it to a gridview, and created 3 bound fields, adding the name of each field, and it showed up in my page just fine -- although I replaced the INSERT statement with "INSERTINTO #zzTemp(iTestID,bTest,cTest)VALUES(newid(),0,'test')"

|||

That's the same proc I posted - and no it won't load in a sqldatasource. I wasn't asking for a work around - I'm fully aware I can manually bind the grid. Kinda defeats the purpose of having a visual designer, don't you think?

What I asked was IF you can use a proc in a SQLDatasource that has it's final select from a temp table. I thought I made this clear by supplying the proc. If you can't, that's yet ONE more thing screwed up with the visual designer.

It's still blowing me away how useless the visual designer is to do any real work.

|||

I changed the sproc, if you notice the definition of one of your fields from [varchar] to varchar(10).

Let me try and make this clearer for you:

What I asked was IF you can use a proc in a SQLDatasource that has it's final select from a temp table. I thought I made this clear by supplying the proc.

What I answered was yes. You can use a proc in a SQLDatasource that has it's final select from a temp table. I thought I made this clear by supplying the proc I used, as well as how I implemented it, and got a gridview to databind to said sproc.

From your last message, you aren't interested in how, you just want to complain. I'm not here to make you feel better. I gave you a solution on how to get done what you needed, and it took me a total of 10 minutes. Take it and use it, or complain some more. I'm done with the thread.

|||

i realize this is considerably after the fact for the originating parties, i will still post my findings for anyone else that might experience this problem. i would define the problem as visual studio barking whenever i tried to drag a stored procedure to the .xsd designer interface which selected from a temp table. im posting here because this is the only thread i could find on the subject. i have rewritten the original posted procedure to show the solution. simply use a TABLE variable instead of a temp table.

CREATE PROCEDURE dbo.repTest_Temp
AS

DECLARE @.Temp TABLE (iTestID uniqueidentifier,
bTest bit,
cTest varchar(10))

INSERT @.Temp
SELECT *
FROM tTest

SELECT *
FROM @.Temp

this worked like a charm for me. hopefully someone else will find this information useful!

|||Another solution is to use Global Temp table instead of local temp table which goes out of scope quickly. ## global temp table and # local temp table. Hope this helps.

no syslogins table in master

i was just reading that logins for mssql are stored in master-->syslogins. i don't have a syslogins table--but there is a sysxlogins.
is this a problem? any ideas why the name has an 'x'?
thanks!In SQL Server 2000 : sysxlogins is system table but syslogins is only a view ...|||thank you thank you

Originally posted by snail
In SQL Server 2000 : sysxlogins is system table but syslogins is only a view ...