Search Results

Search found 1100 results on 44 pages for 'dbo'.

Page 16/44 | < Previous Page | 12 13 14 15 16 17 18 19 20 21 22 23  | Next Page >

  • Why Sql not bringing back results unless I set varchar size?

    - by Tom
    I've got an SQL script that fetches results based on the colour passed to it, but unless I set the size of the variable defined as a varchar to (50) no results are returned. If I use: like ''+@Colour+'%' then it works but I don't really want to use it in case it brings back results I don't need or want. The column FieldValue has a type of Varchar(Max) (which can't be changed as this field can store different things). It is part of aspdotnetstorefront package so I can't really change the tables or field types. This doesn't work: declare @Col VarChar set @Col = 'blu' select * from dbo.MetaData as MD where MD.FieldValue = @Colour But this does work: declare @Col VarChar (50) set @Col = 'blu' select * from dbo.MetaData as MD where MD.FieldValue = @Colour The code is used in the following context, but should work either way <query name="Products" rowElementName="Variant"> <sql> <![CDATA[ select * from dbo.MetaData as MD where MD.Colour = @Colour ]]> </sql> <queryparam paramname="@ProductID" paramtype="runtime" requestparamname="pID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" /> <queryparam paramname="@Colour" paramtype="runtime" requestparamname="pCol" sqlDataType="varchar" defvalue="" validationpattern=""/> </query> Any Ideas?

    Read the article

  • SQL Concurrent test update question

    - by ptoinson
    Howdy Folks, I have a SQLServer 2008 database in which I have a table for Tags. A tag is just an id and a name. The definition of the tags table looks like: CREATE TABLE [dbo].[Tag]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](255) NOT NULL CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) Name is also a unique index. further I have several processes adding data to this table at a pretty rapid rate. These processes use a stored proc that looks like: ALTER PROC [dbo].[lg_Tag_Insert] @Name varchar(255) AS DECLARE @ID int SET @ID = (select ID from Tag where Name=@Name ) if @ID is null begin INSERT Tag(Name) VALUES (@Name) RETURN SCOPE_IDENTITY() end else begin return @ID end My issues is that, other than being a novice at concurrent database design, there seems to be a race condition that is causing me to occasionally get an error that I'm trying to enter duplicate keys (Name) into the DB. The error is: Cannot insert duplicate key row in object 'dbo.Tag' with unique index 'IX_Tag_Name'. This makes sense, I'm just not sure how to fix this. If it where code I would know how to lock the right areas. SQLServer is quite a different beast. First question is what is the proper way to code this 'check, then update pattern'? It seems I need to get an exclusive lock on the row during the check, rather than a shared lock, but it's not clear to me the best way to do that. Any help in the right direction will be greatly appreciated. Thanks in advance.

    Read the article

  • NHibernate CreateSqlQuery and object graph

    - by magellings
    Hello I'm a newbie to NHibernate. I'd like to make one sql query to the database using joins to my three tables. I have an Application with many Roles with many Users. I'm trying to get NHibernate to properly form the object graph starting with the Application object. For example, if I have 10 application records, I want 10 application objects and then those objects have their roles which have their users. What I'm getting however resembles a Cartesian product in which I have as many Application objects as total User records. I've looked into this quite a bit and am not sure if it is possible to form the application hierarchy correctly. I can only get the flattened objects to come back. It seems "maybe" possible as in my research I've read about "grouped joins" and "hierarchical output" with an upcoming LINQ to NHibernate release. Again though I'm a newbie. [Update Based on Frans comment in Ayende's post here I'm guessing what I want to do is not possible http://ayende.com/Blog/archive/2008/12/01/solving-the-select-n1-problem.aspx ] Thanks for you time in advance. Session.CreateSQLQuery(@"SELECT a.ID, a.InternalName, r.ID, r.ApplicationID, r.Name, u.UserID, u.RoleID FROM dbo.[Application] a JOIN dbo.[Roles] r ON a.ID = r.ApplicationID JOIN dbo.[UserRoleXRef] u ON u.RoleID = r.ID") .AddEntity("app", typeof(RightsBasedSecurityApplication)) .AddJoin("role", "app.Roles") .AddJoin("user", "role.RightsUsers") .List<RightsBasedSecurityApplication>().AsQueryable();

    Read the article

  • sql exception arithmetic overflow?

    - by MyHeadHurts
    In my program the user imports a date and it works whenever the year is in 2011 but if i try a date in 2010 i get this error which is weird [ SqlException (0x80131904): Arithmetic overflow error converting int to data type numeric.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlDataReader.HasMoreRows() +157 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +197 System.Data.SqlClient.SqlDataReader.Read() +9 System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +78 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +164 System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +282 System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +19 System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) +222 System.Data.DataTable.Load(IDataReader reader) +14 ( @YearToGet int, @current datetime, @y int, @search datetime ) AS SET @YearToGet = 2006; WITH Years AS ( SELECT DATEPART(year, GETDATE()) [Year] UNION ALL SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet ), q_00 as ( select DIVISION , DYYYY , sum(PARTY) as asofPAX , sum(InsAmount) as asofSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int, DateAdd(year, (Years.Year - @y), @search)) and DYYYY = Years.Year group by DIVISION, DYYYY, years.year having DYYYY = years.year ), q_01 as ( select DIVISION , DYYYY , sum(PARTY) as YEPAX , sum(InsAmount) as YESales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year group by DIVISION, DYYYY , years.year having DYYYY = years.year ), q_02 as ( select DIVISION , DYYYY , sum(PARTY) as CurrentPAX , sum(InsAmount) as CurrentSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int,@current) and DYYYY = (year( getdate() )) group by DIVISION, DYYYY ) select a.DIVISION , a.DYYYY , asofPAX , asofSales , YEPAX , YESales , CurrentPAX , CurrentSales ,asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales, CAST((asofpax) AS DECIMAL(5,1))/yepax as percentpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION) JOIN Years as d on (b.dyyyy = d.year) where A.DYYYY <> (year( getdate() )) order by a.DIVISION, a.DYYYY ;

    Read the article

  • How could I create a FBO with stencil buffer in OpenGL ES 2.0?

    - by Alphones
    I need stencil buffer on 3GS to render planar shadow, and polygon offset won't work prefect, still has z-fighting problem. So I use stencil buffer to make the shadow correct, it works on win32 gles2 emulator, but not on iPhone. After I added a post effect to the whole scene. The stencil buffer won't work even on win32 gles2 emulator. And I tried to attach a stencil buffer to FBO, buf the screen turns to black. Here's my code, glGenRenderbuffers(1, &dbo); // depth buffer glBindRenderbuffer(GL_RENDERBUFFER, dbo); glRenderbufferStorage(GL_RENDERBUFFER, GL_DEPTH_COMPONENT24_OES, widthGL, heightGL); glGenRenderbuffers(1, &sbo); // stencil buffer glBindRenderbuffer(GL_RENDERBUFFER, sbo); glRenderbufferStorage(GL_RENDERBUFFER, GL_STENCIL_INDEX8, widthGL, heightGL); glGenFramebuffers(1, &fbo); glBindFramebuffer(GL_FRAMEBUFFER, fbo); glFramebufferTexture2D(GL_FRAMEBUFFER, GL_COLOR_ATTACHMENT0, GL_TEXTURE_2D, tex, 0); glFramebufferRenderbuffer(GL_FRAMEBUFFER, GL_DEPTH_ATTACHMENT, GL_RENDERBUFFER, dbo); glFramebufferRenderbuffer(GL_FRAMEBUFFER, GL_STENCIL_ATTACHMENT, GL_RENDERBUFFER, sbo); // this make the whole screen black. The eglContext is created with STENCIL_SIZE=8, it works without a RTT. I tried to change the RenderbufferStorage for both depth buffer and stencil buffer, but none of them works. Is there anything I have missed? Does the stencil buffer pack with depth buffer? (I cannot find things like GL_DEPTH24_STENCIL8 ...)

    Read the article

  • Same query has nested loops when used with INSERT, but Hash Match without.

    - by AaronLS
    I have two tables, one has about 1500 records and the other has about 300000 child records. About a 1:200 ratio. I stage the parent table to a staging table, SomeParentTable_Staging, and then I stage all of it's child records, but I only want the ones that are related to the records I staged in the parent table. So I use the below query to perform this staging by joining with the parent tables staged data. --Stage child records INSERT INTO [dbo].[SomeChildTable_Staging] ([SomeChildTableId] ,[SomeParentTableId] ,SomeData1 ,SomeData2 ,SomeData3 ,SomeData4 ) SELECT [SomeChildTableId] ,D.[SomeParentTableId] ,SomeData1 ,SomeData2 ,SomeData3 ,SomeData4 FROM [dbo].[SomeChildTable] D INNER JOIN dbo.SomeParentTable_Staging I ON D.SomeParentTableID = I.SomeParentTableID; The execution plan indicates that the tables are being joined with a Nested Loop. When I run just the select portion of the query without the insert, the join is performed with Hash Match. So the select statement is the same, but in the context of an insert it uses the slower nested loop. I have added non-clustered index on the D.SomeParentTableID so that there is an index on both sides of the join. I.SomeParentTableID is a primary key with clustered index. Why does it use a nested loop for inserts that use a join? Is there a way to improve the performance of the join for the insert?

    Read the article

  • MS Query returns data inside itself but does not export it to Excel

    - by kappa
    Hi, I'm having a strange problem with Excel and MS Query: I'm using MS Query to run a T-SQL query against a Microsoft SQL Server 2000 and return the results to Excel. To do this, I open Excel, go to Data - Import external data - New database query, select my data source, paste the SQL script in MS Query and click File - Return data to Microsoft Office Excel, leaving all the query options to their defaults. This works fine for many other Excel files, but this time although MS Query shows the correct data when I paste the SQL script, after returning to Excel all I get is the query name in the upper left cell, with no data returned. I fear the cause could be the SQL script, as it contains some advanced functions like union all, UDFs and variables. Here's the script: declare @date smalldatetime set @date = dateadd(day, datediff(day, 0, getdate()), 0) select [date], sum([hours]) as [hours] from ( select [date], [hours] from [server].[dbo].[udf] (84, '2010-01-01', @date) union all select [date], [hours] from [server].[dbo].[udf] (89, '2010-01-01', @date) union all select [date], [hours] from [server].[dbo].[udf] (93, '2010-01-01', @date) ) as [a] group by [date] order by [date] asc I can't get rid of the UDF as inside them are done advanced groupings involving cursors and temporary tables, nor I can remove the variable as the UDF won't accept dateadd(day, datediff(day, 0, getdate()), 0) as parameter. Any ideas? Thanks in advance, Andrea.

    Read the article

  • Heterogeneous queries require the ANSI_NULLS

    - by Dezigo
    I wrote a trigger. USE [TEST] GO /****** Object: Trigger [dbo].[TR_POSTGRESQL_UPDATE_YC] Script Date: 05/26/2010 08:54:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[TR_POSTGRESQL_UPDATE_YC] ON [dbo].[BCT_CNTR_EVENTS] FOR INSERT AS BEGIN DECLARE @MOVE_TIME varchar(14); DECLARE @MOVE_TIME_FORMATED varchar(20); DECLARE @RELEASE_NOTE varchar(32); DECLARE @CMR_NUMBER varchar(15); DECLARE @MOVE_TYPE varchar(2); SELECT @MOVE_TIME = inserted.move_time ,@MOVE_TYPE = inserted.move_type ,@RELEASE_NOTE = inserted.release_note ,@CMR_NUMBER = inserted.cmr_number FROM inserted IF(@MOVE_TYPE = 'YC') BEGIN SET @MOVE_TIME_FORMATED = SUBSTRING(@MOVE_TIME,1,4) + '-' + SUBSTRING(@MOVE_TIME,5,2) + '-' + SUBSTRING(@MOVE_TIME,7,2) + ' 00:00:00' --UPDATE OpenQuery(POSTGRESQL_SERV,'SELECT visit_cmr,visit_timestamp,visit_pin FROM VISIT') -- SET visit_cmr = @RELEASE_NOTE -- WHERE visit_timestamp = @MOVE_TIME_FORMATED -- AND visit_pin = right(@CMR_NUMBER,5) -- AND visit_cmr IS NULL END SET NOCOUNT ON; END When I have inserted a row,I have get an error **Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.** Then I ofcourse SET SET ANSI_WARNINGS is ON but it`s not work for me. (trigger fo linked server postgresql) I have restarted a server. not work again.

    Read the article

  • SQL Server problems reading columns with a foreign key

    - by illdev
    I have a weird situation, where simple queries seem to never finish for instance SELECT top 100 ArticleID FROM Article WHERE ProductGroupID=379114 returns immediately SELECT top 1000 ArticleID FROM Article WHERE ProductGroupID=379114 never returns SELECT ArticleID FROM Article WHERE ProductGroupID=379114 never returns SELECT top 1000 ArticleID FROM Article returns immediately By 'returning' I mean 'in query analyzer the green check mark appears and it says "Query executed successfully"'. I sometimes get the rows painted to the grid in qa, but still the query goes on waiting for my client to time out - 'sometimes': SELECT ProductGroupID AS Product23_1_, ArticleID AS ArticleID1_, ArticleID AS ArticleID18_0_, Inventory_Name AS Inventory3_18_0_, Inventory_UnitOfMeasure AS Inventory4_18_0_, BusinessKey AS Business5_18_0_, Name AS Name18_0_, ServesPeople AS ServesPe7_18_0_, InStock AS InStock18_0_, Description AS Descript9_18_0_, Description2 AS Descrip10_18_0_, TechnicalData AS Technic11_18_0_, IsDiscontinued AS IsDisco12_18_0_, Release AS Release18_0_, Classifications AS Classif14_18_0_, DistributorName AS Distrib15_18_0_, DistributorProductCode AS Distrib16_18_0_, Options AS Options18_0_, IsPromoted AS IsPromoted18_0_, IsBulkyFreight AS IsBulky19_18_0_, IsBackOrderOnly AS IsBackO20_18_0_, Price AS Price18_0_, Weight AS Weight18_0_, ProductGroupID AS Product23_18_0_, ConversationID AS Convers24_18_0_, DistributorID AS Distrib25_18_0_, type AS Type18_0_ FROM Article AS articles0_ WHERE (IsDiscontinued = '0') AND (ProductGroupID = 379121) shows this behavior. I have no idea what is going on. Probably select is broken ;) I got a foreign key on ProductGroups ALTER TABLE [dbo].[Article] WITH CHECK ADD CONSTRAINT [FK_ProductGroup_Articles] FOREIGN KEY([ProductGroupID]) REFERENCES [dbo].[ProductGroup] ([ProductGroupID]) GO ALTER TABLE [dbo].[Article] CHECK CONSTRAINT [FK_ProductGroup_Articles] there are some 6000 rows and IsDiscontinued is a bit, not null, but leaving this condition out does not change the outcome. Anyone can tell me how to handle such a situation? More info, anyone? Additional Info: this does not seem to be restricted to this Foreign Key, but all/some referencing this entity.

    Read the article

  • Update/Insert without select

    - by user348731
    I have this very simple class public class ProductAttributeValuePortal { public virtual int ID { get; set; } public virtual Domain.Entity.Portals.ProductPortal Product { get; set; } public virtual Attribute Attribute { get; set; } public virtual string Value { get; set; } } with this very simple map public ProductAttributeValueMap () { Table("DM.dbo.ProductAttributeValues"); Id(x => x.ID, "ProductAttributeValue_id"); References(x => x.Product); References(x => x.Attribute); Map(x => x.Value); } Each time i make a insert NHibernate makes a Select of the attribute like : NHibernate: INSERT INTO MachineData.dbo.ProductAttributeValues (Value, Product_id, Attribute_id) VALUES (@p0, @p1, @p2); select SCOPE_IDENTITY();@p0 = '6745', @p1 = 39, @p2 = 'BSTD' NHibernate: SELECT attribute_.Attribute_id, attribute_.Name as Name21_, attribute_.AttributeType as Attribut3_21_, attribute_.TagName as TagName21_, attribute_.MapTo as MapTo21_ FROM MachineShared.dbo.Attributes attribute_ WHERE attribute_.Attribute_id=@p0;@p0 = 'DLB' What am i doing wrong. And where do i find some really uptodate books about nhibernate/Fluent nhibernate

    Read the article

  • MS Access: Permission problems with views

    - by Keith Williams
    "I'll use an Access ADP" I said, "it's only a tiny project and I've got better things to do", I said, "I can build an interface really quickly in Access" I said. </sarcasm> Sorry for the rant, but it's Friday, I have a date in just under two hours, and I'm here late because this just isn't working - so, in despair, I turn to SO for help. Access ADP front-end, linked to a SQL Server 2008 database Using a SQL Server account to log into the database (for testing); this account is a member of the role, "Api"; this role has SELECT, EXECUTE, INSERT, UPDATE, DELETE access to the "Api" schema The "Api" schema is owned by "dbo" All tables have a corresponding view in the Api schema: e.g. dbo.Customer -- Api.Customers The rationale is that users don't have direct table access, but can deal with views as if they were tables I can log into SQL using my test login, and it works fine: no access to the tables, but I can select, insert, update and delete from the Api views. In Access, I see the views, I can open them, but whenever I try to insert or update, I get the following error: The SELECT permission was denied on the object '[Table name which the view is using]', database '[database name]', schema 'dbo' Crazy as it sounds, Access seems to be trying to access the underlying table rather than the view. Any ideas?

    Read the article

  • How to work with CTE. There is some error related to anchor.

    - by Shantanu Gupta
    I am creating a hierarchy representaion of a column. But an error occurs Details are Msg 240, Level 16, State 1, Line 1 Types don't match between the anchor and the recursive part in column "DISPLAY" of recursive query "CTE". I know there is some typecasting error. But I dont know how to remove error. Please just dont only sort out my error. I need explanation why this error is coming. When this error occurs. I am trying to sort table on the basis of sort col that i m introducing. I want to add '-' at every level and want to sort accordingly. Please help WITH CTE (PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, DISPLAY, SORT, DEPTH) AS ( SELECT PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, '-' AS DISPLAY, '--' AS SORT, 0 AS DEPTH FROM dbo.L_CATEGORY_TYPE WHERE FK_CATEGORY_ID IS NULL UNION ALL SELECT T.PK_CATEGORY_ID, T.[DESCRIPTION], T.FK_CATEGORY_ID, CAST(DISPLAY+T.[DESCRIPTION] AS VARCHAR(1000)), '--' AS SORT, C.DEPTH +1 FROM dbo.L_CATEGORY_TYPE T JOIN CTE C ON C.PK_CATEGORY_ID = T.FK_CATEGORY_ID --SELECT T.PK_CATEGORY_ID, C.SORT+T.[DESCRIPTION], T.FK_CATEGORY_ID --, CAST('--' + C.SORT AS VARCHAR(1000)) AS SORT, CAST(DEPTH +1 AS INT) AS DEPTH --FROM dbo.L_CATEGORY_TYPE T JOIN CTE C ON C.FK_CATEGORY_ID = T.PK_CATEGORY_ID ) SELECT PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, DISPLAY, SORT, DEPTH FROM CTE ORDER BY SORT

    Read the article

  • C# SQL Data Adapter Fill on existing typed Dataset

    - by René
    I have an option to choose between local based data storing (xml file) or SQL Server based. I already created a long time ago a typed dataset for my application to save data local in the xml file. Now, I have a bool that changes between Server based version and local version. If true my application get the data from the SQL Server. I'm not sure but It seems that Sql Adapter's Fill Method can't fill the Data in my existing schema SqlCommand cmd = new SqlCommand("Select * FROM dbo.Categories WHERE CatUserId = 1", _connection); cmd.CommandType = CommandType.Text; _sqlAdapter = new SqlDataAdapter(cmd); _sqlAdapter.TableMappings.Add("Categories", "dbo.Categories"); _sqlAdapter.Fill(Program.Dataset); This should fill my data from dbo.Categories to Categories (in my local, typed dataset). but it doesn't. It creates a new table with the name "Table". It looks like it can't handle the existing schema. I can't figure it out. Where is the problem? btw. of course the database request I do isn't very useful that way. It's just a simplified version for testing...

    Read the article

  • Sql Server 2005 Check Constraint not being applied in execution when using variables

    - by DarylS
    Here is some SQL sample code: --Create 2 Sales tables with constraints based on the saledate create table Sales1(SaleDate datetime, Amount money) ALTER TABLE dbo.Sales1 ADD CONSTRAINT CK_Sales1 CHECK (([SaleDate]>='01 May 2010')) GO create table Sales2(SaleDate datetime, Amount money) ALTER TABLE dbo.Sales2 ADD CONSTRAINT CK_Sales2 CHECK (([SaleDate]<'01 May 2010')) GO --Insert some data into Sales1 insert into Sales1 (SaleDate, Amount) values ('02 May 2010', 50) insert into Sales1 (SaleDate, Amount) values ('03 May 2010', 60) GO --Insert some data into Sales2 insert into Sales2 (SaleDate, Amount) values ('30 Mar 2010', 10) insert into Sales2 (SaleDate, Amount) values ('31 Mar 2010', 20) GO --Create a view that combines these 2 tables create VIEW [dbo].[Sales] AS SELECT SaleDate, Amount FROM Sales1 UNION ALL SELECT SaleDate, Amount FROM Sales2 GO --Get the results --Query 1 select * from Sales where SaleDate < '31 Mar 2010' -- if you look at the execution plan this query only looks at Sales2 (Which is good) --Query 2 DECLARE @SaleDate datetime SET @SaleDate = '31 Mar 2010' select * from Sales where SaleDate < @SaleDate -- if you look at the execution plan this query looks at Sales1 and Sales2 (Which is NOT good) Looking at the execution plan you will see that the two queries are differnt. For Query 1 the only table that is accessed is Sales1 (which is good). For Query 2 both tables are accessed (Which is bad). Why are these execution plans different, and how do i get Query 2 to only access the relevant table when variables are used? I have tried to add indexes for the SaleDate column and that does not seem to help.

    Read the article

  • List all foreign key constraints that refer to a particular column in a specific table

    - by Sid
    I would like to see a list of all the tables and columns that refer (either directly or indirectly) a specific column in the 'main' table via a foreign key constraint that has the ON DELETE=CASCADE setting missing. The tricky part is that there would be an indirect relationships buried across up to 5 levels deep. (example: ... great-grandchild- FK3 = grandchild = FK2 = child = FK1 = main table). We need to dig up the leaf tables-columns, not just the very 1st level. The 'good' part about this is that execution speed isn't of concern, it'll be run on a backup copy of the production db to fix any relational issues for the future. I did SELECT * FROM sys.foreign_keys but that gives me the name of the constraint - not the names of the child-parent tables and the columns in the relationship (the juicy bits). Plus the previous designer used short, non-descriptive/random names for the FK constraints, unlike our practice below The way we're adding constraints into SQL Server: ALTER TABLE [dbo].[UserEmailPrefs] WITH CHECK ADD CONSTRAINT [FK_UserEmailPrefs_UserMasterTable_UserId] FOREIGN KEY([UserId]) REFERENCES [dbo].[UserMasterTable] ([UserId]) ON DELETE CASCADE GO ALTER TABLE [dbo].[UserEmailPrefs] CHECK CONSTRAINT [FK_UserEmailPrefs_UserMasterTable_UserId] GO The comments in this SO question inpire this question.

    Read the article

  • SQL Performance Problem IA64

    - by Vendoran
    We’ve got a performance problem in production. QA and DEV environments are 2 instances on the same physical server: Windows 2003 Enterprise SP2, 32 GB RAM, 1 Quad 3.5 GHz Intel Xeon X5270 (4 cores x64), SQL 2005 SP3 (9.0.4262), SAN Drives Prod: Windows 2003 Datacenter SP2, 64 GB RAM, 4 Dual Core 1.6 GHz Intel Family 80000002, Model 6 Itanium (8 cores IA64), SQL 2005 SP3 (9.0.4262), SAN Drives, Veritas Cluster I am seeing excessive Signal Wait Percentages ( 250%) and Page Reads /s (50) and Page Writes /s (25) are both high occasionally. I did test this query on both QA and PROD and it has the same execution plan and even the same stats: SELECT top 40000000 * INTO dbo.tmp_tbl FROM dbo.tbl GO Scan count 1, logical reads 429564, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. As you can see it’s just logical reads, however: QA: 0:48 Prod: 2:18 So It seems like a processor related issue, however I’m not sure where to go next, any ideas? Thanks, Aaron

    Read the article

  • Dropping duplicate|redundant Unique Constraint from FILESTREAM table

    - by electricsk8
    I have a table with a FILESTREAM column, and it has two unique constraints specified for the same FILESTREAM column, ie: ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [UQ_TableName_33C4988760FC61CA] UNIQUE NONCLUSTERED ([GUID_Column]); GO ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [UQ_TableName_33C49887145C0A3F] UNIQUE NONCLUSTERED ([GUID_Column]); GO I'd like to drop one of the unique constraints, as they are duplicates. However, when I try and drop one of the two duplicate constraints, I receive the following error. "A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column." Anyone know how to remove one of the two constraints?

    Read the article

  • How do you create a SQL query in Excel 2007 with a dynamic date range?

    - by Jordan
    I am trying to create a reporting spreadsheet that can print reports for a given time period. The query below works, but when I try to use a "?" parameter in place of the date, I get an error after selecting a cell containing my date. If I use single quotes ('?') I get a conversion from string to date/time failure, if I don't (?) I get a syntax error near @p1. Eventually I will need either a start and end date or a formula adding a month or shift to the starting date/time to filter the data down to important information. The query was built in Microsoft Query. SELECT FloatTable.DateAndTime, TagTable.TagName FROM master.dbo.FloatTable FloatTable, master.dbo.TagTable TagTable WHERE FloatTable.TagIndex = TagTable.TagIndex AND ((FloatTable.DateAndTime={ts '2012-06-01 00:00:00'})) Any assistance would be much appreciated. Thanks in advance.

    Read the article

  • Delete Stored Proc Deadlock in Sql Server

    - by Chris
    I am having the following deadlock in SQL Server 2005 with a specific delete stored proc and I can't figure out what I need to do to remedy it. <deadlock-list> <deadlock victim="processf3a868"> <process-list> <process id="processcae718" taskpriority="0" logused="0" waitresource="KEY: 7:72057594340311040 (b50041b389fe)" waittime="62" ownerId="1678098" transactionguid="0x950057256032d14db6a2c553a39a8279" transactionname="user_transaction" lasttranstarted="2010-05-26T13:45:23.517" XDES="0x8306c370" lockMode="RangeS-U" schedulerid="1" kpid="2432" status="suspended" spid="59" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-05-26T13:45:23.717" lastbatchcompleted="2010-05-26T13:45:23.717" clientapp=".Net SqlClient Data Provider" hostname="DEVELOPER01" hostpid="28104" loginname="DEVELOPER01\ServiceUser" isolationlevel="serializable (4)" xactid="1678098" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="DB.dbo.sp_DeleteSecuritiesRecords" line="13" stmtstart="708" stmtend="918" sqlhandle="0x030007008b6b662229b10c014f9d00000100000000000000"> DELETE FROM tSecuritiesRecords WHERE [FilingID] = @filingID AND [AccountID] = @accountID </frame> </executionStack> <inputbuf> Proc [Database Id = 7 Object Id = 577137547] </inputbuf> </process> <process id="processf3a868" taskpriority="0" logused="0" waitresource="KEY: 7:72057594340311040 (4f00409af90f)" waittime="93" ownerId="1678019" transactionguid="0xb716547a8f7fdd40b342e5db6b3699fb" transactionname="user_transaction" lasttranstarted="2010-05-26T13:45:21.543" XDES="0x92617130" lockMode="X" schedulerid="3" kpid="13108" status="suspended" spid="57" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-05-26T13:45:23.717" lastbatchcompleted="2010-05-26T13:45:23.717" clientapp=".Net SqlClient Data Provider" hostname="DEVELOPER01" hostpid="28104" loginname="DEVELOPER01\ServiceUser" isolationlevel="serializable (4)" xactid="1678019" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="DB.dbo.sp_DeleteSecuritiesRecords" line="13" stmtstart="708" stmtend="918" sqlhandle="0x030007008b6b662229b10c014f9d00000100000000000000"> DELETE FROM tSecuritiesRecords WHERE [FilingID] = @filingID AND [AccountID] = @accountID </frame> </executionStack> <inputbuf> Proc [Database Id = 7 Object Id = 577137547] </inputbuf> </process> </process-list> <resource-list> <keylock hobtid="72057594340311040" dbid="7" objectname="DB.dbo.tSecuritiesRecords" indexname="PK_tTransactions" id="lock82416380" mode="RangeS-U" associatedObjectId="72057594340311040"> <owner-list> <owner id="processf3a868" mode="RangeS-U"/> </owner-list> <waiter-list> <waiter id="processcae718" mode="RangeS-U" requestType="convert"/> </waiter-list> </keylock> <keylock hobtid="72057594340311040" dbid="7" objectname="DB.dbo.tSecuritiesRecords" indexname="PK_tTransactions" id="lock825fd380" mode="RangeS-U" associatedObjectId="72057594340311040"> <owner-list> <owner id="processcae718" mode="RangeS-S"/> </owner-list> <waiter-list> <waiter id="processf3a868" mode="X" requestType="convert"/> </waiter-list> </keylock> </resource-list> </deadlock> </deadlock-list>

    Read the article

  • Please help fix and optimize this query

    - by user607217
    I am working on a system to find potential duplicates in our customers table (SQL 2005). I am using the built-in SOUNDEX value that our software computes when customers are added/updated, but I also implemented the double metaphone algorithm for better matching. This is the most-nested query I have created, and I can't help but think there is a better way to do it and I'd like to learn. In the inner-most query I am joining the customer table to the metaphone table I created, then finding customers that have identical pKey (primary phonetic key). I take that, union that with customers that have matching soundex values, and then proceed to score those matches with various text similarity functions. This is currently working, but I would also like to add a union of customers whose aKey (alternate phonetic key) match. This would be identical to "QUERY A" except to substitute on (c1Akey = c2Akey) for the join. However, when I attempt to include that, I get errors when I try to execute my query. Here is the code: --Create aggregate ranking select c1Name, c2Name, nDiff, c1Addr, c2Addr, aDiff, c1SSN, c2SSN, sDiff, c1DOB, c2DOB, dDiff, nDiff+aDiff+dDiff+sDiff as Score ,(sDiff+dDiff)*1.5 + (nDiff+dDiff)*1.5 + (nDiff+sDiff)*1.5 + aDiff *.5 + nDiff *.5 as [Rank] FROM ( --Create match scores for different fields SELECT c1Name, c2Name, c1Addr, c2Addr, c1SSN, c2SSN, c1LTD, c2LTD, c1DOB, c2DOB, dbo.Jaro(c1name, c2name) AS nDiff, dbo.JaroWinkler(c1addr, c2addr) AS aDiff, CASE WHEN c1dob = '1901-01-01' OR c2dob = '1901-01-01' OR c1dob = '1800-01-01' OR c2dob = '1800-01-01' THEN .5 ELSE dbo.SmithWaterman(c1dob, c2dob) END AS dDiff, CASE WHEN c1ssn = '000-00-0000' OR c2ssn = '000-00-0000' THEN .5 ELSE dbo.Jaro(c1ssn, c2ssn) END AS sDiff FROM -- Generate list of possible matches based on multiple phonetic matching fields ( select * from -- List of similar names from pKey field of ##Metaphone table --QUERY A BEGIN (select customers.custno as c1Custno, name as c1Name, haddr as c1Addr, ssn as c1SSN, lasttripdate as c1LTD, dob as c1DOB, soundex as c1Soundex, pkey as c1Pkey, akey as c1Akey from Customers WITH (nolock) join ##Metaphone on customers.custno = ##Metaphone.custno) as c1 JOIN (select customers.custno as c2Custno, name as c2Name, haddr as c2Addr, ssn as c2SSN, lasttripdate as c2LTD, dob as c2DOB, soundex as c2Soundex, pkey as c2Pkey, akey as c2Akey from Customers with (nolock) join ##Metaphone on customers.custno = ##Metaphone.custno) as c2 on (c1Pkey = c2Pkey) and (c1Custno < c2Custno) WHERE (c1Name <> 'PARENT, GUARDIAN') and c1soundex != c2soundex --QUERY A END union --List of similar names from pregenerated SOUNDEX field (select t1.custno, t1.name, t1.haddr, t1.ssn, t1.lasttripdate, t1.dob, t1.[soundex], 0, 0, t2.custno, t2.name, t2.haddr, t2.ssn, t2.lasttripdate, t2.dob, t2.[soundex], 0, 0 from Customers t1 WITH (nolock) join customers t2 with (nolock) on t1.[soundex] = t2.[soundex] and t1.custno < t2.custno where (t1.name <> 'PARENT, GUARDIAN')) ) as a ) as b where (sDiff+dDiff)*1.5 + (nDiff+dDiff)*1.5 + (nDiff+sDiff)*1.5 + aDiff *.5 + nDiff *.5 >= 7.5 order by [rank] desc, score desc Previously, I was using joins such as on c1.pkey = c2.pkey or c1.akey = c2.akey or c1.soundex = c2.soundex but the performance was horrendous, and using unions seems to be working a lot better. Out of 103K customers, tt is currently generating a list of 8.5M potential matches (based on the phonetic codes) in 2.25 minutes, and then taking another 2 to score, rank and filter those down to about 3000. So I am happy with the performance, I just can't help but think there is a better way to structure this, and I need help adding the extra union condition. Thanks!

    Read the article

  • C# - Must declare the scalar variable "@ms_id" - Error

    - by user1075106
    I'm writing an web-app that keeps track of deadlines. With this app you have to be able to update records that are being saved in an SQL DB. However I'm having some problem with my update in my aspx-file. <asp:GridView ID="gv_editMilestones" runat="server" DataSourceID="sql_ds_milestones" CellPadding="4" ForeColor="#333333" GridLines="None" Font-Size="Small" AutoGenerateColumns="False" DataKeyNames="id" Visible="false" onrowupdated="gv_editMilestones_RowUpdated" onrowupdating="gv_editMilestones_RowUpdating" onrowediting="gv_editMilestones_RowEditing"> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="id" HeaderText="id" SortExpression="id" ReadOnly="True" Visible="false"/> <asp:BoundField DataField="ms_id" HeaderText="ms_id" SortExpression="ms_id" ReadOnly="True"/> <asp:BoundField DataField="ms_description" HeaderText="ms_description" SortExpression="ms_description"/> <%-- <asp:BoundField DataField="ms_resp_team" HeaderText="ms_resp_team" SortExpression="ms_resp_team"/>--%> <asp:TemplateField HeaderText="ms_resp_team" SortExpression="ms_resp_team"> <ItemTemplate> <%# Eval("ms_resp_team") %> </ItemTemplate> <EditItemTemplate> <asp:DropDownList ID="DDL_ms_resp_team" runat="server" DataSourceID="sql_ds_ms_resp_team" DataTextField="team_name" DataValueField="id"> <%--SelectedValue='<%# Bind("ms_resp_team") %>'--%> </asp:DropDownList> </EditItemTemplate> </asp:TemplateField> <asp:BoundField DataField="ms_focal_point" HeaderText="ms_focal_point" SortExpression="ms_focal_point" /> <asp:BoundField DataField="ms_exp_date" HeaderText="ms_exp_date" SortExpression="ms_exp_date" DataFormatString="{0:d}"/> <asp:BoundField DataField="ms_deal" HeaderText="ms_deal" SortExpression="ms_deal" ReadOnly="True"/> <asp:CheckBoxField DataField="ms_active" HeaderText="ms_active" SortExpression="ms_active"/> </Columns> <FooterStyle BackColor="#CCCC99" /> <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" /> <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> <EditRowStyle BackColor="#999999" /> </asp:GridView> <asp:SqlDataSource ID="sql_ds_milestones" runat="server" ConnectionString="<%$ ConnectionStrings:testServer %>" SelectCommand="SELECT [id] ,[ms_id] ,[ms_description] ,(SELECT [team_name] FROM [NSBP].[dbo].[tbl_teams] as teams WHERE milestones.[ms_resp_team] = teams.[id]) as 'ms_resp_team' ,[ms_focal_point] ,[ms_exp_date] ,(SELECT [deal] FROM [NSBP].[dbo].[tbl_deals] as deals WHERE milestones.[ms_deal] = deals.[id]) as 'ms_deal' ,[ms_active] FROM [NSBP].[dbo].[tbl_milestones] as milestones" UpdateCommand="UPDATE [NSBP].[dbo].[tbl_milestones] SET [ms_description] = @ms_description ,[ms_focal_point] = @ms_focal_point ,[ms_active] = @ms_active WHERE [ms_id] = @ms_id"> <UpdateParameters> <asp:Parameter Name="ms_description" Type="String" /> <%-- <asp:Parameter Name="ms_resp_team" Type="String" />--%> <asp:Parameter Name="ms_focal_point" Type="String" /> <asp:Parameter Name="ms_exp_date" Type="DateTime" /> <asp:Parameter Name="ms_active" Type="Boolean" /> <%-- <asp:Parameter Name="ms_id" Type="String" />--%> </UpdateParameters> </asp:SqlDataSource> You can see my complete GridView-structure + my datasource bound to this GridView. There is nothing written in my onrowupdating-function in my code-behind file. Thx in advance

    Read the article

  • SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068

    - by Pinal Dave
    When we have to move a database from one server to another server or when we have to move a database from one file to another file, we commonly use Database Attach or Detach process. I have been doing this for quite a while as well. Recently, when I was visiting an organization I found that in this organization lots of developers are still using an older version of the code to attach the database. I quickly pointed that out to them the new method to attach the database, however it was really interesting to find out that they really did not know that sp_attach_db is now a deprecated method to attach the database. This really made me to do today’s SQL in Sixty Seconds. I demonstrate in this SQL in Sixty Seconds how to attach or detach the database using a new method of attaching database. The code which I have used in this code is over here: -- Detach Database USE [master] GO EXEC MASTER.dbo.sp_detach_db @dbname = N'AdventureWorks2014_new' GO -- Deprecated Way to Attach Database USE [master] GO EXEC MASTER.dbo.sp_attach_db 'AdventureWorks2014_new', 'E:\AdventureWorks2012_Data_new.mdf', 'E:\AdventureWorks2012_log_new.ldf' GO -- Correct Way to Attach Database USE [master] GO CREATE DATABASE [AdventureWorks2014_new] ON ( FILENAME = 'E:\AdventureWorks2012_Data_new.mdf'), ( FILENAME = 'E:\AdventureWorks2012_log_new.ldf') FOR ATTACH GO Here is the question back to you – Do you still use old methods to attach database? If yes, I suggest that you start using the new method onwards. SQL in Sixty Seconds Video I have attempted to explain the same subject in simple words over in following video. Action Item Here are the blog posts I have previously written on the subject of SA password. You can read it over here: SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database SQL SERVER – Move Database Files MDF and LDF to Another Location SQL SERVER – 2005 Take Off Line or Detach Database SQL SERVER – Attach mdf file without ldf file in Database SQL SERVER – Copy Database from Instance to Another Instance – Copy Paste in SQL Server You can subscribe to my YouTube Channel for frequent updates. Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Book Review, SQLAuthority News, T SQL, Video

    Read the article

  • Change Data Capture

    - by Ricardo Peres
    There's an hidden gem in SQL Server 2008: Change Data Capture (CDC). Using CDC we get full audit capabilities with absolutely no implementation code: we can see all changes made to a specific table, including the old and new values! You can only use CDC in SQL Server 2008 Standard or Enterprise, Express edition is not supported. Here are the steps you need to take, just remember SQL Agent must be running: use SomeDatabase; -- first create a table CREATE TABLE Author ( ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1), Name NVARCHAR(20) NOT NULL, EMail NVARCHAR(50) NOT NULL, Birthday DATE NOT NULL ) -- enable CDC at the DB level EXEC sys.sp_cdc_enable_db -- check CDC is enabled for the current DB SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'SomeDatabase' -- enable CDC for table Author, all columns exec sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'Author', @role_name = null -- insert values into table Author insert into Author (Name, EMail, Birthday, Username) values ('Bla', 'bla@bla', 1990-10-10, 'bla') -- check CDC data for table Author -- __$operation: 1 = DELETE, 2 = INSERT, 3 = BEFORE UPDATE 4 = AFTER UPDATE -- __$start_lsn: operation timestamp select * from cdc.dbo_author_CT -- update table Author update Author set EMail = '[email protected]' where Name = 'Bla' -- check CDC data for table Author select * from cdc.dbo_author_CT -- delete from table Author delete from Author -- check CDC data for table Author select * from cdc.dbo_author_CT -- disable CDC for table Author -- this removes all CDC data, so be carefull exec sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'Author', @capture_instance = 'dbo_Author' -- disable CDC for the entire DB -- this removes all CDC data, so be carefull exec sys.sp_cdc_disable_db SyntaxHighlighter.config.clipboardSwf = 'http://alexgorbatchev.com/pub/sh/2.0.320/scripts/clipboard.swf'; SyntaxHighlighter.all();

    Read the article

  • Smart defaults [SSDT]

    - by jamiet
    I’ve just discovered a new, somewhat hidden, feature in SSDT that I didn’t know about and figured it would be worth highlighting here because I’ll bet not many others know it either; the feature is called Smart Defaults. It gets around the problem of adding a NOT NULLable column to an existing table that has got data in it – previous to SSDT you would need to define a DEFAULT constraint however it does feel rather cumbersome to create an object purely for the purpose of pushing through a deployment – that’s the situation that Smart Defaults is meant to alleviate. The Smart Defaults option exists in the advanced section of a Publish Profile file: The description of the setting is “Automatically provides a default value when updating a table that contains data with a column that does not allow null values”, in other words checking that option will cause SSDT to insert an arbitrary default value into your newly created NON NULLable column. In case you’re wondering how it does it, here’s how: SSDT creates a DEFAULT CONSTRAINT at the same time as the column is created and then immediately removes that constraint: ALTER TABLE [dbo].[T1]    ADD [C1] INT NOT NULL,         CONSTRAINT [SD_T1_1df7a5f76cf44bb593506d05ff9a1e2b] DEFAULT 0 FOR [C1];ALTER TABLE [dbo].[T1] DROP CONSTRAINT [SD_T1_1df7a5f76cf44bb593506d05ff9a1e2b]; You can then update the value as appropriate in a Post-Deployment script. Pretty cool! On the downside, you can only specify this option for the whole project, not for an individual table or even an individual column – I’m not sure that I’d want to turn this on for an entire project as it could hide problems that a failed deployment would highlight, in other words smart defaults could be seen to be “papering over the cracks”. If you think that should be improved go and vote (and leave a comment) at [SSDT] Allow us to specify Smart defaults per table or even per column. @Jamiet

    Read the article

  • Smart defaults [SSDT]

    - by jamiet
    I’ve just discovered a new, somewhat hidden, feature in SSDT that I didn’t know about and figured it would be worth highlighting here because I’ll bet not many others know it either; the feature is called Smart Defaults. It gets around the problem of adding a NOT NULLable column to an existing table that has got data in it – previous to SSDT you would need to define a DEFAULT constraint however it does feel rather cumbersome to create an object purely for the purpose of pushing through a deployment – that’s the situation that Smart Defaults is meant to alleviate. The Smart Defaults option exists in the advanced section of a Publish Profile file: The description of the setting is “Automatically provides a default value when updating a table that contains data with a column that does not allow null values”, in other words checking that option will cause SSDT to insert an arbitrary default value into your newly created NON NULLable column. In case you’re wondering how it does it, here’s how: SSDT creates a DEFAULT CONSTRAINT at the same time as the column is created and then immediately removes that constraint: ALTER TABLE [dbo].[T1]    ADD [C1] INT NOT NULL,         CONSTRAINT [SD_T1_1df7a5f76cf44bb593506d05ff9a1e2b] DEFAULT 0 FOR [C1];ALTER TABLE [dbo].[T1] DROP CONSTRAINT [SD_T1_1df7a5f76cf44bb593506d05ff9a1e2b]; You can then update the value as appropriate in a Post-Deployment script. Pretty cool! On the downside, you can only specify this option for the whole project, not for an individual table or even an individual column – I’m not sure that I’d want to turn this on for an entire project as it could hide problems that a failed deployment would highlight, in other words smart defaults could be seen to be “papering over the cracks”. If you think that should be improved go and vote (and leave a comment) at [SSDT] Allow us to specify Smart defaults per table or even per column. @Jamiet

    Read the article

< Previous Page | 12 13 14 15 16 17 18 19 20 21 22 23  | Next Page >