Ok so I have this storedproc in my SQL 2008 database (works in 2005 too / used to) ... CREATE PROCEDURE [dbo].[SetBinaryContent] @Ref nvarchar(50), @Content varbinary(MAX), @ObjectID uniqueidentifier AS BEGIN DELETE ObjectContent WHERE ObjectId = @ObjectID AND Ref = @Ref IF DATALENGTH(@Content) > 5 BEGIN ...
Suppose I have a table amountInfo with columns (id, amount1, amount2, amount3) where amountX are money values and id is an int value ranging from 1 to some int under 10. Currently I have some code that approximately does this: declare @id int, @idmax int, @amounttotal money select @idmax = (select max(Id) from amountInfo) while (@id <=...
The Background My group has 4 SQL Server Databases: Production UAT Test Dev I work in the Dev environment. When the time comes to promote the objects I've been working on (tables, views, functions, stored procs) I make a request of my manager, who promotes to Test. After testing, she submits a request to an Admin who promotes to UA...
Based on following table (I have kept spaces between the rows for clarity) Path ----------- \node1\node2\node3 \node1\node2\node3\node5 \node1\node6\node3 \node1\node4\node3 \node1\node4\node3\node7 \node1\node4\node3\node8 \node1\node4\node3\node9 \node1\node4\node3\node9\node10 I want to get all the paths containing leaf node. So ...
I need a SQL statement to retrieve records where it key (or any column) is in a associate table, for example: documentId termId 4 1 4 2 3 3 5 1 This: SELECT documentId FROM table WHERE termId IN (1,2,3) ...will retrieve any documentid value where the termid value is 1 or 2 or 3. ...
Hi all, $mysqli = new mysqli("localhost", "root", "", "test"); $mysqli->query('PREPARE mid FROM "SELECT name FROM test_user WHERE id = ?"'); // working code start //$res = $mysqli->query('PREPARE mid FROM "SELECT name FROM test_user" '); //$res = $mysqli->query( 'EXECUTE mid;') or die(mysqli_error($mysqli)); // working code end.. $res ...
By default, parent_id = 0. I want to select all records with parent_id = 0 and only the last ones with parent_id > 0. I tried this, but it didn't work: SELECT * FROM `articles` IF `parent_id` > 0 THEN GROUP BY `parent_id` HAVING COUNT(`parent_id`) >= 1 END; ORDER BY `time` DESC I mean, that if there are a few records with parent_id...
I am trying to run a stored procedure with a while loop in it using Aqua Data Studio 6.5 and as soon as the SP starts Aqua Data starts consuming an increasing amount of my CPU's memory which makes absolutely no sense to me because everything should be off on the Sybase server I am working with. I have commented out and tested every piec...
I'm in the process of migrating some databases. I have this table that has a couple of hundred rows, and has a filename column. To each record in this table, the filename column needs to be altered and part of a path needs to be prepended to the value that is in that field. The table is like: | 1 | filename1 | | 2 | filename2 | and n...
I have to query two different servers from a dynamically built query. It basically gets data from one server, treats it, and inserts it into another server. The only problem is I have to be sure it works for both situations: If both the source and destination databases are on the same server, and if they're not. I understand the conc...
Say that I have a query of the form SELECT * FROM MYTABLE WHERE MYCOL in (?) And I want to parameterize the arguments to in. Is there a straightforward way to do this in Java with JDBC, in a way that could work on multiple databases without modifying the SQL itself? The closest question I've found had to do with C#, I'm wondering i...
This SQL query gives me today's number of users active in the last 30 days: SELECT COUNT(*) FROM table.users WHERE creation_tsz >= (now() - interval '30 days') How can I modify it to get not a single value, but a table of active users for a range of dates? My desired output would look like this: Date Users active in the las...
using System.Collections.Generic; using DataLoader.Domain; using NHibernate.Cfg; using NHibernate.Tool.hbm2ddl; using Microsoft.VisualStudio.TestTools.UnitTesting; namespace UnitTest { /// <summary> /// Unit test that reloads the database schema based on Hibernate mapping files. /// </summary> [TestClass] public clas...
I have a table ('sales') in a MYSQL DB which should rightfully have had a unique constraint enforced to prevent duplicates. To first remove the dupes and set the constraint is proving a bit tricky. Table structure (simplified): 'id (unique, autoinc)' product_id The goal is to enforce uniqueness for product_id. The de-duping policy ...
I have an Postgre database and a table called my_table. There are 4 columns in that table (id, column1, column2, column3). The id column is primary key, there are no other constrains or indexes on columns. The table has about 200000 rows. I want to print out all rows which has value of column column2 equal(case insensitive) to 'value12'...
An alternative title might be: Check for existence of multiple rows? Using a combination of SQL and C# I want a method to return true if all products in a list exist in a table. If it can be done all in SQL that would be preferable. I have written a method that returns whether a single productID exists using the following SQL: SELECT...
How can I create a stored procedure that accepts a start and end date.(e.g April 1 - April 30 1.) Get the business days including Saturdays x (a value). + 2.) Get Holidays x (a value) and return the total. I'm new to this, I guess it would be a tsql function. hmm. any help would be appreciated. Thanks ...
I am trying to mutlipivot source data (as below ) want results as single row (as below) My query so far is SELECT * FROM ( SELECT * FROM ( SELECT NK, DC, VERSION, GEV FROM MULTIPIVOT ) S...
Lets say i installed two named SQL Server 2008 instances e.g. A and B, will i have two services of each type e.g. two analysis service, two reporting service and so on, one service for A and the other for B? If yes, then it is known a service listens on a port number, how two same services going to listen on the port? I hope this is cle...
I make a query that return to me the count of Stored Procedure that created everyday as follow SELECT convert(varchar, crdate, 103) as Date,Count(*) as Counter FROM sysobjects WHERE (xtype = 'p') AND (name NOT LIKE 'dt%') Group by convert(varchar, crdate, 103) and its already work but dates appear in string format that i can't...