This query appears to work well: -- Every supplier that produces some red or green part SELECT Suppliers.sid FROM Suppliers, Catalog, Parts WHERE Suppliers.sid = Catalog.sid AND Catalog.pid = Parts.pid AND (Parts.color = "red" OR Parts.color = "green"); To check it, I'd like to look at every SID that will not be returned by t...
I've made mistake creating clustered primary key on GUID column. There are many tables that reference that table with defined foreign keys. Table size is not significant. I would like to convert it from clustered to non-clustered without manually dropping and recreating any foreign keys or even primary key constraint. Is it possible to...
Hey all, I have a need to check a live table against a transactional archive table and I'm unsure of the fastest way to do this... For instance, let's say my live table is made up of these columns: Term CRN Fee Level Code My archive table would have the same columns, but also have an archive date so I can see what values the live t...
I am trying to convert my wordpress site which is based on MYSQl to a MS SQL because my we have our main database through MS SQL and we are creating database driven wordpress sites. I have been able to automate all of our wordpress installation + setup through database sripts through MYSQL but all of our variables + inputs are in MS SQL...
I cannot view the sql jobs in sql enterprise manager when I log on to the machine that has the SQL Server database. I can see the jobs when I open the Database using Enterprise Manager on a different machine. ...
Hi, I asked this question a little earlier today but am not sure as to how clear I was. I have a MySQL column filled with ordered numbers 1-56. These numbers were generated by my PHP script, not by auto_increment. What I'd like to do is make this column auto_incrementing after the PHP script sets the proper numbers. The PHP script wor...
I'm looking for a free online SQL interpreter that also has a database associated with it. I want to play around with SQL statements, but I don't want to setup my own database. I've looked around google for a bit, but I only found one web site that has interactive courses, an interpreter functionality and a small database: http://sqlco...
This query gets the top item in each group using the ranking function. I want to reduce the number of inner selects down to two instead of three. I tried using the rank() function in the innermost query, but couldn't get it working along with an aggregate function. Then I couldn't use a where clause on 'itemrank' without wrapping it in...
So, yesterday I asked 2 questions that pivoted around the same idea: Reorganizing a database that A- wasn't normalized and B- was a mess by virtue of my ignorance. I spent the better part of the day organizing my thoughts, reading up and working through some tests. Today I think I have a much better idea of how my DB should look and act,...
I'm trying to optimize a complex SQL query and getting wildly different results when I make seemingly inconsequential changes. For example, this takes 336 ms to run: Declare @InstanceID int set @InstanceID=1; With myResults as ( Select Row = Row_Number() Over (Order by sv.LastFirst), ContactID From DirectoryC...
When I click File -> Open on Report Builder, I can see a list of folders under Report Server Home root folder. But I don't want end-user to see any of the folders under root unless I grant them access. I tried hiding and removing permission on the folders but they are still visible in the root folder. ...
To set a flag i write SET status=status|? how do i clear a flag? Usually i write SET status=status&(-1^?) but ^ is illegal in sqlite. How do i clear a flag or use exclusive or? ...
Hi folks, i've got some string in some sql. i need to find out what the character is, for that string, given a index. eg. DECLARE @someString NVARCHAR(MAX) = 'hi folks' DECLARE @index INT = 4 -- assuming the first index is 1, not 0. now .. how do i get the character at 4th index slot, which is an 'f', in that example above. thanks...
I have 4 databases mirrored using High Protection mode without witness server between two servers(principal and mirror) that are in the same domain. Manual failover worked fine for several days. But later somehow the IP of the principal server was changed in the DNS, then onwards the mirror state of these databses went to disconneted sta...
HERE IS A QUERY select IssueNo, KendraCode, IssueTime, DateOfIssue, P_MotaBags, P_MotaWeight, P_PatlaBags, P_PatlaWeight, P_SarnaBags, P_SarnaWeight, NewBags,OldBags, TransporterName, TruckNumber, DriverName, TruckOwner,SocietyCode , (SELECT PaddyMotaW, PaddyPatlaW, PaddySarnaW, BagsMota, BagsPatla, BagsSarna,PC_ID, sangrahankendraid...
I want to retrieve all rows of a particular user with limit 0,x.. So i just want to ask is there any way to retrieve all rows in mysql without calling a method which returns count(id) of x& without making an overload of existing function which does not have limit at all in query & withour string.Relace() functionality. As internally m...
Question: when I create a table (T_TableName) using SQL Server Management-Studio, it always creates the table as Domain\UserName.T_TableName instead of dbo.T_TableName What's wrong ? ...
I basically have an application that has, say 5 threads, which each read from a table. The query is a simple SELECT TOP 1 * from the table, but I want to enforce a lock so that the next thread will select the next record from the table and not the locked one. When the application has finished it's task, it will update the locked record...
Hi all, I use hsqldb to run my unit tests that need a database access. For the moment, when I want to create a table for a specific test, I have the following code: private void createTable() { PreparedStatement ps; try { ps = getConnection().prepareStatement("CREATE TABLE T_DATE (ID NUMERIC PRIMARY KEY, DATEID TIMESTA...
Hi All, Is there a way in sql 2005 to have non localized formatting. For eg. 1,234.567 (FLOAT) in English is 1 234,567 (FLOAT) in Norwegian. So if an English client sends a FLOAT number to Norwegian server, it gets rejected. Is there a way to format types like floats and dates etc. in a way which is not language\localization specific?...