I have no degree, am self taught, and spent the last year moving my call centre database from using a simple Access database to SQL Server, programming the front end in VBA (in an Access project) as an IT Assistant. I want to get into a programming role eventually and I'm wondering whether to staudy for MCTS Installing and Maintaining S...
In SQL Server 2005 I have a table with data that looks something like this: WTN------------Date 555-111-1212 2009-01-01 555-111-1212 2009-01-02 555-111-1212 2009-01-03 555-111-1212 2009-01-15 555-111-1212 2009-01-16 212-999-5555 2009-01-01 212-999-5555 2009-01-10 212-999-5555 2009-01-11 From this I would like...
I have an existing, fairly lengthy, SQL query. I would like to select records which have a distinct mt.ID. I've tried inserting "SELECT DISTINCT" in various places without any success. Could anyone tell me where it should go? Thanks very much. SELECT * FROM (select ROW_NUMBER() OVER(ORDER BY " + orderField + @") as RowNum, ...
I have a employee draw type application, the tables look like: Employee (ID, name, selectionCount) Selections (employeeID, ipAddress) Now I need an update query that will count the number of selections for each employeeID (with unique IPaddresses), and update the selectionCount column in the table Employee. ...
We have two tables, ActivityForm and Field which are given a many-to-many relationship via the ActivityFormField table. The ActivityFormFieldValidator table will have a many-to-one relationship with the ActivityFormField table, so we are giving the ActivityFormField table an identity column ("ActivityFormFieldId"). The ActivityFormFiel...
I am new to the repository pattern but am creating a repository to connect to two different database types that have the same data structure. Where and how should I handle the connection? Here are my requirements/constraints/project description I will be connecting to SQL Server 2005 and DB2 (on Iseries) I will be using the repository...
i have three tables (SQL Server) Month - month_id, month name, .... Award - award_id, award name, .... Nomination - fk_award_id, fk_month_id, name, address,... I need to count the number of different types of awards awarded per month while returning 0 in cases where nobody is awarded for ex. the results should look like A...
Ok I just realized that from my previous question: http://stackoverflow.com/questions/1590370/update-main-table-based-on-rows-from-a-secondary-table That was how to update the main table with the count using the secondary table, but since this query will be in a sql job, how can I delete the rows I just counted, and making sure I don't ...
I'm building a desktop application that will run on multiple laptops. It will need to sync up to a central database whenever the user is back in the office and has access again. My biggest problem to overcome is how to design the database so that it is easily synced with the central database server. One of the major hurdles is trying t...
I have a varchar(100) field that contains both letters and numbers. The values are usually in the form of car 1, car 10, car 100, car 20. But the values can have any word preceding the number. Is there any way to sort these values numerically so that car 2 will come before car 10? Thanks. ...
I want to connect to a SQL Server Reporting Services Report Server from SQL Server Management Studio Express. Unfortunately there is no "Report Server" option in the "Server Type" drop down. Am I missing something here? ...
My stored procedure looks like: WITH MYCTE(....) AS ( ... ) UPDATE ... (using my CTE) DELETE ( using my CTE) <--- says the object, my CTE, doesn't exist Can I only use it once? ...
I'm working with an SQL Server database with about 50 tables and plenty of relationships between those tables. I have already written a backup and restore function which will retrieve all data from the model, export it to XML which it could then import again into a clean database. But maintaining this import/export is a lot of work when ...
I have a table with 1 column of varchar values. I am looking for a way to concatenate those values into a single value without a loop, if possible. If a loop is the most efficient way of going about this, then I'll go that way but figured I'd ask for other options before defaulting to that method. I'd also like to keep this inside of ...
Among other questions, this one asked how to delete from a join. My question: How much of this is standard SQL? On which databases would this actually work (most notably for me would be Oracle, MySQL and SQLServer)? ...
We are looking at creating a custom ASP.NET application for a client, however they are a nonprofit and thus budget is limited. We typically develop ASP.NET web and desktop apps to connect to a central SQl Server 200X database, ie with a full version of SQL Server, running on networked Windows Server. In this case we won't have a full v...
SCENARIO: I have two tables, table1(col1 date) and table2(col2 varchar(20)). REQUIREMENT: Whenever anyone updated the value in col2, todays date should be inserted/updated in col1. It should be done without using triggers. Now, I cannot think of anything possible to do it. So, I need your help; PLEASE. Thank you. ...
My content table looks like (contentID, title, created). I need to get all content that was created more than 3 weeks ago. Sql server database, created is datetime type. ...
Hi i am using the following dynamic sql: declare @cmd nvarchar(4000) set @cmd= 'select ''exec msdb.dbo.sp_update_job @job_id='''''' + convert(nvarchar(255), job_id)+ '''''', @owner_login_name=''''sa'''';'' from msdb..sysjobs' exec sp_executesql @cmd but all it is doing is printing as exec ....... I want to execute the results ...
What datatype is the best to store html content and documents like pdf's, excel files, and word documents. I am currently using ntext, but I am not sure if this is the best datatype to store html content. Also, I currently use FCKEditor to save html content to an ntext field and it stores the markup along with the styles. If I happen ...