sql-server

list of tables without indexes in sql 2008

How do I list tables without indexes in my SQL 2008 database? Edit I want the Schema name and the Table name. ...

Recommendations on a sql comparison (schema & data) tool that supports both Oracle and SQL Server?

I'm trying to find a good tool (open source or commercial) for doing comparisons of database instances, for example: Compare 2 database schemas; generate platform specific change script (either direction) to bring one into synch with the other Compare data (table contents), generate platform specific change script (either direction...

comparion/performance of database field as int vs char(1)

does one perform better over the other in terms of indexing/quering etc ? e.g. declaring a column as active Char(1) with 'Y' / 'N' values vs is_active tinyint with values 1 / 0 Anecdotally speaking most places it seems people prefer to use char(1) whereas my tendency is to declare them as tinyint/int not the least because it's easy...

Using a SQL Ranking Function with a derived column

I am trying to use a SQL Server 2008 Ranking Function on a query sorted by a derived column. Here's an example SELECT table.FirstName, table.LastName, CalculatedValue(table.number) As Points, ROW_NUMBER() OVER (ORDER BY points) AS 'Row Number' FROM table ORDER BY points I always get an error invalid column name "point...

simple way to know the updates in table

hi just wanted to know is there any way to know if any update happens in column like Ex:Author ID, and once its update happen in column is there any simple way so that all the managers should know that new author update happen in Database or via email, i am a newbie, if anyone help me step by step on this it will be really greatful for m...

How to retrieve metadata for return values of an sql server inline table-valued function?

It's not in INFORMATION_SCHEMA.COLUMNS, so where is it? This is a schemabound inline table valued function, so it doesn't have the issues which a stored procedure might have in being able to vary its output schema(s) based on parameters. ...

migrate data from MS SQL to PostgreSQL?

I've looked around and can't seem to find anything that answers this specific question. What is the simplest way to move data from an MS SQL Server 2005 DB to a Postgres install (8.x)? I've looked into several utilities like "Full Convert Enterprise", etc, and they all fail for one reason or another, ranging from strange errors that ma...

How does sql server choose values in an update statement where there are multiple options?

I have an update statement in SQL server where there are four possible values that can be assigned based on the join. It appears that SQL has an algorithm for choosing one value over another, and I'm not sure how that algorithm works. As an example, say there is a table called Source with two columns (Match and Data) structured as be...

Getting "select permission denied" when using LINQ but my account is a sysadmin

I have a console app that's geared to be automatically ran as a Scheduled Task. I use LINQ to SQL to pull some data out of the database, format it into a CSV and email it to a client. All of a sudden I am getting the error "SELECT permission denied for table", but the account I'm using to connect to the database (specified in my app.co...

Serving files from SQL Server through Zend

I am storing files into an image field in SQL server storing the string in hex after converting into using: unpack("H*hex", $datastring); If I read from the database and write to a file, then open the file locally I can open it up just fine. But if I try to send the file as an attachment to the browser the file becomes corrupted and u...

For Nvarchar(Max) I am only getting 4000 characters in TSQL?

Hi, This is for SS 2005. Why I am i only getting 4000 characters and not 8000? It truncates the string @SQL1 at 4000. ALTER PROCEDURE sp_AlloctionReport( @where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS DECLARE @SQL1 NVARCHAR(Max) SET @SQL1 = 'SELECT DISTINCT VenueInfo.VenueID, Ve...

Removing allocated space for Table - SQL Server

I got a table where in got allocated space of 3gig but have no rows. How to remove this allocated space ? ...

DB2 vs PostgreSQL vs SQL Server

Has anyone used all three of these databases? What are you experiences with them? PostgreSQL is starting to look pretty tempting to use for a project but I'm curious to learn more about it (We're a .NET Shop). I've also heard quite a lot of people being satisfied with DB2. ...

SQL Server and .NET: insert fails (silently!) in code but not when run manually

My insert stored procedure: ALTER procedure proj_ins_all ( @proj_number INT, @usr_id INT, @download DATETIME, @status INT ) as INSERT INTO project (proj_number, usr_id, date_download, status_id) VALUES (@proj_number, @usr_id, @download, @status) select SCOPE_IDENTITY() ... runs fine when called manually like so: exec proj_ins_al...

Getting the spid of a Powerbuilder database Connection?

I've been trying to find some manual information on this, but my search is fruitless. I'm trying to see if there is a way to find the unique spid (from sp_who) of a connection into a database from Powerbuilder. As in, a user logs in, and I can see their sp_who record, but I want to be able to obtain and manipulate this id in the applic...

SQL Server LOG Data recovery

I was wondering how you can recover data from your logfile. I'm using sql 2005 (full backup). The problem is that a service cleared my data last night (which it shouldn't have). and now I want to recover those rows that were deleted before. can anyone tell me how I can do this? ...

Something like phpMyAdmin but for SQL Server?

I am looking for web based tool like phpMyAdmin, but for SQL Server. Any suggestions? ...

SQL Function to extract time from Datetime2

Is there a function to extract a timespan field from a datetime2 field? e.g datetime2 has '01/01/2009 12:30:00' i want '12:30:00' ...

Difference between two sets of data in SQL Server 2000

I am trying to work out the difference between customers that have been billed in the last 3 months and the whole customer base. I have been able to calculate the customers that have been billed using the following SQL DECLARE @DateFrom AS DATETIME SET @DateFrom = CONVERT(DATETIME, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + ...

Error when Connecting with oracle from SSMA 2005 for Oracle?

I like to migrate the data from Oracle9i Enterprise Edition Release 9.2.0.8.0. This oracle is running in remote system. I can only communicate with server through Nortel VPN tool. I have installed SSMA 2005 in my system. Aim: Import the all the table along with records from oracle(remote) to my sql server(local) When I am connecting wi...