SS2005 Standard (RTM && SP1 CTP1) Evaluates Sub-queries In Wrong Order

Mar 24, 2006

We are experiencing a problem in SQL Server 2005 Standard Edition (on x86 & x64, RTM & SP1 CTP1). The problem is we have a view which does something like "CREATE VIEW myView;SELECT * FROM MyTable WHERE ISNumeric(MyVal)=1" when you do "SELECT * FROM myView" you see a dataset which only contains numeric values.

However it's clear that if you do "SELECT * FROM myView WHERE MyVal>5" that it is evaluating the >5 before the IsNumeric function (I assume as > is less costly than IsNumeric and thus it is more efficient this way). This didn't happen in Sql Server 2000 & 7.0.

My concern here is that how can you trust views if when you put evaluations on them they're working against a different dataset to that which you view if you do SELECT * ?
I am currently working with a workaround which is to simply put TOP in the sub-queries to force the execution order to that which I've defined. However this is nasty as I can't do TOP 100% as it gets optimised out and so instead I have to do TOP 999999999 or similar.

However my biggest concern by far is that even in "SQL Server 2000 (80)" compatibility mode the behaviour is not consistent wtih SS2000.

CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))

INSERT INTO #Problem (numinastr) values ('1')
INSERT INTO #Problem (numinastr) values ('10')
INSERT INTO #Problem (numinastr) values ('25')
INSERT INTO #Problem (numinastr) values ('40')
INSERT INTO #Problem (numinastr) values ('>500')
INSERT INTO #Problem (numinastr) values ('600')
INSERT INTO #Problem (numinastr) values ('1000')
INSERT INTO #Problem (numinastr) values ('error!')

-- Note Lack of any non-numeric rows
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1

-- This Command executes correctly
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15

--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric
SELECT * from (
SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15

-- Creating a view of SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1 and
-- then querying that also gives the same error

DROP TABLE #Problem

I have been told (by an MVP) that you can't assume a specific execution order for queries. Do any DBA's out there really think this acceptable? I consider this a bug. If I put a query in as a sub-query or view, or if I bracket my where statement in such a way I expect it to respect what I've told it!

View 5 Replies


ADVERTISEMENT

Wrong 'Order By'

May 15, 2003

Does anyone know any bug relationed with memory paging and order by clause?

I try to explain:

I have the following code inside a SP:

create table #tempA (colA char(2), colB numeric(7), colC numeric(3))

create table #tempB (colA char(2), colB numeric(7), colC numeric(3))
)
insert into #tempA
(...)

(...)
insert into #tempB (colA,colB,colC)
select colA,colB,colC
from #tempA
order by
colB,colA,colC

select * from #tempB

While using the reserved memory for the SQL Server, the last 'select' works pretty well. After the memory swapping starts, the 'select' returns data in incorrect order.

Any Ideas?
Thank you for all support than you can give me.


:confused:

View 2 Replies View Related

Views Scripted In The Wrong Order

Dec 19, 2005

Is there a way to make EM script all views of a database in the order in which they depend on each other?

View 11 Replies View Related

Convert Datetime But Order By Is Wrong

Sep 25, 2006

I am trying to convert my datetime to 107 ex: Dec 11 2006 ,but when sorting I get the months sorted correctly but not by year????



ex:

Jan 2004

Feb 2001

Mar 2006

View 1 Replies View Related

Wrong Date Sort Order In Enterprise Mgr

Feb 3, 2006

When clicking on the header for the Date Created column in EM (SQL Server 2000) , in the tables list for a database, I noticed that the tables don't get sorted by created date at all - looks like random order, without looking too close.

Is this is a bug (probably not??) or some kind of collation-related problem?

View 2 Replies View Related

Order By Cluase Cause Wrong Results To Be Returned.

Sep 10, 2007

I have the follow table.

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

If I use the following SQL:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GET Following correct results:

















Adair
284
899989594
0
574857716
190479902
1665327212
0
0
0
0
1665327212
0
1
United States
1
1
IF I use the following SQL I get the wrong results:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName

WRONG results:
















Adair
74
81733110
0
49616018
24671651
156020779
50510500
0
0
0
203870779
0
1
United States
50
1

Adair
437
1468698657
0
495479839
353202768
2317381264
12984266
0
0
0
2315676030
0
1
United States
25
1

Adair
1813
20309722045
0
6597005374
4253819645
31160547064
43636703
0
0
0
31135010742
0
1
United States
11
1

Adair
606
439581417
0
331746662
132240332
903568411
0
0
0
0
903568411
0
1
United States
45
1

Adair
236
350256381
0
524269553
504973831
1379499765
4080368
0
0
0
1380473415
0
1
United States
23
1etc.....

View 6 Replies View Related

OLEDB To Flat File Destination - Comes Out In Wrong Order

Jun 8, 2007

Hello,



My OLE DB Source is getting data from the following column types:

ID varchar(50), Name varchar(100), Date datetime, Currency char(3), Cost numeric(30,10)



My OLE DB Source outputs my information in the following order when I click Preview:

ID Name Date Currency Cost



When I connect the OLE DB Sorce to a Flat File Destination, it comes out in the wrong order.When I examine the "line" between them (Data Flow Path Editor) I get:



Currency DT_STR Length: 3

ID DT_STR Lenght: 50

Name DT_STR Lenght: 100

Date DT_DBTIMESTAMP

Cost DT_NUMERIC



What is the easiest way for me to change this so the Flat File Destination will output my data in the same order as the OLE DB Source:

ID Name Date Currency Cost



Thank you very much!

View 6 Replies View Related

ORDER BY In Sub Queries

May 24, 2008

I understand that I have to have a TOP statement to work with an ORDER BY in a subquery but I am unable to get it to work correctly...
I am using VS 2008 and it does throw a "TOP" error, but it does not go ahead and process it correctly like someone said it would in another post...

Is this possible or simply how do I accomplish the following?

Here is an example:





Code Snippet

SELECT ResultSet1.BCol2, ResultSet1.CCol2, ResultSet1.Expr1
FROM
(SELECT TableB.BCol2, TableC.CCol2, count(*) AS Expr1
FROM TableA
INNER JOIN
TableB ON TableA.ACol2 = TableB.BCol1
INNER JOIN
TableC ON TableA.ACol4 = TableC.CCol1 AND TableA.ACol5 = TableC.CCol4
WHERE (TableC.CCol2= 1)

GROUP BY TableB.BCol2, TableC.CCol2) AS ResultSet1
To...





Code Snippet

SELECT ResultSet1.BCol2, ResultSet1.CCol2, ResultSet1.Expr1
FROM
(SELECT TOP (10) TableB.BCol2, TableC.CCol2, count(*) AS Expr1
FROM TableA
INNER JOIN
TableB ON TableA.ACol2 = TableB.BCol1
INNER JOIN
TableC ON TableA.ACol4 = TableC.CCol1 AND TableA.ACol5 = TableC.CCol4
WHERE (TableC.CCol2= 1)


ORDER BY Expr1



GROUP BY TableB.BCol2, TableC.CCol2) AS ResultSet1


What am I obviously missing here?

Thanks a bunch.

View 5 Replies View Related

Analysis :: Order A Table With MDX Queries

Apr 21, 2015

I have the following MDX query:

SELECT NON EMPTY {[DateT].[Year].[Year].Members } ON ROWS,
NON EMPTY { [Measures].[Val] } ON COLUMNS FROM [MyDB]

I need to order the data by year, so I tried this query:

SELECT
NON EMPTY { Order ( [DateT].[Year].[Year].Members,Desc) }
ON ROWS,
NON EMPTY { [Measures].[Val] } ON COLUMNS
FROM [MyDB]

But it doesn't work.

View 2 Replies View Related

Conditional Split - Expression Evaluates To Null

Sep 5, 2007



Hi everyone!


I'm using a conditional split to discriminate modified records. My expression looks like this:
col1_source != col1_dest || col2_source != col2_des.....and so on. I use OLE DB Command afterward to update modified records.

It all works fine if no columns evaluate to null. If any (source or dest.) evaluates to null, component fails.

Any tips how to solve a problem?

It has to work like this:

If colX_source is null and colX_dest is not null --> Update
If colX_source is not null and colX_dest is null --> Update
If both colX_source and colX_dest are null --> No update

p.s. i apologize if a similar thread exists, I haven't found something of use to me.

View 13 Replies View Related

Analyzing Lock Order Of Queries Against Same Table To Prevent Deadlocks

Aug 5, 2014

I need to discover the actual order in which locks are acquired on a table during a query.

This with a goal of analyzing the lock order of queries against the same table to prevent deadlocks.

I'm using SQL Server 2008 R2.

From Management Studio I execute:

begin transaction
<my query>
exec sp_lock
rollback transaction

In the output I see interesting information about which locks are acquired, but:

- are this locks ordered by the time they're acquired? That is, can I be sure that lock at row n is acquired before lock at row n+1?
- if not, how can I get this information?

View 4 Replies View Related

Inscope Evaluates To False On Second Row Group (totals Column)

Sep 11, 2007

Hi, I have a matrix with 2 row groups and 1 column group.






CGroup1 Val1

CGroup1 Val2

Total


- RGroup1 Val1

RGroup2 Val1

In

In

Out




RGroup2 Val2

In

In

Out


- RGroup1 Val2

RGroup2 Val3

In

In

Out




RGroup2 Val4

In

In

Out


Total

Out

Out

Out


I want to change the row totals at the RGroup2 level. I have put an expression in the measure cell as:

=iif(InScope("matrix1_RowGroup2"), "In", "Out"). Shouldn't the values in the Totals Column on the far right evaluate to "In"? If not, how can I isolate the totals at the RGroup2 level?

Also, I found that when I put =fields!RGroup2.value in the expression for the cell, the Totals Column on the far right is blank but when I put =fields!RGroup1.value the correct value is properly displayed in the Total Column. Why does =fields!RGroup2.value not work?

View 7 Replies View Related

Update Trigger: COLUMNS_UPDATED Evaluates To True For All String Fields

Jul 14, 2006

I have a trigger that writes changes to an audit table. In the case of Updates, I only want to write out the fields that have actually changed. My code for COLUMNS_UPDATED evaluates to true for all varchar fields, even when they haven't changed. All other scenarios appear to be working correctly.



WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1

IF ((@TriggerType = 'I') OR (@TriggerType = 'D') OR (@TriggerType = 'U' AND substring (COLUMNS_UPDATED(),@char, 1) & @bit > 0))
BEGIN..........

END

END

Ideas? Thanks!

View 1 Replies View Related

Sql Server 2005 Inserting Prbblem..wrong SQL? Wrong Parameter?

Feb 19, 2006

Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code:        Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _        ByVal Email As String, ByVal Gender As Integer, _        ByVal FirstName As String, ByVal LastName As String, _        ByVal CellPhone As String, ByVal Street As String, _        ByVal StreetNumber As String, ByVal StreetAddon As String, _        ByVal Zipcode As String, ByVal City As String, _        ByVal Organization As String _        ) As Boolean            'returns true with success, false with failure            Dim MyConnection As SqlConnection = GetConnection()            Dim bResult As Boolean            Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection)            MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName))            MyCommand.Parameters.Add(New SqlParameter("@Password", Password))            MyCommand.Parameters.Add(New SqlParameter("@Email", Email))            MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender))            MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName))            MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName))            MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone))            MyCommand.Parameters.Add(New SqlParameter("@Street", Street))            MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber))            MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon))            MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode))            MyCommand.Parameters.Add(New SqlParameter("@City", City))            MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization))            Try                MyConnection.Open()                MyCommand.ExecuteNonQuery()                bResult = True            Catch ex As Exception                bResult = False            Finally                MyConnection.Close()            End Try            Return bResult        End FunctionThanks!

View 1 Replies View Related

No SS2005 Express

Sep 13, 2007

I have SS 2005 express* working(yrs) on a XP home ver. laptop. I got a new Acer w/ Vista hm/premium and I registered/can't get SS 2005 express* to download from MSDN.

Norton's gives a file block, I click the 'download file' and nothing happens. No dialog, hd drv activity, nothing - just 'Done'
on IE7 status. I have Visual Web Developer beta v2 2008** running on the Acer and don't want to trash the install.

.NET fwrk v2.0 is listed required for SS2005, but .NET fwrk v3.5 says it includes v2.0 + WCF,...(right? - install both?)

I need a SS for VWD 2008. I've ask/tried several times. What am I doing wrong?

Mark

View 1 Replies View Related

SS2005 Re-download

Jun 19, 2006



I downloaded the DVD image to a PC and the DVD drive fails.

Now, when I try to download the executable for install instead, nothing downloads.

Is SQL SVR 2005 eval limited to one download?



View 3 Replies View Related

Db Diagram Of SS2000 In SS2005

Apr 30, 2007

I have MS SQL Server 2005 Developer Ed. and try to make a database diagram of remote MS SQL Server2000 database in MS SQL Server Management Studio.
I receive an error "Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."

View 1 Replies View Related

What Can I Do With The Studio Version In SS2005

Feb 14, 2007

Hello,

I have installed SSs2005 - Standard Edition.
During this installation, also Visual Studio is installed.

I do not understand what i can do with this Studio version.
I mean, i understand that with SS2005 there is a connectivity between sql server and visual studio. For example i could create store procedures with Visual Studio. (clr applications).

But when i start the Viual Studio screen i can only select Business Inteligence Projects.

What do i need, when i want to create functions, stored procedures and what do i need to develop SMO applications?

Please give me some light on this matter.

View 1 Replies View Related

SS2005 Log Has Lots Of These Messages.

Nov 14, 2007

Every time a transaction log is dumped we see the following message in the log file:

BackupDiskFile:penMedia: Backup device '\s-sqlbkups-1g$myserverlogmy_databasemydatabase_backup_200711071430.trn' failed to open. Operating system error 2(The system cannot find the file specified.).


Source spid139
Message
Error: 18204, Severity: 16, State: 1.



And yet, the actual log dump appears fine and the file is found on the share. The dump is done with a maintenance plan.

Any ideas?

View 2 Replies View Related

Median Function In T-SQL For SS2005?

Oct 13, 2006

Hello!

I have been trying to find a T-SQL function that would calculates a Median statistical value for me. I am runnnig on SS 2005? Any examples of using this function would be greatly appreciated.

Thanks for any responses!





View 7 Replies View Related

SS2005 Upgrade Advisor

May 29, 2008

I'm trying to migrate/upgrade some databases from 2000 to 2005 and am having a problem. Apparently, the Upgrade Advisor can't analyze a SS2000 database if it is in a named instance. (see below)


This problem occurs because the SQL Server 2005 Upgrade Advisor cannot connect to the named instance of SQL Server 2000.

The SQL Server 2005 Upgrade Advisor uses information that the SQL Server Browser service returns when the SQL Server 2005 Upgrade Advisor tries to connect to an instance of SQL Server 2000. However, the SQL Server Browser service cannot return the correct information about the connection request. Therefore, the connection fails.

http://support.microsoft.com/kb/908454

Doesn't this make the UA tool useless for named instances on SS2000? Are there any plans to correct this issue? Or, is there a workaround available?

Keith

View 7 Replies View Related

Compare Standard Evalulation To Standard License Version

May 30, 2007

Hi All,



Good Evening.



I need a comparision between Evaluation copy and Standard license version of SQL Server 2005 /SSIS.



I'm assigned a task to evaluate SSIS and migrate a project to SSIS.



I have downloaded SSIS evaluation version and started working on that.



Now i'm being posed questions for the complete functionality of the tool...



- Whether it has a Bulk load trasformation ?

- Whether the evaluation version contain all the features of a standard license version ?



I need to submit a consolidated report for SSIS in comparision to the current ETL tool features.



Can you please let me is there any considerable features not given with a evaluation copy ?



Thanks in advance,

Suresh N

View 4 Replies View Related

SS2005: TRY && CATCH With BULK INSERT

Jan 27, 2007

SQL Server 2005

Hi Everyone. We have a stored proc that performs several bulk inserts. I need to find a way to allow the bulk insert to truncate data. Also, I would like to be able to send back to .NET the exact line that failed if a failure does occur.

Right now, the stored proc fails because of truncation. Could this be because there's a check for @@ERRROR <> 0 right after the bulk insert? Does anyone know if a truncation occurs, if that will "throw" an error to the catch block? This is NOT what we want. Can anyone help me to understand how to do this correctly?

Thanks,

Angel

View 5 Replies View Related

Problem Notifying Operator SS2005

Jun 29, 2006

Hi there!

I expercience some problems in sending the alert mails to the operator when an Agent Failure Alert happens.

I have setup MSSQL 2005 with merge replication and have setup mail the ss2005 way. I also can send test mails via the Database mail context menu.

I have setup a operator with a valid emailadress and enabled is checked
On the notifications page 'Agent Failure Alert' is checked.

When this alert fires the number of occurences in the history of this alert is incremented (as I can see via the alertsection-Agentfailure-history) this alert is also enabled. The problem is that the operator does not get notified!

So I can send test email from the DB, have setup an Operator with emailadress, have enabled an alert and see the number of occurences of this alert increment. I don't get any alert mails. The notifications attempt in the history page of the operator does not increment. It says: [never emailed]



Well I hope you can help!

Sincerley

Edward





View 3 Replies View Related

SS2005 Version Compatible For PerformancePoint

Mar 18, 2008

I'm currently using the evaluation version of SS2005 and would like to use PerformancePoint with Analysis Services. PerformancePoint requires the cumulative update package 3 for SQL Server 2005 Service Pack 2, or Build 3186, according to the documentation. The version for SS2005 is the following:

MSSQLSERVER
Analysis Services
[Version: 9.00.1399.06 Edition: Enterprise Evaluation Edition Patch level: 9.00.1399.06 Language: English (United States)]

The same version, patch level is repeated for all of the SS2005 components. Do I have a compatible SS2005 version for PerformancePoint.

Thanks,
Tom

View 5 Replies View Related

How To Process Incoming EMAIL For SS2005?

Dec 20, 2007



ok, so sp_processmail is deprecated and should no longer be used in SS2005.

how do you process and execute a SS2005 query using an incoming email??

can it even be done in SS2005??

thanks for any ideas,

chester



View 4 Replies View Related

Scheduling SSIS Packages In SS2005

Feb 21, 2008


I'm trying to create a SQL Agent job and schedule that executes an SSIS Package, but when I try to run the job I get the following error:


Executed as user: adApp1. The package could not be loaded.

The App1 user is the Identity used in the Credential for the Proxy which has the SSIS Packages Subsystem.
The package encryption is set EncryptAllWithPassword and that password is included in the Command line with the /DECRYPT option.
The package is stored on the server in the SSIS Package Store and was placed there via the Import package option. Does the method matter?

One thing that I haven't been able to track down is exactly what permissions the domain account adApp1 needs on the server or in SQL Server, if any, in order to run the package. Not sure if that has any affect anyway.

Any information would be greatly appreciated.

Thanks

View 10 Replies View Related

SS2005 - Fuzzy Transforms Availability

Sep 7, 2007



I understand that only Enterprise Edition has support for Fuzzy Lookup and Fuzzy Grouping transforms, but we will likely be migrating to Standard shortly - Enterprise would be complete overkill for our organisation and needs, and is beyond our budget constraints too.

I have been using these in the CTP of Katmai, and have found them exceptionally useful for my work.

Any chance of these transforms being made available as an "add-on" for Standard (or other versions)?
It does not seem that it is something inextricably tied to the different versions - just a licencing decision, right?

Or would the proposal be to go with Standard and one additional purchase of Developer Edition?

Sham

View 1 Replies View Related

Migrating SS2000 DTS To SS2005 SSIS

Mar 11, 2008



Hello,

Is it possbile to migrate SS2000 DTS packages to SS2005 SSIS involving databases with compatibility mode set to 80. If yes, what are the potential issues, which may occur?

Sajish

View 6 Replies View Related

Odd Timing Problem MSAccess To SS2005

Jul 9, 2007

I have an SSIS package that takes data from a table in Access and puts it into a fact table in SS2005. Very little data manipulation is done. It processes approximately 1.5 million rows when it runs weekly. The process is run in an SSIS package that is called by a parent package, and all of that (including the use of the config files and accessing the parent variables) is working fine.



The issue is there is one field in the Access table that must be put into a different SS2005 fact table.



When I run the data flow task that loads the first fact table, it completes in less than two minutes. However, if I either (a) put a multicast step in the dataflow task to redirect a copy of the key data and remaining field to the second fact table, or (b) copy that step in the package to have it perform the same tasks with the different target (and using just the key and the remaining field), the execution time suddenly jumps to 30 minutes. In the case of (b), it remains true whether the copied step remains in the package or is executed in its own package, and also remains true if the package is loading against a table that starts out empty or with data already in it.



Has anyone ever bumped into a situation like this?

View 3 Replies View Related

SS2005 Replication: Can You Replicate Constraints Without Reinitializing?

Jan 11, 2007

We have a two SQL Server 2005 databases set up using Transactional Replication. My manager has asked me to set it up to replicate constraints (default, fk, et) on the tables in the main publication. I said sure, I can do it, but I will have to re-initialize the subscription. He said there was a way to do it without re-initializing the subscription. I cannot find anything in my research to indicate that there is a method to do so (via call sp_ functions, etc). Is it indeed possible to replicate constraints on replicated tables without re-initializing the who subscription? The reason he does not want me to perform a re-initialization is that we are a few weeks before moving into production, and a full re-initialization takes 1.5 days, which would impact system availability.

Thanks in advance for any advice.

View 1 Replies View Related

SS2000 To SS2005 Transactional Replication Issues?

Nov 1, 2007

Hi,

We are implementing push transaction replication from Production SQL Server 2000 database to SQL Server 2005 reporting server and SQL Server 2000 Test server.
SQL Server 2000 PROD to 2000 Test is simple but from SS2000 to SS2005 I have following questions:
Is it feasible to create push subscription from SQL Server 2000 to SQL Server 2005 database?
When I am trying to setup publisher/subscription properties, It only give me options to pick SQL Server 7.0 or 2000 or heterogeneous databases.
When I am trying to register SQL Server 2005 database in EM of SS2000 it don't allow me to add it and through exception that I need to use Management studio, Is there any alternate way to register?

Thanks
--rubs

View 1 Replies View Related

SS2005 Replication - Cannot Drop Subscriptions From Publisher

Jun 22, 2006

I am executing sp_dropmergesubscription, but the rows are still in dbo.msmerge_subscriptions, and are still shown in the replication monitor as expired; the last synch dates were in april (expiration is set to 10 days). The 'expired subscription clean up' job appears to be running okay. I need to remove these subscriptions from the publisher as the subscribers are mobile devices, which sometimes are lost.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved