I am learning SQL and am trying to learn JOINs this week. I have gotten to the level where I can do three table joins, similar to a lot of examples I've seen. I'm still trying to figure out the tiny details of how things work. All the examples I've seen of three table joins use INNER JOINS only. What about LEFT and RIGHT JOINs? Do ...
I have two sql server tables with the same structure. In a stored procedure I have a Select from the first table. Occasionally I want to select from the second table as well based on a passed in parameter. I would like a way to do this without resorting to using dynamic sql or temporary tables. ...
My SQL Server Management Studio suddenly went case sensitive on me. The database and server are both set to case insensitive SQL_Latin1_General_CP1_CI_AS I run queries like Select * From mytable and I get "invalid object name" but if i run select * from MyTable i get data!! I created a new database and created a dummy table a...
I have a table in SQL Server consisting of two columns that track how many users are logged into a system at a particular interval. The first column is Time15, which is the time rounded to the nearest 15 minute interval (datetime). The second column is UserCount, which is the number of users logged into the system during that 15 minut...
I have a table like this: Date StudentName Score 01.01.09 Alex 100 01.01.09 Tom 90 01.01.09 Sam 70 01.02.09 Alex 100 01.02.09 Tom 50 01.02.09 Sam 100 I need to rank the students in the result table by score within different dates, like this: Date ...
Suppose I have these two tables: Invoice ------- iInvoiceID int PK not null dtCompleted datetime null InvoiceItem ----------- iInvoiceItemID int PK not null iInvoiceID int FK (Invoice.iInvoiceID) not null dtCompleted datetime null Each InvoiceItem might be fulfilled by a different process (executable) that runs on a different machine...
How can I stop a particular user from accessing the database for a period of time but at the same time not lose the permissions the user has on db objects. Basically when he comes back (ie when access is given back) he should have all the permissions he had before his access was cut off. If I use sp_revokedbaccess 'myuser' the user myu...
When I use SSMS to script a stored procedure, it wraps the script in a sp_executesql statement which bugs me. Is there a way to stop SSMS from doing this and use a straight CREATE PROCEDURE...? EDIT: I meant SSMS not SSIS ...
I'm a Sql Server Service Broker novice and I'm trying to grasp the best way to set Service Broker up for a (seemingly) simple use case: I want to create a simple work queue, where one application drops work items into the queue, and separate application picks up work items from that queue and processes them. There is no need for the firs...
i have a view that is using linked server to retrieve data from a remote server in SQL Server. On each time viewing the view, the results returned are vary. For example, 1st time execution may return 100 rows of records but on 2nd time of execution, rows returned are 120 rows. Any ideas what is the cause? ...
I have few fields(empid,arrivaltime) in sqlserver. I want to get the query output as follows 79 08/11/2009 3:21PM 78 08/11/2009 3:19PM 98 08/11/2009 9:02AM 97 08/11/2009 9:00AM 96 08/11/2009 8:56AM 95 08/11/2009 8:53AM please give me sql query for this. ...
Reference: SQL Server I have a stored procedure with a while loop in it and I want some messages to be printed after every 500 loops. So, I've written - CREATE spxxx AS BEGIN BEGIN TRAN DECLARE @counter = 0; WHILE <somecondition> SET @counter = @counter + 1; IF @counter % 50 = 0 ...
Hi All, I have the following search sql query: ALTER PROCEDURE [EmployeeSearch] @PayrollNumber varchar(50) = null, @isInternal bit = null, @Firstname varchar(50) = null, @Surname varchar(50) = null AS SET NOCOUNT ON; Select EmployeeSearchView.* From EmployeeSearchView Where EmployeeSearchView.isInternal = @isInternal AND (Employ...
I've read --while trying to figure this out-- that with temporary tables there's the notion of a session in SQL Server. However, I have not been able to find any documentation regarding what exactly constitutes as a SQL Server session. With this information, I'd like to implement some basic logging functionality. My problem is that I ...
Hi All, I'm trying to understand a historical stored procedure I need to fix. I found this DRVTBL key word, and I couldn’t find out what it means??? (This is a sql2000 database) SELECT ... FROM ( ...) ) DRVTBL ...
I am dealing with an interesting situation where I perform many database updates in a single transaction. If these updates fail for any reason, the transaction is rolled-back. IDbTransaction transaction try { transaction = connection.BeginTransaction(); // do lots of updates (where at least one fails) transaction.Commit();...
How to set the Password for sql server 2005 MDF file. Becoz i want to give the trail package to the client,package including the MDF. After installing the package, the MDF will be placed in C drive, user data will store in MDF file through the application. but not allow to attach that MDF file using sql server in that system. ...
I have quite a few stored procedures following the pattern of selecting the row for which a date column is the latest up to a certain date, inclusive. I see two forms in use: select top 1 x, y, z from sometable where a=b and date <= @date order by date desc or select x, y, z from sometable where a=b and date=(select max(date) from so...
I need to store data in a SQL Server 2008 database from various data sources with different data types. Data types allowed are: Bit, Numeric (1, 2 or 4 bytes), Real and String. There is going to be a value, a timestamp, a FK to the item of which the value belongs and some other information for the data stored. The most important points...
So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a 'running total' into a third temp table which is created using CREATE. Then this resulting temp table is joined with other ...