I would like to know how cursor actually works in sql server 2008. For example, are the results held in memory as soon as the cursor is opened? or the results stored in an intermediate format, the real data is retrieved when fetch? Please also suggest some good books or blogs about sql server internals. Thanks. ...
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0 (MSDN reference). So why is the output of this query 1? select ISNUMERIC('5d9') ...
Hi, I have table in my sql server database which I want to convert to PK column To do that I want to change value of each row in this column to 1,2,3 ... Could You write T-Sql query for that task ? Thanks for help begin state: Id | Name | ---------- 1 | One | 2 | Two | 2 | Three| x | xxx | result: Id | Name | ---------- 1...
Hi, I use Visual Studio 2008 database project for managing my database scripts and version control. by default visual studio script templates are encoded in UTF-8 format. when I try to concatenate the scripts for database release (using the DOS command "type *.sql > dbscripts.sql") and run the concatenated script from SQL server manageme...
To perform transformations in my database, I frequently use a chained set of views. Within the views will be common table expressions. For example I would have the following: CREATE VIEW TransformationStep1 AS WITH Transformation1A AS ( SELECT Field1, Field2, Field3, Bla(Field1) AS Calc FROM Table1 ), Transformation1...
Hi Guys, I'm using the following format for commiting changes to my db using linq. Begin Transaction (Scope Serialized, Required) Check Business Rule 1...N MyDataContext.SubmitChanges() Save Changes Done In Previous Query To Log File End Transaction Scope But in the SQL Server profiler I see the following line in the Conne...
When i try to set the Dataset in SSRS IDE, i get the error you see in the snapshot. The query works totally fine in SQL Server Management Studio, i wonder where did i go wrong?! The connection to DB is well established. OPTIONAL: In case you want to have a look at my query (its too long), i checked it very well. Nothing wrong in ...
Hi Guys, DECLARE @cityID bigint; set @cityID = NULL --set @cityID = 3 SELECT ID, Name, CityID, IsProvince FROM TABLE t WHERE ISNULL(t.CityID, -1) = ISNULL(@cityID, -1) whenever the @cityID is NULL the t.CityID is also NULL for that record. Only one record can ever be true for IsProvince Is there a way to...
Sorry for the rubbish title but hopefully this will explain: Given the table name | data --------------------- 1 | 1000 1 | 2000 1 | 3000 2 | 1500 2 | 2500 2 | 3500 I want to be able to select the top( x ) for all names ordered by the data value. So if x = 2 the retur...
I want to find the average of the most recent x results. Is it possible to use an aggregate function on a limited number of results with sql server? Here is what I have tried and the errors I get: select avg(top(100) convert(float, AnalysisData)) from tab order by DatePerformed desc Msg 156, Level 15, State 1, Line 1 Incorrect s...
Hi, This isn't actually a question, it's a problem which I couldn't find any help on, so I thought I'd post the solution I eventually found in case someone else encounters it. The problem was with an XML-typed column in a TVP parameter I was passing to a SQL Server 2008 stored proc. When I called ExecuteNonQuery() I got an error that...
I have created a fulltext catalog that stores the data from some of the columns in a table, but the contents seem to have been split apart by characters that I don't really want to be considered word delimiters. ("/", "-", "_" etc..) I know that I can set the language for word breaker, and http://msdn.microsoft.com/en-us/library/ms34518...
SELECT First 20 Chars of(ColName) from DB Is this possible? ...
Hi! I have procedure on SQL Server! Now,is it possible to create dll from that procedure and if it is, how i can do that? Thanks! ...
After migrating a database from SQL Server 2000 to 2008, in what situations is it justified to leave compatibility level to 80 permanently. The system consists of stored procs, triggers, views and tables only. No DTS, reports and other non-database engine stuff. ...
@TotalRows int = NULL Output this won't work ...
I have a stored procedure that uses a FAST_FORWARD cursor to chronologically loop over a set of ~300k records and assigns them to declaration sets based on the state of a lot of running variables and flags, partly implemented as table variables. I have given a lot of thought on how to do this set-based and I just can't do it. So I am stu...
SSIS Hello. Firstly excuse me if this doesn’t make a lot of sense as I’m fairly (as in 2 days ago) new to SSIS. Ultimately I’m looking to import all data from an XML file and depending on what kind of file it is process in a different way and load to a set of relational tables in a Microsoft SQL Server 2008. Each XML file has a Boo...
I have an sql table with some nvarchar columns, lets call them 'Title' and 'Value'. I currently use a linqtosql query to make a prefix search to find rows in this table, like so var result = from _item in GetTable() where _item.Title.StartsWith( "hello" ) || _item.Value.StartsWith( "hello" ) select _item; return result.Take( 10...
Server is running 2008 R2 Standard 64 bit and SQL Server 2008 R2 Workgroup Edition (64-bit) We store documents in a varbinay(max) and the full text indexes work perfectly for all document types including PDF’s but do not work for Office 2007 documents. We have installed the Office 2007 iFilter and done each step but still do not get a...