I'd like to know what application connected to DB and executing SPs. (I want to limit SP execution to only my App - all other - eg MS SSMS would be ignored) So is there a way to find out connected client name? If not maybe you have other suggestion how to ensure only dedicated app is used (App is using Windows' integrated security) ED...
I have a hierarchical tree table structure .How can i get the left tree and right tree . 1 a NULL 2 b 1 3 c 1 4 d 2 5 e 2 6 f 3 7 g 3 8 h 4 9 i 4 10 j 5 11 k 5 12 l 6 If i have the id of a ie 1 .how can i get the tree of b and c i am expecting the tree under b as 2 b 4 d 5 e 8 h 9 i 10 j 11 k ...
I have a flat data file that I need to import into my SQL Server 2005 DB. Many of the fields need to be split off into different, related tables. For example, the flat file has names, addresses and telephone numbers, all in one record. In my DB, the Person table has many Telephones and Addresses. Is there a one-step process whereby I...
A few months ago Oracle queries stopped working on our reporting services server after a service pack on the reporting server. I ended up having to grant permissions on Oracle directories to eliminate the first error and then renamed the NLS_LANG registry entry (effectively deleting it) to get rid of the second error. The old value was...
Hello, I have multiple databases using merge replication with , @publication_compatibility_level = N'90RTM', @replicate_ddl = 1. I have a backup job on the publisher database that does a full backup nightly. I have another job that backs up the transaction log every 3 hours and runs DBCC SHRINKFILE (N'TheDatabaseName_Log' ) on the publ...
I have a bit of a conundrum I need to deal with, and I'm trying to find a good solution to it. I'm using MS SQL server 2005, and given a table where a uniqueidentifier column contains a data key to other information on the server. What I need to do is select every row in this datatable (sproc from ASP.NET), but separated into tables ba...
I am building a view in SQL Server 2000 (and 2005) and I've noticed that the order of the join statements greatly affects the execution plan and speed of the query. select sr.WTSASessionRangeID, -- bunch of other columns from WTSAVW_UserSessionRange us inner join WTSA_SessionRange sr on sr.WTSASessionRangeID = us.WTSASessionRang...
Setup: Windows Server 2003 / SQL Server 2005. ASP.NET 2.0. IIS 6. The site I'm working on uses ASP.NET Membership and when a user is created it is inserted into the aspnet_membership database. All servers are set at Central Time. I also verified this by doing a Select GetDate() on SQL Server and it returned central time. However, w...
We are having a problem with Visual Studio 2008 Professional, where opening a SQL express database from Solution explorer only opens the file in binary form. We thought that the wrong application might be set, but using open with and selecting SQL express bootstrapper still yields the same result. We can open the database file if we us...
I have a .csv file (semicolon separated) that I am trying to import into an SQL Server 2005 database using the Import and Export Wizard. Some of the entries in the .csv file are blank (i.e. two separators with no characters in between), and these should translate to NULL values in the destination table. The relevant fields are nullable....
I want to delete many rows with the same set of field values in some (6) tables. I could do this by deleting the result of one subquery in every table (Solution 1), which would be redundant, because the subquery would be the same every time; so I want to store the result of the subquery in a temporary table and delete the value of each r...
I'm running a SQL Server 2005 Standard. I got several alerts I need to react on. So I created these alerts, but they never appear so that the jobs depending on them are never run. I found out that for alerts it is necessary that the SQL Server writes to the Windows Event Log, but this is not happening at all. Every source tells me that ...
If my table inside the database look like: userid uniqueidentifier username varchar(20) password varbinary(max) When the user submit(to register), I send the user/pass to a stored procedure. The stored procedure create a new GUID(Using NEWID()) then I use the HashBytes(sha1) function of SQL Server to create the password based on t...
So I've got a SQL statement that pared down looks something like this: SELECT column FROM table t (nolock) LEFT OUTER JOIN table2 (nolock) t2 on t.id = t2.id This statement works on my SQL 2005 and SQL 2008 environments. It does not on a remote SQL 2005 environment. I've switched the last line to: LEFT OUTER JOIN table2 t2 (nolock) o...
I tried to perform the following in order to update a psuedo-identity value at the same time as using the value to create new rows, but APPLY does not like UPDATE statements as the right table source. What's the most elegant alternative outside of simply using an identity column? create table Temp1( id int not null identity(1,1) pr...
Hello gurus, I have a stored procudre (SP) as follows: CREATE PROCEDURE [dbo].[UDSPSelectMissing] -- paramters omitted AS BEGIN -- Code Omitted SELECT c.MemberID, c.FirstName, c.MiddleName, c.LastName, c.Suffix, c.PhoneHome, c.PhoneCell, c.Email FROM [dbo].[Members] c WHE...
I have an existing SQL 2005 stored procedure that for some reason, outputs its results in the Messages pane in SSMS instead of the Results pane. (Its actually a CLR procedure already compiled and deployed to all our servers, and used for another daily process. So I can't change it, I just want to use its output.) For the sake of discuss...
Hi, I am having a problem with my SQL Server 2005 database. The database must handle 1000 inserts a sec constantly. This is proving to be very difficult when the database must also handle reporting of the data, thus indexing. It seems to slow down after a couple of days only achieving 300 inserts per sec. By 10 days it is almost non func...
I have to copy a bunch of data from one database table into another. I can't use SELECT ... INTO because one of the columns is an identity column. Also, I have some changes to make to the schema. I was able to use the export data wizard to create an SSIS package, which I then edited in Visual Studio 2005 to make the changes desired and w...
I'm having a problem with a transaction in linq-to-sql timing out after 30 seconds. Setting the CommandTimeout property on the DataContext does not seem to change the timeout. The error message I get is: "Timeout Expired. The timeout period elapsed prior to the completion of the operation or the server is not responding" Queries that...