Hi guys, I'm updating an old ASP/MySql webapp to ASP.NET/MS SQL. We would like to keep the logins from the old website working in the new app. Unfortunately the passwords were stored in the MySql DB using MySql's password() function. Is it possible to simulate MySql's password() function in either .NET or MS SQL? Any help/links ...
I have a SQL 2005 database with approx 250 tables. I want to temporarily enable ON DELETE CASCADE to all of the Foreign Keys so that I can do a bulk delete easily. I then want to turn off ON DELETE CASCADE on all Foreign Keys. The only way I know of doing this, is to use Management Studio to generate a full database create script, do ...
I have a very large (2.5GB, 55 million node) XML file with the following format: <TopNode> <Item id = "Something"> <Link>A link</Link> <Link>Another link</Link> <Link>One More Link</Link> </Item> <Item id = "Something else"> <Link>Some link</Link> <Link>You get the idea</Link> ...
i need to take all data if Page!=@page, get AllDATA select count(page) as TARIH, (datepart(hour,Date)*60+datepart(minute,Date))/@countgap as SIRA from scr_SecuristLog where Date between @date1 and @date2 and Page=@page or Page = AllDATA ...
This is a generic database design question - What are the benefits of using a synonym in database development, over a simple view? What are the main considerations to keep in mind when choosing between the two? ...
Is there an easy way to check if a foreign key exists for a column in a table? I am writing a script which will add the foreign key only if it does not exist. Thanks ...
I don't think it makes any difference to the database, but when joining tables, which order do you prefer to write the condition: SELECT ... FROM AAA INNER JOIN BBB ON AAA.ID=BBB.ID WHERE ... OR SELECT ... FROM AAA INNER JOIN BBB ON BBB.ID=AAA.ID WHERE ... ...
Something I've noticed recently is people using IN where it seems kind of inappropriate. I'm feeling like I'm missing a trick - perhaps there are speed gains or something? This query: SELECT * FROM pages WHERE is_visible IN ($visibility) Why use that? Why not: SELECT * FROM pages WHERE is_visible = $visibility Sure, if it were thi...
I asked a question on SOF a week or so ago about auditing SQL data changes. The usual stuff about using triggers came up, there was also the mention of CDC in SQL Server 2008. I've been trying it out today and so far so good, the one thing I can't see it supports is keeping a track of who actually made the change. Who executed the stat...
I'm trying to copy data from one Oracle schema (CORE_DATA) into another (MY_DATA) using an 'INSERT INTO (...)' SQL statement. What would the SQL statement look like? Thanks, ...
I'm currently trying to get all of the attributes from some XML with an SQL query. I've been attempting the following to retrieve it, but I must be missing something rather fundamental. DECLARE @T varchar(max) SET @T = '<root> <Field FieldRowId="1000"> <Items> <Item Name="CODE"/> <Item Name="DATE"/> </Items> <Attribute...
How do I define relation rules in mysql? Table1: bookId authorId bookName Table2: authorId authorName This is how I would like the Mysql server to behave: When I try to insert a row into table1 with a authorId that doesn't exsist in table2, mysql would produce a error. ...
I'm looking into adding some unit tests for some classes in my data access layer and I'm looking at an update routine that has no return value. It simply updates a row based on the id you provide at whichever column name you provide. Inside of this method, we collect the parameters and pass them to a helper routine which calls the st...
Hi Where can I find documentation about WDS, specifically, using SQL queries from C# to query WDS? Are there any resources listing the columns that can be queried from the SystemIndex? Additionally, I want the query to return the "context" i.e. just like the WDS client does with a few lines from the document where the search terms are f...
I have three tables in my Crystal Reports and it looks like this: Property ID Other Data Unit ID PropertyID <-- fk to Property Other Data Tenant ID UnitID <-- fk to Unit Other Data The report is going to show only Unit's (grouped by the property they belong to) that do not have any tenant's linked to it. Some nice peop...
Why would the following queries return different result sets? select count(ml.link_type),mc.conv_string from MSP_CONVERSIONS mc,MSP_LINKS ml where ml.PROJ_ID = 4 and mc.STRING_TYPE_ID = 3 and mc.CONV_VALUE *= ml.link_type group by mc.conv_string select count(ml.link_type),mc.conv_string from MSP_CONVERSIONS mc left outer join MSP_LINKS...
Hi, I have a query set up that contains a CASE statement which works 95% of the time... That other 5% is due to the data not being there. There is other data that indicates pointers that will help... I just know if this is possible... So: CASE PRDE.STATUSCODE WHEN 'A' THEN 'Appealed' WHEN 'D' THEN 'Dismissed' WHEN 'P' THEN 'Pending' WH...
It is safe to say that the EAV/CR database model is bad. That said, Question: What database model, technique, or pattern should be used to deal with "classes" of attributes describing e-commerce products which can be changed at run time? In a good E-commerce database, you will store classes of options (like TV resolution then have ...
I am wondering which is a more efficent method to retrieve data from the database. ex. One particular part of my application can have well over 100 objects. Right now I have it setup to query the database twice for each object. This part of the application periodically refreshes itself, say every 2 minutes, and this application will pr...
I have a table of dates call [BadDates], it has just one column where every record is a date to exclude. I have a UDF as follows: CREATE FUNCTION [dbo].[udf_GetDateInBusinessDays] ( @StartDate datetime, --Start Date @NumberDays int --Good days ahead ) RETURNS datetime AS BEGIN -- Declare the return variable here DECLARE ...