Answer Questions

• Tejas34 Restoration of publication database backup

  When I tried to restore backup of publication database, time taken for restoration is too long [ 4 hours]. The actual database restore takes only 20 minutes but the stored procedures used for cleanup replication takes the remaining time. Is the issue related to my environment or the cleanup replication stored procedure i have a restore going on for about 3-4 hours. my db contains not more than 100 records thats too much i should say maybe 50. and number of tables is 10-15, about 10 stored procedures. sql server enterprise , lucky i tested this backup/restore with this small db. Any latest news about taht thanks. I did not suggest there might be blocking during the restore rather in ...Show All

• The ZMan Unit of time-statistics in "client statistics"

  What is the unit of the numbers you get in the Time Statistics -part when running a query in Microsoft SQL Server Management Studio with Client Statistics turned on Currently I get mostly 0’s, but if I try and *** up a query on purpose I can get it up to around 30... Is it milliseconds or som made up number based on clockcycles or... I would also like to know if it’s possible to change the precision. - Nikolaj surprsingly theres not alot about the client stats in BOL but I did manage to find this via MSDN online. Those values are usually in MS fyi. Client Statistics Client statistics are useful in determining how much time the network and client components contribute to the total response time o ...Show All

• smalamas BULK INSERT "AccessDenied" on remote text file.

  OK, Ive read many posts on this problem but have seen no resolution. Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file. Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head. When anyone runs a Bulk Insert command that accesses a text file t ...Show All

• PareshGheewala Performance problem: 2000 vs. 2005

  I copied a 2000 database onto 2005 on another box. Running the exact same SELECT query, with 7 tables joined, is running in less than 2 minutes on 2000 and almost 3 hours on 2005. The 2005 box is "bigger and better". I've compared the sp_configure output. There are differences in max worker threads, cost threshold for parallelism, and priority boost. I don't think any one of these is significant. I checked the server properties and the database properties and, again, I did not see what I would consider any significant differences. The 2005 instance has 4GB memory, as opposed to 2GB, but not using that much. In fact, the Total Server Memory (KB) for the instance indicates around 1,230,000 for some reason. We need to reso ...Show All

• MagedSalah modifying data from extract before import

  Hi all, Having some fun with an excel import. I have a excel sheet that has data that needs to be UpSert'ed into 2 different tables. In addition, I need to use a value in the spreadsheet to determine the PK from a reference data table, for one of the UpSert oprations. That is all working now. The thing I'm struggling with is something I am sure is quite simple - but I'm not seeing a solution from attempts, googling or BOL. 2 of the columns I receive have either nothing, or X in them. The columns they go into are defined as BIT, NOT NULL. So, in SQL it would be something relatively simple like: CASE When IsAvailable = 'X' then 1 When IsAvailable is null then 1 ELSE 0 end But I don't kno ...Show All

• Gurpreet Singh Sawhney SQLServerProvider retrive all rows

  Hi all, I'm trying to use SQLServerProvider to looking for new or changed records but it always retrive all the rows. How could I use this provider to retrive only new or changed records <HostedProvider> <ProviderName>SQLActionItemProvider</ProviderName> <ClassName>SQLProvider</ClassName> <SystemName>%_NSSystem_%</SystemName> <Schedule> <Interval>P0DT00H00M60S</Interval> </Schedule> <Arguments> <Argument> <Name>EventsQuery</Name> <Value>SELECT ActionItemOwner, ActionitemDesc, StatusNm FROM [QA_DEV].dbo.ACTIONITEMS_QA_VW</Value> </Argument> <Argument& ...Show All

• DavidR100 Snippet: Insert into schema

  We're having a hard time with SQL 05's xml. In a nutshell, I have a schema. I have used .Net 2.0's xsd tool to create classes from this schema. This schema is also tied to a XML column in my database server. So far so good. However, when I go to deserialize the xml data stored in the database server I'm getting inconsistent results. Namely the differences in the following queries: SELECT LoanXML.query('declare namespace MISMO="http://mrgdev.local/mismo/";//MISMO:LOAN') FROM Loans WHERE Loans.InternalID = @LoanID returns something completely different from: SELECT LoanXML from Loans where InternalID = @LoanID The difference is that the first query throws xsi:nil=true into EVERY SINGLE NODE. Whereas the second one doesn't ...Show All

• sarika Can I add a record number as data passes through

  Hello. In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination. An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.  MemberID 2898  2899 2899 What I want it to look like when it gets to the destination is: MemberID     RecordNumber 2898     1 2899     1 2899     2 Like an Identity column I suppose, not for the whole table but for each MemberID. Thanks     Ok, the component works great but it is not putting in what I expected i ...Show All

• Josh Lindenmuth How to reset identity column's value?

  Hello friends, I have a table in SQL server 2005. it contains one identity column named EmpID it's datatype is int isidentity and auto increment by 1. I deleted all the records from this table. Now I want that EmpID should start again from 1 how can I do it Thanks & Rgds, Kiran Suthar. Dbcc checkident() will allow you to reset the seed value. If your table does not have any constraint, you can use "truncate table" to quickly delete data and reset the seed in one step. TRUNCATE TABLE "table_name" will delete all the records as well as it resets the identity column to 0. simple way to solve your problem Difference between Delete & Truncate visit TRUNCATE TABLE is a st ...Show All

• D.Hamilton MS RS Report Viewer and "cookieless" session-state

  Hello there, I tried to implement the ReportViewer (local-mode) in our WebApp. The ReportViewer never rendered the report and I found out, the problem is the cookieless="UseUri" in web.config. Ist there any way to use the ReportViewer using the cookieless-state Thanks, Tobias http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=685292&SiteID=1 ...Show All

• HuWu Execute Package Task: setting child variables

  Hi, Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list. How do I pass the InvoiceID to the child I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names. What I would like to do is "push" the value instead of "pulli ...Show All

• WinFormsUser13232 SQLCLR 32-bit Vs 64-bit

  Is anyone here aware of any explicit/definite differences between running sqlclr on 32-bit and 64-bit. Note: These must be documented, well defined differences. There aren't really any explicit, intended functional differences in SQLCLR itself between 32-bit and 64-bit. The only one I'm aware of at all is a subtle difference when running under WoW64 that clr must be enabled on server start-up in order to initialize it. However, there are still several subtle differences that come from either SQL or CLR that will still affect SQL CLR. The most significant one is memory management as the huge VAS in 64-bit removes many of the memory issues that some people hit on x86. There are other differences that ...Show All

• Manmeet Panigrahi Transaction

  how can with in transaction execute statment1 and statment2 then rollback transaction but statment2 not rollback You are asking for something like autonomous transactions which is supported by Oracle. This is not possible to do in SQL Server currently without establishing different connection to the server via loopback mechanism. So in your case, you would execute statement2 in a different loopback connection using extended SPs or CLR SPs (in SQL Server 2005). Note that the transaction semantics depends on what you are doing in the statements and your current logic both server-side & loopback mechanism. Alternatively, from SQL Server 2000 onwards you can use a table variable to hold some state information during the tran ...Show All

• pdurbha Cascade Issue

  By: IT In: microsoft. public. sqlserver. programming I have 2 tables A and Table B respectively: Table A: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aa]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[aa]( [aa1] [int] NOT NULL, [aa2] [int] NULL, [aa3] [int] NULL, CONSTRAINT [PK_aa] PRIMARY KEY CLUSTERED ( [aa1] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END Table B: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bb]') AND type in (N'U')) BEGIN ...Show All

• James M. King Rows in Pages

  Hi everyone, I have a simple question about Rows in Pages for you. In my opinion, rows are the storage of the data in databases. I would like to ask that there are any important properties of Rows which I am not aware. Thanks Hmm, joeydj , I have some doubts about your MS tutorial. First y, I did not know that there is a specific type for texts and images. I supposed that texts and images are required more than one pages generally since their size generally involves this. So would you please explain this to me Thanks If you could please reformulate your question a bit more specifically, it would be easier to answer ;) Anyhow, as a first note let me say that in SQL pages are the phis ...Show All