I have a SQLServer with a linked server onto another database somewhere else. I have created a view on that linked server create view vw_foo as select [id], [name] from LINKEDSERVER.RemoteDatabase.dbo.tbl_bar I'd like to to the following alter table [baz] add foo_id int not null go alter table [baz] with check add constraint [fk1_...
Define a couple of points as follows: declare @p1 geography, @p2 geography set @p1 = 'POINT(1 2)' set @p2 = 'POINT(6 8)' Now I'd like to obtain the shortest line between these two points. What function can I use to get this line? (i.e., it should output a LINESTRING(1 2, 6 8) or LINESTRING(6 8, 1 2)) I know I could do this by formatt...
I'm working on a statement scanner for our updater (looking for statements that will cause problems with synchronized data) and I need to know which TSQL data types get resolved as Types.VARCHAR and which ones resolve to Types.LONGVARCHAR when you invoke DatabaseMetaData.getColumns()? ...
This is probably quite basic, so bear with me (on the other hand there's probably a nice shiny cut-and-dried answer!). I'm diagnosing a deadlocking problem at the moment, and indeed I can see that one of my sessions is being blocked by another. (The other end of the deadlock are Java threads waiting on each other in the opposite order....
Hi all. I am using SQL Server 2005. I am trying to join 2 tables together, but only when a column value in the main table is true. Like this: select * from T1 join T2 on T1.value = T2.value where T2.value2 = 'variable2' and T2.value3 = 'variable3' There is a coulumn value in T1 which says if I have to use the values in T2. I could to...
For my asp.net web applications against sql server(at least the ones that require a logon to access) I generally implement security as follows: I generally roll my own user signup, user login pages and keep a userid and an encrypted password in an sql server and validate the login against that table - I also provide for forgotten passwo...
If all my sql server database access is done thru stored procedures, and I plan on continuing that practice, is using linq2sql and/or the entity framework for future projects an unnecessary layer of complexity that doesn't add much value? Related question: is Microsoft trying to steer developers away from relying on stored procs for dat...
How do you find out how many store procedures that you have in the database and is it a bad practice to have too many store procedure? how many are too many? ...
I'm looking to take the results of a report run (a PDF file from Crystal Reports), serialize it, stick it into a varbinary field, and then later be able to deserialize it and present it back to the user. For now I have to just plain old ADO .NET (SqlClient, SqlCommand, etc.) Are there any pitfalls to this? What is the basic syntax to a...
Hi, I need to store binary files to the SQL Server Database. What's the better Data Type between Varbinary and Image to ? ...
I've got ~15k rows in MSSQL 2005 that I want to migrate into CouchDB, where 1 row = 1 document. I have a CLR-UDF that writes n rows to an schema-bound XML file. I have an XSL transform that converts the schema-bound XML to JSON. With these existing tools I'm thinking I can go MSSQL to XML to JSON. If I batch n rows per JSON file, I...
Hi I am looking for SQL Server Profiler in SQL Server 2008 express edition.I have an instance of SQL Server 2008 running in my machine but I want the profiler too.Does it come with the installation or do I have to install it separately? ...
Nearly identical to http://stackoverflow.com/questions/85978/query-a-tables-foreign-key-relationships, but for SQL Server 2000 For a given table 'foo', I need a query to generate a set of tables that have foreign keys that point to foo. ...
How do I clear the value from a cell and make it NULL? ...
So using the code below... can I parse @xml_data into a table structure without predefining the structure? DECLARE @receiveTable TABLE(xml_data XML) DECLARE @xml_data XML DECLARE @strSQL NVARCHAR(2000) SET @strSQL = 'SELECT * INTO #tmp1 FROM sysobjects; DECLARE @tbl TABLE(xml_data xml); DECLARE @xml xml; Set @xml = (Select * from #t...
I have an application which is connected to a database through a spring.net AdoTemplate. I am charged with creating a restore database method which keeps the app running but drops the network connections so as to drop the old database and bring up the new one. My question is how do I drop all the current connections that this applicati...
Background: We are running a web application where each user has a login to the system. The application login is mapped to an actual SQL Server 2005 login (which we needed to create). Our development and disaster recovery sites are simply copies of this setup. On a nightly basis, the production database is backed up, the dump is archi...
I have a cube in SSAS 2005 that calculates running sums over millions of records. The current implementation is using a PeriodsToDate sum function in MDX and it is horribly slow, taking 15 minutes or more to return results in some cases. There are a huge number of solutions to this problem so I am hoping someone else has already tested...
I've inherited a (Microsoft?) SQL database that wasn't very pristine in its original state. There are still some very strange things in it that I'm trying to fix - one of them is inconsistent ID entries. In the accounts table, each entry has a number called accountID, which is referenced in several other tables (notes, equipment, etc. ...
I want to know if a process is running in the current machine as a part of my application ,but my application is deployed in another machine. I'm using System.Diagnostics.Getprocessbyname but it looks for processes on the machine the application is deployed to. I want to know whether the process is running on my machine. Is there a way...