Hoping this is trivial for a SQL-Ninja... Been trying to get the following query working: This is under MSSQL Server 2008 SELECT ROW_NUMBER() OVER (ORDER BY Date_Time DESC) AS RowNumber, * FROM ( SELECT T.A_ID, T.User_Name, T.Date_Time, T.Value, U.ID, U.Name, U.Field1, U.Field2, COUNT(U.ID) OVER () AS TotalRows FRO...
I want to clone multiple tables' rows that have a single primary key (PrimKey). What's the quickest way to clone a row in SQL Server 2005? Here's an example, Clone PrimKey1 to get PrimKey2. So I try the following : INSERT INTO PrimKeys SELECT 'PrimKey2' AS PrimKey,* FROM PrimKeys WHERE PrimKey='PrimKey1' But of course the is...
I want to update a single record in a table to reflect that a given client session has acquired the record (and now owns it for further updates) within a multi-session environment. I've got this so far: create procedure AcquireRow( @itemNo int, -- Item ID to acquire @sessNo int, -- Session ID @res char(1...
Hi everyone! In my project, I'm in a point that is lack of knowledge of sql programming. I have a tbUsers with a passowrd field. But, now I have to trasnform them to hash MD5, so far so good for the hash. But, how can I select all passwords recorded and update them with its hashvalue? I don't really need the hashvalue part, just the se...
I have VS 2008 Pro (not VSTS DB edition) installed on my desktop and laptop, both running Windows 7, with SqlServer. When creating a Database Project on my desktop machine, the project structure includes a folders for. Database References which is crucial to the utility of a database project However on my laptop, when creating a databa...
CREATE PROCEDURE [dbo].[PL_GEN_PROVN_NO1] @GAD_COMP_CODE VARCHAR(2) =NULL, @@voucher_no numeric =null output AS BEGIN DECLARE @NUM NUMERIC DECLARE @PNO NUMERIC SET @PNO = 0 DECLARE @PNO1 NUMERIC SET @PNO1=0 -- begin transaction IF NOT EXISTS (select GLDC_...
Typically when you specify an identity column you get a convenient interface in SQL Server for asking for particular row. SELECT * FROM $IDENTITY = @pID You don't really need to concern yourself with the name if the identity column because there can only be one. But what if I have a table which mostly consists of temporary data. Lots...
i have a table in SQL Server 2008 which its name is Table1. Table1 has a column named CreateDate which its data type is datetime. Now, I wanna to get records that their createDate field values are more than for instance 1 hour. ...
I want to execute this query : INSERT INTO [Order] (FactorId, ProductId, Entity) VALUES((SELECT Top 1 FactorId FROM Factor WHERE UserId = @UserId AND Status = -1), @ProductId, @Entity) but the following error occurs : Subqueries are not allowed in this context. Only scalar expressions are allowed. ...
So I have been working with multiple different MS Access apps at work (about 8) for different departments. And it looks as though there are going to stand up a SQL Server at work, which makes sense from a capacity/integrations standpoint. Soooo......I have experience with Access, SQL and VBA, but having never used any of it in a SQL Ser...
How can I build a query statement like this: select * from men where Tdate between 01/01/01 and 02/02/02 The Tdate column is of type nvarchar I am working with SQL Server Compact Edition (sqlCE) Thanks ...
I have a NAnt-based script which that I run on my local PC that connects to SQL Server 2008 Express also running on my local PC to drop and recreate databases using .sql files - this works fine, no problem here. The problem comes when I have recreated the same set-up on another PC, I get the error in my NAnt script saying that: Syst...
Hello everyone, I am using SQL Server 2008 Enterprise + .Net 3.5 + C# + ADO.Net. I am using the following SQL statement to monitor connection number, is it correct? If yes, my confusion is, one connection from ADO.Net client maps to only one connection in the following statement? Or one ADO.Net connection could maps to multiple connecti...
I've got a stored procedure which runs this to create a new ticket number: INSERT INTO [Test_DB42].[dbo].[TicketNumber] ([On], [By]) VALUES (CURRENT_TIMESTAMP, CURRENT_USER) When I run with a user with db_datareader and execute permissions, I get the Active Directory samAccountName value which is what I want - this user gets a...
Using SQL 2000, SQL 2005 Old Database Name is – Sysdatabase New Database Name is - Dual_Proone, Dual_Protwo In the above two database table name and column name are different, but values are same. For Example Sysdatabase (Database Name) Person (Table Name) Column Names and values are ID Date 001 23-02-2009 002 24-02-2009 So o...
Using SQL Server 2000 I make a trigger in new database CREATE TRIGGER [CARDEVENTSAVING] ON [dbo].[T_CARDEVENT] AFTER INSERT AS DECLARE @EDATE VARCHAR(50) DECLARE @ETIME VARCHAR(50) DECLARE @EDOOR NVARCHAR(50) DECLARE @EFLOOR NVARCHAR(50) DECLARE @EMPID NVARCHAR(50) DECLARE @TAG NVARCHAR(50) DECLARE @ENAME NVARCHAR(50) DECLARE @ELNA...
How to retrieve the names of all Nonsystem-databases from SQL Server 2000 using a TSQL query? I have anticipated: SELECT * FROM sysdatabases where dbid >4 order by dbid it does not seem to be reliable. Anything else? ...
I have a table with an Identity column which provides my ticketNumber. I want another table to provide a ticketStepNumber. A ticket may have 0 or more steps, so I won't always be creating a ticketStepNumber. The ticketStepNumber value is a combination of the ticketNumber column (int) and the stepNumber column (int). I'd like to def...
With the help of others on SO I've knocked up a couple of Tables and Stored Procedures, this morning, as I'm far from a DB programmer. Would someone mind casting an eye over this and telling me if it's thread-safe? I guess that's probably not the term DBAs/DB developers use but I hope you get the idea: basically, what happens if this s...
Using SQL Server 2000: SELECT PERSONID, CARDEVENTDATE, INTIME, CASE WHEN OUTTIME = INTIME THEN 'No PunchOut' ELSE OUTTIME END AS OUTTIME, CONVERT(char(8), CASE WHEN DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) > '18:00:0...