Error Rendering PDF Files

If anyone can offer some suggestions for fixing this, I would be really
grateful.
I have a web page that shows reports a user can select. The rendering format
I use is PDF. When the user chooses to view the report for the first time,
they get an Adobe error message saying "There was an error opening this
document. The file cannot be found." The second attempt does not generate
this error. I also added a new report and it gets the Adobe error everytime.
If I let the format come out as native SQL Reporting, the error does not
happen at all. The user's would really like to have Adobe instead.
Can anyone tell me why this is happening and how to fix it? Thank you.I found the solution to this problem. Clearing the temporary internet files
enabled the Adobe error to go away.
"Mike Collins" wrote:
> If anyone can offer some suggestions for fixing this, I would be really
> grateful.
> I have a web page that shows reports a user can select. The rendering format
> I use is PDF. When the user chooses to view the report for the first time,
> they get an Adobe error message saying "There was an error opening this
> document. The file cannot be found." The second attempt does not generate
> this error. I also added a new report and it gets the Adobe error everytime.
> If I let the format come out as native SQL Reporting, the error does not
> happen at all. The user's would really like to have Adobe instead.
> Can anyone tell me why this is happening and how to fix it? Thank you.
>sql

error parsing the query

hi all

I try to run the query below it throw me an exception, error parsing , is there anywat to resolve it?

SELECT TYPE2.list_price as 'TYPE2'
FROM TBL_MST_PRICE TYPE2
WHERE
price_grp_code = (SELECT price_grp_code FROM TBL_MST_PRICE_GRP WHERE prd_code = '' AND cust_code = '')

Sub-selects (nested select statements) are not supported by SQL CE. You will have to rephrase your query to use a join or use 2 select statements.

Error on Stored Procedure

I need to perform a select statement across servers... this statement works in query analyzer but not as a procedure. It tells me I need to enable the ANSI_NULLS and ANSI_WARNINGS options when put into a procedure (which it looks like i did!) I think it has something to do with this GO as the stored procedure isn't recognizing it. I tried even building it as a string and executing. Any ideas as to enable this in a procedure?

CREATE PROCEDURE TESTING AS
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SELECT * FROM MIDSERV.DB000.dbo.tblcustomer
select * from #tableTake out GO statements after SET and re-run the SP.|||Originally posted by Satya
Take out GO statements after SET and re-run the SP.

CREATE PROCEDURE TESTING AS
SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SELECT * FROM MIDSERV.DB000.dbo.tblcustomer

I get the same error telling me to enable ansi_nulls and ansi_warnings...|||Aren't the ANSI_NULL and ANSI_WARNING options to has to be set at connection level?

i belive (thought not so sure) that you cannot use it inside a stored procedure, you just have to call it BEFORE you call your stored procedure.|||that is a GOOD CALL!!!!!!!!!!!

You wouldn't know how about to set those at connection level would you? I tried doing it in a job scheduled in EM and it didn't work too well

First transact-sql statement in job:
set ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

Second transact-sql statement in job
exec procedure1

also tried combining
set ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO|||mmm...do have already tried to remove the "GO" statement?

to set an option at connection level you just have to call it BEFORE anything else you want to execute with that options activated.

now i cannot try, but i'll make a test as soon as i get a SQL Server console under my hand :-)sql

Error on Select Statement when Using IIF

What's wrong with this query? I get an error at ">" sign.
SELECT MyCol1, MyCol2, MyCol3Balance,
IIf(MyCol3Balance > 1, "Past", "No") as MyBalance
from MyTableHi,
There is no IIF in SQL Server. Instead use CASE statement.
Eg:-
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
Thanks
Hari
SQL Server MVP
"Shariq" <Shariq@.discussions.microsoft.com> wrote in message
news:88174876-91BB-48E7-BD1B-3481D6B32FDE@.microsoft.com...
> What's wrong with this query? I get an error at ">" sign.
> SELECT MyCol1, MyCol2, MyCol3Balance,
> IIf(MyCol3Balance > 1, "Past", "No") as MyBalance
> from MyTable
>

Error on query (Select distinct)

I am not an expert in sql but I am trying to learn. I have what for me is a
complex query, but I am getting an error when sorting out by one of the
parameters, here is the error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear
in the select list if SELECT DISTINCT is specified.
/BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResults.asp, line 155
--
Line 155 is the end of the recordset (When it executes)
<%
Dim CasesAdvancedReports
Dim CasesAdvancedReports_numRows
Set CasesAdvancedReports = Server.CreateObject("ADODB.Recordset")
CasesAdvancedReports.ActiveConnection = MM_eimmigration_STRING
CasesAdvancedReports.Source = "SELECT distinct a.id,a.caseid, b.userid,
b.FirstNm, b.LastNm, e.Processcatalog, b.I94DateD,
a.ExpDate,a.maincase,a.caserelationship, f.MaidenNm, a.approvaldate FROM
Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER JOIN
Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN Users
AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
AND G.dateinitiated IS NULL WHERE A.firmid = " +
Replace(CasesAdvancedReports__MMColParam
, "'", "''") + " AND a.archived LIKE
'" + Replace(CasesAdvancedReports__MMColParam
3, "'", "''") + "' ORDER BY "
+ Replace(CasesAdvancedReports__MMColParam
2, "'", "''") + ""
setQueryBuilderSource CasesAdvancedReports, WAQueryBuilder1_whereClause,
false
CasesAdvancedReports.CursorType = 0
CasesAdvancedReports.CursorLocation = 2
CasesAdvancedReports.LockType = 1
CasesAdvancedReports.Open()
CasesAdvancedReports_numRows = 0
%>
This is my query:
SELECT distinct a.id,a.caseid, b.userid, b.FirstNm, b.LastNm,
e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm, a.approvaldate
FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER
JOIN Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN
Users AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
AND G.dateinitiated IS NULL
WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
ORDER BY MMColParam2
Any help is appreciated.
AleksJust as the error says -- you need to include the column you're ordering by
in the select list if you use the DISTINCT keyword. If this ruins your
distinctness, you will have to re-write query so as not to use DISTINCT. If
you need help with that, please post DDL and sample data. See this link for
more info:
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:O5eKN2dEFHA.4052@.TK2MSFTNGP09.phx.gbl...
> I am not an expert in sql but I am trying to learn. I have what for me is
a
> complex query, but I am getting an error when sorting out by one of the
> parameters, here is the error:
> --
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must
appear
> in the select list if SELECT DISTINCT is specified.
> /BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResults.asp, line 155
> --
> Line 155 is the end of the recordset (When it executes)
> <%
> Dim CasesAdvancedReports
> Dim CasesAdvancedReports_numRows
> Set CasesAdvancedReports = Server.CreateObject("ADODB.Recordset")
> CasesAdvancedReports.ActiveConnection = MM_eimmigration_STRING
> CasesAdvancedReports.Source = "SELECT distinct a.id,a.caseid, b.userid,
> b.FirstNm, b.LastNm, e.Processcatalog, b.I94DateD,
> a.ExpDate,a.maincase,a.caserelationship, f.MaidenNm, a.approvaldate FROM
> Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER JOIN
> Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN Users
> AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
> G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
> AND G.dateinitiated IS NULL WHERE A.firmid = " +
> Replace(CasesAdvancedReports__MMColParam
, "'", "''") + " AND a.archived
LIKE
> '" + Replace(CasesAdvancedReports__MMColParam
3, "'", "''") + "' ORDER BY
"
> + Replace(CasesAdvancedReports__MMColParam
2, "'", "''") + ""
> setQueryBuilderSource CasesAdvancedReports, WAQueryBuilder1_whereClause,
> false
> CasesAdvancedReports.CursorType = 0
> CasesAdvancedReports.CursorLocation = 2
> CasesAdvancedReports.LockType = 1
> CasesAdvancedReports.Open()
> CasesAdvancedReports_numRows = 0
> %>
>
>
> --
> This is my query:
> SELECT distinct a.id,a.caseid, b.userid, b.FirstNm, b.LastNm,
> e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
> f.MaidenNm, a.approvaldate
> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER
> JOIN Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN
> Users AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id
=
> G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
> AND G.dateinitiated IS NULL
> WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
> ORDER BY MMColParam2
>
> --
> Any help is appreciated.
> Aleks
>|||Like the error message says if you are going to use an order by clause
with distinct then the order by items MUST appear in the select
portion of the statement.
On Sun, 13 Feb 2005 10:06:07 -0500, "Aleks" <arkark2004@.hotmail.com>
wrote:

>I am not an expert in sql but I am trying to learn. I have what for me is a
>complex query, but I am getting an error when sorting out by one of the
>parameters, here is the error:
>--
>Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear
>in the select list if SELECT DISTINCT is specified.
> /BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResults.asp, line 155
>--
>Line 155 is the end of the recordset (When it executes)
><%
>Dim CasesAdvancedReports
>Dim CasesAdvancedReports_numRows
>Set CasesAdvancedReports = Server.CreateObject("ADODB.Recordset")
>CasesAdvancedReports.ActiveConnection = MM_eimmigration_STRING
>CasesAdvancedReports.Source = "SELECT distinct a.id,a.caseid, b.userid,
>b.FirstNm, b.LastNm, e.Processcatalog, b.I94DateD,
>a.ExpDate,a.maincase,a.caserelationship, f.MaidenNm, a.approvaldate FROM
>Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER JOIN
>Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN Users
>AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
>G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
>AND G.dateinitiated IS NULL WHERE A.firmid = " +
> Replace(CasesAdvancedReports__MMColParam
, "'", "''") + " AND a.archived LIK
E
>'" + Replace(CasesAdvancedReports__MMColParam
3, "'", "''") + "' ORDER BY "
>+ Replace(CasesAdvancedReports__MMColParam
2, "'", "''") + ""
>setQueryBuilderSource CasesAdvancedReports, WAQueryBuilder1_whereClause,
>false
>CasesAdvancedReports.CursorType = 0
>CasesAdvancedReports.CursorLocation = 2
>CasesAdvancedReports.LockType = 1
>CasesAdvancedReports.Open()
>CasesAdvancedReports_numRows = 0
>%>
>
>
>--
>This is my query:
>SELECT distinct a.id,a.caseid, b.userid, b.FirstNm, b.LastNm,
>e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
>f.MaidenNm, a.approvaldate
>FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER
>JOIN Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN
>Users AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id
=
>G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
>AND G.dateinitiated IS NULL
>WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
>ORDER BY MMColParam2
>
>--
>Any help is appreciated.
>Aleks
>
We see the world, not as it is, but as we are.

Error on one of SQL Reporting Services Samples

Hi there,
I am running the reports that comes with the samples one and I'm getting
this error when I run this code;
="SELECT c.firstname, c.lastname, e.title, d.departmentID " &
"From HumanResources.EmployeeDepartmentHistory D " &
"INNER JOIN HumanResources.Employee E " &
"ON D.EmployeeID = E.EmployeeID " &
"INNER JOIN Person.Contact C " &
"ON E.ContactID = C.ContactID " &
Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
Parameters!Department.Value) &
"ORDER BY C.LastName"
The error is saying that all those columns can not be bounded. can anybody
tells me where the error is this piece of code?
Thanks very much in advance.
Midway.The IIF in there is for internal RS expressions and not T-SQL. This appears
to have been modified ('). Which report and dataset is this from?
SalesEmps within the Employee Sales Summary report looks similiar:
SELECT E.EmployeeID, C.FirstName + N' ' + C.LastName AS Employee
FROM HumanResources.Employee E INNER JOIN
Sales.SalesPerson SP ON E.EmployeeID = SP.SalesPersonID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID
ORDER BY C.LastName, C.FirstName
RDA Corp
Business Intelligence Evangelist Leader
www.rdacorp.com
"Midway" wrote:
> Hi there,
> I am running the reports that comes with the samples one and I'm getting
> this error when I run this code;
> ="SELECT c.firstname, c.lastname, e.title, d.departmentID " &
> "From HumanResources.EmployeeDepartmentHistory D " &
> "INNER JOIN HumanResources.Employee E " &
> "ON D.EmployeeID = E.EmployeeID " &
> "INNER JOIN Person.Contact C " &
> "ON E.ContactID = C.ContactID " &
> Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
> Parameters!Department.Value) &
> "ORDER BY C.LastName"
> The error is saying that all those columns can not be bounded. can anybody
> tells me where the error is this piece of code?
> Thanks very much in advance.
> Midway.

Error on Index!

Hi, I am having a real headache with a table in my db, one of these days
we found that some basic select queries were taking like 4 minutes, to
execute, and they shouldn’t take more that one second, so we were getting
hundreds of timeouts between our applications, I was assigned to check
what was happening, and I ran a script that i found on the web, that takes
every table of the schema and then reindex it, well the thing was in that
specific table the scrip got an error telling me this:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL
Server Driver][SQL Server]A column has been specified more than once in
the order by list. Columns in the order by list must be unique.
I check all the indexes in that table and i found 4, but none of them had
an order by column duplicated, I even restore the db somewhere else and
played with those indexes, but it seems like I cant delete one of them the
first one, my index script is the following:
CREATE CLUSTERED INDEX [IX_Accountno] ON [dbo].[tblaction]([AccountNo],
[yearno], [wNo], [Dayno]) ON [PRIMARY]
GO
CREATE INDEX [Gradectrl] ON [dbo].[tblaction]([GradeCtrl]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [Linelink] ON [dbo].[tblaction]([LineLINK]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ticketno] ON [dbo].[tblaction]([TicketNo]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblaction] ON [dbo].[tblaction]([yearno], [wNo],
[Dayno]) ON [PRIMARY]
GO
I tried to remove them all and then recreate them , but when I try to
delete the clustered one, the error pops again and doesn’t let me delete
it, I also check the whole db script to see if i got something else in
another place, but I didn’t found anything... any suggestions?
How can i remove all the indexes, and / or get rid of this slowing error?
Thanks for your time
PD: The following is the reindex script
DECLARE @.MyTable varchar(255)
DECLARE myCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @.MyTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @.MyTable
DBCC DBREINDEX(@.MyTable, '', 90)
FETCH NEXT FROM myCursor INTO @.MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
--Thanks!
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/- Can check the output of sp_helpindex table_name?
- Can you try to recreate the clustered index with the option DROP_EXISTING?
AMB
"Edgardo Sepulveda" wrote:

> Hi, I am having a real headache with a table in my db, one of these days
> we found that some basic select queries were taking like 4 minutes, to
> execute, and they shouldn’t take more that one second, so we were gettin
g
> hundreds of timeouts between our applications, I was assigned to check
> what was happening, and I ran a script that i found on the web, that takes
> every table of the schema and then reindex it, well the thing was in that
> specific table the scrip got an error telling me this:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL
> Server Driver][SQL Server]A column has been specified more than once in
> the order by list. Columns in the order by list must be unique.
> I check all the indexes in that table and i found 4, but none of them had
> an order by column duplicated, I even restore the db somewhere else and
> played with those indexes, but it seems like I cant delete one of them the
> first one, my index script is the following:
> CREATE CLUSTERED INDEX [IX_Accountno] ON [dbo].[tblaction]([AccountNo],
> [yearno], [wNo], [Dayno]) ON [PRIMARY]
> GO
> CREATE INDEX [Gradectrl] ON [dbo].[tblaction]([GradeCtrl]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [Linelink] ON [dbo].[tblaction]([LineLINK]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [ticketno] ON [dbo].[tblaction]([TicketNo]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblaction] ON [dbo].[tblaction]([yearno], [wNo],
> [Dayno]) ON [PRIMARY]
> GO
> I tried to remove them all and then recreate them , but when I try to
> delete the clustered one, the error pops again and doesn’t let me delete
> it, I also check the whole db script to see if i got something else in
> another place, but I didn’t found anything... any suggestions?
> How can i remove all the indexes, and / or get rid of this slowing error?
>
> Thanks for your time
>
>
> PD: The following is the reindex script
>
>
> DECLARE @.MyTable varchar(255)
> DECLARE myCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN myCursor
> FETCH NEXT FROM myCursor INTO @.MyTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Reindexing Table: ' + @.MyTable
> DBCC DBREINDEX(@.MyTable, '', 90)
> FETCH NEXT FROM myCursor INTO @.MyTable
> END
> CLOSE myCursor
> DEALLOCATE myCursor
> EXEC sp_updatestats
>
> --Thanks!
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
>|||The output of the sp_helpindex was:
aaaaatblaction_PK nonclustered, unique, primary key located on PRIMARY ID
Gradectrl nonclustered located on PRIMARY GradeCtrl
hind_1612584833_58A_83A nonclustered, hypothetical, auto create located on
PRIMARY wNo, yearno
hind_1612584833_83A_58A nonclustered, hypothetical, auto create located on
PRIMARY yearno, wNo
hind_c_1612584833_34A clustered, hypothetical, auto create located on
PRIMARY GradeCtrl
hind_c_1612584833_58A clustered, hypothetical, auto create located on
PRIMARY wNo
hind_c_1612584833_83A clustered, hypothetical, auto create located on
PRIMARY yearno
hind_c_1612584833_83A_58A clustered, hypothetical, auto create located on
PRIMARY yearno, wNo
IX_Accountno clustered located on PRIMARY AccountNo, yearno, wNo, Dayno
IX_tblaction nonclustered located on PRIMARY yearno, wNo, Dayno
Linelink nonclustered located on PRIMARY LineLINK
ticketno nonclustered located on PRIMARY TicketNo
I think this is the problem, too many indexes. or too many combinations.
Thanks for the tip Alejandro, any suggestions Based on this info?
Thanks for your Time.
Edgardo

Error Msg 7391 in distributed transaction

I have a procedure which is trying to insert data into a table on Server A.
This insert is done based ona return value from a select statement from
Server B . A linked server has been set up for Server B on server A. The
error when populating table by inserting data is
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Is there any other way to get thsi work. TIA
Message posted via http://www.webservertalk.comYou want to start with this kb for your troubleshooting.
http://support.microsoft.com/kb/306212
-oj
"ishaan99 via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:e4a371938b9f454d851da07f5c672039@.SQ
webservertalk.com...
>I have a procedure which is trying to insert data into a table on Server A.
> This insert is done based ona return value from a select statement from
> Server B . A linked server has been set up for Server B on server A. The
> error when populating table by inserting data is
> Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Is there any other way to get thsi work. TIA
> --
> Message posted via http://www.webservertalk.com

error Msg 156 and msg 170

Hi!
I am trying to loop through all the databases using cursor.
Here is my stored proc
if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
('master..temp_Assignments_file_count '))
DROP TABLE temp_Assignments_file_count
declare @.sql nvarchar(4000)
declare @.db varchar(300)
set @.db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
ORDER BY name
CREATE TABLE temp_Assignments_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Title] nvarchar(100),
[File Count] int,
[File Size (MB)] decimal(10,4),
)
open cDB
FETCH NEXT FROM cDB INTO @.db
while (@.@.fetch_status = 0)
begin
SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
Name]'', ' +
'''' + @.db + '''' + '
as ''[Database Name]'', ' +
'max(b.title) as ''[Title]'',' +
'count(*) as ''[File Count]'',' +
'round(cast(sum(length) as decimal)/1048576/1024,10) as
''[File Size]''' +
'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
@.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
'where a.id in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments' +
'where lastprocesskey in (select id from ' + @.db +
'.dbo.filo_processlog where task = ''Create Assignments'' and
starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
'and a.id not in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments where lastprocesskey in' +
'(select id from ' + @.db + '.dbo.filo_processlog where task = ''Create Assignments'' and starttime < ''10/01/2007'')))'
INSERT temp_Assignments_file_count
EXEC sp_executesql @.sql
fetch cDB into @.db
end
close cDB
deallocate cDB
select * from temp_Assignments_file_count
I am getting the following error messages:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
(0 row(s) affected)
any help would be appreciated.
TGDo:
PRINT @.sql
before you try to execute what you have in the variable and you will find a lot of problems with the
query that you built. Based on that you can debug your code so it produces a valid query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<jtammyg@.gmail.com> wrote in message news:1192645043.161544.136800@.v29g2000prd.googlegroups.com...
> Hi!
> I am trying to loop through all the databases using cursor.
> Here is my stored proc
> if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
> ('master..temp_Assignments_file_count '))
> DROP TABLE temp_Assignments_file_count
>
> declare @.sql nvarchar(4000)
> declare @.db varchar(300)
>
> set @.db = 'master'
> declare cDB cursor for
> SELECT name from master..sysdatabases sdb
> WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
> ORDER BY name
>
> CREATE TABLE temp_Assignments_file_count([Server Name]
> nvarchar(40),
> [Database Name]
> nvarchar(100),
> [Title] nvarchar(100),
> [File Count] int,
> [File Size (MB)] decimal(10,4),
> )
>
> open cDB
> FETCH NEXT FROM cDB INTO @.db
> while (@.@.fetch_status = 0)
> begin
> SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
> Name]'', ' +
> '''' + @.db + '''' + '
> as ''[Database Name]'', ' +
> 'max(b.title) as ''[Title]'',' +
> 'count(*) as ''[File Count]'',' +
> 'round(cast(sum(length) as decimal)/1048576/1024,10) as
> ''[File Size]''' +
> 'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
> @.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
> 'where a.id in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments' +
> 'where lastprocesskey in (select id from ' + @.db +
> '.dbo.filo_processlog where task = ''Create Assignments'' and
> starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
> 'and a.id not in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments where lastprocesskey in' +
> '(select id from ' + @.db + '.dbo.filo_processlog where task => ''Create Assignments'' and starttime < ''10/01/2007'')))'
>
> INSERT temp_Assignments_file_count
> EXEC sp_executesql @.sql
>
> fetch cDB into @.db
> end
> close cDB
> deallocate cDB
>
> select * from temp_Assignments_file_count
>
>
> I am getting the following error messages:
>
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> (0 row(s) affected)
>
> any help would be appreciated.
> TG
>

error message: The text, ntext, or image data type cannot be selected as DISTINC

I want to use For XML Explicit to get well-formatted XML from database. Everytime if I tried to select something in DB whose datatype is text, error message came out: The text, ntext, or image data type cannot be selected as DISTINCT.

I tried to change data type from text to varchar and set size to 8000, but result in query analyzer is weird--xml is not complete(cut in the middle).

I wonder if anyone here could help me out with this problem. Your reply will be highly appreciated!

ThanksQuery Analyzer will not return complete fields beyond a certain length (even in "results in text" mode). The only way I know of to overcome this limitation is to:

Declare @.Foo varchar(8000)

SELECT @.Foo = TextColumn FROM MyTable

PRINT @.Foo

You can also use VB, VBScript or VBA and use the Debug.Print functionality (or write the results to a file).

Regards,

Hugh Scott

Originally posted by heidil90
I want to use For XML Explicit to get well-formatted XML from database. Everytime if I tried to select something in DB whose datatype is text, error message came out: The text, ntext, or image data type cannot be selected as DISTINCT.

I tried to change data type from text to varchar and set size to 8000, but result in query analyzer is weird--xml is not complete(cut in the middle).

I wonder if anyone here could help me out with this problem. Your reply will be highly appreciated!

Thanks|||Thanks so much for helping me! Yesterday I figured out how to show the complete XML in Microsoft Internet Explore by running the utility configure SQL server XML support in IIS.

I am still wondering why I use CDATA directive in my FOR XML EXPLICIT for a field that has a text datatype can work in the first level of hierarchy, but not in the second or third level of hierarchy. Do you have any idea? Thanks in advance!

OK version:
select 1 as tag,
null as parent,
Employees.EmployeeID as [Employee!1!EmployeeID],
Employees.Notes as [Employee!1!!CDATA]
from Employees

for xml explicit
**************
Query batch completed.

=================================
==================================
wrong version:

select 1 as tag,
null as parent,
Employees.EmployeeID as [Employee!1!EmployeeID],
null as [Employee!2!!CDATA]
from Employees

union

select 2,1,
null,
Employees.Notes

from Employees
for xml explicit
*********************
Server: Msg 8163, Level 16, State 4, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.

error message on backup

When trying to do a backup, I come up with this message.
Backup, CHECKALLOC, bulk copy, SELECT INTO, and file
manipulation (such as CREATE FILE) operations on a
database must be serialized. Reissue the statement after
the current backup, CHECKALLOC, or file manipulation
operation is completed. [SQLSTATE 42000] (Error 3023)
Backup or restore operation terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.
What could be causing this failed job?Read the error message closely. You are trying to execute two operations at
the same time, and those operations aren't allowed to execute at the same
time. Check your job schedules etc.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"SQL'''?" <anonymous@.discussions.microsoft.com> wrote in
message news:368b01c3aa26$9a6deb80$a601280a@.phx.gbl...
> When trying to do a backup, I come up with this message.
> Backup, CHECKALLOC, bulk copy, SELECT INTO, and file
> manipulation (such as CREATE FILE) operations on a
> database must be serialized. Reissue the statement after
> the current backup, CHECKALLOC, or file manipulation
> operation is completed. [SQLSTATE 42000] (Error 3023)
> Backup or restore operation terminating abnormally.
> [SQLSTATE 42000] (Error 3013). The step failed.
> What could be causing this failed job?|||Only one job is running during the schedule. I even
killed every job right now and tried to run the backup,
and it came up with the same message.
>--Original Message--
>Read the error message closely. You are trying to execute
two operations at
>the same time, and those operations aren't allowed to
execute at the same
>time. Check your job schedules etc.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"SQL'''?"
<anonymous@.discussions.microsoft.com> wrote in
>message news:368b01c3aa26$9a6deb80$a601280a@.phx.gbl...
>> When trying to do a backup, I come up with this message.
>> Backup, CHECKALLOC, bulk copy, SELECT INTO, and file
>> manipulation (such as CREATE FILE) operations on a
>> database must be serialized. Reissue the statement after
>> the current backup, CHECKALLOC, or file manipulation
>> operation is completed. [SQLSTATE 42000] (Error 3023)
>> Backup or restore operation terminating abnormally.
>> [SQLSTATE 42000] (Error 3013). The step failed.
>> What could be causing this failed job?
>
>.
>|||Could be an autogrow or autoshrink. You might have to use Profiler and/or
Performance Monitor to determine where the conflict is. If you can hunt it
down, I suggest you open a case with MS (I assume you have searched KB). One
cannot rule out the possibility of some bug in SQL Server.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:02e701c3aa2c$eae205f0$a401280a@.phx.gbl...
> Only one job is running during the schedule. I even
> killed every job right now and tried to run the backup,
> and it came up with the same message.
>
> >--Original Message--
> >Read the error message closely. You are trying to execute
> two operations at
> >the same time, and those operations aren't allowed to
> execute at the same
> >time. Check your job schedules etc.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"SQL'''?"
> <anonymous@.discussions.microsoft.com> wrote in
> >message news:368b01c3aa26$9a6deb80$a601280a@.phx.gbl...
> >> When trying to do a backup, I come up with this message.
> >>
> >> Backup, CHECKALLOC, bulk copy, SELECT INTO, and file
> >> manipulation (such as CREATE FILE) operations on a
> >> database must be serialized. Reissue the statement after
> >> the current backup, CHECKALLOC, or file manipulation
> >> operation is completed. [SQLSTATE 42000] (Error 3023)
> >> Backup or restore operation terminating abnormally.
> >> [SQLSTATE 42000] (Error 3013). The step failed.
> >>
> >> What could be causing this failed job?
> >
> >
> >.
> >

Error Message is not Displaying in this Query? why?

Hello all

please see this query

select * from Table1 where Table1ID in
(select Table1ID from Table3)

Step1 )select Table1ID from Table3

output->Giving error as Table1ID is not valid column in Table3 .

Step2)select * from Table1 where Table1ID in
(select Table1ID from Table3)

Output-> Giving all records of Table1 as i am expecting error from this query .

Please check with your demo database and reply.

Thanking you.

Ramana.

There are no errors. By TSQL syntaxis, all Table1 columns are visible in subquery.

If you'd alias all your tables, then you will be never confused by such behavior. Look at:

select

*

from Table1 t1

where t1.Table1ID in (

select

t3.Table1ID

from Table3 t3

)

|||

Thanks for your reply .i did that one and i got error .but what was the problem in first query it has to give error .so please tell me what is problem with that one .

Thanks

|||As I said, becuse of all Table1 columns are visible within subquery. As you didn't specify alias for "Table1ID" subquery column, the column have been sought in both Table1 and Table3.|||

Thanks ,

But i want to know that is it necessary to add alias whenever i am using 'in ' in the query .

|||

Sorry, but SQL Server doesn't see an error in your query. If you had the same column in both tables and didn't properly alias them then you'd get an error, because it would need to know which table you were referencing, but since each column referenced in the select only exists in one place, the query parser is happy.

It's really a good idea to ALWAYS alias your columns, and then there's never a doubt regarding the source of the data.

|||

The answer here is that subqueries are technically correlated, whether you add a where clause to join them or not. So in this query:

say that

select table1Id from table1

returns:

table1Id
1
2

select *
from Table1
where Table1ID in (select Table1ID from Table3)

For the row where table1Id = 1, The results of (select Table1ID from Table3)

1 table3.column1 table3.column2
1 table3.column1 table3.column2

For the row where table1Id = 2, The results of (select Table1ID from Table3)

2 table3.column1 table3.column2
2 table3.column1 table3.column2

This allows you to add a where clause of WHERE table3.table1Id = table1.table1Id.

So the advice given on using aliases with subqueries is definitely correct. Or, just use

select *
from Table1
where exists (select *
from table3
where table3.table1Id = table1.table1Id)

where you would be less likely to make that mistake (which I have made a few times myself and asked the same question too :)

Error Message 8152

Hi
I wonder if anyone can help, I am getting the following error messages when
I try to do a number of things.
1. Select Job > Script job as > create to > any option file / query editor
2. View Job History.
The error I get gives you the result script failed for job 'RunMe'
(Microsoft.sqlserver.smo), enumerate target server failed, and so on then
string or binary data would be truncated ( MS SQL Server Error 8152)
I also get the same error if I try and run a job.
The interesting thing is that if I failover the server the view job history
works on the 1st server in the cluster but the select job and script still
does not work.
We have a 2 server SQL 2005 Cluster with SP1, in active/passive mode.
Also on the cluster we have two instances running and these features work on
one instance all the time and not on the other
Any ideas where to look or what to look for would be ideal
Thanks
Al
Hi
Have you checked that the jobsteps reference the correct instance on the
virtual server?
John
"Al" wrote:

> Hi
> I wonder if anyone can help, I am getting the following error messages when
> I try to do a number of things.
> 1. Select Job > Script job as > create to > any option file / query editor
> 2. View Job History.
> The error I get gives you the result script failed for job 'RunMe'
> (Microsoft.sqlserver.smo), enumerate target server failed, and so on then
> string or binary data would be truncated ( MS SQL Server Error 8152)
> I also get the same error if I try and run a job.
> The interesting thing is that if I failover the server the view job history
> works on the 1st server in the cluster but the select job and script still
> does not work.
> We have a 2 server SQL 2005 Cluster with SP1, in active/passive mode.
> Also on the cluster we have two instances running and these features work on
> one instance all the time and not on the other
> Any ideas where to look or what to look for would be ideal
> Thanks
> Al
|||Hi John
The job is specific to a database on that specific instance, as currently
the only db in the other instance is aspstate.
As I say it is a weird one.
I have two servers
server a - 172.21.24.30
server b - 172.21.24.31
sql cluster - 172.21.24.34
Instance One - both have names
Instance Two
Running in an active / passive mode if you are logged on to server A you can
look at the history for the job no problem, but if you look on to server B
you cannot look at the job history and get the 8152 error or 0x80041010.
At this point I must say I am refering to Server A and B as the server that
the cluster is connecting to at that point in time
Also if you try to create a script from the job it reports the same error
but this time on both server A and B.
To add another thing into the mix I downloaded and installed SQL SP2 on my
local PC which was running SQL2005, and had the same problem reading the job
history after the install of SP2 it could read the job history logs but could
not create the script of the job from SQL Agent.
If I try to script the aspstate job from Instance 2 on Server A it works but
do it on Server B and it fails, but do it for Instance 1 and it works.
I have included the error message I get when attempting to create the script.
From SQL
TITLE: Microsoft SQL Server Management Studio
Script failed for Job 'Get LES Payments Daily import from GENIESERVER'.
(Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 3027.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Script+Job&LinkId=20476[/url]
ADDITIONAL INFORMATION:
Enumerate target servers failed for Job 'Get LES Payments Daily import from
GENIESERVER'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 3027.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Enumerate+target+servers+Job&LinkId=20476[/url]
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476[ /url]
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476[/url]
BUTTONS:
OK
From Eventviewer
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the problem
is corrected.
Any advise would be great.
Al
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you checked that the jobsteps reference the correct instance on the
> virtual server?
> John
> "Al" wrote:
|||Hi
Unfortunately I can't suggest anything more other than contacting PSS. It
does sounds like there is something more than just this job which is not
correct, therefore rebuilding the cluster may be one option. It doesn't sound
like you are using version control to maintain your scripts, if you do have a
good script then you could always drop the job and re-create it to see if the
situation changes for the new job.
John
"Al" wrote:
[vbcol=seagreen]
> Hi John
> The job is specific to a database on that specific instance, as currently
> the only db in the other instance is aspstate.
> As I say it is a weird one.
> I have two servers
> server a - 172.21.24.30
> server b - 172.21.24.31
> sql cluster - 172.21.24.34
> Instance One - both have names
> Instance Two
> Running in an active / passive mode if you are logged on to server A you can
> look at the history for the job no problem, but if you look on to server B
> you cannot look at the job history and get the 8152 error or 0x80041010.
> At this point I must say I am refering to Server A and B as the server that
> the cluster is connecting to at that point in time
> Also if you try to create a script from the job it reports the same error
> but this time on both server A and B.
> To add another thing into the mix I downloaded and installed SQL SP2 on my
> local PC which was running SQL2005, and had the same problem reading the job
> history after the install of SP2 it could read the job history logs but could
> not create the script of the job from SQL Agent.
> If I try to script the aspstate job from Instance 2 on Server A it works but
> do it on Server B and it fails, but do it for Instance 1 and it works.
> I have included the error message I get when attempting to create the script.
> From SQL
> --
> TITLE: Microsoft SQL Server Management Studio
> --
> Script failed for Job 'Get LES Payments Daily import from GENIESERVER'.
> (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 3027.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Script+Job&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> Enumerate target servers failed for Job 'Get LES Payments Daily import from
> GENIESERVER'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 3027.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Enumerate+target+servers+Job&LinkId=20476[/url]
> --
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476[ /url]
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476[/url]
> --
> BUTTONS:
> OK
> --
>
> --
> From Eventviewer
> --
> Event filter with query "select * from __InstanceModificationEvent within 10
> where TargetInstance isa 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
> 0x80041010. Events may not be delivered through this filter until the problem
> is corrected.
> Any advise would be great.
> Al
> "John Bell" wrote:
|||John
We do have a microsoft contract so I will contact them, this is the first
script we have put on the server so is the only version
but thanks for trying to help
AL
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Unfortunately I can't suggest anything more other than contacting PSS. It
> does sounds like there is something more than just this job which is not
> correct, therefore rebuilding the cluster may be one option. It doesn't sound
> like you are using version control to maintain your scripts, if you do have a
> good script then you could always drop the job and re-create it to see if the
> situation changes for the new job.
> John
> "Al" wrote:
|||Hi
My assumption was that this script was upgraded from SQL 2000!
If you do have a file with this scripted you can create a new job (with a
different name) and disable the current one and then see if you have the same
problems.
John
"Al" wrote:
[vbcol=seagreen]
> John
> We do have a microsoft contract so I will contact them, this is the first
> script we have put on the server so is the only version
> but thanks for trying to help
> AL
> "John Bell" wrote:
|||John
Thanks. I tried that no difference, I have even created a job from Scratch
then try to use the Create to - so I can create a script and it does not
work. An example of the job is to give users permissions to the aspstate
tables in tempdb. Altthough I can use the Drop to command and it will create
a script. It sounds like there is something not right but I am at a loss to
what that is. It looks like some things work and others dont I may have to
remove the instant and reinstall but I am not sure how easy that will be as
it will be up to the team responsible for the application
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> My assumption was that this script was upgraded from SQL 2000!
> If you do have a file with this scripted you can create a new job (with a
> different name) and disable the current one and then see if you have the same
> problems.
> John
> "Al" wrote:

Error Message 8152

Hi
I wonder if anyone can help, I am getting the following error messages when
I try to do a number of things.
1. Select Job > Script job as > create to > any option file / query editor
2. View Job History.
The error I get gives you the result script failed for job 'RunMe'
(Microsoft.sqlserver.smo), enumerate target server failed, and so on then
string or binary data would be truncated ( MS SQL Server Error 8152)
I also get the same error if I try and run a job.
The interesting thing is that if I failover the server the view job history
works on the 1st server in the cluster but the select job and script still
does not work.
We have a 2 server SQL 2005 Cluster with SP1, in active/passive mode.
Also on the cluster we have two instances running and these features work on
one instance all the time and not on the other
Any ideas where to look or what to look for would be ideal
Thanks
AlHi
Have you checked that the jobsteps reference the correct instance on the
virtual server?
John
"Al" wrote:

> Hi
> I wonder if anyone can help, I am getting the following error messages whe
n
> I try to do a number of things.
> 1. Select Job > Script job as > create to > any option file / query editor
> 2. View Job History.
> The error I get gives you the result script failed for job 'RunMe'
> (Microsoft.sqlserver.smo), enumerate target server failed, and so on then
> string or binary data would be truncated ( MS SQL Server Error 8152)
> I also get the same error if I try and run a job.
> The interesting thing is that if I failover the server the view job histor
y
> works on the 1st server in the cluster but the select job and script still
> does not work.
> We have a 2 server SQL 2005 Cluster with SP1, in active/passive mode.
> Also on the cluster we have two instances running and these features work
on
> one instance all the time and not on the other
> Any ideas where to look or what to look for would be ideal
> Thanks
> Al|||Hi John
The job is specific to a database on that specific instance, as currently
the only db in the other instance is aspstate.
As I say it is a weird one.
I have two servers
server a - 172.21.24.30
server b - 172.21.24.31
sql cluster - 172.21.24.34
Instance One - both have names
Instance Two
Running in an active / passive mode if you are logged on to server A you can
look at the history for the job no problem, but if you look on to server B
you cannot look at the job history and get the 8152 error or 0x80041010.
At this point I must say I am refering to Server A and B as the server that
the cluster is connecting to at that point in time
Also if you try to create a script from the job it reports the same error
but this time on both server A and B.
To add another thing into the mix I downloaded and installed SQL SP2 on my
local PC which was running SQL2005, and had the same problem reading the job
history after the install of SP2 it could read the job history logs but coul
d
not create the script of the job from SQL Agent.
If I try to script the aspstate job from Instance 2 on Server A it works but
do it on Server B and it fails, but do it for Instance 1 and it works.
I have included the error message I get when attempting to create the script
.
From SQL
--
TITLE: Microsoft SQL Server Management Studio
--
Script failed for Job 'Get LES Payments Daily import from GENIESERVER'.
(Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...ob&LinkId=20476
ADDITIONAL INFORMATION:
Enumerate target servers failed for Job 'Get LES Payments Daily import from
GENIESERVER'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...ob&LinkId=20476
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
String or binary data would be truncated. (Microsoft SQL Server, Error: 8152
)
For help, click:
http://go.microsoft.com/fwlink?Prod...52&LinkId=20476
BUTTONS:
OK
--
From Eventviewer
--
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance ISA 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the proble
m
is corrected.
Any advise would be great.
Al
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you checked that the jobsteps reference the correct instance on the
> virtual server?
> John
> "Al" wrote:
>|||Hi
Unfortunately I can't suggest anything more other than contacting PSS. It
does sounds like there is something more than just this job which is not
correct, therefore rebuilding the cluster may be one option. It doesn't soun
d
like you are using version control to maintain your scripts, if you do have
a
good script then you could always drop the job and re-create it to see if th
e
situation changes for the new job.
John
"Al" wrote:
[vbcol=seagreen]
> Hi John
> The job is specific to a database on that specific instance, as currently
> the only db in the other instance is aspstate.
> As I say it is a weird one.
> I have two servers
> server a - 172.21.24.30
> server b - 172.21.24.31
> sql cluster - 172.21.24.34
> Instance One - both have names
> Instance Two
> Running in an active / passive mode if you are logged on to server A you c
an
> look at the history for the job no problem, but if you look on to server B
> you cannot look at the job history and get the 8152 error or 0x80041010.
> At this point I must say I am refering to Server A and B as the server tha
t
> the cluster is connecting to at that point in time
> Also if you try to create a script from the job it reports the same error
> but this time on both server A and B.
> To add another thing into the mix I downloaded and installed SQL SP2 on my
> local PC which was running SQL2005, and had the same problem reading the j
ob
> history after the install of SP2 it could read the job history logs but co
uld
> not create the script of the job from SQL Agent.
> If I try to script the aspstate job from Instance 2 on Server A it works b
ut
> do it on Server B and it fails, but do it for Instance 1 and it works.
> I have included the error message I get when attempting to create the scri
pt.
> From SQL
> --
> TITLE: Microsoft SQL Server Management Studio
> --
> Script failed for Job 'Get LES Payments Daily import from GENIESERVER'.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ob&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> Enumerate target servers failed for Job 'Get LES Payments Daily import fro
m
> GENIESERVER'. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ob&LinkId=20476
> --
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> String or binary data would be truncated. (Microsoft SQL Server, Error: 81
52)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...52&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
> --
> From Eventviewer
> --
> Event filter with query "select * from __InstanceModificationEvent within
10
> where TargetInstance ISA 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of err
or
> 0x80041010. Events may not be delivered through this filter until the prob
lem
> is corrected.
> Any advise would be great.
> Al
> "John Bell" wrote:
>|||John
We do have a microsoft contract so I will contact them, this is the first
script we have put on the server so is the only version
but thanks for trying to help
AL
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Unfortunately I can't suggest anything more other than contacting PSS. It
> does sounds like there is something more than just this job which is not
> correct, therefore rebuilding the cluster may be one option. It doesn't so
und
> like you are using version control to maintain your scripts, if you do hav
e a
> good script then you could always drop the job and re-create it to see if
the
> situation changes for the new job.
> John
> "Al" wrote:
>|||Hi
My assumption was that this script was upgraded from SQL 2000!
If you do have a file with this scripted you can create a new job (with a
different name) and disable the current one and then see if you have the sam
e
problems.
John
"Al" wrote:
[vbcol=seagreen]
> John
> We do have a microsoft contract so I will contact them, this is the first
> script we have put on the server so is the only version
> but thanks for trying to help
> AL
> "John Bell" wrote:
>|||John
Thanks. I tried that no difference, I have even created a job from Scratch
then try to use the Create to - so I can create a script and it does not
work. An example of the job is to give users permissions to the aspstate
tables in tempdb. Altthough I can use the Drop to command and it will create
a script. It sounds like there is something not right but I am at a loss to
what that is. It looks like some things work and others dont I may have to
remove the instant and reinstall but I am not sure how easy that will be as
it will be up to the team responsible for the application
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> My assumption was that this script was upgraded from SQL 2000!
> If you do have a file with this scripted you can create a new job (with a
> different name) and disable the current one and then see if you have the s
ame
> problems.
> John
> "Al" wrote:
>

Error Message 443

Hi,

I'm trying to create the following function:

CREATE FUNCTION dbo.get_id(@.name NVARCHAR(50)) RETURNS INT AS
BEGIN
DECLARE @.id INT
SET @.id = (SELECT id FROM dbo.language WHERE name = @.name)
IF @.@.ROWCOUNT = 0
RAISERROR('Specified item does not exist', 16, 1)

RETURN @.id
END
GO

And I get the following error message:
Invalid use of side-effecting or time-dependent operator in 'RAISERROR' within a function.

I can't find any references to the use of RAISERROR within functions or stored procedures, so I have no idea why this is not working.
What I'm trying to do is check for the existence of the row inside the function and not outside, on all the places I need to call this function. I need to have an exception in my code if this happens, that's why I thought RAISERROR is what I needed...

Any help would be appreciated!

Thanks,
Florin

You cannot use RAISERROR from within TSQL UDFs. You need to put this logic in a stored procedure or use a SQLCLR UDF. Please take a look at http://msdn2.microsoft.com/en-us/library/ms191320(en-US,SQL.90).aspx topic also.|||

Thanks, I eventually figured out that I can't use RAISERROR within a function, so I changed it to a stored procedure. My problem now is that RAISERROR doesn't stop the execution of my stored procedure, but continues with the next statement. Is there a way to achieve the same kind of functionality as with primary key or constraint violations? I'm testing some conditions inside my stored procedure and I want to stop the execution under certain circumstances and get an exception in my .NET calling code. Is that at all possible with RAISERROR?
I noticed that when an error is produced by the SQL Server engine, besides the red error message (which I can also create using RAISERROR) there is another message saying "The statement has been terminated". How can I achieve the same? :-)

Thanks, Florin

|||While you can't raise an error in a udf. . .

this function:

CREATE FUNCTION dbo.get_id(@.name NVARCHAR(50)) RETURNS INT AS
BEGIN
DECLARE @.id INT
SELECT @.id = id FROM dbo.language WHERE name = @.name
RETURN @.id
END
GO

will return a null if name is not found.

Now, I contend, trying to select an id for a name that doesn't exist shouldn't be an error. . . It should return a null. Trying to use the null in a place that requires non-null will raise the error. . . and that might make perfect logical sense.

Just a thought.

On the other hand, if you want to use a procedure, look up @.@.ERROR in the help file.|||

Well, in the application logic, the foreign key can be null, which means that record has no references, but in this particular case I need to throw an error because the calling client has given a foreign key value that does not exist. Null is a valid case, non-existing referenced record is not. That's why I need the error. I want to do as much as I can on the database in one stored procedure call, to avoid multiple database calls/roundtrips...

Florin

|||

huh? I think you misunderstood. . .

run this script. . .

-- BEGIN SCRIPT =========================

create table Song(ID int not null identity(1,1) primary key, name varchar(255))

go

create table Band(ID int not null identity(1,1) primary key, name varchar(255))

go

create table Record(BandID int not null references Band(ID), SongID int not null references Song(ID) )

go

CREATE FUNCTION dbo.getSongID(@.name NVARCHAR(50)) RETURNS INT AS

BEGIN

DECLARE @.id INT

SELECT @.id = id FROM dbo.Song WHERE name = @.name

RETURN @.id

END

go

CREATE FUNCTION dbo.getBandID(@.name NVARCHAR(50)) RETURNS INT AS

BEGIN

DECLARE @.id INT

SELECT @.id = id FROM dbo.Band WHERE name = @.name

RETURN @.id

END

go

Insert into Song(Name) values('Love Will Tear Us Apart')

Insert into Band(Name) values('Joy Division')

-- This works

Insert into Record values(dbo.getBandID('Joy Division'), dbo.getSongID('Love Will Tear Us Apart'))

-- This doesn't and the error is on the database

Insert into Record values(dbo.getBandID('Captain and Tennille'), dbo.getSongID('Love Will Tear Us Apart'))

-- This doesn't and the error is on the database

Insert into Record values(dbo.getBandID('Joy Division'), dbo.getSongID('Love Will Keep Us Together'))

-- END SCRIPT =========================

Error message

Hello,
If a execute a query
SELECT '200703' AS Expr1, Zahlungen.KundeID, Zahlungen.Periode,
Zahlungen.Datum, Zahlungen.Betrag, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS
Expr5,
0 AS Expr6, 'U' AS Expr7, Kunde.H_Kanton , Kunde.H_Plz
FROM Zahlungen INNER JOIN
Kunde ON Zahlungen.KundeID = Kunde.ID
WHERE (Zahlungen.Betrag > 0) AND (Zahlungen.Periode = '200703' OR
Zahlungen.Periode = '200713' OR
Zahlungen.Periode = '200613') AND (Zahlungen.KundeID >
0)
I get an error message:
Server: Nachr.-Nr. 3624, Schweregrad 20, Status 1, Zeile 1
Location: p:\sql\ntdbms\storeng\drs\include\record.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 56
Process ID: 296
Die Verbindung wurde unterbrochen.
If I use "TOP 100000" it works fine. If I use TOP 1000000 (million) I get
the error message.
It seams to bee a new problem, because on Tuesday it worked.
Many thanks for your support and best Regards.
StefanPossibly a data corruption. Check out DBCC CHECKDB.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stefan Braun" <stefan@.braun.name> wrote in message news:58osl6F2hrlctU1@.mid.individual.net...
> Hello,
> If a execute a query
> SELECT '200703' AS Expr1, Zahlungen.KundeID, Zahlungen.Periode,
> Zahlungen.Datum, Zahlungen.Betrag, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS
> Expr5,
> 0 AS Expr6, 'U' AS Expr7, Kunde.H_Kanton , Kunde.H_Plz
> FROM Zahlungen INNER JOIN
> Kunde ON Zahlungen.KundeID = Kunde.ID
> WHERE (Zahlungen.Betrag > 0) AND (Zahlungen.Periode = '200703' OR
> Zahlungen.Periode = '200713' OR
> Zahlungen.Periode = '200613') AND (Zahlungen.KundeID >
> 0)
> I get an error message:
> Server: Nachr.-Nr. 3624, Schweregrad 20, Status 1, Zeile 1
> Location: p:\sql\ntdbms\storeng\drs\include\record.inl:1447
> Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
> SPID: 56
> Process ID: 296
> Die Verbindung wurde unterbrochen.
> If I use "TOP 100000" it works fine. If I use TOP 1000000 (million) I get
> the error message.
> It seams to bee a new problem, because on Tuesday it worked.
> Many thanks for your support and best Regards.
> Stefan
>
>
>