sql-server-2005

How to make a .net dll stored procdure using VS2010 and SQL Server 2005?

Hi Does anyone have a tutorial on how to first make .net dll solution in VS2010 that will work for SQL Server 2005? I heard they have a special solution but I am not sure which one it is. I am trying to write what I think they call an extended stored procedure. Then a tutorial on how to actually right these extended stored procedures ...

Problem With Count()

I wants to write a query to retrieve COUNT(of employees with the salary=1000) and COUNT(of total no of employees) from the same table. any ideas?? ...

Visual Studio 2008 with SQL Server 2005 Developer Edition

I am trying to add a database to the App_Data location in an ASP.NET MVC 2 application in Visual Studio 2008 (VS). I have SQL Server 2005 Developer Edition installed on the local machine. However when adding the database VS complains that SQL Server 2005 Express is required. I configured VS to use the local server instance (MSSQLSERVER)...

Is it bad to put WITH PERMISSION_SET = UNSAFE in SQL 2005?

Hi I need to use CLR to make a stored procedure that uses stuff in .net 3.5. If I don't put Permission_Set = UnSafe I can't do it it will just die and give me this error Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'system.core, version=3.5.0.0, culture=neutral, publick...

How to make this CLR work with 2005?

Hi I am trying to make a clr stored procedure for a sql 2005 database that uses .net 3.5 assemblies So first I had to change sql 2005 to recognize system.core as unsafe what I am not too happy about(I rather have had it say SAFE). Now I get this error Msg 6522, Level 16, State 1, Procedure StoredProcedure1, Line 0 A .NET Framework e...

Convert/Format a column in a select statement sql server 2005..

I have a sql query which is select DateOfBirth from people and it shows up in the result pane as DateOfBirth 07/07/2010 5:08:02 07/09/2010 5:08:02 07/13/2010 5:08:02 I want to format, 07/Jul/2010 09/Jul/2010 13/Jul/2010 NOTE: DateOfBirth column has datatype nvarchar(50) not datetime... ...

What is the equivalent of ORACLE's TRANSLATE funciton in SQL Server 2005

Is there any equivalent function in SQL Server 2005 that will act in a similar fashion like the TRANSLATE funciton of ORACLE? Well 1 option is to do with multiple REPLACE statement. Another is to create a user define function. I am talking about a built-in function in SQL Server (no work around)... e.g. TRANSLATE('1tech23', '123', '4...

Deployment of database on client's computer

I have developed a Windows application in .NET which uses a SQL Server database to store, retrieve and manipulate data and tables through sql queries. Now I want to know when I would deliver its executable (.exe) file to client then in which form I have to deliver that database so that the application can connect to database. Should I ...

Create initial data sqlscripts with binary data, sqlserver 2005

Hi all, with a TFS 2008 Teambuildtype we create a ClickOnce Setup for a review version of our application. Within this Teambuildtype we create an initial database with some data to start directly with tests. Now i need to put some binary data in our sql script insert files (Wordfiles). How can i create a initial script with the binary...

SQL Server: protect database from being changed directly

Our program ships with an SQL Server 2005 database and SQL Server 2005 Express. Usually it installs its own instance of SQL Server 2005 in the client's computer. Now I have to add some tables whose content should only be updated from within the program. I need to prevent these tables from being changed directly, by using Management Stud...

Tricky SQL SELECT statement - combine two rows into two columns

My problem: I have a table with a Channel <int> and a Value <float> column, along with a timestamp and a couple of other columns with additional data. Channel is either 1 or 2, and there is either 1 or 2 rows that have everything except channel and value the same. What I'd like to do is select this data into a new form, where the two ch...

Tweak SQL query help

I have a big query as following, someone please tweak this query? i need to avoid this big group by ... :-) SELECT COALESCE( SUM(total_retail), 0 ) total_retail, COALESCE( SUM(meterial_sub_total), 0 ) meterial_sub_total, COALESCE( MIN(po_template_group_by_code), 0 ) po_template_group_by_code, COALESCE( MIN(po_te...

Convert a few columns in the table to a resultset of name, value pair rows in SQL Server

I have a table like this Table1 Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 1 2 3 4 5 6 7 8 2 2 3 4 5 16 17 18 3 2 3 4 5 26 27 28 4 2 3 4 5 36 37 38 I want the data to be converted in this format Col1 DerivedCol1 DerivedCol2 1 Col6 6 1 C...

XPath must be a literal?

I have a stored proc with a parameter which I want to have contain an XPath expression which can then be used to retrieve a value in a select, but the r.value(...) exception message says that I can only use literals. Is this true or is there a way around this? create proc MySproc @myxml xml, @xpath nvarchar(50) as begin ...

T-Sql Select * Between 30% and 40%

Question How do I write a T-SQL Stored Procedure that lets me select percentages of rows between X% and Y%? So basically I would want to select the rows between 30 PERCENT and 40 PERCENT..... I know that you can do the following, but obviously that doesn't let met specify a set of rows between 2 percentages. SELECT TOP 50 PERCENT * ...

In SQL Server, what's the best way to merge tables from multiple databases?

hi guys, I'm sorry that I can't find a better title of my question. Not lemme describe it in detail. I have 4 database which are a, b, c and d. Database a have all table's that appear in b, c and d, and they have the same structure with the same constraints(pk, fk, default, check). b, c,d just have some tables that appear in a. Now the...

distinct on a single column, yet returning a row from the same table based on an unselected condition

I am new to Sql server, and my MySql background is only decent, nothing exceptional. I have a table with 5 columns: Neighbrhood City State zip_code zip_percent The goal of the query: To provide a list of neighborhoods (unique) within a city and the zip_code that corresponds with the highest zip_percent. Thus returning 2 columns: Nei...

Convert a english numeric words into some other language without writing conversion program

Suppose I have a number as 123. In words it will be one two three Is it possible to convert that number words into some other language without writing a conversion program in sql server. I tried with SET LANGUAGE Italian declare @i int set @i =1 print @i but it is not working.. The expected output being uno due tre Help nee...

i have to do the following to the database, have an idea but its not clear. help!

note:- Please read the explanation of what is to be achieved and provide initial guidance, i know there must be a lot of concepts involved, i am not asking for complete step by step solution, need help in knowing what concepts need studying and a proper path to start with. There are a lot of resources on the internet, which makes it real...

What is "query writer, new querry in sql server management studio express

using :- sql servr 05 and vs08 c# website asp.net Okay, I have always used SQL Server 2005 Express and its Management Studio as a host and a design interface to create tables from design view only. All the queries, transaction, retrieval and anything needed during run time of the website is done using Visual Studio 2008 sql "connection ...