OPENQUERY Problem

Hi,
I have created a linked server to oracle.
I executed the query as
SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
ora_owner.appointment where update_dtm > to_date(''2007-oct-11
18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
Its executing fine.
But I want to get the date from another table from my sql server.
How can I form the OPENQUERY with a variable(contains date)?
SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
tie_owner.rtt_appointment where update_dtm > to_date(''+
@.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
This statement is giving error...
Incorrect sysntax at +
How do I get date in yyyy-mmm-dd hh:mm:ss format?
The same date I will form in the openquery.
This is struggling me a lot. Pls suggest an idea.
Thanks in advanceSome examples
DECLARE @.SQLx VARCHAR(500)
DECLARE @.var VARCHAR(20)
SET @.var = 'abcd'
SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
''EXEC pubs.dbo.sp2 '' + @.var + '')'
EXEC(@.SQLx)
<mrajanikrishna@.gmail.com> wrote in message
news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
> Hi,
> I have created a linked server to oracle.
> I executed the query as
> SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> ora_owner.appointment where update_dtm > to_date(''2007-oct-11
> 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
> Its executing fine.
> But I want to get the date from another table from my sql server.
> How can I form the OPENQUERY with a variable(contains date)?
> SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> tie_owner.rtt_appointment where update_dtm > to_date(''+
> @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
> This statement is giving error...
> Incorrect sysntax at +
> How do I get date in yyyy-mmm-dd hh:mm:ss format?
> The same date I will form in the openquery.
> This is struggling me a lot. Pls suggest an idea.
> Thanks in advance
>|||On Oct 18, 1:11 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Some examples
> DECLARE @.SQLx VARCHAR(500)
> DECLARE @.var VARCHAR(20)
> SET @.var = 'abcd'
> SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
> ''EXEC pubs.dbo.sp2 '' + @.var + '')'
> EXEC(@.SQLx)
> <mrajanikris...@.gmail.com> wrote in message
> news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
>
> > Hi,
> > I have created a linked server to oracle.
> > I executed the query as
> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> > ora_owner.appointment where update_dtm > to_date(''2007-oct-11
> > 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
> > Its executing fine.
> > But I want to get the date from another table from my sql server.
> > How can I form the OPENQUERY with a variable(contains date)?
> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> > tie_owner.rtt_appointment where update_dtm > to_date(''+
> > @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
> > This statement is giving error...
> > Incorrect sysntax at +
> > How do I get date in yyyy-mmm-dd hh:mm:ss format?
> > The same date I will form in the openquery.
> > This is struggling me a lot. Pls suggest an idea.
> > Thanks in advance- Hide quoted text -
> - Show quoted text -
Hi thank u for the reply,
What is the problem in my procedure...
DECLARE @.ApptLastUPdateDateTime varchar(30)
BEGIN
DECLARE @.sql_str VARCHAR(4000)
SELECT @.ApptLastUPdateDateTime = convert(varchar(23),ApptUpdateDtm,
120), FROM [LastUpdateDateTime]
SET @.sql_str ='SELECT * from tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''' +
@.ApptLastUPDateDateTime + ''''
SET @.sql_str = N'select * from OPENQUERY([TIE DB], ''' +
REPLACE(@.sql_str, '''', ''') + ''')'
EXEC @.sql_str
END
I am getting error
The name 'select * from OPENQUERY([TIE DB], 'SELECT * from
tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-01-01
01:01:00''')' is not a valid identifier.
I am unable to fix this error.|||Replace EXEC (@.sql) with PRINT @.sql to see what script it creates in order
to debug
<mrajanikrishna@.gmail.com> wrote in message
news:1192715912.147689.145840@.i13g2000prf.googlegroups.com...
> On Oct 18, 1:11 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Some examples
>> DECLARE @.SQLx VARCHAR(500)
>> DECLARE @.var VARCHAR(20)
>> SET @.var = 'abcd'
>> SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
>> ''EXEC pubs.dbo.sp2 '' + @.var + '')'
>> EXEC(@.SQLx)
>> <mrajanikris...@.gmail.com> wrote in message
>> news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
>>
>> > Hi,
>> > I have created a linked server to oracle.
>> > I executed the query as
>> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
>> > ora_owner.appointment where update_dtm > to_date(''2007-oct-11
>> > 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
>> > Its executing fine.
>> > But I want to get the date from another table from my sql server.
>> > How can I form the OPENQUERY with a variable(contains date)?
>> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
>> > tie_owner.rtt_appointment where update_dtm > to_date(''+
>> > @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
>> > This statement is giving error...
>> > Incorrect sysntax at +
>> > How do I get date in yyyy-mmm-dd hh:mm:ss format?
>> > The same date I will form in the openquery.
>> > This is struggling me a lot. Pls suggest an idea.
>> > Thanks in advance- Hide quoted text -
>> - Show quoted text -
>
> Hi thank u for the reply,
> What is the problem in my procedure...
> DECLARE @.ApptLastUPdateDateTime varchar(30)
> BEGIN
> DECLARE @.sql_str VARCHAR(4000)
> SELECT @.ApptLastUPdateDateTime = convert(varchar(23),ApptUpdateDtm,
> 120), FROM [LastUpdateDateTime]
>
> SET @.sql_str ='SELECT * from tie_owner.rtt_appointment
> WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''' +
> @.ApptLastUPDateDateTime + ''''
> SET @.sql_str = N'select * from OPENQUERY([TIE DB], ''' +
> REPLACE(@.sql_str, '''', ''') + ''')'
> EXEC @.sql_str
> END
> I am getting error
> The name 'select * from OPENQUERY([TIE DB], 'SELECT * from
> tie_owner.rtt_appointment
> WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-01-01
> 01:01:00''')' is not a valid identifier.
> I am unable to fix this error.
>

Openquery from SQL Server to Oracle error

I want to insert records into an Oracle 8.03 database from MS SQL 2000. I have created a link and have used OPENQUERY to successfully query my Oracle tables. See example, DEV is the LINK name. I need to insert and update records from MS SQL to Oracle and also I need update MS SQL from
Oracle. Can you please give me a working example of insert and update?

example that works:
select *
from OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')

It makes sense that this update would work, but it got WORSE after running this:

update
OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
set last_updated_by = 3
where orders_id = 1

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL

Server is terminating this process.

Connection Broken

From now on, I cannot get the link to work AT ALL! It is so strange. I have rebooted the machine with SQL Server and the Link on it. I added a new link. (Both show up as valid links.) I have gone into ODBC and tested the connection successfully. The Oracle server I am linked to is up and running and I can select from the table.

The only help I can find on Microsoft that is similar says I need SQL Server 2000 service pack 2, which I already have installed.

Now after getting this error, in SQL Server when I try to display the tables for the linked server in the Enterprise Mgr or run the simple select query (the first above) I get no response at all. The query goes off and 30 minutes later I have to break out of the SQL Query Analyzer or Enterprise Mgr because NOTHING happened except the logs show me as having timed out. I left the machine for hours to see if there were queries that needed to complete or something?? No luck -- it does not give results from the query.
:rolleyes:if you want to do that in the easy way, create view out of table of oracle database in sql server and then insert the view, but take care when you insert into the view you should take in the consideration all fields even if its null values
:)|||I don't think you can UPDATE using the OPENQUERY, however have you tried using sp_addlinkedserver and then doing the UPDATE
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'MyOracle',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'MyServer'
GO

UPDATE MyOracle...ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1|||Yes, I used sp_addlinkedserver to create the link. Openquery is supposed to work for insert and update. The update statement you suggested doesn't work and that is why Openquery is needed.|||Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY

UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3

There are 3 articles on technet that may help or are just a wild goose chase.

PRB: Installing DA SDK Causes SQL Distributed Queries to Fail (Q196292) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q196292)
After installing the Microsoft Data Access 2.0 SDK, the following errors may occur when trying to perform a SQL Server 7.0 distributed query:

FIX: Cannot Use Dynamic SQL Statements Within OPENQUERY (Q291376) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q291376)
An Access Violation (AV) may occur if you use the OPENQUERY function to execute a stored procedure that has these properties:

FIX: MDX Queries from Query Analyzer to a Linked Analysis Server Result in Fatal Exception (Q316295) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316295)
When you execute a Multidimensional Expressions (MDX) query against a SQL Server Linked Analysis Server configured with the|||Originally posted by achorozy
[B]Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY

UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3

Good idea, but no luck. Thank you!
:D|||Here is a solution using T-sql four part name convention - the previous solution listed like this was missing the Oracle Schema name:

UPDATE DEV..USER.ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1

Alternately, this should also work with OPENQUERY (note that Microsoft recommends the 'where 1 = 2' clause to prevent rows from being returned, which would add overhead to the query, and most likely cause the query to fail):

UPDATE OPENQUERY(DEV, 'Select * from USER.ORDERS_ALL where 1 = 2)
SET last_updated_by = 3
WHERE orders_id = 1

I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.|||Originally posted by zokrc

I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.

I will try the syntax you suggested early next week (the server crashed and needs new drives).

What do you mean by the above? Is that on the MS SQL Server box? How do you do it and which Ole DB provider?|||I should preface my response by saying that this only concerns you if you are trying to perform changes on the Oracle side as a part of a Sql Server distributed transaction (e.g. a transaction which can be rolled back). If you just want to make updates to the Oracle side, the syntax I have provided should stand on its own.

In my instance, I needed to include my updates/deletes/inserts to Oracle as a part of a Sql Server stored procedure which contained a Distributed Transaction. That way, if anything went wrong either side of the procedure (Oracle or SS), I would be able to roll back transactions in both databases.

SQL Server generally uses the MSDAORA Ole DB provider located on the SQL Server box to talk to Oracle. DTC (Distributed Transaction Coordinator) is the Sql Server component which actually manages the transaction and implements the appropriate Ole DB provider for executing heterogeneous queries.

Each ole db provider has certain properties which can be set that describe what functionality the provider will and won't support. To get distributed transactions to work with the MSDAORA, the ITransactionJoin(see books online for more info on this) property should be set accordingly. I believe this property can be set for the linked server through Enterprise Manager.

FINALLY - what I made reference to in my previous post was a problem we ran into where our MDAC registry settings were not set properly (a lot of things have to be in sync for Distributed Transactions to work). Here is the link on Microsoft's support site on how to do this (very complete!): http://search.support.microsoft.com/search/viewDoc.aspx?docID=KC.Q280106&dialogID=16829074&iterationID=1&sessionID=anonymous|15672521&url=kb;en-us;Q280106

Again, though, if your transactions aren't a part of a distributed transaction, you probably won't have to worry about this part. Let me know if you have any other questions.

opening reports with toggleitems set according to user defaults

i'm new to RS and have created reports with jump to url capability. the main
report has drill down capability up to 4 levels. i would like to know if it
is possible to open the report and have the toggleitems collapsed or expanded
(showing the next level if expanded) based on pre-defined choices. some
users might want drill-down expanded all the way to level 4 for some
groupings while only to level 2 on other groupings. for example, we create
financial reports that show monthly balances for staff salaries, fringe
costs, supplies, etc. a manager might want to see much detail on the staff
salaries and drill down another 2 levels to actually see staff names while
only want to see the highest level for fringe and not be concerned about how
much was paid for social security, retirement, etc..
rather than require the user each time to expand or collapse as they want
each time they run report, is there some way to save their choices to a table
and then through a query obtain their choices and set the toggleitem property
for each row based on its value.
i have found many examples of setting properties based on "value" property
but not other properties. i suspect that if it can be done, the "inscope"
function would be used. hope this makes sense!
any help you can give would be appreciated. thanksi think i found the answer to my question. RS doesn't appear to support
events and only exposes one reportitems property and that is "value". so i'm
guessing that what i'm asking is probably not possible within RS itself.
"dalem" wrote:
> i'm new to RS and have created reports with jump to url capability. the main
> report has drill down capability up to 4 levels. i would like to know if it
> is possible to open the report and have the toggleitems collapsed or expanded
> (showing the next level if expanded) based on pre-defined choices. some
> users might want drill-down expanded all the way to level 4 for some
> groupings while only to level 2 on other groupings. for example, we create
> financial reports that show monthly balances for staff salaries, fringe
> costs, supplies, etc. a manager might want to see much detail on the staff
> salaries and drill down another 2 levels to actually see staff names while
> only want to see the highest level for fringe and not be concerned about how
> much was paid for social security, retirement, etc..
> rather than require the user each time to expand or collapse as they want
> each time they run report, is there some way to save their choices to a table
> and then through a query obtain their choices and set the toggleitem property
> for each row based on its value.
> i have found many examples of setting properties based on "value" property
> but not other properties. i suspect that if it can be done, the "inscope"
> function would be used. hope this makes sense!
> any help you can give would be appreciated. thanks

Opening report takes too long

Hi,

I've created reports using CR v10 Professional developer SP2 and all the latest hotfixes. The reports run fine in most environments except on one computer (XP SP2), where opening some of the reports takes several minutes (normally in a blink of the eye). The problem is that the computer is our report server. The reports reside locally on the computer and database connections etc. work fine. There does not seem to be any logic as to which reports work and which don't.

The problem seems to reside in the .rpt file since opening the problem reports using both the developer and a run time engine take evenly long. We've managed to isolalte the problem to the file opening porcess, before setting any parameter values or database connections logging the runtime engine.

The only work around seems to be rebuilding the reports on the computer creating our concerns. Does anyone know the cause or have a fix?

Thanks in advance,
JukkaMay be virus?
Run Anti Virus and see the performance|||you may try with dao connection taht whold slove your problem

Opening package stored in MSDB

I created a package using the Import\Export wizard and saved it to the local Sql server. Under Management Studio I can see the package, can run in, import it, export it BUT how am I supposed to edit (modify the thing) it?

TIA,

barkingdog

You can make simple edits like changing the property, enabling / disabling task etc by exporting the SSIS package as dtsx files in file system and opening it in SQL Server Management Studio.

To perform more advanced functionalities -like adding new tasks / flows - you need to open and edit the dtsx package in Visual Studio / SQL Server Business Intelligence Development Studio.

Thanks,
Loonysan

|||

Loonysan,

What you described strikes me as totally counter-intuitive. Under sql 2000 after I create and save a simple package, I can double-click it in Enteprse manager (the package opens), and proceed to work on it. End of story.

If I undestood what you said, I have to export my sql 2005 package (say, stored in sql servr) to a dtsx file and then import that into BIDS. This seems overly complicated to me.

Thanks,

Barkingdog.

|||

In SQL 2000 one edited and managed DTS packages in the same environment. This was perhaps ok when packages were simple utilites for the DBA. But in SQL 2005, SSIS Packages are much richer objects and have a full developer environment to support them, as well as a better management environment.

So in 2005 we have BIDS for developing (editing) and Management Studio for administration of deployed packages.

Just like a code project, one typically develops in a specialised environment and then deploys to the server. The developers code (package) is stored where the developer wants it - perhaps in version control. The deployed version lives on the server.

Modifying the version on the server is not good practice. One should modify the version kept in the development environment and deploy the new version to the server.

Some have discovered that it is possible to open a package in the management studio, but as pointed out, this is a most limited experience as it does not have the full range of development features. It may be useful for a quick fix to a property, but even then I would not recommend it.

Is this more complex than in 2000? Perhaps, but practices will should be better for it.

Donald

|||How do you find the SSIS package to save as a file once it is in MSDB? When I go to SSIS, the package itself doesn't show. I'm using Instances, and it will only open for the default install|||

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

|||

AndyHzzzz wrote:

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

Andy,
Did you read Donald's post? *That* is what Microsoft was thinking and I agree with it. SSIS is more of an enterprise development tool, not the toy that DTS was and hence it needs to follow a more rigid methodology.|||

Phil,

I did read Donald's post, and I understand what he is saying. However, that doesn't excuse the fact that I can create a package in the Import/Export wizard, configure it, run it, re-run it, re-configure it, etc., all from within that wizard, but if I ever close that wizard, I have to go to a completely different environment to work with that package again. That, to me, is bad interface design.

Furthermore, consider the actual steps in all of this. Today I needed to import 4 tables from Access to SQL Server. These were the steps involved (excluding cursing and searching Books Online):

Started Import/Export wizard

Attempted import of 4 Access tables using default options

Got errors regarding data conversion, clicked 'Back' to the mappings dialog, changed datatypes (Repeat)

Successfully imported the data.

Closed the Wizard.

Later, realized that I wanted to change some datatypes of the target fields.

Looked for my package in Management Studio. Got an error when selecting MSDB store

Modified MsDtsSrvr.ini.xml file on server. Re-started Integration Services (good thing I have admin privileges)

Found my package, exported to file, opened in Visual Studio.

Attempted to modify the SQL statement "Preparation SQL Task", where the target tables are created.

Got an error telling me that "CREATE TABLE" statements are not allowed, and cannot be parsed (the wizard created this package, remember. Also, this SQL statement was full of embedded carriage returns, so I could only edit the first CREATE TABLE statement anyway).

Copied the CREATE TABLE statements into a text file, modified the task to read the SQL from that file.

Changed the datatypes in the text file.

Ran the package. Success.

What did I gain from this new methodology? Nothing. I was not trying to develop an enterprise application, I just wanted to import 4 tables! This story is typical of my experiences with 2005. Simple tasks have become adventures through seemingly untested tools.

Furthermore, even if I did need to develop the sort of app that SSIS is geared toward, I don't trust it. I have run into too many weird problems with this whole release.

Andy

Opening package stored in MSDB

I created a package using the Import\Export wizard and saved it to the local Sql server. Under Management Studio I can see the package, can run in, import it, export it BUT how am I supposed to edit (modify the thing) it?

TIA,

barkingdog

You can make simple edits like changing the property, enabling / disabling task etc by exporting the SSIS package as dtsx files in file system and opening it in SQL Server Management Studio.

To perform more advanced functionalities -like adding new tasks / flows - you need to open and edit the dtsx package in Visual Studio / SQL Server Business Intelligence Development Studio.

Thanks,
Loonysan

|||

Loonysan,

What you described strikes me as totally counter-intuitive. Under sql 2000 after I create and save a simple package, I can double-click it in Enteprse manager (the package opens), and proceed to work on it. End of story.

If I undestood what you said, I have to export my sql 2005 package (say, stored in sql servr) to a dtsx file and then import that into BIDS. This seems overly complicated to me.

Thanks,

Barkingdog.

|||

In SQL 2000 one edited and managed DTS packages in the same environment. This was perhaps ok when packages were simple utilites for the DBA. But in SQL 2005, SSIS Packages are much richer objects and have a full developer environment to support them, as well as a better management environment.

So in 2005 we have BIDS for developing (editing) and Management Studio for administration of deployed packages.

Just like a code project, one typically develops in a specialised environment and then deploys to the server. The developers code (package) is stored where the developer wants it - perhaps in version control. The deployed version lives on the server.

Modifying the version on the server is not good practice. One should modify the version kept in the development environment and deploy the new version to the server.

Some have discovered that it is possible to open a package in the management studio, but as pointed out, this is a most limited experience as it does not have the full range of development features. It may be useful for a quick fix to a property, but even then I would not recommend it.

Is this more complex than in 2000? Perhaps, but practices will should be better for it.

Donald

|||How do you find the SSIS package to save as a file once it is in MSDB? When I go to SSIS, the package itself doesn't show. I'm using Instances, and it will only open for the default install|||

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

|||

AndyHzzzz wrote:

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

Andy,
Did you read Donald's post? *That* is what Microsoft was thinking and I agree with it. SSIS is more of an enterprise development tool, not the toy that DTS was and hence it needs to follow a more rigid methodology.|||

Phil,

I did read Donald's post, and I understand what he is saying. However, that doesn't excuse the fact that I can create a package in the Import/Export wizard, configure it, run it, re-run it, re-configure it, etc., all from within that wizard, but if I ever close that wizard, I have to go to a completely different environment to work with that package again. That, to me, is bad interface design.

Furthermore, consider the actual steps in all of this. Today I needed to import 4 tables from Access to SQL Server. These were the steps involved (excluding cursing and searching Books Online):

Started Import/Export wizard

Attempted import of 4 Access tables using default options

Got errors regarding data conversion, clicked 'Back' to the mappings dialog, changed datatypes (Repeat)

Successfully imported the data.

Closed the Wizard.

Later, realized that I wanted to change some datatypes of the target fields.

Looked for my package in Management Studio. Got an error when selecting MSDB store

Modified MsDtsSrvr.ini.xml file on server. Re-started Integration Services (good thing I have admin privileges)

Found my package, exported to file, opened in Visual Studio.

Attempted to modify the SQL statement "Preparation SQL Task", where the target tables are created.

Got an error telling me that "CREATE TABLE" statements are not allowed, and cannot be parsed (the wizard created this package, remember. Also, this SQL statement was full of embedded carriage returns, so I could only edit the first CREATE TABLE statement anyway).

Copied the CREATE TABLE statements into a text file, modified the task to read the SQL from that file.

Changed the datatypes in the text file.

Ran the package. Success.

What did I gain from this new methodology? Nothing. I was not trying to develop an enterprise application, I just wanted to import 4 tables! This story is typical of my experiences with 2005. Simple tasks have become adventures through seemingly untested tools.

Furthermore, even if I did need to develop the sort of app that SSIS is geared toward, I don't trust it. I have run into too many weird problems with this whole release.

Andy

sql

Opening package stored in MSDB

I created a package using the Import\Export wizard and saved it to the local Sql server. Under Management Studio I can see the package, can run in, import it, export it BUT how am I supposed to edit (modify the thing) it?

TIA,

barkingdog

You can make simple edits like changing the property, enabling / disabling task etc by exporting the SSIS package as dtsx files in file system and opening it in SQL Server Management Studio.

To perform more advanced functionalities -like adding new tasks / flows - you need to open and edit the dtsx package in Visual Studio / SQL Server Business Intelligence Development Studio.

Thanks,
Loonysan

|||

Loonysan,

What you described strikes me as totally counter-intuitive. Under sql 2000 after I create and save a simple package, I can double-click it in Enteprse manager (the package opens), and proceed to work on it. End of story.

If I undestood what you said, I have to export my sql 2005 package (say, stored in sql servr) to a dtsx file and then import that into BIDS. This seems overly complicated to me.

Thanks,

Barkingdog.

|||

In SQL 2000 one edited and managed DTS packages in the same environment. This was perhaps ok when packages were simple utilites for the DBA. But in SQL 2005, SSIS Packages are much richer objects and have a full developer environment to support them, as well as a better management environment.

So in 2005 we have BIDS for developing (editing) and Management Studio for administration of deployed packages.

Just like a code project, one typically develops in a specialised environment and then deploys to the server. The developers code (package) is stored where the developer wants it - perhaps in version control. The deployed version lives on the server.

Modifying the version on the server is not good practice. One should modify the version kept in the development environment and deploy the new version to the server.

Some have discovered that it is possible to open a package in the management studio, but as pointed out, this is a most limited experience as it does not have the full range of development features. It may be useful for a quick fix to a property, but even then I would not recommend it.

Is this more complex than in 2000? Perhaps, but practices will should be better for it.

Donald

|||How do you find the SSIS package to save as a file once it is in MSDB? When I go to SSIS, the package itself doesn't show. I'm using Instances, and it will only open for the default install|||

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

|||

AndyHzzzz wrote:

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

Andy,
Did you read Donald's post? *That* is what Microsoft was thinking and I agree with it. SSIS is more of an enterprise development tool, not the toy that DTS was and hence it needs to follow a more rigid methodology.|||

Phil,

I did read Donald's post, and I understand what he is saying. However, that doesn't excuse the fact that I can create a package in the Import/Export wizard, configure it, run it, re-run it, re-configure it, etc., all from within that wizard, but if I ever close that wizard, I have to go to a completely different environment to work with that package again. That, to me, is bad interface design.

Furthermore, consider the actual steps in all of this. Today I needed to import 4 tables from Access to SQL Server. These were the steps involved (excluding cursing and searching Books Online):

Started Import/Export wizard

Attempted import of 4 Access tables using default options

Got errors regarding data conversion, clicked 'Back' to the mappings dialog, changed datatypes (Repeat)

Successfully imported the data.

Closed the Wizard.

Later, realized that I wanted to change some datatypes of the target fields.

Looked for my package in Management Studio. Got an error when selecting MSDB store

Modified MsDtsSrvr.ini.xml file on server. Re-started Integration Services (good thing I have admin privileges)

Found my package, exported to file, opened in Visual Studio.

Attempted to modify the SQL statement "Preparation SQL Task", where the target tables are created.

Got an error telling me that "CREATE TABLE" statements are not allowed, and cannot be parsed (the wizard created this package, remember. Also, this SQL statement was full of embedded carriage returns, so I could only edit the first CREATE TABLE statement anyway).

Copied the CREATE TABLE statements into a text file, modified the task to read the SQL from that file.

Changed the datatypes in the text file.

Ran the package. Success.

What did I gain from this new methodology? Nothing. I was not trying to develop an enterprise application, I just wanted to import 4 tables! This story is typical of my experiences with 2005. Simple tasks have become adventures through seemingly untested tools.

Furthermore, even if I did need to develop the sort of app that SSIS is geared toward, I don't trust it. I have run into too many weird problems with this whole release.

Andy

Opening a rs2005 rdl in rs2000

Can a report created in VS2005 be opened in VS2003?
No, the schema is not compatible.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Opening a rs2005 rdl in rs2000

Can a report created in VS2005 be opened in VS2003?No, the schema is not compatible.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

Opening a rs2005 rdl in rs2000

Can a report created in VS2005 be opened in VS2003?No, the schema is not compatible.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--sql

Open Visual Studio file in Embarcadero erstudio

hello Friends,
I am trying to import a database model that I created in Microsoft visio into Embarcadero erstudio 7.1.1? Any idea how that could be done? There seems to be no option to import vsd files?I think the Professional sku of Visio can export a sql script from a .vsd

Open SqlCeConnection from an ASP.NET Web Service?

Help!

I am trying to implement a web service that creates and populates a SQL Mobile database file, then returns the created database to a mobile device as a byte array. The database size could be in excess of 500,000 rows, which is why I want to do as much of this preprocessing on the server before it gets to the mobile device. I can't use replication since I have to do some shaping of the data before I can use it on the mobile device.

Unfortunately, the web service is throwing the following exception when I try to instantiate a SqlCeConnection object:

"System.NotSupportedException: SQL Server Everywhere Edition is not intended for ASP.NET development."

Are there any suggestions as to how I can get around this potential limitation? If I refactor out the code that actually performs the SqlCe operations to a separate assembly, but still call that assembly from within the ASP.NET process, will I get the same error?

I know that you can work with Sql Mobile databases from the deskop, and I suppose I could invoke a console application to create the database, but that seems like such a hack.

Thanks for any advice,

Matthew

This limitation was built into the SQL Everywhere engine because it is supposed to be used as an embedded database (running in-proc with your application), and not in a client-server scenario. The engine will actually test if there are any IIS processes in the calling stack (not sure about the terminology here), so you are out of luck.

Maybe someone smarter than me has a solution - I would also love to know it.

|||

I can understand the reasoning behind that decision; SQL Express is positioned perfectly for that case, but I am definitely not trying to use the SQL Everywhere engine in a client/server scenario.

I am just trying to let a server with significantly more resources pre-build a database for use on a mobile device, then ship the database down to the client. I'd hate to have to write some process that I can "shell" out to from the web app, generate the .sdf file, then read it in by the web service, but I really don't want to if I don't have to. Replication is not really an option, unfortunately.

Thanks,

Matthew

Open Report in MS Word.

I created Report in SQLREporting services. I need open in in Worl, Is it
possible , using "Format=" ; I can open it in Excel by giving "Format=Excel".
plz help me .its an urgent..Not directly.
You could format it as MHTML or Excel, and open it in
Word.
Word can contain Excel spreadsheets as objects, so you could may be do
something fancy in office vba.
But I guess you want to export directly to Word, this is not possible
with RS out of the box.
Chris
pramod wrote:
> I created Report in SQLREporting services. I need open in in Worl, Is
> it possible , using "Format=" ; I can open it in Excel by giving
> "Format=Excel". plz help me .its an urgent..

Open Report Builder and Link to Report Directly?

Hi,

I've created a simple webpart that lists all the reports that a user has created using the Report Builder and next to each line item is an "Edit" link. What I would like to do is open up the Report Builder application and pass a parameter of the report path or something like that so that report builder opens up the report automatically.

Is this possible?

Thanks

Tristian

http://blogs.msdn.com/bobmeyers/archive/2006/01/24/517083.aspx|||THanks, this worked great!

Open an Password Protected Access-2000 Database (OLE DB)

Hi,
I have created reports for a password protected database using Crystal Reports-8 thru OLE DB Connection. Whenever calling the report from a VB Form(thru Crystal Report Active-x Control), it reports "Unable to logon to SQL Server".

But if I try same thru ODBC Connection thru a DSN, it works perfect.

Since, there is a need to change the name of the source database (during runtime, the report has to access data from different databases), I require the same thru an OLE DB Connection. OR there is any way to change the database name in an odbc dsn by modifying connection parameters?

I remain.

Thanks.If u r tring to open Access database which has password. to change the database at runtime u can use the following code in vb.My report using Direct Database file connection not oledb or others.

Report.Database.Tables.Item(1).Location = App.Path & "\" & dbName
Report.Database.Tables(1).SetSessionInfo "", Chr(10) & txtPass

if u have more then on table u can use loop.

Ooverflow pages nonunique clustered indexes

What are the perforamnce hits for non clustered indexes?
Does SQL 2000 still use overflow pages are created for nonunique clustered
indexes?
Thanks,
Jon A
SQL Server never used overflow pages...
Can you be a bit more specific in your question? NC indexes has a perf hit when you modify data as
the data in the indexes affected by the modification need to be reflected in the indexes as well.
Also, page splits can occur. Is that what you are referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:C53B3CE3-65A5-4F6A-B848-49D9F7B88F48@.microsoft.com...
> What are the perforamnce hits for non clustered indexes?
> Does SQL 2000 still use overflow pages are created for nonunique clustered
> indexes?
> --
> Thanks,
> Jon A
|||If you create a nonunique clustered index does SQL add an identifier to the
key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
bytes)
The table I have is unique by a key that would be 41 bytes long. So I was
going to make this the Primary Key as unique non clustered. But this table is
very large.
What performance hit do you get from creating a nonunique clustered index?
I cannot control fragmentation without a clustered index.
And if SQl Server adds an indentifier to the key I define, what is the type?
I know that the design is poor for this table but due to the vast amount of
programs that access it I cannot change the design. It has a large number of
inserts.
I am thinking of adding an identity colum and putting the clustered index on
that. And making the primary key a unique non clustered index on the 3
columns that total 41 bytes. Almost all data is update or selected or ordered
by these 3 columns.
I feel 41 bytes is to large for a clustered index.
"Tibor Karaszi" wrote:

> SQL Server never used overflow pages...
> Can you be a bit more specific in your question? NC indexes has a perf hit when you modify data as
> the data in the indexes affected by the modification need to be reflected in the indexes as well.
> Also, page splits can occur. Is that what you are referring to?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:C53B3CE3-65A5-4F6A-B848-49D9F7B88F48@.microsoft.com...
>
>
|||> If you create a nonunique clustered index does SQL add an identifier to the
> key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
> bytes)
Yes, SQL Server add a 4 byte "uniqiefier".

> What performance hit do you get from creating a nonunique clustered index?
Can you qualify "performance hit"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:214C7867-4CFE-4B64-ABF4-78AC62D60BF4@.microsoft.com...[vbcol=seagreen]
> If you create a nonunique clustered index does SQL add an identifier to the
> key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
> bytes)
> The table I have is unique by a key that would be 41 bytes long. So I was
> going to make this the Primary Key as unique non clustered. But this table is
> very large.
> What performance hit do you get from creating a nonunique clustered index?
> I cannot control fragmentation without a clustered index.
> And if SQl Server adds an indentifier to the key I define, what is the type?
> I know that the design is poor for this table but due to the vast amount of
> programs that access it I cannot change the design. It has a large number of
> inserts.
> I am thinking of adding an identity colum and putting the clustered index on
> that. And making the primary key a unique non clustered index on the 3
> columns that total 41 bytes. Almost all data is update or selected or ordered
> by these 3 columns.
> I feel 41 bytes is to large for a clustered index.
> "Tibor Karaszi" wrote:
|||I have 300,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
I am wondering which would be better
1. Put a unique Clustered index on the 40 byte fields
member(int),vin(20),stock(18)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the identifier)
and put the Primary Key on member(int),vin(20),stock(18) as a unique non
clustered index.
This table has heavy updates at night in batch.
There is currently no clustered index and there is no way to control
fragmentation.
"Tibor Karaszi" wrote:

> Yes, SQL Server add a 4 byte "uniqiefier".
>
> Can you qualify "performance hit"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:214C7867-4CFE-4B64-ABF4-78AC62D60BF4@.microsoft.com...
>
>

Ooverflow pages nonunique clustered indexes

What are the perforamnce hits for non clustered indexes?
Does SQL 2000 still use overflow pages are created for nonunique clustered
indexes?
--
Thanks,
Jon ASQL Server never used overflow pages...
Can you be a bit more specific in your question? NC indexes has a perf hit when you modify data as
the data in the indexes affected by the modification need to be reflected in the indexes as well.
Also, page splits can occur. Is that what you are referring to?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:C53B3CE3-65A5-4F6A-B848-49D9F7B88F48@.microsoft.com...
> What are the perforamnce hits for non clustered indexes?
> Does SQL 2000 still use overflow pages are created for nonunique clustered
> indexes?
> --
> Thanks,
> Jon A|||If you create a nonunique clustered index does SQL add an identifier to the
key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
bytes)
The table I have is unique by a key that would be 41 bytes long. So I was
going to make this the Primary Key as unique non clustered. But this table is
very large.
What performance hit do you get from creating a nonunique clustered index?
I cannot control fragmentation without a clustered index.
And if SQl Server adds an indentifier to the key I define, what is the type?
I know that the design is poor for this table but due to the vast amount of
programs that access it I cannot change the design. It has a large number of
inserts.
I am thinking of adding an identity colum and putting the clustered index on
that. And making the primary key a unique non clustered index on the 3
columns that total 41 bytes. Almost all data is update or selected or ordered
by these 3 columns.
I feel 41 bytes is to large for a clustered index.
"Tibor Karaszi" wrote:
> SQL Server never used overflow pages...
> Can you be a bit more specific in your question? NC indexes has a perf hit when you modify data as
> the data in the indexes affected by the modification need to be reflected in the indexes as well.
> Also, page splits can occur. Is that what you are referring to?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:C53B3CE3-65A5-4F6A-B848-49D9F7B88F48@.microsoft.com...
> > What are the perforamnce hits for non clustered indexes?
> > Does SQL 2000 still use overflow pages are created for nonunique clustered
> > indexes?
> > --
> > Thanks,
> > Jon A
>
>|||> If you create a nonunique clustered index does SQL add an identifier to the
> key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
> bytes)
Yes, SQL Server add a 4 byte "uniqiefier".
> What performance hit do you get from creating a nonunique clustered index?
Can you qualify "performance hit"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:214C7867-4CFE-4B64-ABF4-78AC62D60BF4@.microsoft.com...
> If you create a nonunique clustered index does SQL add an identifier to the
> key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
> bytes)
> The table I have is unique by a key that would be 41 bytes long. So I was
> going to make this the Primary Key as unique non clustered. But this table is
> very large.
> What performance hit do you get from creating a nonunique clustered index?
> I cannot control fragmentation without a clustered index.
> And if SQl Server adds an indentifier to the key I define, what is the type?
> I know that the design is poor for this table but due to the vast amount of
> programs that access it I cannot change the design. It has a large number of
> inserts.
> I am thinking of adding an identity colum and putting the clustered index on
> that. And making the primary key a unique non clustered index on the 3
> columns that total 41 bytes. Almost all data is update or selected or ordered
> by these 3 columns.
> I feel 41 bytes is to large for a clustered index.
> "Tibor Karaszi" wrote:
>> SQL Server never used overflow pages...
>> Can you be a bit more specific in your question? NC indexes has a perf hit when you modify data
>> as
>> the data in the indexes affected by the modification need to be reflected in the indexes as well.
>> Also, page splits can occur. Is that what you are referring to?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
>> news:C53B3CE3-65A5-4F6A-B848-49D9F7B88F48@.microsoft.com...
>> > What are the perforamnce hits for non clustered indexes?
>> > Does SQL 2000 still use overflow pages are created for nonunique clustered
>> > indexes?
>> > --
>> > Thanks,
>> > Jon A
>>|||I have 300,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
I am wondering which would be better
1. Put a unique Clustered index on the 40 byte fields
member(int),vin(20),stock(18)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the identifier)
and put the Primary Key on member(int),vin(20),stock(18) as a unique non
clustered index.
This table has heavy updates at night in batch.
There is currently no clustered index and there is no way to control
fragmentation.
"Tibor Karaszi" wrote:
> > If you create a nonunique clustered index does SQL add an identifier to the
> > key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
> > bytes)
> Yes, SQL Server add a 4 byte "uniqiefier".
>
> > What performance hit do you get from creating a nonunique clustered index?
> Can you qualify "performance hit"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:214C7867-4CFE-4B64-ABF4-78AC62D60BF4@.microsoft.com...
> > If you create a nonunique clustered index does SQL add an identifier to the
> > key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
> > bytes)
> > The table I have is unique by a key that would be 41 bytes long. So I was
> > going to make this the Primary Key as unique non clustered. But this table is
> > very large.
> > What performance hit do you get from creating a nonunique clustered index?
> > I cannot control fragmentation without a clustered index.
> > And if SQl Server adds an indentifier to the key I define, what is the type?
> > I know that the design is poor for this table but due to the vast amount of
> > programs that access it I cannot change the design. It has a large number of
> > inserts.
> > I am thinking of adding an identity colum and putting the clustered index on
> > that. And making the primary key a unique non clustered index on the 3
> > columns that total 41 bytes. Almost all data is update or selected or ordered
> > by these 3 columns.
> > I feel 41 bytes is to large for a clustered index.
> >
> > "Tibor Karaszi" wrote:
> >
> >> SQL Server never used overflow pages...
> >>
> >> Can you be a bit more specific in your question? NC indexes has a perf hit when you modify data
> >> as
> >> the data in the indexes affected by the modification need to be reflected in the indexes as well.
> >> Also, page splits can occur. Is that what you are referring to?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> >> news:C53B3CE3-65A5-4F6A-B848-49D9F7B88F48@.microsoft.com...
> >> > What are the perforamnce hits for non clustered indexes?
> >> > Does SQL 2000 still use overflow pages are created for nonunique clustered
> >> > indexes?
> >> > --
> >> > Thanks,
> >> > Jon A
> >>
> >>
> >>
>
>

Ooverflow pages nonunique clustered indexes

What are the perforamnce hits for non clustered indexes?
Does SQL 2000 still use overflow pages are created for nonunique clustered
indexes?
--
Thanks,
Jon ASQL Server never used overflow pages...
Can you be a bit more specific in your question? NC indexes has a perf hit w
hen you modify data as
the data in the indexes affected by the modification need to be reflected in
the indexes as well.
Also, page splits can occur. Is that what you are referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:C53B3CE3-65A5-4F6A-B848-49D9F7B88F48@.microsoft.com...
> What are the perforamnce hits for non clustered indexes?
> Does SQL 2000 still use overflow pages are created for nonunique clustered
> indexes?
> --
> Thanks,
> Jon A|||If you create a nonunique clustered index does SQL add an identifier to the
key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
bytes)
The table I have is unique by a key that would be 41 bytes long. So I was
going to make this the Primary Key as unique non clustered. But this table i
s
very large.
What performance hit do you get from creating a nonunique clustered index?
I cannot control fragmentation without a clustered index.
And if SQl Server adds an indentifier to the key I define, what is the type?
I know that the design is poor for this table but due to the vast amount of
programs that access it I cannot change the design. It has a large number of
inserts.
I am thinking of adding an identity colum and putting the clustered index on
that. And making the primary key a unique non clustered index on the 3
columns that total 41 bytes. Almost all data is update or selected or ordere
d
by these 3 columns.
I feel 41 bytes is to large for a clustered index.
"Tibor Karaszi" wrote:

> SQL Server never used overflow pages...
> Can you be a bit more specific in your question? NC indexes has a perf hit
when you modify data as
> the data in the indexes affected by the modification need to be reflected
in the indexes as well.
> Also, page splits can occur. Is that what you are referring to?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:C53B3CE3-65A5-4F6A-B848-49D9F7B88F48@.microsoft.com...
>
>|||> If you create a nonunique clustered index does SQL add an identifier to the">
> key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
> bytes)
Yes, SQL Server add a 4 byte "uniqiefier".

> What performance hit do you get from creating a nonunique clustered index?
Can you qualify "performance hit"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:214C7867-4CFE-4B64-ABF4-78AC62D60BF4@.microsoft.com...[vbcol=seagreen]
> If you create a nonunique clustered index does SQL add an identifier to th
e
> key to unforce uniqueness. What type is that? Is it a uniqueidentifier?(16
> bytes)
> The table I have is unique by a key that would be 41 bytes long. So I was
> going to make this the Primary Key as unique non clustered. But this table
is
> very large.
> What performance hit do you get from creating a nonunique clustered index?
> I cannot control fragmentation without a clustered index.
> And if SQl Server adds an indentifier to the key I define, what is the typ
e?
> I know that the design is poor for this table but due to the vast amount o
f
> programs that access it I cannot change the design. It has a large number
of
> inserts.
> I am thinking of adding an identity colum and putting the clustered index
on
> that. And making the primary key a unique non clustered index on the 3
> columns that total 41 bytes. Almost all data is update or selected or orde
red
> by these 3 columns.
> I feel 41 bytes is to large for a clustered index.
> "Tibor Karaszi" wrote:
>|||I have 300,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
I am wondering which would be better
1. Put a unique Clustered index on the 40 byte fields
member(int),vin(20),stock(18)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the identifie
r)
and put the Primary Key on member(int),vin(20),stock(18) as a unique non
clustered index.
This table has heavy updates at night in batch.
There is currently no clustered index and there is no way to control
fragmentation.
"Tibor Karaszi" wrote:

> Yes, SQL Server add a 4 byte "uniqiefier".
>
> Can you qualify "performance hit"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon A" <JonA@.discussions.microsoft.com> wrote in message
> news:214C7867-4CFE-4B64-ABF4-78AC62D60BF4@.microsoft.com...
>
>

Only transform unique rows

Hi,

I have created a SSIS package which includes a Data Flow that will transform my data. The Data Flow Source for this Data Flow is a SQL Server table with approx. 60 mill. rows. Theses rows ought to be unique but isn't quite, so I would like to ensure that only one of each row makes it throug to my Data Flow Destination, another SQL Server table.

What is the most efficient way to ensure this - is there some Data Flow Transformation that can assure this, should I write a SQL statement that deletes all duplicates before the Data Flow or should I take a third approch?

- SuneI can see two ways to do so:

1. OLE DB source -> Sort -> OLE DB dest

Configure Sort transform to "Remove rows with duplicate sort values".

2. OLE DB source -> Aggregate -> OLE DB dest

Configure Aggregate transform to group by all columns.

No.2 might be more efficient. Would you like to try and tell us the results? I'm very interested to know.|||Actually there is a third way and that is to use the T-SQL statement to do the dedupe. This can be much more efficient than either of the 2 mentioned above since the engine is optimized to do this especially if it can use an index. I think you would have to do some benchmarks to determine which is optimal for your particular scenario.

Thanks,
Matt|||I have actually been using a T-SQL statement up until now and I has been working okay. However all of a sudden performance have degraded significantly which made me wonder if there was any alternative ways.

I hope to get around to make some benchmarks during the weekend and promise to post the results.

Thanks for your help so far.

- Sune

Only the newest articles

Hi all,
I've created a database with 1 table, Articles. In that table, there are 4
colmns: ArticleID(autonumber), Title, XMLFile and Date(date/time).
I want to write an SQL command to get only the newest entries. How can I
solve this?
Thanks,
Vincent
Hi
The following query will return the last 10 rows in the table, ordered by
date, with the newest row first.
SELECT
TOP 10
ArticleID,
Title,
XMLFile,
Date
FROM
dbo.Articles
ORDER BY
Date DESC
Regards
Mike
"JJ" wrote:

> Hi all,
> I've created a database with 1 table, Articles. In that table, there are 4
> colmns: ArticleID(autonumber), Title, XMLFile and Date(date/time).
> I want to write an SQL command to get only the newest entries. How can I
> solve this?
> Thanks,
> Vincent
>
>