Hi, I am using the following code to check if the temp table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, it will give error saying "invalid column". Please let me know what I am doing wrong. IF OBJECT_ID('tempdb..#Results') IS NOT NULL D...
I have the following TSQL codes: -- 1. define a cursor DECLARE c_Temp CURSOR FOR SELECT name FROM employees; DECLARE @name varchar(100); -- 2. open it OPEN c_Temp; -- 3. first fetch FETCH NEXT FROM c_Temp INTO @name; WHILE @@FETCH_STATUS = 0 BEGIN print @name; FETCH NEXT FROM c_Temp INTO @name; -- fetch again in a loop END -- 4...
Good day, In SQL Server 2005, I have a table numerous columns, including a few boolean (bit) columns. For example, table 'Person' has columns ID and columns HasItem1, HasItem2, HasItem3, HasItem4. This table is kinda large, so I would like to create indexes to get faster search results. I know that is not I good idea to create an index...
Hello Friends, I am using Asp.Net c# and Sql Server 2005. I am using Masterpage and content page. when i debug my code that time it's give error :: Window Internet Explorer SYS.WEBFORMS.PAGEREQUESTMANAGER TIMEOUTEXCEPTION: THE SERVER REQUEST TIMED OUT. Any body please help me out ? Thanks ...
I have an application with some sql queries in a class, each query inside stringBuilders.. I've made another application to extract the query from each string builder parsing the code. The point is: I need to generate an execution plan for each one of this queries. Is there any way to do this automatically without coping and pasting t...
We have a couple of SQL Server instances at our main office, and one on our colocated web server. There are a few replications that handle data exchange between the web server and the main office servers. We switched ISPs today at our main office. We did our homework and were ready for the switch (ips in hosts files changed, etc...) ...
I have a table MyTable with a trigger defined like this: ALTER TRIGGER [MyTableInsertDeleteUpdate] ON [dbo].[MyTable] AFTER INSERT,DELETE,UPDATE AS DECLARE @id int; BEGIN SELECT @id = ins.id FROM inserted ins; IF (@id IS NOT NULL) BEGIN -- insert a new record to audit table PRINT 'inserted/updated id: ' + CAS...
All of the documentation on SQL Server deadlocks talks about the scenario in which operation 1 locks resource A then attempts to access resource B and operation 2 locks resource B and attempts to access resource A. However, I quite often see deadlocks between a select and an update or even between multiple selects in some of our busy ap...
We have a cube that we are populating the data from the source tables. To get the data to reflect we are calling the "Analysis services processing task" component. While the cube is being refreshed we are getting the following error. Description: OLE DB error: OLE DB or ODBC error: Communication link failure; 08S01. End Error Error: 2...
I've been using this for some time: SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col)) However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to match. An alternative technique I've seen is to use TRIM: REPLACE(LTRIM(REPLACE(str_col, '0', ' ')...
I am trying to insert a row in a table using simple INSERT Query in a transaction. It works fine in SQL Server but I am not able to insert the data using my business object. I am calling a SELECT query using the Command as: Using cm As New SqlCommand With cm .Connection = tr.Connection .Transaction = tr .Com...
I have the following example table: Dt Value1 Value2 Value3 ... 2008-12-01 12:34:00 100.1 0.123 43 .... Is there any way by using TSQL to generate trend graphics as image such as jpg? Or do I need Reporting service to do it? I need to do it TSQL so that the daily trend images can be generated in a sche...
I've query executing ~2 secs in MSSMS (returning 25K of rows) Same query used in .NET (sqlReader) exetuting few minutes! I've also tried to execute only reader (commented all code in while loop just leaving reader.Read() ) - still same! Any idea what's up? ...
How are CLR (.NET) objects managed in SQL Server? The entry point to any CLR code from SQL Server is a static method. Typically you'll only create objects that exist within the scope of that method. However, you could conceivably store references to objects in static members, letting them escape the method call scope. If SQL Server ret...
Hi Guys, I have some SQL from our DBA which I'm just checking it is right, since the EF doesn't seem to fully link entities together. It knows there is a relation but doesn't perform the FK to PK link. Any ideas or thoughts (rather than use NHibernate! ) on it are appreciated. 1 CREATE TABLE [dbo].[Employee]( 2 [ID] [int] I...
Hi there. I'm experimenting with CTE's in SQL Server but have reached a dead end with getting the following scenario to work. I have a hierarchy table similar to this: Node(ID:439) Node(ID:123) Node(ID:900) Node(ID:56) Node(ID:900) Expected results: NodeID ParentNodeID 439 0 123 439 900 123 56 439 90...
I have a database in which the main table has a really badly named primary key field which I want to rename. This primary key field is referenced by about 20 other tables and about 15 stored procedures. What is the easiest way to rename this field everywhere it is referenced throughout the database? ...
Database: MS SQL 2005 Table: EmployeeNumber | EntryDate | Status Sample Data: 200 | 3/1/2009 | P 200 | 3/2/2009 | A 200 | 3/3/2009 | A 201 | 3/1/2009 | A 201 | 3/2/2009 | P Where P is present, A is absent. I have tried row_number over partion. But it does not generate the sequence which I expect. For the above data the sequence...
I have a typical table of hierarchical data in id, parentId form. CREATE TABLE Hierarchy (Id int, ParentId int NULL, Name varchar(128)); INSERT INTO Hierarchy VALUES (1, NULL, '1'); INSERT INTO Hierarchy VALUES (2, NULL, '2'); INSERT INTO Hierarchy VALUES (3, NULL, '3'); INSERT INTO Hierarchy VALUES (4, 1, '1.1'); INSERT INTO Hierarchy ...
how do you create a new database user with password in sql server 2005? i will need this user/password to use in the connection string eg: uid=user;pwd=password; ...