Hi all, I'm having some trouble with the following sproc Create PROCEDURE GetMatchingUsers @id int = NULL, @lastName varchar(50) = NULL, @firstName varchar(50) = NULL AS BEGIN SET NOCOUNT ON DECLARE @q nvarchar(4000), @paramlist nvarchar(4000) SELECT @q = 'SELECT Id , LastName , FirstName ' SELECT @q = @q + 'FROM User...
I have a table with multiple records, each contains a field called "coords". This field has been updated with a geography point. UPDATE testing SET [coords] = geography::Point(52.029736, -113.973541, 4326) WHERE id=2" What I need to do is... when a user is logged in, they have a record that belongs to them, for this example says its r...
I have a hierarchy table with the hierarchy (Schools under different owners). I have another hierarchy table (Departments under different schools) both has diff path. But how to relate both the table to get the Different departments under diff schools and diff owners. how to relate both the tables..... ...
Hi, I want to drop a lot of SPs from my SQL Server database. However, I want to write "Delete all procedures except those which contain a certain string in their name". How can I do this? I am using SQL Server 2008 R2. Thank ...
i have this table of comments (little over 1 milion rows) that gets around 10.000 inserts and around 100.000 queries against it every day, and minor deletions and updates. the query that gets the comments causes performance issues that sometimes it locks up entire database and i am getting a lot of timeouts. please help me adjust my inde...
I am trying to build a query, but I am having some difficulty. I have a SQL Server 2008 database with a table that includes, among other fields, a geography field that describes road segments. (This data has been imported from TIGER/Line data from the U.S. Census.) I have another fixed point describing a user's location. I want to find...
Hi. I have an application in which the user can select a location and view it's distance from several Points Of Interests (POIs). When I retrieve these distances, I would also like to retrieve the ID's of the locations that are the next closest and the next furthest away from each POI. eg. If we have 10 locations, each of them a mile f...
I want to create a stored procedure to do some combined keyword search using CONTAINS,something like below: SELECT theContent FROM FtsTest WHERE CONTAINS (theContent, ' FORMSOF (INFLECTIONAL, keyword1) AND FORMSOF (INFLECTIONAL, keyword2)'); and he number of keywords may vary, so I tried to pass the whole 'FORMSOF... AND FORMSO...
I have deploy my code in visual studio .NET 2010 in order to select images and save them into a SQL server (remote) 2008 express When i deploy my tables as: [dbo].[tablename] then at the last command of my code which is: sqlFile = New SqlFileStream(filePathName, fileToken, FileAccess.Write) I receive the error of 'Access denied' When...
Hi, I'm doing data migration between two SQL Server 2008 database using SSIS because I need to do some data transformation. I guess I need to use Business Intelligence Developement Studio which I have on my develop machine. But because I don't have SQL Server Standard Edition on my develop machine. The BIDS do not allow me to build the ...
Hello, I was wondering how sql server sorts it's data. I noticed that if I have a table that doesn't contain the column "Id" and you select data without "ORDER BY" sql server doesn't automatically sort on the primary column. Does anyone know what rule sql server follows to sort it's data? Regards, M. ...
I wonder if type of a column matters in terms of sorting performance. I have heard that int columns are sorted faster than float columns. Do you think it is correct? ...
This has been bugging me for a while and I'm hoping that one of the SQL Server experts can shed some light on it. The question is: When you index a SQL Server column containing a UDT (CLR type), how does SQL Server determine what index operation to perform for a given query? Specifically I am thinking of the hierarchyid (AKA SqlHierar...
In MSSQL I have a table created like this: CREATE TABLE [mytable] (fkid int NOT NULL, data varchar(255) CONSTRAINT DF_mytable_data DEFAULT '' NOT NULL); ALTER TABLE [mytable] ADD CONSTRAINT PK_mytable_data PRIMARY KEY (fkid, data); Now I want to increase the length of the 'data' column from 255 to 4000. If I just try: ALTER TABLE [...
Dumb question - what's the best way to copy instances in an environment where I want to refresh a development server with instances from a production server? I've done backup-restore, but I've heard detach-copy-attach and one guy even told me he would just copy the datafiles between the filesystems.... Are these the three (or two, th...
My company is in the process of moving a database from SQL 2000 to SQL 2008. I am in the process of testing some of our legacy applications and came across a VB6 app that is running into a bug. The code is attempting to run a simple update for a record and check that the Execute method has affected more then one row using the RecordsAf...
Creating Comma Separated Lists In SQL Hi All, I am attempting to merge something like this in my SQL Server database: [TicketID], [Person] T0001 Alice T0001 Bob T0002 Catherine T0002 Doug T0003 Elaine Into this: [TicketID], [People] T0001 Alice, Bob T0002 Catherine, Doug T0003 ...
is there a sample database for SQL Server 2008 that I can download to practice learning how to develop/use SQL Server...I have downloaded the SQL Server 2008 Express Edition, Visual Studio 2008, MS Web Dev 2008, ASP.NET 3.5..... ...don't think I can download anything else before I start learning how to use this stuff. Thanks guys... p...
i have two hierarchy tables, how to link these two tables using hierarchyid. whether it is possible to link using, deptpath hierarchyid, HID as (previoustablename). deptpath.GetAncestor(1) persisted or any other suggestion.. ...
Hi, all Is that possible for BIDS to build a SSIS package on remote SQL Server instance? Since I only have SQL Server Standard Edition on the production server. BTW, when I use BIDS to build a Integration Service Connection Project, it throws a exception like this: exception of type 'System.Exception' was thrown And nothing is created....