fDate fProduct fClr fIn fOut fStock 03/06/2006 aa xx1 3 2 1 03/08/2006 aa xx1 7 4 4 03/18/2006 aa xx1 6 4 6 03/09/2006 bb xx2 3 2 1 03/11/2006 bb xx2 9 2 8 due : until 03/09/2006 all grouping fProduct,fClr the answer table is below. how to make query? fProduct fClr fStock aa xx...more >>

Hello, I'm designing a web application that must be able to connect different databases. So I decided to use OleDb connector instead of SqlClient. While with SqlClient, I can run queries like "select * from mytable where col = @param" then giving a value to the parameter, I get an error whe...more >>

Hi all, I select two cols from a table, but I want to add another column that is just a number count. For example, selecting the top ten selling books returns Book A, ISBN Book B, ISBN Book C, ISBN etc... I want to in the my Stored Proc, just add a number that would make the ret...more >>

SQL Server 2005 SP1 I have a table with a datetime column called CreatedOn . CreatedOn is indexed. This query uses the index and is lightning fast select sum(compressedfilesize) / (1024 * 1024) as 'Repo growth today' from dmsrepositorydigitalassets where createdon > '20...more >>

Hello, I want to do a global search and replace in my SQL database where the data type could also be "ntext". I did find the code which works for (char, vachar, nchar, nvachar) Thank you for your help regards ...more >>

I found this in a old stored procedure and wonder if anyone has seen it before? I check BOL and could not find any explanation. Thanks! select columnA from TableABC where ColumnB = @Val1 and ColumnC = #Breakout.Col1 what is the # symbol means?...more >>

I'm looking for a 1-pager that will explain data warehousing simply, and preferably with examples (so I can prssent to/sell my boss). I.e... We have our normal OLTP database with tables like... Here is a simple star schema and why it's what we do And then there are OLAP tools in SQL Server 2...more >>

Our IT group has been running a number of DTS packages via stored procedures (the sp_OA... procedures) for some time. Recently, we've started having a lot of problems with the OLE automation procedures failing. The sp_OACreate procedure seems to work all the time, but the sp_OAMethod @hPkg, 'L...more >>

SQL Server 2005. How can I discover if a particular computer has BOL SP1 installed (vs if SP1 for BOL is not installed)? Thanks. ...more >>

I have two select statements. The only different between them is in the where clause. Instead of returning "Community, Monthly Total" and "Community, YTD", I'd really like one statement to return "Community, Monthly Total, YTD". Any suggestions? Thanks, Randy select cr.ecity as 'Commun...more >>

(I had posted this in another group that I realized is probably not the right one). I have a fairly complex SELECT statement. It is running on a server with 8 CPUs and 8GB RAM (using about 6.5GB). The statistics that I get when I run it are below. It is not the first time that I ran it so t...more >>

I have an offline database that gets occasional updates maybe once or twice a week. The attached script runs every night and each morning I check it for successful completion. Today the log showed a size of 12 gig (All my other logs after shrinking are around 1 meg). This is after successful ...more >>

What is the significance of COLLATE in a database script file? I have db script I use for a piece of software I've created - and I have (and understand its meaning: COLLATE SQL_Latin1_General_CP1_CI_AS My DB works with or without that statement - so I'm trying to figure out the significan...more >>

Hello, I am using an insert statement in a stored procedure that insert and image datatype column into a table on a different sql server, same edition server 2000. It was working a few days ago in a job, but I had to stop the server to defragment the drive. Now the stored procedure produces ...more >>

Hi All How can i change the resultset of a query from being a column and several rows to just 1 column and 1 row? eg select * from tb1 The resultset is as below: Col1 ------ ab ac ad I would like it to come as below (that is as 1 column and 1 row): Col1 ------------ ab, ac, a...more >>

Is there a way to keep a stored procedure from executing more than once simultaneously? That is, if three commands to execute the procedure arrive nearly simultaneously on different connections, how can I make the requests queue up so that the procedure executes one at a time with no overlap,...more >>

I created a master key in my database... create master key encryption by password = 'pw'; Then I created a certificate... create certificate my_cert with subject = 'encrypt cc numbers'; Then I created symmetric key... create symmetric key my_sk with algorithm = aes_19...more >>

I have a fairly complex SELECT statement. It is running on a server with 8 CPUs and 8GB RAM (using about 6.5GB). The statistics that I get when I run it are below. It is not the first time that I ran it so the execution plan already exists and it looks like the data is all in RAM which is ...more >>

I installed SQL server 2005 Developer edition on an XP box. The Reporting Services option was grayed out so I was unable to install that module. Is that normal? Thanks. ...more >>

I have a datetime field that I'm storing entries for activities in, but I need to retrieve the dates and times seperately (no problem there, Convert() saves the day for seperating them), but I need the Time in a 12 hour format instead of the 24 hour that SQL Server returns. Is there a way to ...more >>

Hi I need to make test plans for some sql select queries, what conditions or things I should test? I was thinking of testing where clause for data types etc? any help is greatly appreciated thanks ...more >>

Hello, I have noticed the difference in transaction handling between SQL Server 2000 EE installed on local WinXP machine and SQL Virtual server on WIN 2003 failover cluster. And this difference put me in big trouble. For example: If you need to update 100000 record (update of one integer f...more >>

Hi All, Is it possible to update a specific field based on the case statement? Something like this, for example: UPDATE Person SET CASE WHEN HB =3D '1' THEN HasBrother =3D '1' WHEN HS =3D '1' THEN HasSister =3D '1' END FROM <Some other table> ...... I know one way is to speci...more >>

My situation is this. using mssql 2000: I have a series of tables. Each table is designated for a particular "profile" There are lets say 4 profile tables. Each table has a list of assets in them. Then the information pertaining to that particular profile for that particular asset. The...more >>

I have a dataaset like the one below: Qualifier Date Whole/Partial Partial Count A 1/12/2000 W NULL A 1/3/2001 P 1 A 1/6/2001 P 2 A 1/9/2001 P 3 A 1/12/2001 P 4 B 1/4/2001 W NULL B 1/7/2001 P 1 B 1/10/2001 P 2 B 1/1/2002 P 3 B 1/1/2006 P 4 B 1/4/2002 P 4 There are more than a mil...more >>

I am having trouble running SSIS packages through SQL Agent Jobs. The error I get is that the command line is invalid. The command line is automatically generated by sql server not by me. I can manually execute the package through SSIS, but not through an SQL Agent Job. I even tried coping...more >>

Good Morning Sql Gurus Please give me some inputs on the following issues. create table x ( col1 int not null, col2 int null ) alter table x add constraint xpk primary key (col1) create index xsk on x(col2) drop table y create table Y ( col1 int not null, col2 int null ) ...more >>

I am trying to return a result set in a cross-tab format. My problem is that my date field has the time, thus returning more than one record for date instead of summing it. How can I fix this? This is my query: SELECT dbo.tblDrivers.First_Name + ' ' + dbo.tblDrivers.Last_Name AS Driver, ...more >>

I have an applictaion where when I update/ insert any record in child tables, it calls update statement for parent table (poorly designed) so I want to know if there is any database/ table property which I can set to check run commit only in case update statement is really making changes to th...more >>

I have a very strange problem, it only happen to one SQL Server, other SQL Server seems to be fine I got the following error when trying to run a sp against one of the SQL Server: SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET ...more >>

This might be a stupid question, i got a string "20,30,40", I want to use the string in the following SQL stmt: @user_id_list VARCHAR(400) .... -- now @user_id_list is "20,30,40" .... SELECT user_id from user_table where user_id IN ( a_magic_function(@user_id_list) ) Is there such a ...more >>

Hi, Is there any function in SQL Server similar to decode function in oracle ? Thanks, Amit...more >>

hi, I have ms sql server 2000. I need to send automatic emails from it. I am using: exec sp_OACreate 'CDO.Message', @iMsg OUT; ---- so on--- the properties of the object are set up by: sp_OASetProperty @iMsg, 'To', @variable -- that is an example buy I really need to modify the mail header....more >>

I am pretty sure this is possible in .Net 2.0...? I will build a web service in vb which will also include calling object written in c#. This will work wont it?...more >>

We have a strong performance problem with a quite complex query. The query executes in approximately 7 seconds in Query Analyser (which is okay for us), but takes much more time from ADO.NET : it timesout after 30 sec, or after 240 sec if we increase the timeout to that value. The ADO query ...more >>

Hi there, I have a function that splits data into intervals based on their timestamp as follows: ALTER FUNCTION intervals(@begin DATETIME, @end DATETIME, @lengthInSeconds INT) RETURNS @intervals TABLE(interval INT PRIMARY KEY, interval_begin DATETIME, interval_end DATETIME) AS BEGIN D...more >>

hi to all, i am using sql 2000 server now i want to set null value to my table from visual basic code. i am try to some update query shown below. my table name rs.open "update student set newid = null where sname='raja'" rs.open "update student set newid = 'null' where sn...more >>

I hope the code sample below is well enough formatted and easy enough to read. In short: 1. Creating a table of 250k random data in tempdb 2. Run a batch of two statements, with actual exectuion plan on. Execution plan tell these queries have the same cost, or very close to. 3. Run a ...more >>

I have function, which returns the dataType of some column in some table: declare @columnType nVarchar(50) set @columnType=dbo.colType('myTable','myColumn') It works, the result is for example 'varchar(50)'. Now, I would like to declare variable of this column type. What is the best way...more >>

Hi All, I want to have a SQL statement which query from a dynamic table inside a store procedure. However, I have no idea on how to get it done. For examples: DECLARE @TABLE AS VARCHAR(10) SELECT @TABLE = 'MyTable' SELECT * FROM @TABLE However, an error message appears "Must declare th...more >>

Hi, all I have a question : I created a stored procedure with 1 string type parameter [input], it can work perfectly when I use an input string with Big5 characters if I execute the stored procedure from SQL Query Analyzer tool, but when I call this stored procedure in VC++ ,it ca...more >>

does SQL Server 2005 have something similar to Oracle's START WITH CONNECT BY sql syntax, for returning hierarchical records? ...more >>

I'm after some help with an insert that uses a select cursor that probably should not. The schema if for an existing application and cannot be altered. I do not have access to the source for this application [Table: Forms] FormId (int identity) Name (varchar) 1 Apple 2 Banana 3 Cherry ...more >>

Sometimes we got an error (Not enough storage is available to complete this operation) from one of our servers which on win2000 sp 4 iis5.0 (all hotfix installed) sql server sp4. mdac 2.81. It occures random and we could not find the error reason. When the error occured the server cpu or m...more >>

Dear all, Could anyone tell me how to convert binary data in buffer to string format,thanks in advance! Anderson ...more >>