I have a simple database: ARTICLE ---------- ArticleId (PK), ArticleTitle ..other stuff... USER-ARTICLE ------------ ArchiveId (PK), UserId, ArticleId ..other stuff... The articleId's are foreign keys. I want to be able to delete a user article row by UserArticleId using the following code UserArticle myobjtodelete = PersonalArc...
I'm working on an app that takes data from our DB and outputs an xml file using the FOR XML AUTO, ELEMENTS on the end of the generated query, followed by an XSLT to transform it the way we want. However in a particular case where we are generating some data using an sql scalar function, it always puts that element into a sub-table node ...
When @RadioServiceGroup is set to NULL, I want to return all the records from the sbi_l_radioservicecodes table which has about 120 records. However, when I execute the following procedure and set the @RadioServiceGroup to NULL, it returns no records. Here is the stored proc: CREATE PROCEDURE [dbo].[GetRadioServiceCodes] @RadioServic...
I have a stored procedure that creates and opens some cursors. It closes them at the end, but if it hits an error those cursors are left open! Then subsequent runs fail when it tries to create cursors since a cursor with the name already exists. Is there a way I can query which cursors exists and if they are open or not so I can close...
I added the columns in the select list to the order by list, but it is still giving me the error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Here is the stored proc: CREATE PROCEDURE [dbo].[GetRadioServiceCodesINGroup] @RadioServiceGroup nvarchar(1000) = NULL AS BEGIN SET NOCOUNT ON; SELECT DISTIN...
Usually, I need to retrieve data from a table in some range; for example, a separate page for each search result. In MySQL I use LIMIT keyword but in DB2 I don't know. Now I use this query for retrieve range of data. SELECT * FROM( SELECT SMALLINT(RANK() OVER(ORDER BY NAME DESC)) AS RUNNING_NO , DATA_KEY_VALUE , ...
I get this error message when installing SQL 2005 Analysis services. The cabinet File 'sql.cab' required for this installation is corrupt and cannot be used. This could indicate a network error, an error reading from the CD-ROM, or a problem with this package. Im installing from an ISO file downloaded from MSDN. How could anything b...
I've got a DB table where we store a lot of MD5 hashes (and yes I know that they aren't 100% unique...) where we have a lot of comparison queries against those strings. This table can become quite large with over 5M rows. My question is this: Is it wise to keep the data as hexadecimal strings or should I convert the hex to binary or dec...
So I have the following user defined type in my oracle database: CREATE OR REPLACE TYPE METRIC_IMPERIAL_DISTANCE AS OBJECT ( METERS_FEET INTEGER, CENTIMETERS_INCHES INTEGER, FRACTION NUMBER ) I can do the following: SELECT t_id, get_distance_breakdown (h.height, h.unit_of_measure_id) height_breakdown FROM heights h an...
I am coding in ColdFusion, but trying to stay in cfscript, so I have a function that allows me to pass in a query to run it with <cfquery blah > #query# </cfquery> Somehow though, when i construct my queries with sql = "SELECT * FROM a WHERE b='#c#'" and pass it in, coldfusion has replaced the single quotes with 2 single quotes. ...
I have a stored procedure that has a optional parameter, @UserID VARCHAR(50). The thing is, there are two ways to work with it: Give it a default value of NULL, the have an IF...ELSE clause, that performs two different SELECT queries, one with 'WHERE UserID = @UserID' and without the where. Give it a default value of '%' and then just ...
Hello! I'm pivoting data in MS SQL stored procedure. Columns which are pivoted are dynamically created using stored procedure parameter (for exampe: "location1,location2,location3,") so number of columns which will be generated is not known. Output should look like (where locations are taken from stored procedure parameter): OrderTi...
I am trying to convert a date with individual parts such as 12, 1, 2007 into a datetime in SQL Server 2005. I have tried the following: CAST(DATEPART(year, DATE)+'-'+ DATEPART(month, DATE) +'-'+ DATEPART(day, DATE) AS DATETIME) but this results in the wrong date. What is the correct way to turn the three date values into a proper date...
In your experience, how long does it take for a professional procedural coder to become a professional SQL coder? And what's the best way to make the transition? (Disregarding database design skills). What percent of programmers are professional-level at both? a. Self evaluation. b. Unbiased evaluation. (In my experience, one problem i...
I learned SQL at a local college using a small SQL book, it had a baby blue hard cover, and was probably around 100-150 pages. It was in 1992 or 1993 that I took the course. It was, as I recall, not related to any specific SQL tool - very generic. It did a great job of explaining the basics and I really wish I could find it again. EDI...
I have two tables that I would like to join but I am getting an error from MySQL Table: books bookTagNum ShelfTagNum book1 1 book2 2 book3 2 Table: shelf shelfNum shelfTagNum 1 shelf1 2 shelf2 I want my results to be: bookTagNum ShelfTagNum shelfNum book1 shelf1 1 book2 shelf2 ...
Does anyone know how to generate SQL scripts from a query? For example, Script some tables. Do custom action 1. Script the views. Do custom action 2. Etc. ...
I need to do a date comparison in Mysql without taking into account the time component i.e. i need to convert '2008-11-05 14:30:00' to '2008-11-05' Currently i am doing this: SELECT from_days(to_days(my_date)) Is there a proper way of doing this? ...
(Note: This is for MySQL's SQL, not SQL Server.) I have a database column with values like "abc def GHI JKL". I want to write a WHERE clause that includes a case-insensitive test for any word that begins with a specific letter. For example, that example would test true for the letters a,c,g,j because there's a 'word' beginning with each...
I have 3 lists, I will make them simple here. list of letters A B C list of numbers 1 2 3 Mixed A,1 A,2 B,2 B,3 C,1 C,3 I need to know what is missing: A,3 B,1 C,2 The list of letters has about 85 entries and the list of numbers has about 500 entries. The mixed list has about 75,000...