SQL Server 2008 :: How To Make Sproc Return Errors For Underlying Table Errors
Jul 1, 2015
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
View 1 Replies
ADVERTISEMENT
Jun 14, 2015
I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup
The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?
View 3 Replies
View Related
Jul 31, 2006
I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.
If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?
Thanks,
Lee
View 5 Replies
View Related
Jun 6, 2008
Hi all, if have problem to display error message in vb.net that comes from a stored procedure. IF ...... then msgbox "ERROR at Update" returnvalue = SUCCES ELSE Msgbox "Successfull Updated" returnvalue = ERROREND IFHow can I return values from Stored Procedure to VB.NET and then give to User a Message that the Update was successful or not.Thanks to all
View 2 Replies
View Related
Apr 20, 2006
Starwin writes "when i execute DBCC CHECKDB, DBCC CHECKCATALOG
I reveived the following error.
how to solve it?
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -2093955965, index ID 711, page ID (3:2530). The PageId in the page header = (34443:343146507).
. . . .
. . . .
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1635188736)' (object ID -1635188736).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1600811521)' (object ID -1600811521).
. . . .
. . . .
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -8748568, index ID 50307, page ID (3:2497). The PageId in the page header = (26707:762626875).
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -7615284, index ID 35836, page ID (3:2534). The PageId in the page heade"
View 1 Replies
View Related
Apr 1, 2015
I have a basic load proc (loading a staging area.) that truncates a target table just before inserting fresh data in said table, all wrapped in a distributed transaction. What happens (only with certain tables and very inconsistently.) is the truncate will fail (no error) and duplicate data ends up in the target table since no wipe actually occurs. This proc only started having this issue when we went from SQL 2008 to SQL 2014.
View 9 Replies
View Related
Jan 26, 2015
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
View 6 Replies
View Related
Apr 25, 2015
I'm creating a sql stored procedure inside this proc it returns some information about the user, i.e location, logged in, last logged in, etc I need to join this on to the photos table and return the photo which has been set as the profile picture, if it hasn't been set then return the first top 1 if that makes sense?
The user has the option to upload photos so there might be no photos for a particular user, which I believe I can fix by using a left join
My photos table is constructed as follows:
CREATE TABLE [User].[User_Photos](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[PhotoId] [varchar](100) NOT NULL,
[IsProfilePic] [bit] NULL,
[Code] ....
Currently as it stands the proc runs but it doesn't return a particular user because they have uploaded a photo so I need to some how tweak the above to return null if a photo isn't present which is where I'm stuck.
View 3 Replies
View Related
Sep 3, 2015
I have several databases to deal with, all with + 250 tables. The databases are not identical and do not conform to a specific naming convention for table names. Most but not all tables have a column called "LastUpdated" containing a date/time (obviously). I'd like to be able to find all rows within a whole database (table by table) where the date/time is greater than a specified date/time.
I'm looking for a reliable query that will return all the rows in each of the tables but without me having to write hundreds of individual scripts "SELECT * FROM [dbo.xyz] WHERE LastUpdated > '2015-01-01 09:00:00:000'", or have to look through each table first to determine which of them has the LastUpdated field.
View 9 Replies
View Related
Feb 3, 2004
Hi - please excuse my newness to this. I have a database with several tables and one of them is causing my application to lag really bad. I figure there is either not enough space or something is just wrong in general and i don't know what. Does SQL Server have a shortcut or easy way to test a table in the database?
Thanks =)
View 6 Replies
View Related
Jan 10, 2007
Hello,
I am trying to transfer a database from one server to another using the Import Export wizard in SSIS and I am consistantly getting this error on 2 different tables so far.
- Execute the transfer with the TransferProvider. (Error)
Messages
* ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of
"output column "ErrorCode" (79)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
This error message is beyond cryptic and when I click on the link it sends me to a web page the just tells me that there is no information available for my current issue. I am transfering the tables to an empty database so I do not understand why I am receiving this error. I have to say that I am not impressed with SSIS at all. I know alot of developers think it's the best thing since sliced bread, however either I am doing something wrong or Microsoft needs to come out with a service pack that fixes these bugs...
Any help would be appreciated...
Thanks,
David
View 29 Replies
View Related
Feb 8, 2007
Hi ALL,
Somehow one of tables in my database with clustered index got tempered and the pageid are not in proper order. I tried the checkdb command with repair_rebuild option but it is giving the following error.
Table error: Object ID 1984530649, index ID 1. Page (1:254682) is missing a reference from previous page (1:254681). Possible chain linkage problem.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1984530649, index ID 1. B-tree chain linkage mismatch. (1:254680)->next = (1:256198), but (1:256198)->Prev = (1:256197).
Not able to reindex the index of table. I can not even export the data to any other table with same structure.
Please help me...........
Thanks,
Pracheer
View 2 Replies
View Related
May 29, 2012
I have procedure. say for example...
begin
select * from table_abc
end;
suppose, i get errors i.e table not found etc etc. I want to save the errors in a file or even in a table will do.
View 1 Replies
View Related
Jun 19, 2006
Paul writes "Hi,
We have run the following command...
DBCC CHECKDB ('db_name')
... and 12'622 consistency errors were found (all on the same index on the same table).
So next we ran...
DBCC CHECKTABLE ('table_name', REPAIR_ALLOW_DATA_LOSS)
... and this fixed SOME of the errors, but not all...
12586 consistency errors in table
Does anyone have any ideas?
Just to confirm this is SQL Server 2000.
I do have both of the results logs available if that would help.
Many Thanks,
Paul."
View 2 Replies
View Related
Aug 5, 2014
CREATE TABLE SampleData (
CoID nvarchar(255),
Company varchar(255),
FirstName varchar(255),
LastName varchar(255),
JobLevel varchar(255))
[Code] ....
I am trying to make changes to the above query. This query will select 2 records per Company with top down priority on JobLevel. For example: This query will select 2 records from "123 Inc", where they have 2 Managers and 3 Staff contacts. The return results selects (2) managers and no staff records and FirstName and LastName are not duplicate. Then for "A Small Co.", they have 1 VP, 1 Director, 1 Manager and 3 Staff contacts. the return results chooses 1 VP and 1 Director and no managers or staff level people. And so on and so forth for the remainder of the companies.
What I was trying to accomplish is having this same query point to the database tables instead creating a table each and every time. I have this data residing in our database. I tried to make the change so it would point to the database tables but it kept giving me errors. This is way more complex than I thought.
My failed attempt:
Select *
From CampServ.dbo.SampleFakeCustomerData
//This is where I am getting stuck
WITH Priorities(Priority, JobLevel) AS(
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL
[Code] ....
View 2 Replies
View Related
May 10, 2007
We recently restored a database from a...
windows 2000 x86 sp4
sql 2000 enterprise x86 sp4 stand-alone
all drives hosting sql databases are SAN attached, write-caching is enabled
server, to a...
windows 2003 enterprise x64 sp1
SQL 2005 enterprise x64 sp2 2-node active/passive
all drives hosting sql databases are SAN attached, write-caching is enabled
server.
We are experiencing data corruption on different tables in different databases. So far, we have always been able to quickly resolve the issue by simply running a DBCC DBREINDEX against the table. However, we are unable to resolve the issue with the latest occurrence. Any help would be greatly appreciated. Here is the output (or partial output in some cases) of various commands:
DBCC CHECKDB ('DATABASE_NM')
DBCC results for 'main_notes'.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 0, text ID 3788411843723132928 does not match its reference from page (1:34174), slot 0.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 2, text ID 341442560 does not match its reference from page (1:2487), slot 0.
Msg 8929, Level 16, State 1, Line 1
Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 72057594151239680 (type In-row data): Errors found in off-row data with ID 341442560 owned by data record identified by RID = (1:34174:0)
There are 1 rows in 1 pages for object "main_notes".
CHECKDB found 0 allocation errors and 3 consistency errors in table 'main_notes' (object ID 434100587).
** NOTE ** Index_ID 1 is the clustered index which is on a single-column PK of int type.
DBCC CHECKTABLE ('Main_Notes')
DBCC results for 'main_notes'.
Msg 8961, Level 16, State 1, Line 2
Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 0, text ID 3788411843723132928 does not match its reference from page (1:34174), slot 0.
Msg 8961, Level 16, State 1, Line 2
Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 2, text ID 341442560 does not match its reference from page (1:2487), slot 0.
Msg 8929, Level 16, State 1, Line 2
Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 72057594151239680 (type In-row data): Errors found in off-row data with ID 341442560 owned by data record identified by RID = (1:34174:0)
There are 1 rows in 1 pages for object "main_notes".
CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'main_notes' (object ID 434100587).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (DATABASE_NM.dbo.main_notes).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC PAGE (F00115_Chase_TPP, 1, 2487, 1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC PAGE (F00115_Chase_TPP, 1, 34174, 1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC DBREINDEX ('Main_Notes')
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
UPDATE STATISTICS Main_Notes WITH FULLSCAN
Command(s) completed successfully.
select * from main_notes
Msg 7105, Level 22, State 6, Line 1
The Database ID 17, Page (1:2487), slot 0 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.
select * from main_notes (nolock)
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
View 11 Replies
View Related
Aug 31, 2006
hi
Here are the two tables again.
1)PATIENT(PATIENT_ID,NAME,CITY)
2) DISEASES(DISEASE_ID,NAME)
I am trying to select patient table as case and diseases table as nested to create an association model. i m getting following error.
Disease table cannot be used as a nested table because it does not have a many-to-one relationship with the case table. You need to create a many-to-one relationship between the two tables in the data source file.
i have created a relationship by dragging Disease_id from diseases table on Patient_id in patient table. when i am trying to select Patient_id as key, City as input, it is not showing disease_id to choose as a predict column.
please suggest me if i am doing anything wrong? i have not done any thing to do my datbase, just selected the tables i want to create an association model on and trying to create association model.
your help and insight is highly appreciated.
regards
Raju
View 4 Replies
View Related
Aug 22, 2007
The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.
DECLARE @OutParam VARCHAR(5)
EXEC ('{Call getnextpin(1)}') AT ALTTEST
GetNextPin takes a single input parameter.
I need to modify the above code snippet to return the next PIN number into @OutParam.
Any ideas?
Any help appreciated.
Regards,
Paul.
View 1 Replies
View Related
Jan 2, 2006
This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0
Function get_all_events() As SqlDataReader
Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)
myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Variable Pages is global integer.
This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
View 3 Replies
View Related
Mar 2, 2007
It's an error "The 'MINIMUM_DEPENDENCY_PROBABILITY' data mining parameter is not valid..." always I try to run Analyze Key Influencers Tool. How it's possible to fix it?
View 5 Replies
View Related
Jan 21, 2008
Hello,
I am facing an issue with a table in my database.
1) When I run a query Select * from MY_Table the query never completes. If I press cancel in Query Analyzer after 1 second or 2 minutes, it always shows the same 174 records. The total table size is 800 KB with 3148 rows .. according to TaskPad
2) No queries, even queries that would return only 1 record complete execution against that table
3) I am unable to rename the table, if I try to rename the table Enterprise Manager becomes unresponsive and has to be closed.
4) If I try to browse the rows through Enterprise manager by scrolling through the records, it allows me to go down through a number of rows and then Enterprise manager gives me a timeout expired error.
5) I ran a DBCC CheckDB command ran for 1 hr and 7 minutes. It says there are 0 allocation errors and 0 consistency errors in the database.
6) The other database tables seem to be OK and the Database is operational except for calls involving the 1 Table
7) I am unable to Drop the Table
8) DBCC CheckAlloc finds 0 allocation errors and 0 consistency errors.
9) DBCC CheckTable on MY_Table returns with: There are 3148 rows in 33 pages for object 'MY_Table' .. no errors are printed.
Can one of the SQL Gurus on this forum please recommend a course of action.
Thanks in advance.
View 1 Replies
View Related
Aug 28, 2006
Recently installed Sql Server 2005 client and am now attempting to import data from a spreadsheet into an existing table. This works fine with Sql Server 2000 but I am getting data conversion truncation errors that stop the process when this runs using import utility in Sql Server 2005.
Any help would be appreciated.
View 1 Replies
View Related
Apr 6, 2007
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.
Code Snippet
[SqlFunction ( FillRowMethodName = "FillRow",
TableDefinition = "CustomerID nvarchar(MAX)",
SystemDataAccess = SystemDataAccessKind.Read,
DataAccess = DataAccessKind.Read,
IsDeterministic=false)]
public static IEnumerable GetWishlist () {
using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {
List<string> myList = new List<string> ();
conn.Open ();
SqlCommand command = conn.CreateCommand ();
command.CommandText = "GetObject";
command.Parameters.AddWithValue ( "@map", "Item" );
command.CommandType = System.Data.CommandType.StoredProcedure;
using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {
if (reader.Read ()) {
myList.Add ( reader[0] as string );
}
}
return (IEnumerable)myList;
}
}
When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.
Code SnippetEXEC GetObject 'Item'
Is there some sorf of trick I am missing?
Thank you!
View 3 Replies
View Related
May 29, 2002
It gives following errors
1.time out
2.general network failure
* Are these due to large number of online connnections
*If so what is the maximum number of connections possible
*what is the hardware requirement to avoid this
*what is the impact on stored procedures
*How to tune the server to overcome the problems
*I'm currently using the standard edition what is the impact on enterprise edition
View 1 Replies
View Related
Oct 11, 2007
I am getting the following errors when I try to connect to certain databases. Thanks for any help. Do I need to use the Surface area configuration tools to allow remote connections?
Error: 0xC0202009 at Populate_DBA_REP, Connection manager "MultiServer": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ".
Error: 0xC020801C at Load Servers, OLE DB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MultiServer" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Load Servers, DTS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Load Servers, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Load Servers: There were errors during task validation.
View 2 Replies
View Related
Jan 4, 2008
Hi all,
I copied the following code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):
--CET.sql--
USE AdventureWorks
GO
--Use column value from a table pointed at by a foreign key
WITH ProductItemPrices AS
(
SELECT ProductID, AVG(LineTotal) 'AvgPrice'
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT p.Name, pp.AvgPrice
FROM ProductItemPrices pp
JOIN
Production.Product p
ON
pp.ProductID = p.ProductID
ORDER BY p.Name
SELECT * FROM ProductItemPrices
GO
--Display rows from SalesOrderDetail table with a LineTotal
--value greater than the average for all Linetotal values with
--the same ProductID value
WITH ProductItemPrices AS
(
SELECT ProductID, AVG(LineTotal) 'AvgPrice'
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT TOP 29 sd.SalesOrderID, sd.ProductID, sd.LineTotal, pp.AvgPrice
FROM Sales.SalesOrderDetail sd
JOIN
ProductItemPrices pp
ON pp.ProductID = sd.ProductID
WHERE sd.LineTotal > pp.AvgPrice
ORDER BY sd.SalesOrderID, sd.ProductID
--Return EmployeeID along with first and last name of employees
--not reporting to any other employee
SELECT e.EmployeeID, c.FirstName, c.LastName
JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
JOIN HumanResources.EmployeeDepartmentHistory d
ON d.EmployeeID = e.EmployeeID
JOIN HumanResources.Department dn
ON dn.DepartmentID = d.DepartmentID)
JOIN Empcte a
ON e.ManagerID = a.empid)
--Order and display result set from CTE
SELECT * Hi all,
I copied the following T-SQL code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):
--CTE.sql--
USE AdventureWorks
GO
--Use column value from a table pointed at by a foreign key
WITH ProductItemPrices AS
(
SELECT ProductID, AVG(LineTotal) 'AvgPrice'
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT p.Name, pp.AvgPrice
FROM ProductItemPrices pp
JOIN
Production.Product p
ON
pp.ProductID = p.ProductID
ORDER BY p.Name
SELECT * FROM ProductItemPrices
GO
--Display rows from SalesOrderDetail table with a LineTotal
--value greater than the average for all Linetotal values with
--the same ProductID value
WITH ProductItemPrices AS
(
SELECT ProductID, AVG(LineTotal) 'AvgPrice'
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT TOP 29 sd.SalesOrderID, sd.ProductID, sd.LineTotal, pp.AvgPrice
FROM Sales.SalesOrderDetail sd
JOIN
ProductItemPrices pp
ON pp.ProductID = sd.ProductID
WHERE sd.LineTotal > pp.AvgPrice
ORDER BY sd.SalesOrderID, sd.ProductID
--Return EmployeeID along with first and last name of employees
--not reporting to any other employee
SELECT e.EmployeeID, c.FirstName, c.LastName
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.ContactID
where ManagerID IS NULL
--Specify top level EmployeeID for direct reports
DECLARE @TopEmp as int
SET @TopEmp = 109;
--Names and departments for direct reports to
--EmployeeID = @TopEmp; calculate employee name
WITH Empcte(empid, empname, mgrid, dName, lvl)
AS
(
-- Anchor row
SELECT e.EmployeeID,
REPLACE(c.FirstName + ' ' + ISNULL(c.MiddleName, '') +
' ' + c.LastName, ' ', ' ') 'Employee name',
e.ManagerID, dn.Name, 0
FROM Person.Contact c
JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
JOIN HumanResources.EmployeeDepartmentHistory d
ON d.EmployeeID = e.EmployeeID
JOIN HumanResources.Department dn
ON dn.DepartmentID = d.DepartmentID
WHERE e.EmployeeID = @TopEmp
UNION ALL
-- Recursive rows
SELECT e.EmployeeID,
REPLACE(c.FirstName + ' ' + ISNULL(c.MiddleName, '') +
' ' + c.LastName, ' ', ' ') 'Employee name',
e.ManagerID, dn.Name, a.lvl+1
FROM (Person.Contact c
JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
JOIN HumanResources.EmployeeDepartmentHistory d
ON d.EmployeeID = e.EmployeeID
JOIN HumanResources.Department dn
ON dn.DepartmentID = d.DepartmentID)
JOIN Empcte a
ON e.ManagerID = a.empid)
--Order and display result set from CTE
SELECT *
FROM Empcte
WHERE lvl <= 1
ORDER BY lvl, mgrid, empid
--Alternate statement using MAXRECURSION;
--must position immediately after Empcte to work
SELECT *
FROM Empcte
OPTION (MAXRECURSION 1)
====================================
This is Part 1 (due to the length of input > 50000 characters).
Scott Chang
View 5 Replies
View Related
Jun 22, 2006
I have a web application with a sql server 2000 backend. I use stored procedures to enter data, and use the sql RAISEERROR function to raise custom errors with the input.
My question is, is there anyway I can assign a number to these error and then catch them in my code?
e.g. I have an asset. Someone has authorised an inspection to occur on it. Now, they cannot authorise two inpsections - they must wait for one of the inspections to occur. I catch when someone is trying to authorise two inspections in the database, and raise an error.
But I then want my code to catch this error message and display a user friendly message to the user. Is there a way of attaching a number to the message so that I can catch it in my code and display an appropriate message?
View 1 Replies
View Related
Jun 9, 2004
I run a large support site - which was recently rebuilt from the ground up using the CSK - its hosted on a shared SQL server enviroment - and although bandwith and SQL server usage are huge - it was running fine.
But increasingly badly now - to the point where almost no internal pages will work in anything but the quietest times, we are getting sql timeout errors. This basically means most of the site (which is 1850 odd pages of dynamic) is offline. Since this is a support site and responsible for saving a great many lives - this is a serious serious problem.
the SQL DB, which is shared between the CSK, and a dotnetbb forum - is a little over a gig...big I know - but surely SQL is capable of managing tons more than that?
Has anyone heard of a situation like this - and perhaps shed some light? there are a number of connections that are set to sleeping (about 5-10 at a time) - but they tend only to be about 2 minutes old, and I am under the impression that only if they are sleeping for more than 5 minutes should you be concerned?
I really hope someone can help...because this is a complete nightmare.
Many Thanks,
Harley
View 3 Replies
View Related
Mar 16, 2001
Please help. We have no idea what is wrong.
Error message occurs at the end of the load.
"Error at Destination for Row number 6218607. Errors encountered so far in this task: 1. SqlDumpExecptionHandler: Preocess 11 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
Thanks for your help
Adrian.
View 1 Replies
View Related
Sep 10, 2001
Hello,
I want the server to check validation rules and not the user application
is this possible???
I want to send my own messages to the user and dont want the user to see the
servers messages.
Thank you in advance
Eran
View 1 Replies
View Related
Apr 21, 2008
I moved an instance of SQL Server 2005 Express to a different server and now receive the following error:
System.Data.SqlClient.SqlException: An error occurred in the Microsoft .NET Framework while trying to load assembly id 65538. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'xmpdbif, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred.
I have read about a resolution that requires you to change the db owner on select databases; I have contacted the software provider to see what users own what databases. I wanted to see if anyone had suggestions on this as well.
View 4 Replies
View Related
Oct 1, 2007
Hi, im new to SQL server and the report services. I have SQL 2005 express installed on windows xp pro with reporting services and use .net 2.0 framework.
I have recently tried deploying a report i have wrote in VB.net (VS2005 standard) but encountered problems. I have setup the report server to allow remote connections and i am able to connect to it from other computers on my LAN. I have setup anonymous login through IIS to both reports and reportserver instances, this works fine. When i try to deploy a report to my server i am asked for login details. I have tried to login with the execution account set in report server manager but that doesn't work and i have tried the sa SQL account.
When i connect to the report server by http://servername/reportserver it says internat error and there is also the error page displayed when i connect with http://servername/reports. Is this becuase i have not deployed any reports? If i check the server status it says running and initialised, sometimes. I have reinstalled SQL 2005 and report services about three times and still i am getting a problem. I am sure there is something wrong with how i have configured the server but dont know what.
Also when deploying reports should i put the URL as http://servername/reports or ../reportserver?
I can provide error logs if someone could point me in the direction of them if required to. (reportserver just says refer to error log)
I am sure this is an old problem and down to my config of the server. Need help though!!
Thanks in advance for any replies
Regards,
Matt
View 4 Replies
View Related
Oct 27, 2006
I notice this running the SQL Server Profiler under the application name Report Server. Every time it runs, it reports an error (in the Error field of Profiler). Does anyone know what this is?declare @p1 nvarchar(64)set @p1=NULLexec GetDBVersion @DBVersion=@p1 outputselect @p1
View 1 Replies
View Related
