general comment is required

Hi,
I am trying to implement master detail kind of functionality. table1 has
primary key t1 and table2 has primary key t2. I have not placed any
relationship on these tables. these tables are free tables.
what will happen if I place foreign key will search sort become faster ?
assume data is perfect with/without relationship, so main purpose of having
foreign key relationship is almost equall to 0.
your comments are required.
Kishorkishor (kishor@.discussions.microsoft.com) writes:
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of
> having foreign key relationship is almost equall to 0.
You don't add foreign keys fort sorts to make faster (and I find it
difficult a imagine where an FK would affect the speed of a sort). You
add foreign-key constraints to enforce referential integrity.
If you have, say, an Orders and an OrderDetails table, the Order table
would have OrderID as its primary key, and OrderDeatails would have a
two-column of OrderID and some other column (e.g. RowNo or ProductID).
Furthermore OrderDetails would have a FK constraint to say that any
OrderID must be an existing OrderID in Orders. This constraint applies
both when inserting into OrderDetails and when deleting from Orders.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, there will be a difference in performance.
BOL states that a FK relationship b/w 2 tables indicates that the 2 tables
have been optimized to be combined in a query that uses the keys as its
criteria.
Ideally, u shud maintain an index on the FK columns for even better
performance of ur queries. I doubt if the index is maintained in the 2 cases
mentioned (with & without FK), will there be any speed difference or not?
Probably, not.
- R
"kishor" wrote:

> Hi,
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of havin
g
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
>
>|||If a FK relationship makes sense there is always a benefit for using it
and keeping therefore referential integrity, but just using a FK does
NOT mean do use an index. FK can be also place on columns which aren=B4t
indexed, so you should be aware not to mix that up. But I hope that you
are in the design phase and not the reengineering phase, because your
thoughts should take place in the designing phase.
HTH, jens Suessmeyer.|||kishor wrote:

> Hi,
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of havin
g
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
You say you assume the data is perfect but how will you enforce that
rule without a foreign key? If you enforce it only in your application
then you have to duplicate that integrity feature for each application
or data entry screen. A bug in any one place could affect all other
users of the data. When you come to write queries against the data, the
server won't see a referential integrity constraint so it won't be able
to use that constraint to help construct a better query plan.
Always declare the constraints that are required to enforce your
business rules.
David Portas
SQL Server MVP
--|||"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:D8FDB80A-F7E6-4600-8BED-B20A80CD5815@.microsoft.com...
> Hi,
> I am trying to implement master detail kind of functionality.
table1 has
> primary key t1 and table2 has primary key t2. I have not placed
any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become
faster ?
> assume data is perfect with/without relationship, so main purpose
of having
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
>
kishor,
Establishing a foreign key constraint is done for the purpose of
enforcing referential integrity. These constraints should always
been done where cardinality indicates it. Referential integrity is
one of the key features of an RDBMS, avoiding its use would be
substantially less than ideal (I would almost go so far as to say,
"Why have an RDBMS if you don't bother with referential
integrity?").
The foreign key constraint may be enforced by the use of an index
(in SQL Server, it usually is). Indexes can improve performance on
some types of queries (SELECT), and the query optimizer may or may
not use that index on those queries depending on *many* other
factors. Indexes may degrade performance on some types of queries
(UPDATE, INSERT, DELETE). But these considerations are entirely
beside the consideration of enforcing referential integrity.
RI provides for database consistency. Without it, your database
may, without any warning, enter an "inconsistent state". If you
encounter such an error in your database(quite likely without
enforced RI, IMO), I wish you luck in fixing it.
Sincerely,
Chris O.

Fuzzy Lookup Error

Hi

I get the following error when I use Fuzzy Lookup in a Data Flow task with TransactionOption property set to “Required”

[Fuzzy Lookup [61]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot create new connection because in manual or distributed transaction mode.".

When I Change the TransactionProperty to “Supported” it works fine.

I need the property set to Required for it does an undo in the event of a failure.

Any ideas on how to get the Fuzzy Lookup to work

Are you executing the package containing the data flow task from a parent package? I have seen an issue where the OLE DB connection does not properly defect from the transaction, and throws an error like you're seeing here. The work around was to execute the child package out of process.

~Matt

|||i dont have any child packages..|||

Set Required on the package container, and Supported on all child containers. Then they will automatically enlist with the transaction from the package container.

Why do you need a transaction around a lookup?

sql

Fundamental help required with SQL connection

I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
server.

When I create and instance of the server, it has a the format
machinename/instancename

This is fine for the developement machine but how does my code connect to
the server when it is on another machine ? When an instance of MSDE is run
on the target, it will produce...
anotherPCname/instancename

Thanks for any helpThe 'Data Source' keyword in the connection string specifies the SQL Server
instance you want to connect to. A best practice is to store the connection
string externally (e.g. config file) rather than hard-code it in your app so
that you can connect to different servers without changing code. Connection
string examples:

Local default instance:
Data Source=ThisServer;Initial Catalog=MyDatabase;Integrated Security=SSPI

Local named instance:
Data Source=ThisServer\ThisInstance;Initial Catalog=MyDatabase;Integrated
Security=SSPI

Remote default instance:
Data Source=OtherServer;Initial Catalog=MyDatabase;Integrated Security=SSPI

Remote named instance:
Data Source=OtherServer\OtherInstance;Initial Catalog=MyDatabase;Integrated
Security=SSPI

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <david@.orbitcoms.com> wrote in message
news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> server.
> When I create and instance of the server, it has a the format
> machinename/instancename
> This is fine for the developement machine but how does my code connect to
> the server when it is on another machine ? When an instance of MSDE is run
> on the target, it will produce...
> anotherPCname/instancename
> Thanks for any help
>|||Dan,

Thanks for the reply.

So, I just need to create a text file with the connection string with say

the local machine ID and database name. The application loads this at boot

and

connects to the named server.

When I deploy the application, I have the client alter the text file by

finding

the machine name and modifying the text to suit ??

I suppose I could use a Try Catch and have try with the local settings and

if it fails

it loads the file in the catch sub (or maybe the string is stored in

registry).

One thing I am also unable to find out is. How does MSDE know where the file

is located on the third party machine ? Should the Initial Catalog =

Path+dasename ?

Thanks

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:FayEc.70$oD3.17@.newsread1.news.pas.earthlink. net...
> The 'Data Source' keyword in the connection string specifies the SQL
Server
> instance you want to connect to. A best practice is to store the
connection
> string externally (e.g. config file) rather than hard-code it in your app
so
> that you can connect to different servers without changing code.
Connection
> string examples:
> Local default instance:
> Data Source=ThisServer;Initial Catalog=MyDatabase;Integrated Security=SSPI
> Local named instance:
> Data Source=ThisServer\ThisInstance;Initial Catalog=MyDatabase;Integrated
> Security=SSPI
> Remote default instance:
> Data Source=OtherServer;Initial Catalog=MyDatabase;Integrated
Security=SSPI
> Remote named instance:
> Data Source=OtherServer\OtherInstance;Initial
Catalog=MyDatabase;Integrated
> Security=SSPI
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David" <david@.orbitcoms.com> wrote in message
> news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> > server.
> > When I create and instance of the server, it has a the format
> > machinename/instancename
> > This is fine for the developement machine but how does my code connect
to
> > the server when it is on another machine ? When an instance of MSDE is
run
> > on the target, it will produce...
> > anotherPCname/instancename
> > Thanks for any help|||Hi

To connect to a different instance/server that is running your database then
the connection string will need to be changed. Quite often this sort of
thing is held in the registry and you configure it on installation.
Alternatively you may want to provide a small application to configure the
settings.

http://www.microsoft.com/sql/msde/t...integration.asp

John

"David" <david@.orbitcoms.com> wrote in message
news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> server.
> When I create and instance of the server, it has a the format
> machinename/instancename
> This is fine for the developement machine but how does my code connect to
> the server when it is on another machine ? When an instance of MSDE is run
> on the target, it will produce...
> anotherPCname/instancename
> Thanks for any help
>|||Thanks again for the replies.

I have tried a hard-coded option of connection and it worked.

ie.

TRY
sqlconnectionstring = ...... originalmachinename......password=..."
CATCH
sqlconnectionstring = ...... newmachinename......password=..."
END TRY

I tried to hook this into a config file but I had trouble(I used the wizard
in VB.NET to make the connection string and edited it but the area the code
was placed did not seem to like me playing with the code adding file open
command etc.

I will try by completely coding the connection in the main form load
routine.
Like this

TRY
Load my original instance for development and test
CATCH
read registry and see if validconnect string exists
if so then try to connect else
prompt user for PC name
save name to registry
try to connect
FINALLY
give up
END TRY

would this approach seem reasonable ?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:_FyEc.6675$%N6.73110644@.news-text.cableinet.net...
> Hi
> To connect to a different instance/server that is running your database
then
> the connection string will need to be changed. Quite often this sort of
> thing is held in the registry and you configure it on installation.
> Alternatively you may want to provide a small application to configure the
> settings.
> http://www.microsoft.com/sql/msde/t...integration.asp
> John
> "David" <david@.orbitcoms.com> wrote in message
> news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> > server.
> > When I create and instance of the server, it has a the format
> > machinename/instancename
> > This is fine for the developement machine but how does my code connect
to
> > the server when it is on another machine ? When an instance of MSDE is
run
> > on the target, it will produce...
> > anotherPCname/instancename
> > Thanks for any help|||Hi

This may help:
http://support.microsoft.com/defaul...kb;en-us;309485
http://support.microsoft.com/defaul...kb;EN-US;283245

John

"David" <david@.orbitcoms.com> wrote in message
news:qazEc.71562$sj4.10264@.news-server.bigpond.net.au...
> Thanks again for the replies.
> I have tried a hard-coded option of connection and it worked.
> ie.
> TRY
> sqlconnectionstring = ......
originalmachinename......password=..."
> CATCH
> sqlconnectionstring = ...... newmachinename......password=..."
> END TRY
> I tried to hook this into a config file but I had trouble(I used the
wizard
> in VB.NET to make the connection string and edited it but the area the
code
> was placed did not seem to like me playing with the code adding file open
> command etc.
> I will try by completely coding the connection in the main form load
> routine.
> Like this
> TRY
> Load my original instance for development and test
> CATCH
> read registry and see if validconnect string exists
> if so then try to connect else
> prompt user for PC name
> save name to registry
> try to connect
> FINALLY
> give up
> END TRY
> would this approach seem reasonable ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:_FyEc.6675$%N6.73110644@.news-text.cableinet.net...
> > Hi
> > To connect to a different instance/server that is running your database
> then
> > the connection string will need to be changed. Quite often this sort of
> > thing is held in the registry and you configure it on installation.
> > Alternatively you may want to provide a small application to configure
the
> > settings.
> > http://www.microsoft.com/sql/msde/t...integration.asp
> > John
> > "David" <david@.orbitcoms.com> wrote in message
> > news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> > > I am writing SQL data apps using VB.NET 2003, MSDE is being used as
the
> > > server.
> > > > When I create and instance of the server, it has a the format
> > > machinename/instancename
> > > > This is fine for the developement machine but how does my code connect
> to
> > > the server when it is on another machine ? When an instance of MSDE is
> run
> > > on the target, it will produce...
> > > anotherPCname/instancename
> > > > Thanks for any help
> > > >|||I finally have the problem figured out. I needed to ATTACH the database to
sql server (MSDE2000) using the osql utility.

Now, would someone be able to explain how I can automate the attachment at
the customer end.
Preferably, I would like my VN.NET application install to copy the
unattached database files and attach them to the MSDE server.

Thanks

"David" <david@.orbitcoms.com> wrote in message
news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> server.
> When I create and instance of the server, it has a the format
> machinename/instancename
> This is fine for the developement machine but how does my code connect to
> the server when it is on another machine ? When an instance of MSDE is run
> on the target, it will produce...
> anotherPCname/instancename
> Thanks for any help
>|||Hi
I am totally new to the VB.net, only learn this about two weeks. I
used codes from MS to create a file upload class then add function to
store the uploaded filename to a table in SQL server. However I kept
getting the error message about "cannot connect to database" No matter
what format I used. I have tried at least two dozen connection stringe
formats either in Sqlconnection or OleDBConnection. Here is the basic
formats that I used:
strCon = "Data Source =localhost;Password=;User ID=sa;Initial
Catalog=Northwind" for SqlConnection
strCon = "Provider=SQLOLEDB.1;User ID=sa;Password=;Persist Security
Info=True;Initial Catalog=Northwind;Data Source=FAL-L6388" for
OleDbConnection.
I use SQL 7, which was installed by default, i,e only have user ID =
sa, no password, use the default Northwind database. That's all. My
Server is local named FAL-L688.
I have replaced the Data Source with Server= but still give me same
error. I even used an udl to create the OleDB coonnection and put
into my string, still NO. I have exhauseted all alternatives. Does
anyone have clue? Is anyway to debug the trasaction? so i can find out
what went wrong?|||Here is my codes:
Imports System.Data.OleDb
Imports System.Configuration
...
Dim oCon As New OleDbConnection
Dim oCmd As New OleDbCommand
oCon = New OleDbConnection(ConfigurationSettings.AppSettings( "strCon"))
oCon.Open()

oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "set_order_attachments"
oCmd.Parameters.Add("@.cart_id", OleDbType.VarChar, 15, cart_id)
oCmd.Parameters.Add("@.attachment", OleDbType.VarChar, 255,
strFilenamesCollection)
oCmd.Connection = oCon
Try
oCon.Open()
oCmd.ExecuteNonQuery()
...
And I have fololowing setting in the Web.config file under the
<configuration>
<appSettings>
<add key="strCon"
value="Provider=SQLOLEDB.1;User ID=sa;Password=;Initial
Catalog=Northwind;Server=(local);" />
</appSettings>
Also tried to use "Data Source=.." and enter my local machine name
there.
None of them work , came back the same "Non DB connection"

C Chang|||There was a duplicated "oCon.Open()" in my codes, but removal it does
not help. However, I tried to use the OracleCommand with the SQL query
script directly and IT WORKS!. Does anyone know why it does not work
with the Oracle procedure. I hate to change all my procedure calls to
in-line scripts. Besides if there is a complicated procedure then the
query becomes a problem.|||(chi-soon_x_chang@.raytheon.com) writes:
> There was a duplicated "oCon.Open()" in my codes, but removal it does
> not help. However, I tried to use the OracleCommand with the SQL query
> script directly and IT WORKS!. Does anyone know why it does not work
> with the Oracle procedure. I hate to change all my procedure calls to
> in-line scripts. Besides if there is a complicated procedure then the
> query becomes a problem.

I'm getting confused, are you connecting to SQL Server or to Oracle?

To go back to your original code, the error message seems like it
would from your own code. That is, I cannot really recall any error
that says "Cannot connect to database". It would help, if you could
get hold of the actual message from the client library. To that end,
it would help if you posted more parts of the code, and also if you
found out on exactly which statement, things go wrong.

Stupid check: you have SQL Server running, haven't you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

In addition to Erlands comments, you may want to move the try block earlier
to catch the connection error. Also check out
http://www.connectionstrings.com/ for the connection string, I think you
should be using Data Source instead of Server. Try using the IP address
instead of the name if necessary.

John

<chi-soon_x_chang@.raytheon.com> wrote in message
news:1103838243.185277.162240@.z14g2000cwz.googlegr oups.com...
> There was a duplicated "oCon.Open()" in my codes, but removal it does
> not help. However, I tried to use the OracleCommand with the SQL query
> script directly and IT WORKS!. Does anyone know why it does not work
> with the Oracle procedure. I hate to change all my procedure calls to
> in-line scripts. Besides if there is a complicated procedure then the
> query becomes a problem.

Function required to convert Packed Decimal in binary(6) to int (or float)

I have been given some data from a Mainframe (AS400?) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value.

The field contains values such as the following:-

0x20202020200C

0x202020022025

0x20202020DFFA

I don't know how to interpret these. Has anyone got a function that can do this for me?

I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help?

Thanks!each of these is 6 bytes wide, which is too big for int (int is 32bit = 4bytes). you can fit them in bigint though.

to convert to bigint just use convert, eg: convert(bigint, 0x20202020200C)

they are already binary so you don't need to do anything special to convert to binary(6).|||Sorry, but you don't understand. They are encoded as "Packed Decimal" (or Binary-Coded Decimal). In that system 0x123456 translates to the integer (yes, integer!) 123456, although the system is a little more complicated than that, because it has special ways of representing negative numbers and floating-point values).