I have inherited a database that has no real referential integrity at all, so I've created a new database with this corrected. The problem I have now, is that I have large amounts of data (around 50million rows) with previously assigned IDs. I need to keep these IDs in my new Database else I'll lose a lot of references from the data. ...
Trying to run the command: grant exec on GetPrograms to fp\ouruser_api When I run it, I get the error message "incorrect syntax near \", but this is the login id stored in our database, how can I grant this user permissions? Thanks ...
I have a bunch of PDF documents that I need to use in a website I am making. I do need to be able to search the PDFs as well. So is it better to save these files to the database or to a file folder? Also, in both cases how do I search them? I will basically be searching them for 1 or 2 words and return the list of PDFs that have the ...
Imagine a classifieds website, a very simple one where users don't have login details. I have this currently with MySql as a db. The db has several tables, because of the categories, but one main table for the classified itself. Total of 7 tables in my case. I want to use only Solr as a "db" because some people on SO thinks it would be...
The current application is a kind of CRM application built upon MS Access. The application is for internal use. My job is to migrate it to ASP.NET web-based application. Now boss requires to keep Access as database and develop ASP.NET code against it. My question is, is there any disadvantages of using Access as database in ASP.NET appl...
If I say: select max(length(Name)) from my_table I get the result as 18, but I want the concerned data also. So if I say: select max(length(Name)), Name from my_table ...it does not work. There should be a self join I guess which I am unable to figure it out. Can anyone please provide me a clue? Thanks, ...
I am trying to create a Stored Procedure that will be used for a Report and I want the 2 date parameters to have a DEFAULT value of today's date and 1 month prior. Is the below the proper way to do this? I was reading elsewhere that I should use COALESCE...(SEE HERE)This is a touchy production system so I wanted to double check before ...
This query works fine but I'm having trouble trying to figure out why it does not return anything if the user does not have any categories in the table "user_categories"? This is the table structure: users: user id, username, user city categories: category id, category name user_categories: user id, category id SELECT users.*, GROUP...
I'm looking for an elegant simple way to determine the date of the Friday that is closest to a particular date. Any ideas? ...
I have a fact table as below: ID OpportunityID Status CreateDate 1 A Contract Submitted 1/1/2010 2 A Action Required by Branch ...
What is the best way to determine whether or not a field's value is an integer in SQL Server (2000/2005/2008)? IsNumeric returns true for a variety of formats that would not likely convert to an integer. Examples include '15,000' and '15.1'. You can use a like statement but that only appears to work well for fields that have a pre-det...
SQLCMD uses windows authentication by default. According to the MSDN documentation, you can change it by adding a -U parameter to the command and then the user name. The problem is with the password. Is there any way to access the sql server if the password is blank? sqlcmd -U admin password: Whenever I try this and just press ente...
Hello! So I have this query that works perfectly: SELECT users.*, GROUP_CONCAT(categories.category_name) AS categories FROM users LEFT OUTER JOIN user_categories ON users.user_id = user_categories.user_id LEFT OUTER JOIN categories ON user_categories.category_id = categories.category_id WHERE users.user_city = 'brooklyn' GROUP BY users...
Is it possible to both alter a column and add a new column in the same alter table query for MSQL? I've tried looking at the below MSDN article, but it was a bit confusing to understand. I could easily do it with multiple queries, but would rather do it with one query if possible. Thanks. http://msdn.microsoft.com/en-us/library/ms19027...
I've got a table that people have been inserting into getting the primary key by doing a SELECT max(id)+1 from table_a; I want to add some records to that table using a INSERT INTO table_a SELECT ... FROM table_b, table_c ... simple SQL script, and I'm wondering how to generate the primary keys. My first thought was to create a temp...
Imagine I have three tables, called "customers", "companies" and "phone_numbers". Both customers and companies can have multiple phone numbers. What would be the best way to index phone_numbers? Have both customer_id and company_id and keep one of them null? What if there are more than two tables with a one-to-many relationship with phon...
CREATE TABLE batsman ( InningId int NOT NULL, PlayerId int NOT NULL, BatsmanOrder int(2) NOT NULL, BatScore int NOT NULL default 0, Balls int NOT NULL default 0, sixes int NOT NULL default 0, fours int NOT NULL default 0, `out` varchar(10) NOT NULL, /*Foreign*/ catcher int, /*Foreign*/ bowler int, /*Foreign*/ Primar...
I am querying a data system with an OLEDB interface that supports SQL92. My query problem is equivalent to the one solved here: http://stackoverflow.com/questions/2034094/sql-query-to-find-earliest-date-dependent-on-column-value-changing, but the solution provided there and copied below is too advanced for SQL92: SELECT JobCodeId, M...
This is the default behavior for PostgreSQL is a search path like so: SHOW search_path; search_path -------------- "$user",public Is there a way to make it so the "$user" is case insensitive? For instance I have a user tp1, and a schema TP1... I'd like them both to be seen as "equal" Is this even possible? ...
I'm using groovy gsql to query to Mysql database. Everything goes well on localhost (testing mode), unfortunately when I switching to remote db groovy don't query db. Here is the code : def sqlModule = Sql.newInstance("jdbc:mysql://localhost/module-test", "b", "b", "com.mysql.jdbc.Driver") def sampleQuery(int dataset) { def Sampl...