sql-server

Can SQL CLR triggers do this? Or is there a better way?

I want to write a service (probably in c#) that monitors a database table. When a record is inserted into the table I want the service to grab the newly inserted data, and perform some complex business logic with it (too complex for TSQL). One option is to have the service periodically check the table to see if new records have been in...

Converting MSSQL GetUTCDate() into PHP/Unix/MySQL Ticks

I'm inserting a DateTime into MsSQL using the GetUTCDate() function provided by MsSQL. I need to convert the time in C# to show it as the Unix / MySQL integer, so that it can be eventually manipulated with PHP. I believe the Unix / PHP / MySQL ticks start at 1/1/1970, but I'm not sure how I would convert the equiv MsSql / C# time into ...

Is there a 64 bit version of SQL Server Management Studio (SSMS) 2005?

Is there a x64 or ia64 version of SQL Server Management Studio (SSMS) 2005? All the information I'm seeing leads me to believe there is only a x86 version. This link is my evidence that a 64-bit version doesn't exist - http://support.microsoft.com/?id=906892 ...

How to Get Transactional MySQL data into a SQL Server database

I'm working on a project that has a MySQL transactional database backing up a web application. The company uses SQL Server for back office and reporting applications. What is the best way to update SQL Server with the data from MySQL? Right now, we are performing a dump of the MySQL data and doing a full restore. This may not be feas...

sql group by versus distinct

Why would someone use a group by versus distinct when there are no aggregations done in the query? Also, does someone know the group by versus distinct performance considerations in MySQL and SQL Server. I'm guessing that SQL Server has a better optimizer and they might be close to equivalent there, but in MySQL, I expect a significant...

Text in PDF render of Reporting Services report "overprints"

A report I have been working on some time ago started mis-rendering around Christmas. Some of the text boxes on it suddenly started rendering each character in their text content on top of each other, as if the printing of each character no longer "advances the cursor" to the correct position for the next one. The report is rendered to P...

Is it possible to restore only the schema and stored procedures from a MS SQL database backup?

I have a full MS SQL Backup file that I would like to extract the stored procedures and schema from. Is there a way to restore only the schema definitions and stored procedures without restoring the data/table rows? ...

Best approach to SQL Server query using distinct

Hi Folks, I've two tables: TableA and TableB, joined by TableA.TableA_Id->1..n<-TableB.TableA_Id. A simple PK-FK. I need to extract distinct TableA records given a certain condition on TableB. Here's my 1st approach: SELECT * FROM TableA A INNER JOIN TableB B ON A.idA = B.IdA AND B.Date = '2009-01-10' ORDER BY A.Id; This is nice, bu...

Failed to remove package protection with error 0x80131940

Hi, I'm trying to open a password protected package in SQL Server BID and I keep getting the following error message each time I pu in the correct password: Failed to remove package protection with error 0x80131940 "(null)", This occurs in the CPaqckage::LoadFromXML method. Any Ideas? ...

Determine if a specific Windows application is running using SQL Server 2005

I need to determine if a specific application is running from a SQL Server 2005 job. The issue is that one of our applications we use to send data will hang, causing problems with any subsequent jobs that invokes it. If I can also obtain the CPU time, I can determine if it's likely a hung process. A list of running applications would ...

MSBuild return value from database

Hi, does anyone know how to return a value from a (sql) database into an MSBuild script? I want to get the value into a property so I can pass it to subsequent build tasks. I am fiddling round with something like the following at the moment, but this is returning -1 to indicate the query has executed successfully, whereas what I want is...

Using Triggers To Enforce Constraints

I'm trying to enforce integrity on a table in a way which I do not think a Constraint can... CREATE TABLE myData ( id INTEGER IDENTITY(1,1) NOT NULL, fk_item_id INTEGER NOT NULL, valid_from DATETIME NOT NULL, invlaid_from DATETIME NOT NULL ) The constraint I wan...

storing check box state in database with bitwise operation?

let say you have between 35 and 50 checkbox on a form. would you create 2 tables: accessTable, field: userid, accessType accessLookup, field: accessType, description and add X and/or remove rows to accessTable depending on the user selection OR would you create 2 tables: accessTable, field: userid, haveAccessBit accessLookup, fi...

SQL 2008 SMO Database Status property memory leak.

It appears there is a memory leak in the Status property of the SMO Database class. Using the code below with SQL 2005 SMO libraries works fine, but as soon as you use SQL 2008, the memory leak appears.... Any other good way of getting the database staus in SQL 2008? A quick example that magnifies the problem: private void button...

Counting rows for all tables at once

I'm using SQL Server 2005 and would like to know how I can get a list of all tables with the number of records in each. I know I can get a list of tables using the sys.tables view, but I'm unable to find the count. Thank you ...

Strange intermittent SQL connection error, fixes on reboot, comes back after 3-5 days (ASP.NET)

For some reason every 3-5 days our web app loses the ability to open a connection to the db with the following error, the strange thing is that all we have to do is reboot the container (it is a VPS) and it is restored to normal functionality. Then a few days later or so it happens again. Has anyone ever had such a problem? I have notice...

"Conversion failed when converting the varchar value 'NULL' to data type int"

when I insert records into a long table,I am getting the error "Conversion failed when converting the varchar value 'NULL' to data type int" How can I determine which column errors out? ob The table has many fields and millions of records. Each iteration takes 10 minutes to bomb when i try to insert "NULL" string into integer column so...

How do I return multiple datatables from a SQL Server stored procedured?

I need to make two queries on two different tables and the data isn't really related. So when I call the stored proc through my code, I should be getting a DataSet with two DataTables, one DataTable for each query. How is that done in SQL Server stored procs? ...

Should CONTROL permission be given on a Stored Procedure in SQL Server 2005?

I'm running into an issue where granting EXECUTE permissions on a specific Stored Procedure in SQL Server 2005 is not working. Some of the testers messed around with the permissions - and found that if they also granted CONTROL permissions on the Stored Procedure - then it ran fine. They are now convinced that granting CONTROL permissi...

SQL Cluster - using datasource (local)

Hi, Using (local) in the connection string doesn't work on my cluster. I'm assuming it's looking for the default instance on the currently active node instead of the Virtual SQL name. Anyone know how to make this work? edit note: I'd like to use (local) and not localhost - I don't want to change any application generated code. ...