Answer Questions

• Ben.Siemon Composite primary key or not?

  This is really not a T_SQL question but there's no good category for it so I thought I'd just put it here.   I have a table that will contain a large amoutn of data in one field, and every piece of this data is uniquely identitied by six other attributes, that is, six other fields (e.g. user_ID, type_ID, year, country, state, item_ID).  I can either make these six fields into a composite primary key, or add an additonal field (say an identity column) as the primary key and add a unique constraint on these six fields.  What are the pros and cons of both designs   The one data field is of nvarchar(2000) type and the table is likely to have 50 million+ rows in a couple of years of real use.   Thi ...Show All

• seco LIKE clause in IF statement

  Can the like clause be used in IF statement or only applicable to SELECT statement e.g. IF (@lADDRESS LIKE '%P.O. BOX') BEGIN PRINT 'IGNORE ADDRESS' END Thank you Yog Yes.  Did you try the example you posted   It should work...   -- Adam Machanic Pro SQL Server 2005, available now http://www..apress.com/book/bookDisplay.html bID=457 --     < yog_23@discussions.microsoft.com > wrote in message news:aca634c4-82de-46fc-a326-926711b349e2@discussions.microsoft.com ... Can the like clause be used in IF statement or only applicable to SELECT statement e.g. IF (@lADDRESS LIKE '%P.O. BOX') BE ...Show All

• RenoMike SQL Everywhere Remote

  Being new to this mobile game, ive created a database locally and now wish to place it onto my server but when I change my connection string from c:\folder\db.sdf to \\Server\folder\db.sdf i get an error that says "value does not fall within the expected range" ive also tried mapping it ie Q:\db.sdf what am I doing wrong I have now fixed this issue, I was using vs05 -> dataset -> connectionstring, to get around this error you need to manually go into the app.config and edit the connection string there. The VS designers don't know to allow |DataDirectory| or unc shares. Express actually specifically blocks UNC shares, although SSEv does support ...Show All

• Jasper22 ODBC Data Source

  I'm trying to import tables from COBOL thru Relativity client. With SQL Server 2000, I used Other (ODBC Data Source) to do this. I cannot find a comparible data source in SQL Server 2005. What do I do Well, great! We use SQL Express. Won't my boss love this! Thanks for the info. Where are you looking to find this data source Mike Thanks, The Import wizard is not supported in SQL Express so this isn't the best place to get information about it. I'm going to move this thread into a forum where the folks who should be able to answer this question hang out. Mike In the Import wizard. There's some very good reasons wh ...Show All

• Johnny Ashcan failed insert because of duplicate rows and still locks held by application

  Hi, we are using SQL Server 2005. We have a problem because of an application which tries to insert a duplicate row in a table with primary key. This insert fails of course but the locks are not released within this transcation. This is not locking up on MS SQL Server 2000. But we can see the lock is still applied there. Can someone help me on this Thanks, Hmmm. What locks are held by the INSERT Is this a stored procedure you are calling Can you post the code you are calling It sounds like you might be in a Serializable isolation level, perhaps, and an explicit transaction is being started but not cleared. Are you using connection pooling Thanks for reply. The first quer ...Show All

• Gregor Suttie Format date in a Stored Procedure

  Dear friends, I have a stored procedure that returns some fiels. One of the fields is a datetime type. The field return in the follow format : 2006-11-13 0:00:00 How can I return only 2006-11-13 How can I use the format function regards!!! ok, but how I return the time value of the system You should not care about formatting the date on the server this is a thing for the presentation layer. HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Or... Select Convert(varchar(10), GetDate(), 120) Lookup the convert function in Books On Line for more formats. Do you mean at the presentation layer That depends on your used coding lan ...Show All

• Wesolek use exists in join

  Hello, Can i somehow use exists() function in JOIN ON clause for example SELECT * FROM Table1 INNER JOIN ON Table1.Col1 = Table2.Col1 AND EXISTS(SELECT * FROM Table2 WHERE Table2.Col2 = Table1.Col2) It doesn't work on my query. So, basically i want to do an inner join so that Table1 gets filtered. Then I make sure that every entry in the filtered Table1 has an existing related entry in Table2. i need to do exists because there could be more than one relation in Table2 for each entry in Table1, but i don't want multiple repetition of entries in the filtered Table1. (Not sure if i am making it clear!) I need distict selection, but i am adding NEWID() at the beginning of my query so distinct doesn't work for the rows. with my current query, i ...Show All

• A.Russell PERSISTED Column In View

  I have a view that calls a User Defined Function. I want to index this calculated column if possible, but trying to do so gives me an error saying that I have to mark the column PERSISTED. I see how it's possible to do this when creating tables with calculated columns, but is it possible to do this from within the view itself Thanks for the reply, Adam. Yes. I do have a clusterred index on the view already. Adding a non-clustered index on the calculated non-key column isn't working, though. It gives a message about the column: "Cannot create index or statistics '[Index Name]' on view '[View Name]' because key column '[Column Name I Want To Index]' is imprecise, computed and not persis ...Show All

• cgatti attached in vb8 vs vb6 incompatability

  I have two programs which access sql server 2005. One was written in vb6 the other in vb8 (aka vb.net 2005). vb6 works well with this connection string: Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Database=MyDbName;Data Source=.\MyServerInstanceName vb8 (in vs2005) generated the following connection string which works well in the vb8 application: Data Source=.\MyServerInstanceName;AttachDbFilename='C:\MyDbPath\MyDbName.mdf';Integrated Security=True Within their own programs each connection string works well. However, if I use one program and then the other (e.g., vb8 program then vb6), I get an error message: Unable to open the physical file "C:\MyDbPath\MyDbName.mdf". Operating system ...Show All

• ahmedilyas Mirroring dedicated traffic

  We are using the following configuration: 2 stand alone Windows 2003 servers 64 bit. Each have two Gig Network cards configured with different IP addresses. Each running multiple instances of SQL Server 64. I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC. I am also trying to avoid Windows authentication. Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option... Would someone please point me in the right direction Thank you. Thank you for all your help. Resolved. ...Show All

• CIAndrew Importing Data from access 2000 in SQL

  Hi All, At the moment i have a piece of software that uses Access to store the data into a number of tables. I am developing a new piece of software that has been built around SQL express and need to upgrade users that are using access. I have managed to write the installation to install etc and also the start of a script to insert all of the tables. I cant work out how to get the data into SQL express using a script. Basically the access db (CentralDb) will be stored a a given location C:\Program Files\CentralDB.mdb The tables within the SQL version are indentical to access but i need a way of pulling out the information stored and putting it into SQL, and as there is no DTS i am becoming stuck on how to automate the proce ...Show All

• Kshama How can I shrink SQL Express database on local machines?

  Hello, I have deployed a vb.net application to a few laptops that holds the SQL Express database as a local datastore. What is the best way to compress or shrink the database and log files I would like a way that this can be done automatically or unattended if possible... Thanks in advance. YOu could create a job (like an at job) on the the machine to execute the procedures for shrinking the database or the logfiles. Another option would be to use some kind of logic within your application code to shrink the database from time to time / at startup. (Look for DBCC SHRINKDATABASE in the BOL) HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Rashar ...Show All

• Keith Chapman connection management

  hi.. i have like 50 ssis packages,, most of all have the samne conexion to my target server.,, the user an pwd i'm using is the same for all of them... but the pwd has been changed because of security rules,., the result: these packages will no longer execute correctly .. is there anyway i can change this conexion for all the packages without doing it manually and if so.. how thanks!!!!! You should have used package configuration from the really beginning. If you didn't and you are planning to go over all the packages again; do that for adding the required package configurations to set the connection string of your connection manager at run time; that way next time a change of this nature happens you will have to updat ...Show All

• Sam Jost why I cant set the matrix's row height with 0

  HI everyone set two matrixs as one matrix I want to hide the second matrix's column header I hidden the textbox, but the Height is exists I want to set the row height with 0cm, but I can't to do this the row height has a min height, 0.07937cm how can i set row height with 0cm I want to hidden the column header. and set the header height with 0 cm total 1 **** **** **** row1 **** **** **** row2 **** **** **** row2row2 **** **** **** row2row2 **** **** **** row2row2 **** **** **** the header total2 **** **** **** ...Show All

• Peter Doss How to move Transactional Distributor to new platform?

  I have to move our dedicated Sql 2000 transactional distributor to a new and faster machine. Great! Can someone give me or point me to a cookbook of the best way to do this This has to be done quickly so I would like to script it and have it ready ahead of time. I am not a replication guru so I am a bit nervous . . . I have looked at the scripts output by EM, but I am sure there are some assumptions . . Thanks I think to move a distributor you have to remove replication altogether. ...Show All