Hi. I have a table called [Test], which has a single column [Id] int. I open a transaction, insert one row to the table and NOT commit it. begin tran insert into [Test]([Id]) values(1) In another request I want to select data from table [Test]. How can I read only commited data immediately? Readcommited table hint holds a lock. sel...
It is written everywhere that data in SQL Server is written in pages of 8K (8192 B) each with 8060 bytes for data and the rest is overhead (system info). Though, the recent article [1] gives code example illustrating, as I understood, that 8078 bytes of data fit into a page. What do I miss in understanding 8,060 B per per page? I ve...
Hello, So I basicly have a table which has a list of table names. All these listed tables have exact same structure. Then I have a query template, with place holder for table name. I need to create a view, which should return results of that query UNIONed from all the tables listed in that one setup table. So far what I've done is cr...
SQL Server 2005 How to check if given date is valid smalldatetime? Before converting datetime to smalldatetime, I already know that the value is valid datetime. But while converting datetime to smalldatetime, it is raising overflow error. So I wanted to check if the value is valid smalldatetime and if it is then convert to smalldatetim...
I am trying to understnad how SQL Server allocates and reserves space. Having run the examples from the article "How table design can impact your SQL Server performance?" [1], I received the results [My 1.1] diverting from those in article [1.1]. Why? Why in one case the excessive space is reserved/allocated (all cases in [1]) but ...
I'm building some HTML to be included in the body of an email and sent using sp_send_dbmail. I'd like to right-align some of the columns. Is there an easy way to do this (short of rewriting it using FOR XML EXPLICIT)? declare @html varchar(max) set @html = '<table cellpadding=0 cellspacing=0 border=0>' set @html += cast( (sele...
Hello all When retriving Excel sheet Named region it returns spurious column names OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "No value given for one or more required parameters.". Msg 7320, Level 16, State 2, Line 1 Cannot execute the query "SELECT `Tbl1005`.`CUSTOMER` AS `Col1031`,`Tbl1005`...
I have a stored procedure that takes a table name and writes out a series of INSERT statements, one for each row in the table. It's being used to provide sample, "real world" data for our test environment. It works well but some of these sample rowsets are 10, 20k records. The stored proc writes them out using the PRINT statement and ...
Hello, I want to write a stored procedure that queries XML files after I have input a certain string pattern to look for. I'm already stuck at the input parameters, consider the following XML-document. <root> <container> <element>A</element> <option>1</option> </container> <container> <element>B</element> ...
Hello, "Yet another string function" question for TSQL. The ExecutionLogStorage table in the ReportServer$InstanceName database for SSRS 2008 puts all parameters called to the report in a single column - FullName=LastName, FirstName&CalledBy=Lastname, FirstName&DateSelected=MM/DD/YY&CheeseorFries=Fries Some are null values, so the u...
I have two tables: AgeMilestones //Represents available timespans Id int Description varchar //(newborn, 1 month old, 2 month old, etc.) NbrMonths int //( 0, 1, 2, etc.) NbrDays int //(0, 1, 2, etc.) Child Id int DateOfBirth DateTime I need to get the AgeMilestones that a given child has currently passed in age. The ...
I am trying to export data into into a text file using bcp utility? I could n't figure out what i am doing wrong here:- Declare @Cmd nvarchar(1000) Declare @Query nvarchar(1000) Declare @Path nvarchar(500) Declare @ServerName nvarchar(500) Declare @UserName nvarchar(50) Declare @Password nvarchar(50) Declare @Delimiter char(1) SET @Pat...
Let's say I have this SQL query: declare @input varchar(20) select * from myTable where CONTAINS (myColumn, ISNULL(@input, 'replacement text')) If I try to execute this, I get an error that says "Incorrect syntax near 'ISNULL'." I know that this is the correct syntax for ISNULL. Is there a reason why the ISNULL cannot be called inside...
Hi, I'm trying to achieve this select case when Org_CD = '1111' or Org_CD in (select distinct New_Org_CD from #temp) then 'International' end as 'Organisation', count(*) from #AnotherTempTable group by case when Org_CD = '1111' or Org_CD in (select distinct New_Org_CD from #temp) then 'International' end I re...
Hi I'm having a problem with using the max statement in the below query i am working on UPDATE TB_TS_CM_OFIRECPT SET AMT = (COALESCE (ABS(CMLEDG.TRANAMT), 0) + COALESCE (ABS(CMLEDG_1.TRANAMT), 0)) * - 1, TRANAMT = ABS(CMLEDG.TRANAMT) *-1, RTAXAMT = COALESCE (ABS(CMLEDG_1.TRANAMT) * - 1, 0), TRANID = CMLEDG.TRANID FROM TB_TS_CM_OFIREC...
I'm trying to add a simple trigger to a table- the 1st issue i came accross was that this table has text columns - so the for delete, insert, update triggers aren't going to float. 'instead of' does though. I am now up against the fact that the table has cascades set on it. Do you know if there's a way to get around that little gem or a...
Hello. I was wondering if someone could help I've successfully been able to import an XML document to a table with an XML data type in SQL Server2008, how ever when I try and shred from that table to a staging table any DATE values without an entered date are inserted to the staging table as 1900-01-01. Is there a cleaver way i'm mi...
I'm trying to run the following TSQL statement on Microsoft SQL 2008 DECLARE @tmpMessage nvarchar(max) SET @tmpMessage = 'select * from openquery(GLive,''select ID from Links WHERE [HREF] LIKE ''test'''')'; exec sp_executesql @tmpMessage the above code doesnt work because the single quotes before test closes the main quotes around t...
This is the sample output Let me explain what's going on: The query returns all invoices # of every year along with the products that is involved in the invoice. As you see, we have two invoice in 2010...The invoices are 30463 and 30516. The invoice 30463 has 4 products, its shipping price is 105.88. As you see the shipping price is r...
DECLARE @DatabaseName NVARCHAR(max); SET @DatabaseName = 'MainDb' USE @DatabaseName Wouldn't work. How to make it? ...