sql-server

T-SQL, Cursors, FETCH INTO. How to use SELECT *

I am building a one off query to iterate through a set of joined tables. The select statement is using "SELECT *". Since it's is a table with lots of fields, I don't want to specify each column as a variable. Is there a way to FETCH INTO an array or SET of some sort and just grab the values I want? ...

Usable view for dumb users?

I'm working on a small project that requires some of our users to be able to view/sort/filter and generally play around with a large amount of data. It's based on a sql server 2000 view and is something on the order of 125k rows worth of historical event data (think sports). Normally I would turn to excel for this kind of simple manipu...

Maintenance plan duration shown in history

Hi, Here is an export of our sql 2005 maintenance plan history Date,Source,Severity,Plan Name,SubPlan Name,Task Name,Duration 06/07/2009 05:08:29,,Unknown,Weekly optimisation,Subplan_1,,05:08:25 06/07/2009 05:07:41,,Unknown,,,Shrink Database Task (localhost),00:00:48 06/07/2009 04:50:40,,Unknown,,,Rebuild Index Task (localhost),00:16:4...

Recursive Query using CTE in SQL Server 2005

OK, here's what I'm trying to do. I'm using a CTE query in MSSQL2005. The objective of the query is to recurse through Parent child relationships of product categories and return the number of products under each category (this includes any products contained in children categories) My current version only returns the product count for ...

Selecting the top n rows within a group by clause

I have schema similar to the following: create table bar ( instrument varchar(255) not null, bar_dttm datetime not null, bar_open int not null, bar_close int not null ) I would like to query the table, and return the most recent 5 rows per instrument. I can do it instrument by instrument, with: select top 5 instrumen...

Pros and Cons of massive table that controls all data flow with stored procs

DBA (with only 2 years of google for training) has created a massive data management table (108 columns and growing) containing all neccessary attribute for any data flow in the system. Well call this table BFT for short. Of these columns: 10 are for meta-data references. 15 are for data source and temporal tracking 1 instance of new/cu...

SQL 2005 Find Out Who Created a View

Hi: Does SQL store any information about who originally created a view, or who last modified it? ...

SQL Server LOB data compression and full-text search

I have a SQL Server 2008 database with a large amount of varchar(max) data that is currently indexed with full-text search. Unfortunately, row-level compression in SQL Server 2008 does not support LOB data. I am toying with the idea of using SQLCLR to compress the data and a custom iFilter to enable the data to be indexed with full-text...

How can I identify a file type from a blob/filestream?

We bought an "off the shelf" application a lonnng time ago that is capable of storing files as a blob within SQL Server. We've noticed that the database has more than doubled in size within the past six months due to more frequent usage of this blob field by one department. As a result, the application has become painfully slow. I'v...

TSQL ORDER-BY with a UNION of disparate datasets

I have a query that UNIONs two somewhat similar datasets, but they both have some columns that are not present in the other -- i.e. the columns have NULL values in the resulting UNION. The purpose of the UNION is to get the data in a friendly format for the software-side. The problem is, I need to ORDER the resulting data using those c...

How to maintain null for numeric values when using BeanUtils.copyProperties?

I have a numeric database field (numeric(3) in SQL Server 2000) that allows nulls, and null is my preferred "no value" value. That field is mapped to the non-primitive java Long class in Hibernate: <property column="my_column" name="my_column" type="java.lang.Long" not-null="false" /> The field is set as java.lang.Long in both my stru...

What is the cost of coding and deploying asp.net MVC or stackoverflow.com, ASP.NET MVC VS struts,spring

I'm wondering what the cost is in tools to work with and deploy a website like stackoverflow with asp.net mvc. I have just checked and it seems like visual studio professional has suddenly got a lot cheaper, infact almost too good to be true price. But if they are working with SQL enterprise, the cost of this seems around £10,000 for t...

How do I ensure SQL Server replication is running?

I have two SQL Server 2005 instances that are geographically separated. Important databases are replicated from the primary location to the secondary using transactional replication. I'm looking for a way that I can monitor this replication and be alerted immediately if it fails. We've had occasions in the past where the network conne...

Is there a way to specify the Date Format used with SQLServer Management Object Scripting

When I'm generating my database scripts for my project I'd like to manage the date format that is used in the comments in the SQLScript. I have my regional settings set to Australian format, but they seem to be disregarded. Imports Microsoft.SqlServer.Management ... Dim scr As Smo.Scripter Dim opt As Smo.ScriptingOption...

How can i speed up this Indexed View?

Hi folks, I have a simple Indexed View. When I query against it, it's pretty slow. First I show you the schema's and indexes. Then the simple queries. Finally a query plan screnie. Update: Proof of Solution at the bottom of this post. Schema This is what it looks like :- CREATE view [dbo].[PostsCleanSubjectView] with SCHEMABINDING ...

Use SQL View or SQL Query?

I am working on an application to get data from a MS-SQL server (2005). In the command text, I can pass a sql query like this: string query = "SELECT T1.f1, T1.f2, T2.f3 FROM table1 T1 join table2 T2" + "on T1.id = T2.id AND T1.dt = T2.dt ..." .... cmd.CommandText = query; I could also put the query as a view on my SQL server like ...

SQL Round function not working, any ideas?

Here is the SELECT statement: SELECT ROUND(ISNULL(SUM(Price),0),2) As TotalPrice FROM Inventory WHERE (DateAdded BETWEEN @StartDate AND @EndDate) Any ideas of why it's not rounding to two decimal places? ...

How to retrieve an Image from MS SQL Server to bind in Gridview ASP.NET by using LINQ to SQL?

I have a binary file which stores in the customerPicture column that has Image as a datatype in the CUSTOMERs table. I have saved an image by using this line of code in LINQ to SQL. Dim db = new MyCompanyDataContext Dim newCus = new CUSTOMERs Dim filebyte As Byte() = fileUploader.FileBytes Dim fileBinary As New System.Data.Linq.Binary(...

Call a store procedure from .net

I have a store procedure like this: CREATE PROCEDURE up_action (@id int ,@group varchar(30)='' ,@nom varchar(30)='' ,@compte varchar(30)=NULL ) AS BEGIN DECLARE @chrono int ...... select @date=date from users where compte=@compte INSERT INTO dialog (numappel,auteur,commentaire,etape,etapews,operant) VALUES (@numappel,@nomprenom,@dialog...

In storprocedure how to set variable on where ?

CREATE PROCEDURE[BoardID] @sParent varchar(8000) AS Select Boardid,PONO,ImageStatus from BoardDetail Where Boardid=1 and @sParent Why @sParent create error how to use this variable on where ......i want to supply string as parameter and it will be set on where ......like @sParent ...