I have a web application that uses a fairly large table (millions of rows, about 30 columns). Let's call that TableA. Among the 30 columns, this table has a primary key named "id", and another column named "campaignID". As part of the application, users are able to upload new sets of data pertaining to new "campaigns". These data sets...
I write: :CREATE TABLE Person ( :name CHAR(10), : :ssn INTEGER); and save it to a file "a.sql" (colon represents beginning of line, is not in actual code.) If I then run it by typing "@a" in the mysql command prompt, it will tell me that the line starting with "ssn" is not recognized as a command, and is ignored. From what I gathe...
hi... i want to get the data of five table having a common id from one query can we do this , for example tbl_student,tbl_batch,tbl_section,tbl_level,tbl_faculty all have a common id college_id how can i get all the tables value with one query if anybody can help me i would be greatful ...
I need to change a database to add a unique constraint on a table column, but the VARCHAR data in it is not unique. How can I update those duplicate records so that each value is unique by adding a sequential number at the end of the existing data? e.g. I would like to change 'name' to 'name1', 'name2', 'name3' ...
I use database scripts where I check for the existence of a Stored Procedure then drop it then create it. Which of the following would be more efficient for checking and dropping SPs Option 1 IF EXISTS(SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[myStoredProc]',N'P')) DROP PROCEDURE dbo.myStoredProc; Option 2 IF ...
Creating XML out of data in Database by calling proc with bcp as SET @SQL= 'bcp "exec dbo.proc" queryout '+ @FileName +' -w -r -t -Sdd\SQL2005 -T ' (proc produced below) Everything is fine => creates XML as desired. Now task is to Add Declaration to this XML (<?xml version="1.0" ?>) How can this be achieved either in below proc or...
How to delete from two tables at once using the same delete statement in ASP.Net? ...
We're using an old application that stores dates in C / Unix format. C time is basically the number of seconds since Jan 1st, 1970. The dates are stored as an integer in a SQL Server database. I am writing a view for a report that uses these dates. So far, I'm converting from the UNIX time to a native datetime with: DateAdd(s,3600+uni...
Is it possible to execute a stored procedure in a view? I.e. CREATE VIEW [dbo].[v_ReportInvoiceClientsThisMonth] AS EXEC [dbo].[GetInvoiceClients] @startDate = '2009-03-01', @endDate = '2009-04-01' (doesn't work) The reason I need this is that I need a way to access the SP in Excel (in an idiot safe way, i.e. without VBA). ...
I'm running an asp.net 2.0 web application. Suddenly this morning, ExecuteNonQuery started returning -1, even though the commands in the SP that ExecuteNonQuery is running, are executing (I see items inserted and updated in the DB), and there is no exception thrown. This happens only when I am connected to our production DB. When I'm co...
I would like to get some of your ideas about resource name / categorizing place of resources Let me just give you the scope of the application: 3 or more supported languages 3 MVC websites [with a lot of shared resources, and also some unique resources] 1 shared MVC extensions library 1 core business library, which has shared func...
Hi, I have in my table a column that has values of type FLOAT. How can I get average value of all elements in this column? Thank you in advance. ...
I am planning to use P6Spy to intercept database statements within our architecture. However, I noticed on the P6Spy website that the last release of the software was back in 2003. Is there anything out there that is better or should I just stick with P6Spy? ...
I have (non-Hibernated) database tables that contain ids for Hibernate entities. I can query them (using createSQLQuery), which gives me the ids, from which I can then load the entities. I'd like to do that in one step, and I think I can do that with addEntity, but I am not sure how exactly. (Hibernate's documentation web site is down. ...
How can I get the number of records that were inserted? Is there an easier way, with L2S, than count before and count after and taking the difference? ...
I have the following the query running dynamically SELECT * FROM Vehicles WHERE (DKID IN (69954)) ORDER BY case when ImageName1 = 'na' then 0 else 1 end, Make , Model, Year DESC This is returning the following error: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'na'. ...
I'd like to reformat some SQL statements that are a single string with newlines in to something that's much easier to read. I don't personally know of a good coding style for indenting SQL - how should nested queries / where clauses / left joins / etc by represented to maximise readability? Has anyone seen a pretty-printing algorithm t...
Hello, I am stuck figuring out a working SQL Query for the fallowing: I need to generate a Fire Register report (how many people are still inside the building) based on an Access database that records login/logout events along with some metadata. The Access DB looks like this: +----+---------------------+---------+---------+------+ | i...
Hello, I've got a search box that users can type terms into. I have a table setup with fulltext searching on a string column. Lets say a user types this: "word, office, microsoft" and clicks "search". Is this the best way to deal with multiple search terms? (pseudocode) foreach (string searchWord in searchTerms){ select col1 f...
I have a column in SQL Server 2005 that stores a simple chunk of XML. At a later point processing is performed and I need to merge some processing info into the XML. While I can do this at an intermediate point I would much prefer to keep this method centraliazed within the stored procedure that is responsible for updating other fields...