sql-server-2005

ASP Calendar control returns Date type but I need Datetime to insert into SQL Server 2005

Hello, I am using a ASP Calendar control to insert a datetime value into a field to be part of an insert to a SQL Server 2005 db table. I get the following error when i submit the form to server and try to insert into table: [ArgumentException: The version of SQL Server in use does not support datatype 'date'.] Seems like Calendar c...

Data in two databases, eager spool resulting in query

I have two databases in SQL2k5: one that holds a large amount of static data (SQL Database 1) (never updated but frequently inserted into) and one that holds relational data (SQL Database 2) related to the static data. They're separated mainly because of corporate guidelines and business requirements: assume for the following problem th...

Dynamic SQL to generate column names?

I have a query where I'm trying pivot row values into column names and currently I'm using SUM(Case...) As 'ColumnName' statements, like so: SELECT SKU1, SUM(Case When Sku2=157 Then Quantity Else 0 End) As '157', SUM(Case When Sku2=158 Then Quantity Else 0 End) As '158', SUM(Case When Sku2=167 Then Quantity Else 0 End) As '167' FROM Ord...

What is happening in this T-SQL code? (Concatenting the results of a SELECT statement)

I'm just starting to learn T-SQL and could use some help in understanding what's going on in a particular block of code. I modified some code in an answer I received in a previous question, and here is the code in question: DECLARE @column_list AS varchar(max) SELECT @column_list = COALESCE(@column_list, ',') + 'SUM(Case When Sku...

SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc HELP!

I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup stuff, but the table maintenance baffles me some. Does anyone have any input on how often I should (or how often you do would suffice too) the...

Change the precision of all decimal columns in every table in the database

Hi all, I have a rather large database that has alot of decimal columns in alot of tables, the customer has now changed their mind and wants all the numbers (decimals) to have a precision of 3 d.p. instead of the original two. Is there any quick way of going through all the tables in a database and changing any decimal column in that t...

SQL Server 2008 - Shrinking the Transaction Log - Any way to automate?

I went in and checked my Transaction log the other day and it was something crazy like 15GB. I ran the following code: USE mydb GO BACKUP LOG mydb WITH TRUNCATE_ONLY GO DBCC SHRINKFILE(mydb_log,8) GO Which worked fine, shrank it down to 8MB...but the DB in question is a Log Shipping Publisher, and the log is already back up to some 5...

SSIS - How do I see/set the field types in a Recordset?

I'm looking at an inherited SSIS package, and a stored procedure is sending records to a recordset called USER:NEW_RECORDS. It's of type Object, and the value is System.Object. It is then used for inputting that data to a SQL table. We're getting an error, because it seems that the numeric results of the stored procedure are being put...

T-SQL String Functions: difference between using Left/Right and Substring and strange behaviour

I am using SQL Server 2008 & 2005 (Express). I'm trying to extract part of an alpha numeric string from a varchar field. RIGHT(str_field, 3) yields null values but SUBSTRING(str_field, LEN(str_field)-2, LEN(str_field)) gives the right value. LEFT(str_field, 7) gives the expected values. What gives? I would have thought that RIGHT(st...

SQL Server 2005 - How to find out what database files a full backup restore will create using T-SQL?

How do you find out what files a database backup restore would create using T-SQL? i.e. The restore would create Bob.mdf ,Bob_log.ldf ,Bob2.ndf etc etc? ...

sql query to get the sql server 2005

i have table with filmname and actors column in sql server 2005 i want the sql query to get all the actors of the film starred by both "bradpitt" and "rusellcrowe" the table design is as follows CREATE TABLE [dbo].[mytable]( [actors ] [nchar](10) NULL, [filmname] [nchar](10) NULL, ) ON [PRIMARY] ...

SQL-query task, decision?

There is a table of currencies rates in MS SQL Server 2005: ID | CURR | RATE | DATE 1   | USD   | 30      | 01.10.2010 3   | GBP   | 45      | 07.10.2010 5   | USD   | 31      | 08.10.2010 7   | GBP   | 46      | 09.10.2010 9   | USD   | 32      | 12.10.2010 11 | GBP   | 48      | 03.10.2010 Rate are updated in real time and there are ...

SQL server 2005 - user rights

I have created one user named "tuser" with create database rights in SQL server 2005. and given the 'db_owner' database role of master and msdb database to "tuser". From this user login when I run the script for create database then it will create new database. But "tuser" don't have access that newly created database generated from scr...

Distinct or group by on some columns but not others

I have a view that I'm trying to filter with something similar to DISTINCT on some columns but not others. I have a view like this: Name LastName Zip Street1 HouseholdID (may not be unique because it may have multiple addresses -- think of it in the logical sense as grouping persons but not physical locations; If you lookup HouseholdID...

SQL Query to delete oldest rows over a certain row count?

I have a table that contains log entries for a program I'm writing. I'm looking for ideas on an SQL query (I'm using SQL Server Express 2005) that will keep the newest X number of records, and delete the rest. I have a datetime column that is a timestamp for the log entry. I figure something like the following would work, but I'm not ...

SQL Server 2005 Reporting Services and the Report Viewer

I am having an issue embedding my report into an aspx page. Here's my setup: 1 Server running SQL Server 2005 and SQL Server 2005 Reporting Services 1 Workstation running XP and VS 2005 The server is not on a domain. Reporting Services is a default installation. I have one report called TestMe in a folder called TestReports using a sha...

how can i store data from csv file to sql database in asp.net

in my project i have existing csv file and all they have up to 20 lak records. and they all i store in my sql database. so plz help me how can i do this. i use sql bulk insert query but they didn't run in asp.net ...

sql server 2005 express - Invalid Object Name error

I have a copy of a query from a view (by filtering) and when i tried to excute it, it throws an error message that says "invalid object name 'bla bla'". How can i fix it? I am using windows 7(ultimate) os and sql server 2005 express. ...

Use a named custom column in SQL 2005 in WHERE clause?

Can I name a custom column in the SELECT statement and reference that in the WHERE clause without duplicating code? For example; SELECT RIGHT(REPLICATE('0', 5) + RTRIM(SOME_ID)), 5) AS DISPLAY_ID FROM dbo.MY_TABLE WHERE DISPLAY_ID LIKE '%005%' Only much more complicated. I want to maintain this code in one place only but SQL Server 2...

SSRS Forms Authentication - Authorization Extension Issue

Hello, I am using a custom forms authentication component in SSRS 2005. Everything works great with that except for authorization. I was hoping to be able to configure authorization so that it would control which features were available within the application. So if I wanted the user to only have access to the report builder, I could...