This should be so simple, but I'm drawing a blank. I have two tables. Table:article_tag_pvt colum: article_id column: tag_id Table: tag column: tag_id column: name The article_tag_pvt table is a many-to-many pivot table. The problem is, I need a query which given a list of tag names, will retrieves article Ids which ONLY match tho...
I have everyday schedule to backup my database in sqlserver 2005. Now it's looks like this: BACKUP DATABASE [db1] TO DISK = N'D:\SqlServer\Backup\db1.bak' WITH NOINIT , NOUNLOAD , NAME = N'db1backup', NOSKIP , STATS = 10, NOFORMAT But in this case, it's will grow infinitely and I want to store only last 7 backups in file. How c...
hi, i am trying to create a report from a row of data with about 60 columns. The workflow is as follows. 1] user selects the fields they want to view in the report from within c# 2] c# calls a sp in sql and passes the fields (expecting between 1 and 5 fields per report) 3] ssrs displays these fields on a pie chart in reporting services....
Hi, I am executing a stored procedure and i got an error saying 'Error SQL Server Database Error: Error converting data type varchar to int." in sqlserver 2000. But when I execute the same stored proc in Sql server 2005,i am not getting any error and result is getting displayed. Any one is having idea regarding this??Please do help me....
Having an interesting issue. I'm reading from an excel file on a server via an OpenRowset in Sql2005. I've run the query a number of times without any problems. I've just gone out for a quick meeting and suddenly I get the error "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)"" I've made ...
hello, every one i'm using ms sql server 2005 and i need to store date values partially for example, the day part of a date may stay unknown in fact but mssql constrains to specify the full date like the follow: INSERT foo(dt) VALUES('2001-10-31'); though i would like to use something like this: INSERT foo(dt) VALUES ('2001-10-??');...
I need help on writing a trigger in SQL Server to be used in Siebel: The system field ROW_ID has to be unique (key) When the field INSERT_CD and CAMP_WAVE_ID is null then ROW_ID must be generated (see below). If not, leave ROW_ID as is. ROW_ID is a key field of varchar(15). The following statement generates the perfect key/row id: s...
Hi guys, I have the following table structure as follows: (please ignore the blackbox. was formatting the question in Excel) What I need to do is to transform the SessionGUID data into the following using TSQL: This is the user sessions table for my website and we screwed up the SessionGUID's in that it was generating a NEWID() on ...
Simple question? Why is READ_COMMITTED_SNAPSHOT not on by default? I'm guessing either backwards compatibility, performance, or both? [Edit] Note that I'm interested in the effect relating to the READ_COMMITTED isolation level, and not the snapshot isolation level. Why would this be a breaking-change, as it holds less locks, and stil...
Guys, In SSMS when I try to execute: SELECT CONVERT(VARCHAR(MAX), REPLICATE('a',9000)) I see only the first 8000 characters displayed. The settings Tool >> Options >> Query Results >> Sql Server >> Results to Grid is set to 65534 and Results to Text is set to 8192. Also when I try to run this from SQLCMD sqlcmd -S Server -E -y ...
I'm re-casting a question I asked earlier now that I have an idea of the cause of the problem I'm seeing. What I have: Tables in a DB on SQL Server 2000. I view/edit the tables with Access 2007, with SQL Server Management Studio 2005, and occasionally with SQL 2000 Enterprise Manager. What I did: I tried to copy the DB from SQL Serv...
How do you alter a column to remove the default value? The column was created with: ALTER table sometable Add somecolumn nchar(1) NOT NULL DEFAULT 'N' And then altered with: alter table sometable alter column somecolumn nchar(1) null That allows nulls, but the default value remains. How can you remove it? ...
Hello All! How to do a global search for a key word in all the Stored Proc's for a given DB? I used the following but I am unable to get the desired results... SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'loadStatus' COLLATE SQL_Latin1_General_CP1_CI_AS ) SELECT name FROM ...
I have created a stored procedure similar to this simplified example: CREATE PROCEDURE dbo.sp_MyStoredProcedure @Var1 INT OUTPUT, @Var2 DECIMAL(10,2) OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @Var1 = COUNT(*), @Var2 = SUM(TranAmount) FROM MyTable SELECT * FROM MyTable END When I try to re...
Hello Everyone, I have an interesting SQL problem that I need help with. Here is the sample dataset: Warehouse DateStamp TimeStamp ItemNumber ID A 8/1/2009 10001 abc 1 B 8/1/2009 10002 abc 1 A 8/3/2009 12144 qrs 5 C 8/3/2009 12143 ...
I have a table to store product reviews like this: Id -int ProductId -int Timestamp -datetime Comments -text Is there an easy way to count and determine the rate of reviews a product has received in any 60 minute timespan? ie. Widget1 maximum reviews/hour is 55. working with sql05. ...
Hi everyone, i was just wondering if anyone knows how to select rows where a specified column will come under a casting issue. ie. SELECT * FROM ThisTable t WHERE 0 <> ( select cast(t.value as datetime) ) the 'select cast(t.value as datetime)' would ideally return the result of @@error to indicate the casting issue has oc...
I'm getting the following error when trying to connect to a Sql Server (2005) using JDBC: unhandled token type: unknown token: 0x53 Any ideas anyone? I would have liked a more descriptive error too!! Also, I've done the 'telnet servername 1433' test and can confirm that machine can create a TCP connection. ...
How can I convert a database from MySQL to MS SQL Server 2005? ...
I have a query that updates one record, and only one record. Is there are way to get the Id updated in the same query such as Select ScopeIdentity when inserting. UPDATE Task SET MyTime = GetDate(), MyUserId = @userid FROM (select top 1 table where SomeStuff) Select Lastrow that just got updated. ...