Hello, first i must admit that i'm not very familiar with sql server's recursive CTE's but i think this is the best approach. I have a table tabData. Its PK is named idData and there is a self referencing FK fiData. So fiData references the parent record and SELECT * FROM tabData WHERE idData=fiData returns all data of the parent. T...
Hello, a few minutes ago i asked here how to get parent records with a recursive CTE. This works now, but I get the wrong order(backwards, ordered by the PK idData) when i create a Table valued Function which returns all parents. I cannot order directly because i need the logical order provided by the CTE. This gives the correct order(f...
Hi all, I am working on an attendance system that has the following tables: Entry +---------+-----------+ + EntryID + EntryDate + +---------+ ----------+ Hour +---------+--------+---------+ + EntryID + InHour + OutHour + +---------+--------+---------+ With the following example data: Entry +---------+---------------------+ + En...
Hey all, I've been struggling with a select statement for a bit now and I was hoping to get some help. I currently have a list IPs in a temporary table and I want to calculate how many times this IP acts as a server and how many times it acts as a client. No matter how I manipluate this select statement I always get the same error ...
We have a database with a bunch of wide tables (40-80 columns each) and just found a bug that introduced NULL values into about 500 of the records. The NULL values can appear in any of the columns (all are integer columns, see image below) but these NULL values are causing issues with one of our reporting systems that cannot be changed e...
Hi all, I have problem when trying generate script insert with specific condition. So far I am already trying this step. Add references Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Smo Add reference in code Microsoft.SqlServer.Management.Smo Add this to script. var srv = new Server(@"localhost\SQLEXPRESS"); var db ...
I'm looking for a simple script that I can use to overwrite one database with another one. We have a master db with master schema and data and every so often a developer wants to blow away his messed up db with a complete overwrite from the master. I currently accomplish this with SQL Server Studio and the GUI controls but I want somethi...
How can I configure my mirror database to publish its data to the same replication database that my primary has ( I need to take my primary database offline for maintenance purposes.)? ...
Which edition of SQL Server 2008 should I install for development purposes? I'm presuming the developer edition, but... What I do is (SQL Server 2005 until now): Develop databases to plonk onto our web server (that runs SQL Server Express) Manage and extend these database using the SSMS (SQL Server Management Studio) Develop SQL Se...
hEY ALL i have a table that shows transactions and their status. How can i create a column that shows the total of transactions that are completed,rejected,declined and the grand total of all on a different column for example i am looking something like this Completed 100 Rejected 50 Declined 20 Total 170 In 3 different rows...
Hi! I have a subquery within a WHERE-clause within a subquery. Notwithstanding the design issues of the database (not my job), I am getting some strange errors when trying to extend the top level WHERE-clause in this expression. The example below WORKS. LEFT OUTER JOIN CargoVoyageLocation on CargoVoyageLocation.VoyageLocationI...
Hello, I've cretaed a TVF which returns a table with parent-records from a recursive CTE here. That works excellent and the result is available directly. Now i wanted to get the child-records(they have the same FK as the current record's PK). The problem is that it takes 1:10 minutes to get 22 child-records for a given id. Why is this s...
The question is simple, I couldn't find any solution though. If it is possible, how do I repeat a field that's in the Footer on a new page if it doesn't fit the container? For example: I have a column that can store up to 5000 characters, and I must display it in the Footer. So if it reachs a certain number of characters, I'd like it t...
Hi all! I would like to have your assistance in order to know if it's possible to achieve the desired query without needing a stored procedure. I will try to explain myself as good as I can. I have a similar structure to this: PK / FK / DateTime / Value 1 / 68 / 10:30 / 60.5 2 / 68 / 09:30 / 10.5 3 / 61 / 05:30 / 01.0 4 /...
Hi, The topic of sql server deadlock has been discussed many times, however, I was unsure that even two simultaneous inserts on a table can end up in a deadlock situation. Scenario: While testing our application (SQL Server 2005 as backend, ASP.net 3.5) we inserted records into a table simultaneously (simplified overview) and that resu...
Is it possible to edit the data returned from command.ExecuteReader, and then return that to SqlContext.Pipe.Send()? Are there any forseeable issues (I have to reset the cursor to the beginning)? I have a .NET stored procedure that will query a table like this (code from MSDN) public class StoredProcedures { /// <summary> ///...
What are the differences between SET vs. SELECT statement when assigning variables in T-SQL? ...
I'm a junior developer in our team. In one project we have about twenty databases in one SQL Server instance. We have db_owner rights to these databases. My intention is to monitor certain things from these databases (e.g. file size). But because we don't have sysadmin rights, we don't have all those management tools for these database...
I am supposed to remove the following from the address field. Replace all words named as 'Flat' to empty space, also remove the number that comes along with it. eg. I have word called as 'Flat 234 5th Street'. It should be replaced as 5th Street. I gave the query as select ltrim(rtrim( substring ('Flat 123 5th Street', cha...
Hi! Here's my data table in my SQL DB, and below that is the code for my Repeater control: ArticleID int NOT NULL, ArticleTitle varchar(100) NOT NULL, ArticleCategory int NOT NULL, ArticleDate datetime NOT NULL, ArticleContent text NOT NULL, DeletedYN char(3) NOT NULL <asp:Repeater runat="server" ID="rptArticles" DataSourceID="dsLates...