Need files from Companion CD of SQL Server 2000 High Availability

Dear All,
Last time I bought ebook entitle "Microsoft SQL Server 2000 High
Availability".
I need the files below :
- LRQ.zip
-Sysperfinfo_Examples.sql
-Database_Capacity_ and_Disk_Capacity_Monitor.zip
-Defrag.zip
Please some one could send me the files.
Thanks
Robert LieRobert Lie wrote:
> Dear All,
> Last time I bought ebook entitle "Microsoft SQL Server 2000 High
> Availability".
> I need the files below :
> - LRQ.zip
> -Sysperfinfo_Examples.sql
> -Database_Capacity_ and_Disk_Capacity_Monitor.zip
> -Defrag.zip
> Please some one could send me the files.
> Thanks
> Robert Lie
Have you checked the author's or publishers web site?
--
David Gugick
Imceda Software
www.imceda.com

Need Bulk Insert help

Hi all,
I am not sure I post this in the correct forum or not, if not please reguide me to the correct forum. I have data files created from streamwriter using tab for field terminator and line break from streamwriter. Then I try to use Bulk Insert to load the data file into a table with format file using bcp command. Then I received the following error:

Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (codeNum).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

the bulk insert query is as follows:

BULK insert revrpt_staging_2.dbo.outgoing
from '<data file path>\<filename>.data'
WITH (
FIELDTERMINATOR = '\t',
FIRSTROW = 1,
FORMATFILE='<format file path>\format1.fmt',
ROWTERMINATOR = '\r\n',
KEEPIDENTITY,
KEEPNULLS
);

using format file as shown below:
9.0
11
1 SQLINT 1 4 "\t" 2 codeNum ""
2 SQLINT 1 4 "\t" 3 code2Num ""
3 SQLINT 1 4 "\t" 4 code3Num ""
4 SQLINT 1 4 "\t" 5 code4Num ""
5 SQLNCHAR 2 30 "\t" 6 messageId SQL_Latin1_General_CP1_CI_AS
6 SQLNCHAR 2 24 "\t" 7 phone SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 8 0 "\t" 8 message SQL_Latin1_General_CP1_CI_AS
8 SQLDATETIME 1 8 "\t" 9 recDateTime ""
9 SQLDECIMAL 1 19 "\t" 10 chargeAmt ""
10 SQLNCHAR 2 4 "\t" 11 eStatus SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 60 "\r\n" 12 dStatus SQL_Latin1_General_CP1_CI_AS

below is 1 row of the data:
1000 4 12345 0 8EDBDEBF 10111111111 Free msg. Call us at xxxxxxxx, Mon - Fri, 10am-5pm 04/25/2006 17:04:45 0 0 1
Anyone have any idea what is wrong? and how should I go about fixing it? Please help. Thanks in advance.
Daren

I believe you should set prefix length field to 0, as you don't have a prefix:

1 SQLINT 0 4 "\t" 2 codeNum ""

BTW, Books Online "Using Format Files" article is a good read on this matter.

|||Hi Yaroslave,
Sorry to bother you, but could you redirect me to the exact link that explains the meaning of the format files, like explain 1 represents the column number declared, SQLINT represents the type of the column, 0 represent the prefix, etc. for the following example:

1 SQLINT 0 4 "\t" 2 codeNum ""

And one more thing, I changed the prefix to 0 for all the rest and rerun the command, I got an error on the 4th one, which is code4Num the data for it is 12345. Any idea what is wrong with it?
Daren
|||Hi Yaroslave,
the exact error for it is "Bulk load data conversion error (overflow)".
Daren
|||Hi Yaroslave,
sorry to bother you, but it still cannot insert into the table, the following errors pop up, I have no idea what to do with it. The error is as follows:
Msg 4866, Level 16, State 7, Line 1

The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

please help. Thanks in advance.
Daren
|||

It does seem like the bulk insert is having objections about the separators in the file. Are you sure that you have tabs in there?

fwiw, I've always used another strategy regarding formatfiles. (though only with bcp, but I believe that it'd work the same with bulk insert)

The idea is that no matter the datatypes in the destination table, the source is just a plain ascii file, so specify all columns in the format file as SQLCHAR, all prefixes as zero, and the length as the actual charachter length, not the bytelength for the datatype. (ie a datetime becomes 26 instead of 8 etc)

An easy method to create the formatfile, is to use bcp without specifying the -c and -t parameters. You'll then be prompted for the format on each column, 4 prompts on each.

For the first, if the suggestion is anything else than [char], type in 'char' at the prompt, if it is [char], press enter.
For the second, always type 0 (zero)
For the third, press <enter>, always accept the suggested length
For the fourth, type your delimiter. (ie /t)

Repeat for all columns in the table until the last one, where you enter the rowdelimiter at the last (4th) prompt.

Save the file when prompted.

When this work is done, you have a formatfile that you can use, should there be any typos in it (could happen =;o) it's easy to just open it and edit where necessary.

I've used this method since 6.x days, and it has never failed. (ie all CHAR types, all prefixes as zero, suggested lenght, and the appropriate delimeter)

Hope it helps some.

=;o)
/Kenneth

|||Hi Kenneth,
I tried using the method you said, I use the following command:

bcp <tablename> format test.fmt -T

first prompt: char
second prompt: 0
third prompt: <press enter>
fourth prompt: \t for all except last which is \r\n

but the following error came out:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Format file could not be opened. Invalid n
ame specified or access denied.
what do I do about it? Thanks in advance.
Daren
|||Sorry Kenneth, I fixed my previous post with the following command:

bcp <tablename> format nul -T -f test.fmt

I try to bulk insert the data file into the table using this format file, the following error came up for a nvarchar column:

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (<column name>).

Any idea on this? Thanks again for helping me out.
Daren
|||Sorry Kenneth,
My mistake again, I use unicode file instead of ascii that bring up the error I posted previously. I change to use ascii file then it works! Thanks again Kenneth, you saved me a lot of time wondering around.
Daren
|||

Glad it worked out for you. =:o)

/Kenneth

Need basic.

Hi All,

Can this be done and if so can you give a bullet list of the steps need to accomplish this.

I need to load a bunch of files into a stagging table. Need to loop through the files and load them.

Thanks,

Michael

Essentially you're looking at is the following:

1) A for each container using the for each file enumerator.

2) Within that, you'll need to assign the filepath to a variable

3) Within the container, create a dataflow task with a flatfile source and a sql server destination, mapping the columns.

4) dynamically change the connection string of the flatfile source (properties/expression)

Hope this helps

|||

Thanks Rick,

That is a good start, I will see how far I can get.

Michael

need assistance with project

I have a project that is going to use ADP as a front end and SQL server as
the database. Now I need to import flat text files to a temptable in SQL. I
been reading that I should use DTS packages but unfor, from what I have
heard, DTS can only be triggered if scheduled. I am creating a form where th
e
user will have to trigger it. Wonder if there are any other suggestions out
there. Right now, what I created is a macro in ADP that will call a batch
file on the drive that will open up an MDB database and import the files tha
t
way onto sql. Few problems I have right now
1) I have a form where the user clicks on "Locate files" and a subform
opens. It shows all the files in that particular directory. Right next to
each files there is an upload check box. If it check box is ckecked, then
files will be uploaded. There are 2 drop downs in the subform where user wil
l
have to fill in. What I need is if
the check box has been checked but either or both drop downs hasn't been
filled that an error message should pop up before procedure can execute. I a
m
tryin to have to msg box be like a list of all the files that is missing a
drop down. My message box right now only tells one file name but not the
others, if applicable.
for instance, if there are 7 files tha are to be uploaded and 2 of them i
didn't have any drop downs for, i have a message to say "file name text 1 an
d
text 8 are missing..."
2) everytime I open up that database, I get SQL server login error
Connection Failed
SQL state '28000'
Login failed for user(null). Reason, not associated with trusted SQL server
connection.
If i click on "ok" connection pops up and I have to manually enter the
infor. Can I somehow add this in a vb code so user wont' have to keep
manually typing info.
3) Currently, after the import, the MDB kills itself and you are back in the
ADP. Wonder if before the MDB kills itself, opens up an form in ADP and then
kills. THe form is a summary form that shows all the data that was just
imported.(if any other way, suggestions are welcomed).
please help"Justin" <Justin@.discussions.microsoft.com> wrote in message
news:F4E455A7-49AC-4E29-8178-9FE43071C905@.microsoft.com...
>I have a project that is going to use ADP as a front end and SQL server as
> the database. Now I need to import flat text files to a temptable in SQL.
> I
> been reading that I should use DTS packages but unfor, from what I have
> heard, DTS can only be triggered if scheduled. I am creating a form where
> the
> user will have to trigger it. Wonder if there are any other suggestions
> out
> there. Right now, what I created is a macro in ADP that will call a batch
> file on the drive that will open up an MDB database and import the files
> that
> way onto sql. Few problems I have right now
> 1) I have a form where the user clicks on "Locate files" and a subform
> opens. It shows all the files in that particular directory. Right next to
> each files there is an upload check box. If it check box is ckecked, then
> files will be uploaded. There are 2 drop downs in the subform where user
> will
> have to fill in. What I need is if
> the check box has been checked but either or both drop downs hasn't been
> filled that an error message should pop up before procedure can execute. I
> am
> tryin to have to msg box be like a list of all the files that is missing a
> drop down. My message box right now only tells one file name but not the
> others, if applicable.
> for instance, if there are 7 files tha are to be uploaded and 2 of them i
> didn't have any drop downs for, i have a message to say "file name text 1
> and
> text 8 are missing..."
> 2) everytime I open up that database, I get SQL server login error
> Connection Failed
> SQL state '28000'
> Login failed for user(null). Reason, not associated with trusted SQL
> server
> connection.
> If i click on "ok" connection pops up and I have to manually enter the
> infor. Can I somehow add this in a vb code so user wont' have to keep
> manually typing info.
> 3) Currently, after the import, the MDB kills itself and you are back in
> the
> ADP. Wonder if before the MDB kills itself, opens up an form in ADP and
> then
> kills. THe form is a summary form that shows all the data that was just
> imported.(if any other way, suggestions are welcomed).
> please help
There are a number of options for executing DTS packages. They don't have to
be scheduled and they can be run from your code or using the DTSRUN.EXE
executable. See Books Online for details of DTSRUN. See the following link
for other options:
http://www.sqldts.com/default.aspx?104
Depending on the format of your source file another possibility could be to
use BCP or BULK INSERT to load the data. Again, see BOL for details.
For the rest of your questions you might get more help in an Access group.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Need advice on managing the size of Tran logs and log files

We are trying to manage our transaction log files a little bit
differently as we found out in many cases our tran log files have grown
way too big. The db's are set to full recovery mode and we do take
tran log backups daily at night but that is not sufficient and because
we have the tran log to grow with no limit it is getting really big.
So what I am planning to do is set a maximum size for the tran log and
then also set up an alert to back up the log when it is 70% full.
However the one thing that I am not clear about is how SQL Server
handles the log, for example let's say if I set the max size of tran
log to 200MB and let's say that there is a transaction or a process
that rebuilds indexes that would take more than 200MB to complete the
entire taks would SQL Server give me an error or will it kick off the
alert and do a log back up and continue with the process?
In that scenario will I have to set the max size of the tran log larger
than the largest transaction size that my application may run?
Any help or advice in this regard will be greatly appreciated.
Thanksshub wrote:
> We are trying to manage our transaction log files a little bit
> differently as we found out in many cases our tran log files have grown
> way too big. The db's are set to full recovery mode and we do take
> tran log backups daily at night but that is not sufficient and because
> we have the tran log to grow with no limit it is getting really big.
> So what I am planning to do is set a maximum size for the tran log and
> then also set up an alert to back up the log when it is 70% full.
> However the one thing that I am not clear about is how SQL Server
> handles the log, for example let's say if I set the max size of tran
> log to 200MB and let's say that there is a transaction or a process
> that rebuilds indexes that would take more than 200MB to complete the
> entire taks would SQL Server give me an error or will it kick off the
> alert and do a log back up and continue with the process?
> In that scenario will I have to set the max size of the tran log larger
> than the largest transaction size that my application may run?
> Any help or advice in this regard will be greatly appreciated.
> Thanks
>
Have a look at my backup script:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
It's designed to do transaction log backups at five minute intervals, or
when the log reaches 70% full. You could modify it to do longer
intervals, or just use it as-is.
To answer your question, the alert will fire, and a backup will run, but
that reindexing activity will likely be inside an uncommitted
transaction, and the backup won't be able to flush it out of the log.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for sharing your code, I will take a look at it and see how I
could implement it.
I just wanted to clarify your response, since the backup won't be able
to flush out the log because of uncomitted transaction will I possibly
get an error of transaction being full unless I set the max size
greater than the size what the rebuild/or any other process would take.
What is the best way to handle such operations without setting the max
size of tran log file to a large number?
I just did few tests and it appears after I do rebuilding of indexes
it seems the transaction log file grows approximately to the size of
the data file, so does that mean that I would need to set the max size
for tran log to be greater than that?
Thanks
Tracy McKibben wrote:
> shub wrote:
> > We are trying to manage our transaction log files a little bit
> > differently as we found out in many cases our tran log files have grown
> > way too big. The db's are set to full recovery mode and we do take
> > tran log backups daily at night but that is not sufficient and because
> > we have the tran log to grow with no limit it is getting really big.
> >
> > So what I am planning to do is set a maximum size for the tran log and
> > then also set up an alert to back up the log when it is 70% full.
> > However the one thing that I am not clear about is how SQL Server
> > handles the log, for example let's say if I set the max size of tran
> > log to 200MB and let's say that there is a transaction or a process
> > that rebuilds indexes that would take more than 200MB to complete the
> > entire taks would SQL Server give me an error or will it kick off the
> > alert and do a log back up and continue with the process?
> >
> > In that scenario will I have to set the max size of the tran log larger
> > than the largest transaction size that my application may run?
> >
> > Any help or advice in this regard will be greatly appreciated.
> > Thanks
> >
> Have a look at my backup script:
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
> It's designed to do transaction log backups at five minute intervals, or
> when the log reaches 70% full. You could modify it to do longer
> intervals, or just use it as-is.
> To answer your question, the alert will fire, and a backup will run, but
> that reindexing activity will likely be inside an uncommitted
> transaction, and the backup won't be able to flush it out of the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||shub wrote:
> Thanks for sharing your code, I will take a look at it and see how I
> could implement it.
> I just wanted to clarify your response, since the backup won't be able
> to flush out the log because of uncomitted transaction will I possibly
> get an error of transaction being full unless I set the max size
> greater than the size what the rebuild/or any other process would take.
> What is the best way to handle such operations without setting the max
> size of tran log file to a large number?
>
Reindexing generates a lot of transactional activity, and those
transactions tend to be large. Bottom line is, if the transaction log
isn't large enough to hold a transaction, you will receive an error, and
the process generating the transaction will fail.
That said, you have a couple of options:
1. Don't rebuild every index, only rebuild those that are fragmented.
I have another script on my site that will automate this process for
you. Consider running this script once per week, with a fragmentation
threshold of 20-30%.
2. If you're not doing log shipping, you might consider putting the
database into Simple mode before reindexing. This won't reduce the size
of the log, but it will help flush out the committed transactions. If
you're doing frequent t-log backups (5 minute intervals), Simple mode
won't really gain you anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Need advice on managing the size of Tran logs and log files

We are trying to manage our transaction log files a little bit
differently as we found out in many cases our tran log files have grown
way too big. The db's are set to full recovery mode and we do take
tran log backups daily at night but that is not sufficient and because
we have the tran log to grow with no limit it is getting really big.
So what I am planning to do is set a maximum size for the tran log and
then also set up an alert to back up the log when it is 70% full.
However the one thing that I am not clear about is how SQL Server
handles the log, for example let's say if I set the max size of tran
log to 200MB and let's say that there is a transaction or a process
that rebuilds indexes that would take more than 200MB to complete the
entire taks would SQL Server give me an error or will it kick off the
alert and do a log back up and continue with the process?
In that scenario will I have to set the max size of the tran log larger
than the largest transaction size that my application may run?
Any help or advice in this regard will be greatly appreciated.
Thanksshub wrote:
> We are trying to manage our transaction log files a little bit
> differently as we found out in many cases our tran log files have grown
> way too big. The db's are set to full recovery mode and we do take
> tran log backups daily at night but that is not sufficient and because
> we have the tran log to grow with no limit it is getting really big.
> So what I am planning to do is set a maximum size for the tran log and
> then also set up an alert to back up the log when it is 70% full.
> However the one thing that I am not clear about is how SQL Server
> handles the log, for example let's say if I set the max size of tran
> log to 200MB and let's say that there is a transaction or a process
> that rebuilds indexes that would take more than 200MB to complete the
> entire taks would SQL Server give me an error or will it kick off the
> alert and do a log back up and continue with the process?
> In that scenario will I have to set the max size of the tran log larger
> than the largest transaction size that my application may run?
> Any help or advice in this regard will be greatly appreciated.
> Thanks
>
Have a look at my backup script:
http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||Thanks for sharing your code, I will take a look at it and see how I
could implement it.
I just wanted to clarify your response, since the backup won't be able
to flush out the log because of uncomitted transaction will I possibly
get an error of transaction being full unless I set the max size
greater than the size what the rebuild/or any other process would take.
What is the best way to handle such operations without setting the max
size of tran log file to a large number?
I just did few tests and it appears after I do rebuilding of indexes
it seems the transaction log file grows approximately to the size of
the data file, so does that mean that I would need to set the max size
for tran log to be greater than that?
Thanks
Tracy McKibben wrote:
> shub wrote:
> Have a look at my backup script:
> http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||shub wrote:
> Thanks for sharing your code, I will take a look at it and see how I
> could implement it.
> I just wanted to clarify your response, since the backup won't be able
> to flush out the log because of uncomitted transaction will I possibly
> get an error of transaction being full unless I set the max size
> greater than the size what the rebuild/or any other process would take.
> What is the best way to handle such operations without setting the max
> size of tran log file to a large number?
>
Reindexing generates a lot of transactional activity, and those
transactions tend to be large. Bottom line is, if the transaction log
isn't large enough to hold a transaction, you will receive an error, and
the process generating the transaction will fail.
That said, you have a couple of options:
1. Don't rebuild every index, only rebuild those that are fragmented.
I have another script on my site that will automate this process for
you. Consider running this script once per week, with a fragmentation
threshold of 20-30%.
2. If you're not doing log shipping, you might consider putting the
database into Simple mode before reindexing. This won't reduce the size
of the log, but it will help flush out the committed transactions. If
you're doing frequent t-log backups (5 minute intervals), Simple mode
won't really gain you anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Need advice on best way to make dbase record updates

Approximately once a month, a client of ours sends us a bunch of
comma-delimited text files which I have to clean up and then import
into their MS SQL database. All last week, I was using a Cold Fusion
script to upload the cleaned up files and then import the records they
contained into the database, though obviously, the process took
friggin' forever, and could have been done 500x quicker had I done it
directly on the server. My SQL knowledge is somewhat limited, however,
so I had no choice but to stick to what I know, which is Cold Fusion
programming.

In the process of cleaning up some of these comma-delimited text files,
I inadvertently messed up some of the 10-digit zip codes, by applying
the wrong Excel formula to the ZIP columns. These records were imported
into the database with obviously incorrect zip codes (ie: single
digit). So now, I have to find the best and quickest way possible to
compare these records in the database (that have the single digit zip
codes) with the unmodified data, and to update the zip codes with the
correct data.

I've had no luck setting up a TEXT file as an ODBC datasource, -- so
I've ruled that out completely. I've also managed to import the
unmodified data into an Access database, and to set it up as a Cold
Fusion datasource. But it seems this 2nd road I've been traveling down
is not the ideal approach either.

My question is, -- assuming that I'll be able to import the records
from the Access database into their own table on the SQL server, -- how
should I go about the process of updating these records that have the
incorrect zip codes?

Here is the specific logic I would need to employ:

* Here is a list of records, each of which contains an incorrect
1-digit zip code (Database A / Table A)

* Here is a much longer list of records (which contains all of the
records from Database A / Table A + thousands more), each of which
contains a correct 5-digit zip code (Database B / Table B)

* Compare both lists of records and run the following query/update:

When a record in Database A / Table A has matching "name", "address1",
and "address2" values as a record in Database B / Table B -- update the
record in Database B / Table B with the zip code from the matching
record in Database A / Table A.

Would anyone care to write a sample query for me that I could run
directly on the SQL server, or at least give me some pointers?

The specific field names are as follows:
name,address1,address2,city,state,zip

Thanks in advance!
- yvanGive this a shot:
Here is a list of records, each of which contains an incorrect
1-digit zip code (Database A / Table A)

select name, address1, address2, zipcode
from dbA.tblA where len(zipcode) = 1 -- this will retrieve all the zip
codes records of len = 1
select count(*) from dbA.tblA where len(zipcode) = 1 -- this will give
the counts

* Here is a much longer list of records (which contains all of the
records from Database A / Table A + thousands more), each of which
contains a correct 5-digit zip code (Database B / Table B)

select A.zipcodeA, A.record, B.Record
from dbA.tblA A
INNER JOIN dbB.tblB ON
A.record = B.Record

IF you have more record of tblA in tblB then a cross join will work but
I am not clear what is your specification.
* Compare both lists of records and run the following query/update:

When a record in Database A / Table A has matching "name", "address1",
and "address2" values as a record in Database B / Table B -- update the

record in Database B / Table B with the zip code from the matching
record in Database A / Table A.

UPDATE dbB.tblB
SET zipcode = A.Zipcode
from dbB.tblB B
INNER JOIN dbA.tblA A ON
A.name = B.name and
A.address1 = B.address1 and
A.address2 = B.address2

If you will have more question let me know in my gmail account that is
sp.karma_no_spam@.gmail.com

thanks
sri

Would anyone care to write a sample query for me that I could run
directly on the SQL server, or at least give me some pointers?

The specific field names are as follows:
name,address1,address2,city,state,zip|||my email id is before the _no_s part and gmail.com

thanks
Sri|||<yvan@.ideasdesign.com> wrote in message
news:1107977508.903032.29260@.c13g2000cwb.googlegro ups.com...
<<>>
> My question is, -- assuming that I'll be able to import the records
> from the Access database into their own table on the SQL server, -- how
> should I go about the process of updating these records that have the
> incorrect zip codes?

If you are not confident with SQL then I'd suggest doing it from access.
This could be slower, but you can check what'll happen easier...

Once your sql dsn is set up, attach the table in sql.
Select new query.
Choose one of your tables.
From the query drop down menu, select update.
Add in your other table.
Create the relationships between the two tables by clicking on name in one
and dragging to name in the other.
A line will appear.
Repeat for rest.
Add your zip code and put something like [tablename].[zip] in the update
cell.
IF it matters that you exclude the ones no wrong you can detect this using
len([tablename].[zip])
as an additional column and criteria > 1

Up the top of the query window you have a drop down combo with set square
and such ( indicating design mode ).
If you choose the table thing off there then it'll show you what it'll
update.
You could temporarily add fields by double clicking.
That way you can be confident.

As I think you will see, this approach enables you to write stuff with
pretty much zero access or SQL knowledge.

Putting something together in access added future loads in with less work
seems quite possible.
Certainly, you could verify your data before loading it.

Of course if the client was to send you an access table with their data in
each time then you could create this and make the table ensure some data
integrity.
Maybe that's not an option though.

> When a record in Database A / Table A has matching "name", "address1",
> and "address2" values as a record in Database B / Table B -- update the
> record in Database B / Table B with the zip code from the matching
> record in Database A / Table A.
> Would anyone care to write a sample query for me that I could run
> directly on the SQL server, or at least give me some pointers?
> The specific field names are as follows:
> name,address1,address2,city,state,zip
> Thanks in advance!
> - yvan|||Okay, .. I've gotten a bit further on the creation of my SQL update
statement. However, I am having difficulty assigning the table name
aliases correctly.

This is what I have so far (A = messed up zips / B = correct 5-digit
zips):

UPDATE mydbase.dbo.mytable1 A
SET A.zip = LEFT(B.zip,5)
INNER JOIN mydbase.dbo.mytable2 B
ON A.name = B.name
AND A.address1 = B.address1
AND A.address2 = B.address2
WHERE A.entered = '1/21/2005'
AND A.magID = 1
AND A.scid = 0
AND (A.zip = '00000'
OR A.zip = '00001'
OR A.zip = '00002'
OR A.zip = '00003'
OR A.zip = '00004'
OR A.zip = '00005'
OR A.zip = '00006'
OR A.zip = '00007'
OR A.zip = '00008'
OR A.zip = '00009')

When I attempt to run this update statement AS IS, I get the following
error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'A'.

Anyone know what the proper way to assign table name aliases is in this
scenario?

Thanks,
- yvan|||(yvan@.ideasdesign.com) writes:
> UPDATE mydbase.dbo.mytable1 A
> SET A.zip = LEFT(B.zip,5)
> INNER JOIN mydbase.dbo.mytable2 B
> ON A.name = B.name
> AND A.address1 = B.address1
> AND A.address2 = B.address2
> WHERE A.entered = '1/21/2005'
> AND A.magID = 1
> AND A.scid = 0
> AND (A.zip = '00000'
> OR A.zip = '00001'
> OR A.zip = '00002'
> OR A.zip = '00003'
> OR A.zip = '00004'
> OR A.zip = '00005'
> OR A.zip = '00006'
> OR A.zip = '00007'
> OR A.zip = '00008'
> OR A.zip = '00009')
>
> When I attempt to run this update statement AS IS, I get the following
> error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'A'.
>
> Anyone know what the proper way to assign table name aliases is in this
> scenario?

Use a FROM clause. That's not a standard SQL thing, but a very very
handy proprietart extension to SQL.

The syntax for UPDATE is Books Online. While it may look bewildering,
it's probably more effective to read it that post questions about
syntax and wait for answers.

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

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

Need a function

I'm looking for a string function that is similar to the INSTR
function in VB. I haven't seen anything in the help files that I can
use. Does anyone have any suggestions?

Here's what I'm trying to do:

There is a field in a table that will look something like this -
"XXXXXX - YY".
I want to separate it on the dash and get two strings out of it -
"XXXXXX" and "YY". I'm trying to keep it all in a stored procedure
and avoid a vb script or exe.

I'm envisioning something like this:

declare @.CDT datetime

select @.CDT = createdatetime from imOrderHdr
where VendorCode = 'SYG' and createdatetime is not null
and status in (1,2,3)

select d.VendorStockNumber, substring(i.ItemDescription, 1,
instr(iItemDescription, '-') - 1),
substring(i.ItemDescription, instr(iItemDescription, '-') + 1),
d.QtyOrdered, d.PurchasePrice, (d.QtyOrdered * d.PurchasePrice) as
Extension
from imOrderDetail d
join imItem i on i.ItemCode = d.ItemCode
where d.CreateDateTime = @.CDT

I'd write my own function, but the computers this will be run on have
SQL 7.

Any suggestions will be appreciated.

Thanks!
JenniferHave a look at CHARINDEX and PATINDEX to get the positions that you need for
subsequest SUBSTRING calls to parse out your data.

"Jennifer" <jennifer1970@.hotmail.com> wrote in message
news:3358f49d.0308150815.2a818c27@.posting.google.c om...
> I'm looking for a string function that is similar to the INSTR
> function in VB. I haven't seen anything in the help files that I can
> use. Does anyone have any suggestions?
> Here's what I'm trying to do:
> There is a field in a table that will look something like this -
> "XXXXXX - YY".
> I want to separate it on the dash and get two strings out of it -
> "XXXXXX" and "YY". I'm trying to keep it all in a stored procedure
> and avoid a vb script or exe.
> I'm envisioning something like this:
> declare @.CDT datetime
> select @.CDT = createdatetime from imOrderHdr
> where VendorCode = 'SYG' and createdatetime is not null
> and status in (1,2,3)
> select d.VendorStockNumber, substring(i.ItemDescription, 1,
> instr(iItemDescription, '-') - 1),
> substring(i.ItemDescription, instr(iItemDescription, '-') + 1),
> d.QtyOrdered, d.PurchasePrice, (d.QtyOrdered * d.PurchasePrice) as
> Extension
> from imOrderDetail d
> join imItem i on i.ItemCode = d.ItemCode
> where d.CreateDateTime = @.CDT
> I'd write my own function, but the computers this will be run on have
> SQL 7.
> Any suggestions will be appreciated.
> Thanks!
> Jennifer

NDFs into MDF

Hi,
One simple question. I have a DB with MDF+NDFs files and I want to restore/c
reate
its backup to a DB with only one MDF file.
How?
Thanks,
PS: My INET does not allow me any domains but MS. So, if your solution point
s to
links outside MS, please copy the content in the message.A restored database is exactly like the original. The number and size of
the files will be the same after the restore.
You can consolidate the data files after the restore by executing DBCC
SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
from the secondary data file to the primary data file. You can remove the
secondary file afterward with ALTER DATABASE REMOVE FILE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...

quote:

---

> Hi,
> One simple question. I have a DB with MDF+NDFs files and I want to

---

restore/create

quote:

---

> its backup to a DB with only one MDF file.
> How?
> Thanks,
> PS: My INET does not allow me any domains but MS. So, if your solution

---

points to

quote:

---

> links outside MS, please copy the content in the message.

---

|||Dan's answer assumes he's got multiple files in the same filegroup. if he's
got multiple filegroups and objects in those filegroups then i think the onl
y
option is to go into each object and move it to the primary filegroup. once
the secondary filegroup is emptied, then the file(s) for that filegroup can
be
deleted.
Dan Guzman wrote:
[QUOTE]
> A restored database is exactly like the original. The number and size of
> the files will be the same after the restore.
> You can consolidate the data files after the restore by executing DBCC
> SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
> from the secondary data file to the primary data file. You can remove the
> secondary file afterward with ALTER DATABASE REMOVE FILE.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
> news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
> restore/create
> points to

NDFs into MDF

Hi,
One simple question. I have a DB with MDF+NDFs files and I want to restore/create
its backup to a DB with only one MDF file.
How?
Thanks,
PS: My INET does not allow me any domains but MS. So, if your solution points to
links outside MS, please copy the content in the message.A restored database is exactly like the original. The number and size of
the files will be the same after the restore.
You can consolidate the data files after the restore by executing DBCC
SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
from the secondary data file to the primary data file. You can remove the
secondary file afterward with ALTER DATABASE REMOVE FILE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
> Hi,
> One simple question. I have a DB with MDF+NDFs files and I want to
restore/create
> its backup to a DB with only one MDF file.
> How?
> Thanks,
> PS: My INET does not allow me any domains but MS. So, if your solution
points to
> links outside MS, please copy the content in the message.|||Dan's answer assumes he's got multiple files in the same filegroup. if he's
got multiple filegroups and objects in those filegroups then i think the only
option is to go into each object and move it to the primary filegroup. once
the secondary filegroup is emptied, then the file(s) for that filegroup can be
deleted.
Dan Guzman wrote:
> A restored database is exactly like the original. The number and size of
> the files will be the same after the restore.
> You can consolidate the data files after the restore by executing DBCC
> SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
> from the secondary data file to the primary data file. You can remove the
> secondary file afterward with ALTER DATABASE REMOVE FILE.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
> news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
> > Hi,
> > One simple question. I have a DB with MDF+NDFs files and I want to
> restore/create
> > its backup to a DB with only one MDF file.
> > How?
> > Thanks,
> > PS: My INET does not allow me any domains but MS. So, if your solution
> points to
> > links outside MS, please copy the content in the message.