Strange decimal rounding

Hi all I'm having a strange problem of rounding calculations with decimal variables in SQL Server (2008). It seems to me that it do not return the correct value when it performs a division to 1000000 (one million). I have this test script, that anyone can try in Management Studio: declare @input table (Consumi decimal(28,10), PCI decimal(28,10)) Insert into @input (Consumi,PCI) VALUES(3.767, 42621.0000000000) select * From @input declare @test table (Consumi decimal(28,10), PCI decimal(28,10), Contenuto decimal(38,10)) Insert into @test select Consumi, PCI, Consumi*PCI/1

Ranking question

Hello - Each individual has many records that are ranked as follows: Jim 1 Jim 2 Jim 3 George 1 George 2 Bob 1 Bob 2 Bob 3 Bob 4 I need a list returned with each individual's highest ranking e.g. Jim 3 George 2 Bob 4 How do I get to this result? -- Sheldon

Run as a transaction?

Hi Periodically I upload a table "tblLanguageValues" from my laptop to our hosted SQL 2005 server in Florida. It has 65,000 rows so takes about 30 minutes. During that time our website goes down because each page uses that table to fetch text items. Once the upload is complete all is well with it. I create a script locally using the "Database Publishing Wizard" to script that one table only and save it to a file "1.SQL". Then I open the remote database via SQL Server Management Studio Express and run the script. Can I do the whole thing as a transaction so the website rem

optimizer not executing my SP

I am getting "invalid object name '#Workspace_List'" in display estimated execution plan which works fine when I actually run it. One explanation was that the temporary table doesn't yet exist. But I have other stored procedures that have temporary tables that work fine. The procedure is doing a SELECT/INTO the temporary table. Why would I get the error and is there a way fix it so I can look at my plan to see what needs to be optimized? Thanks, Tom

Infinite Recursive CTE

I have a situation where you can have an infinite recusion. There is an issue where editing a page could cause infinite recursion. This would happen if a distribution folder were put into itself. It would also happen if you put folder "A" into folder "B" where folder "B" is in any of the folders contained in any of the folders found in folder "A". A little convoluted sentence but that is the problem. A is in B and B is in C If you put A into C then C will eventually point back to itself and you the recursion will continue on YOu have to have a way to say that you have alr

SQL connections

Hi All, I have a requirement to be monitoring the SQL connnection to one server, and before to do a process to be loggin into a table each log in and each log out, I would like to ask you if you know if exists something in SQL that could be saving all the conections for a range of time. I don't want to use the SQL profiler because it depends if the DBA turn it or not. the solution should be something that can be installed and/or reader directly from the server. Thanks Carlos A. Lopez

convert string to datetime

Hi all, I used this to append to a some backups. DECLARE @DATE VARCHAR(20) = CONVERT(VARCHAR(10),CURRENT_TIMESTAMP,112) + '-' + RIGHT('00' + CAST(DATEPART(HOUR,CURRENT_TIMESTAMP) AS VARCHAR(10)),2) + RIGHT('00' + CAST(DATEPART(MINUTE,CURRENT_TIMESTAMP)AS VARCHAR(10)),2) + RIGHT('00' + CAST(DATEPART(SECOND,CURRENT_TIMESTAMP)AS VARCHAR(10)),2) How can I convert this back to datetime? After pasing out of the filename and removing the dash this is what is left: 20100303075951 thanks gv

Optimizing query

I have a query where the execution plan is showing a table scan with 32% cost. I have tried to put indexes on pretty much all the fields and still get the same result. The predicate from the Query Analysers execution plan for this table is: [dbo].[REVIEWER].[EnvironmentID]=[@EnvironmentID] AND ([@NameChoice] IS NULL OR CASE WHEN isnull([dbo].[REVIEWER].[FirstName],'')<>'' THEN ([dbo].[REVIEWER].[LastName]+', ')+[dbo].[REVIEWER].[FirstName] ELSE CASE WHEN isnull([dbo].[REVIEWER].[FirstName],'')='' THEN [dbo].[REVIEWER].[LastName] ELSE NULL END END like [@NameChoice]+'%')

order by case value

If I have the following: SELECT UserID as RID ,EnvironmentID as EnvID ,@authorType as RType ,'A' as Type ,'Author' as FullType ,Case When (isnull(FirstName, '') <> '' AND isnull(LastName, '') <> '') then LastName + ', ' + FirstName When (isnull(FirstName, '') <> '' AND isnull(LastName, '') = '') then FirstName When isnull(FirstName, '') = '' then LastName End as RName ,Email as EmailAddress FROM User WHERE EnvironmentID = @EnvironmentID Now I want to only have rows that have values that start with a letter. Do I have to do this with a derived table

Option(RECOMPILE)

I have a UDF say Which is caluculating salary of an employee by using Cusror. Now i m using that UDF in a View in which i m passing it Id for a value in view. That's working fine if no of records are say around 1000 but if No are Increasing then View is working very slow Because foreach row in view that function is executing and Output is produced I have Searched for solution I Find an way to use Option(Recompile) but don't know how to use with UDF. Any Other Suggestion to Improve performance of view will bw welcomed Thanx

SQL2000: Permissions Stored Procedures

I have some stored procedures, which I've granted execute permissions to a user for. When the user hits the stored procedures from an outside program, it errors out stating that the permissions are required for the underlying tables. Both the tables & the stored procedure are owned by dbo. I only want to grant permissions on the procedures, not the individual tables. Thanks in advance for any help. It would be greatly appreciated. bh

Convert Positive Value to Negative Value

How can I get the "Amount" to be a negative number when the "Code" = "G6"? Maybe I could multiply by -1 and use the CASE statement, but I don't know how. Table Name = EncounterData Amount Code $15.00 G2 $13.00 G2 $18.00 G6 $16.00 G2 $25.00 G6 $30.00 G6 -- Tyro from Missouri

Subquery for column names

I have a database table with a large amount of analytical data in it - all floats but for a timestamp column. Instead of returning the entire set, I want to be able to return specific columns based on a "system" number that is specified as a parameter (in this case it is written in as "S02" for testing purposes) and compare the provided system number with the column names within the table - which is what the subquery currently does correctly. Aside from creating a stored procedure to handle the result and reformat it into a string to then use as a parameter of a second query, is th

Query

I would like to get sum of the price for all items from table ProductVariant CREATE TABLE #Kits ( ShoppingCartRecId int, InventoryVariantId int, SumTotal money ) INSERT #Kits (ShoppingCartRecId,InventoryVariantId) select ShoppingCartRecId,InventoryVariantId from Orders_KitCart update x set x.SumTotal=y.max([Price]) from #Kits as x inner join ProductVariant as y on x.InventoryVariantId=y.skusuffix I get an error Cannot find either column "y" or the user-defined function or aggregate "y.sum", or the name is ambiguous.

SELECT SUBSTRING

I want to select a substring from a text this way: blablaNAME:myNameADDRESS:myAddressblabla What I want to get is all the text between "NAME:" and "ADDRESS:" I'm trying: SELECT SUBSTRING(myField, PATINDEX ('%NAME:%', testo) + 5, xxxxxx) Of course I can't know how long the substring is, so I don't know what to put over the xxxxx Anybody helps me? Thanks. Luciano

Xml shredding performance

Hi, we are having some performance issues regarding xml shredding. At this point we are extracting data from xmls from nearly 60 different companies - and therefore 60 different xml structures. The total amount of xml is about 350MB and we are trying to extract the data as fast as possible. Our current system extracts, transforms and loads the data in about five minutes. We would however like to do this in about one minute to be pleased. We use the "nodes/cross apply"-technique to shred the xmls into our internal format. This is how we shred the data. ----------------

Table Insert Strategy in SQL Server 2005

Nice people in this forum showed me excellent trick how to perform delete in small chunks. I was wondering if there is a trick to perfrom the same but for inserts. I have a single query that inserts rows from select statement. The select statement produces tens of millions of rows. Thanks in advance

Transactions in SQL Server 2005

A stored procedure loops through a cursor. For each entry it invokes another stored procedure. That other stored procedure updates table other than was defined in the cursor. After so many updates, the other stored procedure commits work. Would the commit in other stored procedure affect the cursor? Thanks in advance

index needed for join and where clause

I have an index related question and let me set up the tables and indexes as follows. Table Test Field A int primary cluster key Field B int Field C varchar(50) Field D int Field Z int Table Test2 E int Primary clustered index F int Index ix_test_a on Test(B, D) index ix_test_b on Test(D, C,Z, B) Index ix_test2 on Test2(F) Will the above index, ix_test_a on table Test be used for the following three statements? Do I need to create another index ix_text_c on Test(D, B)? or ix_text_b will cover this order specific situation already. Let assume all the indexes

sql server displays

I would like to know what I can use in sql server report 2008 to display several sentences or paragraphs prior to where I have a table generated. Each table in each report will display the data. To display the paragraph(s) and/or sentence(s) before the table containing data what toolbox item would I use? Should I use a matrix, rectangle, textbox, and or another table?

Index include primiary key

I have the following Table structure Table Test Field A int primary cluster key Field B int Field C varchar(50) Field D int Say I do a select statement as follows: Select B, C from Test where D = 1000 To improve this query, I create an index for field D. My question is, is there any benefit to put Field A in the 'Include columns'? Would it be redundent and unnecessary as the root key of the index already has the RID to point to the actual data location? Or this will make the search even quicker since the actual primary key is provided within the index. od -- Mess

Q;re Descriptive stats

Hi Suppose I have the following data in a column (if is sample data, but the DB I am working with has some specific values to indicate other data properties - I fully accept that there may well be better ways to indicate this). Elapsed Days 1 2 3 4 5 6 7 8 NULL -99999 -88888 What SQL can I write so that I can eliminate the -99999 and -88888 for the purposes of min/max/avg/sum yet include them for the purposes of count So my stats would be count=10 min=1 max=8 sum=36 avg=3.6 Ideally I need to be able to do this in a single SQL statement. I have had a

Null in select list

I have never seen this format before: INSERT INTO #temp SELECT name, null [Title] FROM persons Is that the same as: INSERT INTO #temp SELECT name, CASE WHEN Title IS NULL THEN NULL ELSE Title END FROM persons Thanks, Tom

Need help with a recursive query

I have the following query that takes a base member of it & traces its parentage that excludes the top level of the table (PLOB). select z.id, parent = (select top 1 y.id from mbrProduct y where y.id = z.parenth1), grandparent = (select top 1 a.id from mbrProduct a where a.id = (select top 1 y.parenth1 from mbrProduct y where y.id = z.parenth1)), grgrandparent = (select top 1 b.id from mbrProduct b where b.id = (select top 1 a.parenth1 from mbrProduct a where a.id = (select top 1 y.parenth1 from mbrProduct y where y.id = z.parenth1))and b.parenth1 not in ('PLOB')) , grgrgrand

Random assignment

I have three tables CREATE TABLE [dbo].[STAFF]( [StaffKEY] [int] NOT NULL, [StaffName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ) ON [PRIMARY] CREATE TABLE [dbo].[Clients]( [ClientKEY] [int] not NULL, [ClientName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ) ON [PRIMARY] CREATE TABLE [dbo].[STAFF_Collections]( [CLIENTKEY] [int] NULL, [StaffName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Staffkey] [int] NULL ) ON [PRIMARY] Sample data Staff: 303, 'Paul Newman' 405, 'Jane Fonda' 605,'Lola Falana' 789

trying to return data with only the latest date

when i run this i get data for the last day. i'm trying to get it to return only for the latest date. for example the instance name column contains all the drive letters. i would like it to return one row for each machinename and drive letter. tried adding distinct but didn't work with free_hd_space_cte as ( select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, b.countervalue, convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead from counterdetails a inner join counterdata b on a.counterid = b.counterid wher

Memory Table vs. CTE

I had posted a week or so ago about a performance issue with Null-able variables being passed into stored procedures and the performance gains by setting those parameters to local variables declared in the stored procedure. I followed that advice and all was well. How ever, the stored procedure started to time out the again, running the stored proc on my local SQL server would take 3:45 to return 5 rows. Something was wrong. The stored procedure was using 4 CTE tables, one of those tables being referenced in 5 different locations through-out the stored proc. The only change I made t

non-equal DRI: a possible new sql feature

(contrived example coming) if i sell christmas trees and hire temp workers each year and i also reward the top sellers how can i enforce sales data within the timeframe the employee works for me? The same guy could work for me each year. I don't want to be concerned having to figure out sales from an Emp that were outside his hiredates. the DB should do that for me. CREATE TABLE Employees (Fname VARCHAR(20), LName VARCHAR(20)); CREATE TABLE EmployeeHireDates( Fname VARCHAR(20), LName VARCHAR(20), HireDate DATETIME, TerminationDate DATETIME); (insert obvious PK and FK in the abbr

T-SQL to retrieve language pairs with a fallback to a default language when missing

I'm having difficulty trying to write a stored procedure that retrieves records from a table that stores culture codes. Here is the code used to create the table and store some records for test purposes... CREATE TABLE [dbo].[CultureCodes] ( [LanguageID] [nvarchar](15) NOT NULL, [CultureCode] [nvarchar](15) NOT NULL, [Language] [nvarchar](35) NOT NULL ) Insert Into CultureCodes (LanguageID, CultureCode, Language) Values ('en-us', 'en-us', 'English - USA') Insert Into CultureCodes (LanguageID, CultureCode, Language) Values ('en-ca', 'en-us', 'English - Canada') Insert In

CLR UDF, how to tell selectivity to the optimizer?

We use some CLR UDFs to process BLOBs. In many cases, the optimizer seems to choose a bad plan because it guesses that our UDF will limit the results much more than it does. For example, when we have a WHERE condition such as OurUDF( Data ) = 1, the optimizer apparently guesses that the number of rows will drop to one third (33%). In reality, the number of rows remains almost the same because the UDF will return 1 for 95% to 100% of rows. Is it possible to tell the optimizer that this UDF has poor selectivity and that it is thus better to evaluate almost any other restricting co

TSQL Transpose

Hi All! I'm trying to create a query that does a real transpose similar to Excel function - it is slightly different from Pivot/Unpivot functionality of SQL Server. Basically, I have a date dimension table and I need to list corresponding dates, separated by coma for each month. So, for January 2010, my results would be something like this: Month Dates 2010-01 01/01/2010, 01/02/2010, 01/03/2010...01/31/2010 What would be a way to achieve this? Thanks, Pasha

AdventureWorks: can I query table w/o using table_schema name - in

I tried to query the Contacts (Person.Contacts) table in the AdventureWorks DB, but it wouldn't let me unless I included Person. in front of the table name: select * from Person.Contact If I query the same table from a VS(2008) project using LinQ To Sql, this appears to forgo the table_schema name. Is there a way to forgo the table_schema name in QA? Thanks, Rich

Update Query

I have a table called accthist which has the following coloums Acct, subacct, BegBal, YearEndBal, FiscYR I want to update the BegBal for FiscYR 2010 for all distinct (acct subacct) items with the YearEndBal for these distinct (acct subacct) items from FiscYR 2009. So basically set the BegBal for 2010 with the YearEndBal for year 2009. Could someone guide me on the sql for this. It will be highly appreciated. Thanks Sam

eigenheimzulage antragsformular , kredit zum hauskauf , kredit hausbau , hypothekenkredit konditionen , hypothek formel , ratgeber immobilienfinanzierung , baufinanzierung aktuell ,

eigenheimzulage antragsformular , kredit zum hauskauf , kredit hausbau , hypothekenkredit konditionen , hypothek formel , ratgeber immobilienfinanzierung , baufinanzierung aktuell , + + +++ GUENSTIGE KREDITE ONLINE +++ KREDITE IM INTERNET OHNE SCHUFA IMMOBILIEN +++ + http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL ht

Set Transaction Syntax?

I am trying to get some 'case/set' logic down to change the transaction level in TSQL and it's not working, can anyone comment on what I would have to change? Declare @tx Int Set @tx = ( select transaction_isolation_level from sys.dm_exec_sessions where session_id = @@Spid ) print @tx -- later the transaction level is set to something else for some -- other tsql to run -- now i need to set it back to what it was earlier select case @origTxLevel When 1 Then Set Transaction Isolation Level Read Uncommitted When 2 Then Set Transaction Isolation Level Read Committed When 3 The

Returning ID's with broken series

Table 'TEST' has information on 4 staff members. For Staff_ids 1 and 4 there is no missing (brake) year while for staff_id 2 and 3 there is brake in years. Can someone help me with a query to return only the staff_ids for which one or more years are missing in between. CREATE TABLE [dbo].[TEST]( [Staff_id] [int] NOT NULL, [Year] [int] NOT NULL ) ON [PRIMARY] GO insert into TEST select 1,2005 insert into TEST select 1,2006 insert into TEST select 1,2007 insert into TEST select 1,2008 insert into TEST select 2,2005 insert into TEST select 2,2007 insert into TEST

likining view from other DB?

Hi, It have 2 databases on my SQL Server, a Project DB and a Village Stastictic DB. Most of the users use Project DB. I want users to assess the statistic from village DB, but instead of creating new front ent and seting up new ODBC connection for them, can I just link the view from Village Statistic DB to the Project DB. If so how to do that. SF

Show actual days

I have two tables : CREATE TABLE [dbo].[FD__PROGRAM_CLIENT]( [ClientKey] [int] NOT NULL, [PgmKey] [int] NULL, [Date_Admit_Program] [datetime] NULL, [Date_Discharged_Program] [datetime] NULL, CONSTRAINT [PK_SYS_FD__PROGRAM_CLIENT] PRIMARY KEY CLUSTERED CREATE TABLE [dbo].[DateLookup]( [DateKey] [int] NOT NULL, [DateFull] [datetime] NULL, [CharacterDate] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FullYear] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [QuarterNumber] [tinyint] NULL, [WeekNumber] [tinyint] NULL, [WeekDayName] [varchar](10)

Drop time conditionally

I have an SQL SELECT (see below) that I want to have the column named ActivityDate display as either a date and time or just a date. For example, if the time is anything other than 12:00 AM then I want to show the date and time, otherwise, just the date. Thanks. SELECT [ActivityID], [PeopleLinkID], [ActivityCode], [ActivityDate], [EnteredBy], [ActivityNotes FROM [vw_ActivityHistoryCaseNotes] WHERE ([ActivityID] = @ActivityID) -- David

DBCC Showcontig

What does it mean when this report shows blank under index name? Thanks in advance

insert BLOB from web source

Hi, I'm trying to fetch and insert an image into a column, but I keep getting an error: Cannot bulk load because the file "http:\ \www.google.co.uk/images/nav_logo7.png" could not be opened. Operating system error code 123(error not found). Here is my query: declare @BLOBTest table (BLOBName VarChar(50), BLOBData VarBinary(Max)) INSERT INTO BLOBTest (BLOBName, BLOBData) SELECT 'First test file', BulkColumn FROM OPENROWSET( Bulk 'http:\\www.google.co.uk/images/nav_logo7.png', SINGLE_BLOB) AS BLOB select * from @BLOBTest Any ideas?

NTUserName not working in Profiler as a filter.

We are running traces and tried to use NTUserName as a filter. If I have "Like" = "joe" and joe is not an NTUserName - it still displays the traces. The Column shows nothing in the NTUserName when stored procedures come across from my web page. I expected that since this is tracking a web page and not logged on as a user, but does it show the traces where the column is blank? It also works fine from a Query from SSMS where I am logged on a Windows user. thanks, tom

HELP-SQL Server 2005 Rollup Query for Distinct Values

Folks, I realize that group by rollup for distinct values in SQL Server 2005 is not supported. Can you please help me in writing the following situation. I have a table which keeps track of an Employee working in multiple offices located in different states. I need to get the count of Employees working in each office by state and/or office code. Please keep in mind that I have simplified the question using this example. My case actually has close to 7 dimensions. Table=WorkPlace State OfficeCode EmployeeId ========================== NJ O1

Need help with OUTPUT parameters

Hi, I am trying to execute two child stored procedures from a parent one. The first stored procedure inserts a new company into Companies table and returns its CompanyID, the second stored procedure inserts an address into Addresses table and returns its AddressID and the third one assigns the address to the company. Everything works fine but when I run the parent stored procedure, I only want to return the CompanyID back to my application. However, the code below returns both CompanyID and AddressID. I'm using OUTPUT clause so that I can grab CompanyID and AddressID retur

UPDATETEXT Dropping Character

I am using UPDATETEXT to update and empty ntext field. I con't figure out why the last character, a period, keeps getting dropped? This is SQL 2005. DECLARE @name nvarchar(60) DECLARE @ptr binary(16) DECLARE @buf nvarchar(1000) UPDATE receiptFormat SET [xmlData] = '', jsData = '', defaultBeforeFl = 0, defaultDuringFl = 0, defaultAfterFl = 0, allCentersFl = 1, warningFl = 0, errorFl = 0, workflowID = NULL, receiptFormatTypeID = 1 WHERE [name] = 'DeliveryAdjustmentReceipt' SELECT @ptr = TEXTPTR(xmlData) FROM receiptFor

sql server deadlock (keylock)

hi guys, I have two procedures: one for selecting data from a table and do some calculations and the other for update or insert into the same table. There are two indexes on that table: one clustered (primary key): Pk_Table and one nonclustered: Ix_Table. I've captured a few deadlocks on those two procedure executions. It seems one proc gets x lock on the Pk_table key first and the other proc gets the x lock on Ix_Table and then both proc tries to get s lock on the other key. Then the deadlock happens. The below is part of deadlock xml: <resource-list> <key

How to get a list of a tables column names

[I originally sent this last week, but as it hasn't shown up yet, I'm sending it again. Apologies if it shows up twice.] Using SQL 2008. In SSMS Query Analyzer, given a table that I'm interested in, I would like to output the names of all that tables column names. I'd like it to be a list displayed in the Results pane, with one column name per row, left-justified if possible. I want to be able to copy and paste those names. (I know that if I set "Result to Text" and execute "select top 0 * from tablename" it will display all the column names such that I can copy and paste

what hosting deal you recommend me?

Hi asp.net 3.5 My site take about 3 seconds to load, as a result my site has a drop in google ranking from 40 to 500 (very competive niche). Google has started taking site peformance as a factor for ranking. Today my site is hosted on a shared cluster at the host, because it's shared the host will not enable compression or browser caching etc The database is MSSQL 2005, so the eventuel new host must support .NET and MSSQL 2005 I talked to a support guy at webhuset.no (the host) and they said the next step would be to host the site on a virtual server. the entry price f

Copying Information

> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3350283589_2423585 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit Hello everyone, I'm having trouble copying data from a table ( st ) in one db ( tecnicil_industria1 ) to the same table in a different db ( tecnicil_ind2010r ). Below is my query and the result error I'm getting. Can any help on how to go about doing this: Use tecnicil_ind2010r Go insert into st select * from tecnicil_industria1..st

How to script all Stored procedures as Alter

I am trying to script all my stored procedures to a query window. And I am using the Scripts Wizard which by default does a Create for all my procedures. I tried changing the "Script Create" option to false but it didn't work. It ran as if it did but I assume since I said false to Script Create it had nothing to do and a new Query window never opened up. If I say true to Script Create it works fine. If you do an individual script it works fine (without the wizard). Is there a way to do it? Thanks, Tom