How to modify the column size without affecting the data in column.? ...more >>

Hey I've created a simple database in Sql Server 2005 Express. I created the database using Microsoft Sql Server Management Studio Express... After creating the database, I took a full backup of the database and deleted the database, because I wanted to learn about doing database restore...more >>

Hello, I had an access application and have moved my tables into SQL Server. I had an access query that ran fine but no longer does. I am getting an invalid operation error. Can someone help me with why this no longer works. Several of my queries are not working. I am wondering if there ...more >>

Hi, We recently got an exception in the SOA-solution I'm working on and none of the developers have seen this error before, and we are not sure where to begin. We are running against a busy sql server 2005 and as I understand it logging cannot be turned on. The exception message is as fol...more >>

Hello, I have 4 tables related as follows: [A] > Aid (PK), Bid(FK), Uid (FK), ... [B] > Bid (PK), Aid(FK), Uid (FK), ... [C] > Cid (PK), Aid(FK), Uid (FK), ... [U] > Uid (PK) U ----- A |------ B ----- U |------ C ----- U I need, given an Aid, to create a...more >>

I have a script I wrote that does some database data conversion. In one section I have a line that says select * into ##mytemptable from mytablename it then does some work on the data and exports it. I have to do a similar operation on another table and so I copied the code and added a li...more >>

Hey guys, I need to be able to total this column within the same script and then have it display the total of the column during the output. I am running SQL 2000 and this data cannot be modified due to its EXTREME importance. And for some reason, it will display a 0 amount even though I speci...more >>

Thanks ...more >>

Hello, I have two tables: [Posts] > PostId, ... [Ratings] > RatingId, PostId, Rating I want to select all posts and add a new column named AverageRating. This new column is the average of all ratings associated to that Post. If a post was not rated then its AverageRating would be NULL. ...more >>

Hello, I need to retrieve all records from a table named Blogs and the number of Posts associated with which Blog giving the name NumberOfPosts to that extra column. I have the following: SELECT b.*, p.COUNT(*) AS NumberOfPosts FROM dbo.Blogs b LEFT JOIN dbo.Posts p ON b.BlogId = p.Blo...more >>

I have a table that stores dates such as 9/26/2007 1:44:38 PM, I need to = write a SQL statement that goes against the table but only gets todays = data.=20 Is GetDate() something I would use, if so how? So I need something like this select * from tbl1 where payDate =3D Today so it o...more >>

Compiling Procedures with Incorrect Column Names - Shouldn't the compile fail? We are not only migrating from SQL Server 2000 to SQL Server 2005 but we are also changing our database structure (changing column names, datatypes, table names, etc.). Thus our one developer who has about 100 sto...more >>

We move the data from DevServer to TestServer. select convert(varchar(12), convert(datetime, ExpiryDate), 6) from [MyTable] where ContentId = 613 On DevServer: 11 Oct 07 On TestServer: 10 Nov 07 What could it be wrong ? Thanks, xke ...more >>

Hello everyone I would like to get the # of days since the last score was recorded. Zero(0) is NOT considered a recorded score. So I would like for the query to return the value 729, Since the last recorded score NOT zero(0) was on '2005-09-27'. I think that I am geting the latest date wi...more >>

What would be an easy way to group an integer datatype column with commas for every 3 digit? for instance, I want to comma group the rowcount column into ###,### I've tried below the it's giving me: ###,###.00 select table_name = object_name(id), row_count = convert(varchar,convert(money,...more >>

Hello, I have a Sales cube and I want to be able to display Products and the Price at which they were purchased in a given period. I have a Product dimension while Price is a measure in my Sales Fact Table. Is there a way to have a "Group By" aggregation instead of a Sum? This way, I can sh...more >>

Hi, I'm trying to come up with a select statement that is a combination of a full outer join and a self join. There are two separate tables, but three logical tables. The two logical tables are for tacking software and documents. If DocumentType is greater than zero, it's a document. If...more >>

We load a bunch of flat files into SQL Server using SSIS. All most all the tables have 4 datetime columns and not all are mandatory fields. Sample file: 1,custname,cust_address,cust_date,cust_DateofBirth 2,joe,123 main st,,2007-01-01 <-- no cust_date value 3,joe,123 main st,, ...more >>

Dear all, I have a table which collect different type of machine alarms. When an alarm get recorded it is identified by the following attributes : Name : Name of the alarm Time : Time when alarm occurs Machine : Name of the machine which raise the alarm Measures: A set of measure colloecte...more >>

I'm not sure if this is the place to ask but figured I'd start here since the problem is occuring within a sql stored proc. There is a proc that is calling CPP32.exe to calculate mileage between two city and states. It works fine until you try to execute it using the zip code functionality, th...more >>

Dear all, I have a table name LANGUAGE with following entries ID Name == ===== fr French en English -1 fr When the selected application language is identified by the value -1 inside the ID field. So what I need to do is define a trigger on this table but ...more >>

Hi, I'm a TA in a university business school for a security course. I'm trying to put together a demonstration on how to prevent SQL Injection attacks. I'm doing this demo in ASP.NET 2.0 using VB.NET. I know how to use parameters so I'm fine with demonstrating how to prevent the attacks, but ...more >>

Hi, I'm a TA in a university business school for a security course. I'm trying to put together a demonstration on how to prevent SQL Injection attacks. I'm doing this demo in ASP.NET 2.0 using VB.NET. I know how to use parameters so I'm fine with demonstrating how to prevent the attacks, but ...more >>

I have a problem... when doing something like: SELECT * FROM employee WHERE EmployeeNo= :lsEmployeeNo; from within a Powerbuilder 7.0 application I get an error... SQLState = 37000... is that the correct syntax to send a parameter to MS SQL 2000? Thanks..! ...more >>

Hi, I have moved my Access tables to SQL Server tables. There is a query that ran in Access but SQL Server does not like my date restrictor. I am using SQL passthrough. The error message I get is "Dateserial is not a recognized function name". Can someone help? Thanks, Chuck SEL...more >>

What does the @ sign in front of parameter names denote? Why is it required? Thanks, Abdullah ...more >>

hey all, given any date: declare @varDate datetime select @varDate = 9/3/2005 how do i would i change the date variable to the first day of that month? 9/1/2005 thanks, rodchar...more >>

Hi, I'm not an SQL expert, but need to be able to quickly assist our developers during a crunch, which is why I'm posting here, so I hope someone can help. In an app we are developing, we have a hierarchical setup of organisations. So, each organisation can have many child organisations. E...more >>

hey all, when i restored my db from production to a virtual pc, (restricted user) showed up in parens next to the db. can someone please explain this and how i need to resolve it? thanks, rodchar...more >>

Hi there! I'm having trouble getting around the 8k limit for varchar with SQL Server 2005. Consider the following sql executed in Microsoft SQL Server Management Studio: declare @sql varchar(max) declare @loop1 varchar(max) declare @content varchar(max) declare @year int declare @STAR...more >>

I have a table like this Date Message 10.00 Time is 10 10.30 Time is 10.30 11.00 Time is 11 11.15 Time is 11.15 12.00 Time is 12.00 12.45 Time is 12.45 If the system date matches the vaule in DATE column , the coressponding message should be come as pop up.. How to acheive this ???...more >>

I have a table partitioned on a key(column(s)). There are 350 partitions. There are clustered and non-clustered indexes on the tables which are aligned. If I run a query with the above partition key in the where clause, the performance of the query is good. But if I run a query without the pa...more >>

Hello, our programmers have set up Service Broker to use for part of their application. We constantly run into a problem where somewhere in the process, X and IX locks are held on the target_queue table as well as the underlying system table, and the locks are never released. Thus, we have t...more >>

I often have occation to implement inheritance and haven't found a good solution. I would like to see what some of the MVP's and other experts in this group suggest. An example is a simple asset system. Suppose I have a table/object class for the asset type 'Server'. In my object model ...more >>

hey all, is there another way to say the following? (i know i can leave out the begin and end) IF (@InitialRate > @VacationAccrualRate) BEGIN SELECT @VacationAccrualRate = @InitialRate END thanks, rodchar...more >>

Hi all I have the following table structure: [ID] [Column] [Value] 1 col_1 value_int_col_1 1 col_2 value_varchar_col_2 1 col_3 value_varchar_col_3 2 ... 2 ... 2...more >>

We have a table with around 800,000 rows in our SQL Server 2005 database. Its got a column which stores a char(6) serial number representing the product identification number. We require to generate reports such that we retrieve a certian range of serial numbers. But out of the 6 characters 3...more >>

Hi! I manage almost 400 servers with SQL 2005 SP2. All of them are set up to accept both intergrated Windows and SQL Authorisation. On all of these servers I got following importan view: CREATE VIEW dbo.create_text_from_systb AS SELECT com.[id] data_id, com.colid data_col, obj.[name] da...more >>

This should be simple but I seem to be missing the message. I'd like to execute a procedure when I initially schedule it for the first time and then every four hours at 4:30, 8:30, and 12:30 around the clock. Is there a more straightforward way to accomplish this rather than the following fairly...more >>

Hi, all I need some help with this...actually I think I need lot help =) Lets say I have data like DATA1 date x 1.5.2007 5 2.5.2007 7 3.5.2007 6 now I want to convert all dates to 15th of the data month so in this case DATA2 date2 x ...more >>

I am troubleshooting a deadlocking issue in sql 2k, got to the point where sql is logging the info in the sql log (using trace 1204) Both processes do an insert in the same table, but the object key on which the deadlock occurs point to the very same table/index (non-clustered) for both proces...more >>

I created a linked server on my local SQL 2005. (Remote Database is SQL 2000 but i use 2005 Management Studio ) I created a linked server by using my ip address. When i try to execute a query i get an error. An error occurred while executing batch. Error message is: Processing of result...more >>