With SQL Server 2005 and transactional replication, can I remove the primary key constraints on the subscriber, while leaving the primary key constraints on the publisher? Primary I want to do this because I want to cluster on different columns than the existing clustered constraints. I don't think I can convert a constraint from clust...
I'm working on some upgrades to an internal web analytics system we provide for our clients (in the absence of a preferred vendor or Google Analytics), and I'm working on the following query: select path as EntryPage, count(Path) as [Count] from ( /* Sub-query 1 */ select pv2.path from pagevi...
I'm trying to use System.Transaction.TransactionScope to create a transaction to call a few stored procedures but it doesn't seem to clean up after itself. Once the transaction is finished (commited or not and the transaction scope object is disposed) subsequent connections to the database open up with the read commit level of serializab...
I have a MS SQL DB with various tables, and one field in particular is causing me grief. The Data type is set to varchar(100), however the field is limited to 60 characters. If I try to put any string with more than 60 characters into the field I get an exception, "String or binary data would be truncated". Although the string is short...
I need to update a record in a database with the following fields [ID] int (AutoIncr. PK) [ScorerID] int [Score] int [DateCreated] smalldatetime If a record exists for todays date (only the date portion should be checked, not the time) and a given scorer, I'd like to update the score value for this guy and this day. If the scorer do...
I'm asking myself if it is possible to check if in ADO.NET the current transaction can be rolled back. The msdn suggests the following implementation: private static void ExecuteSqlTransaction(string connectionString) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); ...
I want to create a linked server in one sql server to another using the sp_addlinkedserver procedure. When i access the remote server I would like it to logon as me (i.e. using my windows account). How do I do this? ...
So I was thinking about creating a dynamic sql question, meaning that i want the amount of parameters to be dynamic. Having looked at this: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause#337725 i was thinking that using like '%x%' is SLOW and not good. What i actually would like to do is using the IN keyword a...
Hello, I want to find a way to allow our reporting guy to export data returned from a stored procedure into a CSV file. The procedure will need a (date) parameter passing to it. Is there a data export wizard I can get him to use, or some other user friendly way in SQL2005 that he can generate the CSV (after providing some parameters)....
I need to update a group of cells by inserting the same two characters into all of them, but I'm just drawing a blank on how to do this. Could someone point me in the right direction? Old Cells HI.1 HI.2 HII.1 New Cells H08I.1 H08I.2 H08II.1 ...
Not so much a question as an observation... I'm just upgrading to SQL Server 2008 on my development machine in anticipation of upgrading my live applications. I didn't anticipate any problems since [I think] I generally use standard T-SQL, and probably not too far from ANSI standard SQL. So far so good, but I was really thrown by a very...
In postgres you can do a comparison against multiple items like so: SELECT 'test' IN ('not','in','here'); Which is the same as doing: SELECT ('test' = 'not' OR 'test' = 'in' OR 'test' = 'here'); Is there a functional equivalent for SQL Server ? ...
We are upgrading our servers to SQL Server 2005 from SQL Server 2000. We currently use the jtds drivers. I'm interested to know what peoples opinions are of the different jdbc drivers available (in particular the latest Microsoft driver), how they perform with SQL Server 2005 and any other lessons from your collective experience. Thank...
I tried to make the title as clear as possible... here is my scenario: I have 2 tables (let's call them table A and table B) that have a similar schema. I would like write a stored procedure that would select specific columns of data out of table A, and insert this data as a new record in table B. Can someone point me in the write dire...
Greetings – To automate testing of our database SPROCs, we’ve been using dynamically created databases inside of a User Instance. This has been working very well – the build server and, until very recently, all the developers could all run the tests. However, one of our developer machines is now returning the following error when we tr...
How do you implement a last-modified column in SQL? I know for a date-created column you can just set the default value to getdate(). For last-modified I have always used triggers, but it seems like there must be a better way. Thanks. ...
I'm using MS Sql 2005. Why does this give me the correct results (returns 169 rows)... select * from [import_Data] where [import_Data].name not in ( select [import_Data].name from [import_Data] inner join [resource] on [import_Data].name = [resource].name where [import_Data].Provide...
Hi I'm trying to produce a report that has multiple grouping but does not just show the group as the single field being grouped. I think it may be best shown with an example: > Date <- Grouped, broken by page > Meeting type <- grouped > Meeting_Time Meeting_Place Meeting_Title Meeting_Priority > Agenda_item_1 > ...
Hi, Does anyone know of a way to monitor table record changes in a SQL Server (2005 or 2008) database from a .Net application? It needs to be able to support multiple clients at a time. Each client will "subscribe" when it starts, and "unsubscribe" when it exits. Multiple users could be accessing the system at once and I want to refl...
I am trying to select a record out of the table1 by joining table2 and using table2 columns in the WHERE statement. Col1 and Col2 in table1 can be stored in col1 of table2. I need to join col1 of table2 with either the value of col1 or col2 in table1 here is the sql statement I created so (pseudo): SELECT t1.Col1, t1.Col2, t...