This has effectively ruined my day. I have a larger number of tables with many FK relationships in between. One of the tables (lets call it table A) has a computed column, which is computed via a UDF with schemabinding and is also fulltext indexed. If I edit any table (lets call it table B) that in any way is related (e.g via FK) to the...
I want this function to take a datetime and return the time expressed as a decimal. E.G. - 2:33 PM would be returned as 14.55 ALTER FUNCTION [dbo].[GetTimeAsDecimal]( @DateTime as datetime ) RETURNS decimal AS BEGIN DECLARE @hour decimal DECLARE @min decimal DECLARE @result decimal SELECT @hour = DATEPART(HOUR, @DateTime) SELECT @min =...
I have a recursive trigger, that seems to do exactly what I want it to with no recursion, however when I turn recursion on I get the error: "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" This should not happen, as I expect 2 or maybe 3 levels of nesting, so I need to debug the trigger and work...
Hello i am just Studying 3-tier Application. I am now having a problem with this simple login form. I always come up with this Error: Entities.Entities is a type but is not like used like a variable. Always Shows: USer ID is incorrect. public int SearchEntry(UsersEntity UserEntity) { SqlConnection con = new SqlConnection(); ...
SQL Server 2008. I have a parent row with pk id 1. While blocking all other DB users (this is a cleanup operation so resource contention is not an issue), I would like to insert a new row, then take all of the child rows and change their fk column to the new row. With the below DDL for example, I would like to insert a new row and give ...
I'm having issues with the way I've been reading my image datatype blobs from my SQL database (code below). This code works fine when I access this page directly. The PDF will open and works just fine. However, when I try to use this page to download a file and save it to the file-system programmatically, it fails (500 server error) a...
I have a stored procedure that gets a list of items, sorts and applies paging. However I also need to add filtering. So what I want to do is pass in a string of filters like "27='Some Company';32='Auckland'", and split it into a temp table (see below split code) Proposed Split code CREATE TABLE #Filters ( ModelEn...
I am getting this error: [OLE/DB provider returned message: [IBM][CLI Driver] [DB2 / linuxx8664] SQL0206N "LS_SCHEDULED_DATE" is not valid in the context where used. SQLSTATE=42703) OLE DB error trace [OLE /DB Provider 'MSDASQL' IColumnsInfo: GetColumnsInfo returned 0x80004005: Msg7339, Level 1, State 1, Line 83 OLE DB provider 'MSDASQL'...
is there an elegant object-orient based framework? ...
Okay, so I have this problem with my Fact Table. I need it to automatically be filled when a new data is entered on all the other tables in the database which has a foreign key on my Fact Table. In my stored procedure, I compiled all the insert statements I have and at the end, since I also want to update my Fact Table, I place this quer...
Possibly some of you don't even know about these features so you will learn a lot from this post which will in fact help me to optimize better and some of you probably use them on daily basis so you can help me and other less DBA proof users. I'm using SQL-Server 2005 Standard I run SQL Server Profiler a lot. Each time i find ad hoc qu...
I'm using SQL-Server 2005 Standard. I have Users table with following columns: userID(int), userImage(varchar), userText(varchar), userLastVisit(smalldatetime), isActivated (bit), userHobby1 (bit), ..... userHobby10 (bit) userCharacteristic1 (bit), ..... userCharacteristic10 (bit) I did 6 queries to select users that haven't been o...
What's wrong with this T-SQL : DECLARE @temp TABLE(ID INT IDENTITY,[Value] VARCHAR(100)) SET @temp = dbo.[fnCSVToTable](',2,3') ...
As pointed out in this question when using filestream with sql server 2008 the data must be stored locally. This means that I cannot use \\FILESERVER\FileStreamData\MyDatabase for Filestream filegroup path. So if I need to use filesrteam and have lots of data, is buying a very large hard drive the only solution (this could be quite l...
I have a legacy stored procedure that returns a large resultset. The problem is that the resultset is too large to get all at once and then do paging and sorting on the web side. Without changing the stored procedure is it possible to wrap it in some other sql code to get only certain results back in a fast manner. Something like: EX...
HI, I have a sql stored procedure, which i am calling from my asp.net (2.0) code by using sqlCom.ExecuteNonQuery(), but it tooks around 60 to 100 second to complete, and while i execute the same SP from sql query tool runs in 3 to 4 seconds. Please help on this. Edit - code from comments: public int ExecuteNonQuery(string strSpNa...
Hi everyone, I have a many-to-many relationship, in which I query all M which have a specific N, for example: SELECT M.* FROM M INNER JOIN ManyToManyTable ON M.Id = ManyToManyTable.M WHERE ManyToManyTable.N = @Id Or: SELECT M.* FROM M INNER JOIN ManyToManyTable ON M.Id = ManyToManyTable.M INNER JOIN N ON N.Id = ManyToManyTable.N WHE...
I have a general Database Design question: When is it better to create a new Database instead of adding new Tables to an existing? The question is related to design/maintainability andalso performance issues. Background: we are mirroring the main tables of our customer by importing the data every night into our DB called RM2. Some new (...
Hi try to create batch execution list of object to SqlServer through Hibernate DAO . my DAO code as follows: @Override public void save(List<Tagihan> listTagihan) { logger.debug("save list invoked"); Session session = sessionFactory.openSession(); try { session.beginTransaction(); int ...
Hi. I am trying to understand how JOINS work on SQL Server 2000 and 2005 SPECIFICALLY. In general, I understand how inner joins, left joins and right joins work. However, all the articles that I've read, explain it with examples, but are not specific to any RDBMS. So now I am confused to the different types of joins, which are just ...