Ok, so here's the thing. I'm developing an existing (it started being an ASP classic app, so you can imagine :P) web application under ASP.NET 4.0 and SQLServer 2005. We are 4 developers using local instances of SQL Server 2005 Express, having the source-code and the Visual Studio database project This webapp has several "universes" (t...
I am trying to understand what is going on with CREATE INDEX internally. When I create a NONCLUSTERED index it shows as an INSERT in the execution plan as well as when I get the query test. DECLARE @sqltext VARBINARY(128) SELECT @sqltext = sql_handle FROM sys.sysprocesses s WHERE spid = 73 --73 is the process creating the index SELECT ...
Hi Based on the following table Path ---------------------- area1 area1\area2 area1\area2\area3 area1\area2\area3\area4 area1\area2\area5 area1\area2\area6 area1\area7 Input to my stored procedure is areapath and no.of children (indicates the depth that needs to considered from the input areapath) areapath=area1 children=2 Above sh...
StringBuilder query = new StringBuilder(); query.Append("CREATE TABLE #Codes (Code nvarchar(100) collate database_default ) "); query.Append("Insert into #Codes (Code) "); int lengthOfCodesArray = targetCodes.Length; for (int index = 0; index < lengthOfCodesA...
I have an INSTEAD OF trigger on a table in my SQL Server 2005 database that checks several incoming values. If an incoming value is invalid, an error is raised and the transaction is rolled back. Otherwise the record is inserted. I would like to include a TSQLUnit test of this trigger where, if an invalid value is inserted, having the...
Hi For the below data (well..there are many more nodes in the team foundation server table which i need to refer to..below is just a sample) Nodes ------------------------ \node1\node2\node3\ \node1\node2\node5\ \node1\node2\node3\node4\ \node1\node2\node3\node4\node5\ I was wondering if i can apply something like (below query does n...
Hi, I have the following Table definition with sample data. In the following table, Customer Product & Date are key fields Table One Customer Product Date SALE X A 01/01/2010 YES X A 02/01/2010 YES X A 03/01/2010 NO X A 04/01/2010 NO X ...
I used the Redgate packager (ran MSI) to reset all the data in my database (i.e. I deleted everything, and let it build the new database). Unfortunately, I discovered that it didn't retain my diagrams, which has a nice arrangement and several annotations. Is there any way to copy/migrate/script the diagram from one database to anothe...
I’m trying to display a list of shops each with 3 random items from their shop, if they have 3 or more listings, that are actively advertising. I have 3 tables: one for the shops – “Shops”, one for the listings – “Listings” and one that tracks active advertisers – “AdShops”. Using the below statement, the listings returned are random ho...
How do I do this SELECT CEILING(COUNT(*) / 10) NumberOfPages FROM MyTable in Linq to SQL? ...
Store a byte[] stored in a SQL XML parameter to a varbinary(MAX) field in SQL Server 2005. Can it be done ? Here's my stored procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddPerson] @Data AS XML AS INSERT INTO Persons (name,image_binary) SELECT rowWals.value('./@Name', 'varchar(64)') AS [Nam...
I have created a SSIS-package that imports a file into a table (simple enough). I have some variables, a few set in a config-file such as server, database, importfolder. at runtime I want to pass the filename. This is done through a stored procedure using dtexec. When setting the paramters throught the configfile it works fine also when...
I am struggling understanding what a clustered index in SQL Server 2005 is. I read the MSDN article Clustered Index Structures (among other things) but I am still unsure if I understand it correctly. The (main) question is: what happens if I insert a row (with a "low" key) into a table with a clustered index? The above mentioned MSDN a...
I would like to get a list of Active Directory users along with the security groups they are members of using SQL Server 2005 linked servers. I have the query working to retrieve records but I'm not sure how to access the memberOf attribute (it is a multi-value LDAP attribute). I have this temporary to store the information: DROP TABLE...
To display data on google maps, I have >50k rows of data in SQL server 2005 with the following (simplified) structure PointID Latitude Longitude TypeID I can select a small subset within a bounding box, ensuring a decent mix of Types like this: ..... ( @NELat float, @NELong float, @SWLat float, @SWLong float ) as select top 100 Poin...
Using the following code to compute MD5 hashs of files: Private _MD5Hash As String Dim _BinaryData As Byte() = New Byte(FileUpload1.PostedFile.InputStream.Length) {} FileUpload1.PostedFile.InputStream.Read(_BinaryData, 0, _BinaryData.Length) Dim md5 As New System.Security.Cryptography.MD5CryptoServiceProvider Dim md5hash() As Byte...
Hi Based on following TableA Data -------- Dummy1 Dummy2 Dummy3 . . DummyN is there a way to generate sequence number while selecting rows from the table. something like select sequence() as ID,* from Data that will give ID Data --------- 1 Dummy1 2 Dummy2 3 Dummy3 .... N DummyN Thanks. ...
I'm trying to call a parameterized stored procedure from SQL Server 2005 in my C# Winforms app. I add the parameters from TextBoxeslike so (there are 88 of them): cmd.Parameters.Add("@CustomerName", SqlDbType.VarChar, 100).Value = CustomerName.Text; I get the following exception: "System.InvalidCastException: Failed to convert parame...
Hi! I've got a problem with multiple deadlocks on SQL server 2005. This one is between an INSERT and a SELECT statement. There are two tables. Table 1 and Table2. Table2 has Table1's PK (table1_id) as foreign key. Index on table1_id is clustered. The INSERT inserts a single row into table2 at a time. The SELCET joins the 2 tables. (it'...
i have created views for my project now i want to optimize them for the speed purpose...how can i identify that the view can be optimize? is index usefull for this.... let's say the following example... SELECT dbo.vw_WebInventory.skref AS SaleID, dbo.vw_WebInventory.lot_number AS LotNumber, dbo.vw_WebInventory.Description, ...