Is it possible to pass a parameter of type "table" with a column of type "[int] IDENTITY(1,1)" to a procedure and execute this stored procedure with a DataTable object passed as the input parameter? I get the following error: "INSERT into an identity column not allowed on table variables. The data for table-valued parameter \"@xxxxx\" d...
I'm writing a SSRS report that is the direct result of a SSIS package. It is my understanding that SSIS must have exclusive access to Excel files for import. Is there any way around this? If I can't read what was there as of the last save, can I at least send an e-mail notifying the user of the failure? How do I catch that error? ...
I have created a SSIS 2008 Package which imports rows from a spreadsheet. I need to start on row 4 so I have used the OpenRowset property of the Excel connection manager. However, I need to import multiple tables. Can this be done with the Excel Connection Manager? I believe it can be done with an ADO.Net Source, but I don't know how ...
I have a page on my site which has multiple drop down boxes as filters. So the SQL procedure for that page would be something like this IF @Filter1 = 0, @Filter2 = 0, @Filter3 = 0 BEGIN SELECT * FROM Table1 END ELSE IF @Filter1 = 1, @Filter2 = 0, @Filter3 = 0 BEGIN SELECT * FROM Table2 ...
In my report I display a record from a table of my database. One of my columns has data formated as html. I want to display this as rendered html. So <b>nice header</b> should render nice header Can I do this in a report? ...
In SQL Server 2008, I am seeing some strange behavior when ordering NVARCHAR columns; here are a few quick use cases to demonstrate: Case 1: ORDER on VARCHAR values: SELECT t.Name FROM ( SELECT CAST('A' AS VARCHAR(500)) As Name UNION SELECT CAST('-A' AS VARCHAR(500)) AS NAME ) As t ORDER BY t.Name ASC Which produces (my desired) o...
Is it possible to create a view (not stored procedure) with dynamic column names based on another table? For example: Code: CodeId|Description ------------------ 1|Title 2|Notes Data: DataId|Content|CodeId|GroupId ----------------------------- 1|Title1 | 1| 1 2|Note1 | 2| 1 3|Title2 | 1|...
I have an interesting case of results sets returned by SQL Server being different depending on whether a query is executed locally or remotely. In essence, if I execute the following query: SELECT p.ID AS Id FROM csdb.users.People AS p LEFT JOIN csdb.users.Accounts AS a ON p.ID = a.IdentityId then the left join is performed, and I o...
SQL Server 2008 - I want to concatenate four columns into delimited values, but I want them to be ordered alphabetically. Is this possible? *UPDATE:*More info... This will be used on approx 700k-1M rows per day in an ETL job via SSIS. If there is an easier way to do it within SSIS, please let me know (script task, etc). It could also be...
In MSSQL2008, I am trying to compute the median of a column of numbers from a common table expression using the classic median query as follows: WITH cte AS ( SELECT number FROM table ) SELECT cte.*, (SELECT (SELECT ( (SELECT TOP 1 cte.number FROM (SELECT TOP 50 PERCENT cte.number FROM cte ...
I have the following Db and query. The query takes two parameters: the sort column and the direction. However, I have to add custom sorting to the query (based on the Fuji should come first and Gala second, etc.). This part also work but it create duplicated code in my query. Because of that, I am pretty sure people will not let me check...
Hello everyone, I am using SQL Server 2008 Enterprise on Windows Server 2008. I want to select result from top 11 to top 20 (e.g. I am only interested in the 11th to the 20th result). Any ideas how to write this query in tsql efficiently? thanks in advance, George ...
I have a composite primary key (Int and DateTime) on one of my tables. When I try to add a new record using LINQ (with the DateTime field set to "AutoGeneratedValue = true", and getdate() as default value on the server), I get the following errors: The primary key column of type 'DateTime' cannot be generated by the server. Any ide...
trail server of sql server 2008 r2, downloaded not installed does it have a managemnt studio like sql server 05 express or not? from where to get it. ...
Hi I have this table: id name 1 A 1 B 2 C 2 D 2 E 3 F and I need to get this: id qty 1 2 2 2 3 1 ...
This has effectively ruined my day. I have a larger number of tables with many FK relationships in between. One of the tables (lets call it table A) has a computed column, which is computed via a UDF with schemabinding and is also fulltext indexed. If I edit any table (lets call it table B) that in any way is related (e.g via FK) to the...
I have a web app that stores a large amount of text data. The db is currently increasing by 1GB a week. I expect this to grow exponentially as we get more customers, so 1GB this week, 2GB next week, 4GB the following week, then 8GB, etc... Right now this data is stored in a single MS SQL 2008 database that 10GB in size. Performance is ...
Is there a way to escape the * character in a full text Contains function in sql server 2008? I've tried a standard escape by using square brackets, but that just throws a syntax error. Also, any solution can only use full-text functions as the column I'm searching is an image/blob column. ...
Hello all i have a table in SQL 2008 as ID Items 1 A 1 B 2 C 3 D 3 B i would like to get the result as ID Items 1 A,B 2 C 3 B,D I have used cursors but it has considerably slowed the process , can i achieve the above result using group by query or through any other way. Thanks and R...
As pointed out in this question when using filestream with sql server 2008 the data must be stored locally. This means that I cannot use \\FILESERVER\FileStreamData\MyDatabase for Filestream filegroup path. So if I need to use filesrteam and have lots of data, is buying a very large hard drive the only solution (this could be quite l...