Hi, Suppose I have a recursive table (e.g. employees with managers) and a list of size 0..n of ids. How can I find the lowest common parent for these ids? For example, if my table looks like this: Id | ParentId ---|--------- 1 | NULL 2 | 1 3 | 1 4 | 2 5 | 2 6 | 3 7 | 3 8 | 7...
I have written the following trigger in SQL server: create trigger test_trigger on invoice -- This is the invoice table for insert as declare @invoiceAmount int -- This is the amount specified in the invoice declare @custNumber int -- This is the customer's id --use the 'inserted' keyword to access the values inserted into th...
Hi Can any body plz tell me how to find the location where my data and log files stored using SMO in c# Thanks in Advance ...
I have an execution plan for a fairly complex join which shows an index seek being performed on a table with the "Actual Number of Rows" reading ~70,000, when there are in fact only ~600 rows in the table in total (the estimated number of rows is only 127). Note that all of the statistics are up to date and the input parameters to the q...
I'm having an issue where I can view my reports in an ASP.NET environment, and can even export them, but as soon as I got print them, I get the following javascript error: "Object doesn't support this property or method" When I run the debugger, I see the following code: <HTML> <BODY onload="Print()"> <OBJECT ID="RSCl...
I have a table with some 30 columns, already used in the application extensively. i.e Select, Insert and Update operations for this table written in many different ways(in whatever ways the developers thought they were comfortable) in number of stored procedures and UDFs. I'm now handed with a task to extend the functionality for which t...
what is the best place to start learning about SQL Server 2005 Integration services? I'm looking for links for setup and tutorials on how to get started. I've read I need a toolkit called SSIS designer however I haven't been able to find it on the SQL Server Enterprise ed dvd nor online anywhere. I did come across some links in vario...
I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in: CREATE PROCEDURE MyProc @MyDate DATETIME = NULL AS IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP -- Do Something using @MyDate I'm having problems whereby if @MyDate is passed in as NULL when the stored ...
Using SQL Server 2005 I'm trying to group based on a case statement with a subquery, but I'm getting an error ("Each GROUP BY expression must contain at least one column reference. "). I can work round it quite easily, but can anyone explain the error? I've got a column reference to #header.header. create table #header (header int) crea...
We use VSTS Database Edition to version control our database schemas. I like the idea of how it is supposed to work but have a big issue with the Schema Comparison feature. I like to use it for my deployments to see what has changed in the project vs a target database. Unfortunately, it shows many changes for objects that have not been c...
How can I see the execution plan for stored procedures and triggers in SQL Server? ...
I have coded my own tiny static DAL class for .NET Compact Framework and local SQL Server that does just what I need; GetTable(SQL,params), Exec(SQL,params), ExecScalar(SQL,Params) etc. I wanted to do everything by SQL so that's how I rolled, if there are better ways to handle SQL Connection in C# -except ORM- please let me know. Here's ...
How could you remove all characters that are not alphabetic from a string? What about non-alphanumeric? Does this have to be a custom function or are there also more generalizable solutions? ...
I'm trying to call my assemblys SP and receive the following error: A .NET Framework error occurred during execution of user-defined routine or aggregate "Pmt_ImportJournal": System.InvalidCastException: Specified cast is not valid. System.InvalidCastException: at JournalImport.Pmt_ImportJournal(Int32 orderno) My SP in the assemb...
If I take a backup of a database on a SQL 2005 Server, and then restore that backup to a different (new) database on the same database server instance, what things will not be persisted (things like cached execution plans, statistics etc...) ...
Input: Name Id N1 1 N1 3 N1 4 N1 7 N2 2 N2 1 N2 8 N2 5 N3 4 N3 8 N3 5 N3 3 N4 7 N4 7 N4 7 N4 8 Output: Name1 Name2 Name3 Name4 ---------------------...
Let's say I have a function call on a select or where clause in Oracle like this: select a, b, c, dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3) from my_table A similar example can be constructed for MS SQLServer. What's the expected behavior in each case? Is the HASH function going to be called once for each row in the table, o...
I need to pull a specific substring from a string of the form: foo=abc;bar=def;baz=ghi For example, how would I get the value of "bar" from that string? ...
I have a XML schema, and I know that "xsd.exe" can generate the C# code for it. But I want to know if is possible to automatically create the MS SQL Server 2005+ tables from the XSD, with the help of this or other tools. BTW I didn't get what the C# code generated by "xsd.exe" is worth for. What's the difference between code generated b...
I am looking for a solution to use pivot on two tables. My problem though, number/name of column & row headers exist as rows in third table. My third table looks like this: ID Name 1 Row1 1 Row2 1 Row3 2 Col1 2 Col2 2 Col3 My final solution should look like this: Col1 Col2 Col3 C...