anyone imported text file generated from DB2?

Has anyone import into SQL Server a text file generated by DB2?

the DB2 files has bunch of weird characters in it that look like they are used for formatting...as opposed to actually being data.

Also, SQL can't find a row terminator.

I received a cobol declaration copy book for the data, but I have no idea what to do with the special characters.
Any help would be appreiciated.

There's a sample of the copybook...

* COBOL DECLARATION FOR TABLE @.TNJ00.PL_JUDGM_ATTORNEY *
************************************************** ****************
01 DCLPL-JUDGM-ATTORNEY.
10 PJATY-ATTORNEY-KEY.
15 PJATY-JUDGMENT-ID PIC X(14).
15 PJATY-GROUPING-CDE PIC S9(4) USAGE COMP.
15 PJATY-ROLE-CDE PIC X(1).
88 PJATY-CREDITOR VALUE 'C'.
88 PJATY-DEBTOR VALUE 'D'.
88 PJATY-TRUSTEE VALUE 'T'.
15 PJATY-GROUP-SEQ PIC S9(4) USAGE COMP.
15 PJATY-ENTRY-SEQ PIC S9(4) USAGE COMP.
10 PJATY-NAME-PREFIX PIC X(4).
10 PJATY-LAST-NME PIC X(25).
10 PJATY-FIRST-NME PIC X(12).
10 PJATY-MIDDLE-NME PIC X(12).
10 PJATY-NAME-SUFFIX PIC X(4).
10 PJATY-CITY-NME PIC X(25).
10 PJATY-STATE PIC X(2).
10 PJATY-ZIP-CDE PIC X(9).
10 PJATY-TELEPHONE PIC X(10).
10 PJATY-LST-MNT-DTE PIC S9(9) USAGE COMP.
10 PJATY-REC-LOCK-CDE PIC X(1).
10 PJATY-ALT-NAME.
49 PJATY-ALT-NAME-LEN PIC S9(4) USAGE COMP.
49 PJATY-ALT-NAME-TEXT PIC X(75).
10 PJATY-ADDR-LINE-1.
49 PJATY-ADDR-LINE-1-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-1-TEXT PIC X(50).
10 PJATY-ADDR-LINE-2.
49 PJATY-ADDR-LINE-2-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-2-TEXT PIC X(50).
10 PJATY-ADDR-LINE-3.
49 PJATY-ADDR-LINE-3-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-3-TEXT PIC X(50).
************************************************** ****************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 20 *
************************************************** ****************Tell them to send you another file and unpack the numeric fields, and to make the sign a separate character.

The "USAGE COMP" (usage computational) packs a digit into 4 bits, so that each byte contains two digits. Then COBOL also uses a half byte for the sign unless they specify sign separate and it's location (trailing or leading).

PIC 9(04) would mean numeric values occupying 4 bytes.
PIC 9(04) usage comp would mean numeric values occupying 2 bytes
PIC S9(04) usage comp means numeric values occupying 3 bytes (2 for the numbers and one for the sign with the sign leading the numeric values.


The 01 level is a record level descriptor. The 10 is a field. the 15 are the sub-fields that make up the field if the 10 level does not have a type identifier. All PIC X fields are fixed length. The 88 level describes the allowed values in the 15 field just above them. The 49 level is the same as the 15 level ... it describes the data in the sub-fields that make up the 10 field.

Clear as mud, i'm sure. As for the row terminator, you have to calculate the row length and apply your own. You might be able to import this with DTS, but it would be easier to have them send it to you as all character data, them you import and convert to numeric (all numbers in this example are integers (thank goodness) ... but couls have positive or negative signs).|||Very good...I thought I was the last salt on the planet that knew this stuff...

In either case, not only do you have that, you also have varchar's it looks like...which shouldn't be a problem.

Also, you don't know if any of the columns are nullable.

What you really need is the LOAD Card that is generated by DB2 when they unload the data. THAT will tell you what you have, not the COBOL Copybook.

TYhey should probably unload the data using PARM('SQL') and use SQL DML to creat a nice loadable file|||164 150 141 156 153 040 171 157 165 040 166 145 162 171 040 155 165 143 150

in octal

(http://nickciske.com/tools/octal.php)|||You will get CRLF in the file. I've never needed to generate a row terminator...in any event, the Column names are definetly not the column names in DB2...they are limited to 18 bytes...another reason to get the LOAD CARD

You want them to do this

//UNLOAD JOB (B,X,XXXXX),'UNLOAD',PRTY=13,GROUP=XXXXXXXX,
// NOTIFY=&SYSUID,MSGCLASS=V,TIME=60
//*+JBS BIND XTDDBB4.ONLINE
//UNCAT EXEC PGM=IEXUNCAT,COND=(4,LT)
//SYSIN DD *
XXXXXX.DBB4.SBD000DB.UNLOAD.INDEX.D060606
/*
//UNLOAD EXEC PGM=IKJEFT01,REGION=6M,COND=(4,LT)
//STEPLIB DD DSN=BXXXB4.DB2.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD DUMMY
//SYSREC00 DD SPACE=(CYL,(100,25),RLSE),
// UNIT=DASD,DISP=(,CATLG),LABEL=RETPD=365,
// DSN=XXXXXX.DBB4.SBD000DB.UNLOAD.INDEX.D060606
//SYSPUNCH DD DUMMY
//*SYSPUNCH DD DISP=SHR,
//* DSN=XXXXXX.DBA.DBB4.SBD000DB.CTLCARD(INDEX)
//SYSTSIN DD *
DSN SYSTEM(DBB4)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
LIB('BXXXB4.DB2.RUNLIB.LOAD') PARMS('SQL')
END
/*
//SYSIN DD *
SELECT PJATY_JUDGMENT_ID
, CHAR(PJATY_GROUPING_CDE,8)
, PJATY_ROLE_CDE
, CHAR(PJATY_GROUP_SEQ,8)
, CHAR(PJATY_ENTRY_SEQ,8)
, PJATY_NAME_PREFIX
, PJATY_LAST_NME
, PJATY_FIRST_NME
, PJATY_MIDDLE_NME
, PJATY_NAME_SUFFIX
, PJATY_CITY_NME
, PJATY_STATE
, PJATY_ZIP_CDE
, PJATY_TELEPHONE
, CHAR(PJATY_LST_MNT_DTE,18)
, PJATY_REC_LOCK_CDE
, CHAR(PJATY_ALT_NAME_TEXT,75)
, CHAR(PJATY_ADDR_LINE_1_TEXT,50)
, CHAR(PJATY_ADDR_LINE_2_TEXT,50)
, CHAR(PJATY_ADDR_LINE_3_TEXT,50)
FROM TABLE
/*|||And if you really want to smoke their minds...ask them for a comma delimted file

SELECT ' "'||COALESCE(RTRIM(PJATY_JUDGMENT_ID),'')||'"'
||',"'||COALESCE(RTRIM(CHAR(PJATY_GROUPING_CDE,8)),'')| |'"'
||',"'||COALESCE(RTRIM(PJATY_ROLE_CDE),'')||'"'
||',"'||COALESCE(RTRIM(CHAR(PJATY_GROUP_SEQ,8)),'')||'"'
||',"'||COALESCE(RTRIM(CHAR(PJATY_ENTRY_SEQ,8)),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_NAME_PREFIX),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_LAST_NME),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_FIRST_NME),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_MIDDLE_NME),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_NAME_SUFFIX),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_CITY_NME),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_STATE),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_ZIP_CDE),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_TELEPHONE),'')||'"'
||',"'||COALESCE(RTRIM(CHAR(PJATY_LST_MNT_DTE,18)),'')| |'"'
||',"'||COALESCE(RTRIM(PJATY_REC_LOCK_CDE),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_ALT_NAME_TEXT),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_ADDR_LINE_1_TEXT),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_ADDR_LINE_2_TEXT),'')||'"'
||',"'||COALESCE(RTRIM(PJATY_ADDR_LINE_3_TEXT),'')||'"'
FROM TABLE|||And if you already have a file based on that layout...you can't use it...or at least you can't use it with out A LOT of coding on your end

Anyone ever tried this one?

I have an audio conversion and tagging project I'm working on. I'm working with .wav files which become .mp3. I have been able to automate tagging of the mp3 using an external flat file. But this process requires that I fire off a batch file from a DOS window. I use two programs. One is called Razorlame v1.1.5 and the other is called Tag. There are several other things left for me to figure out.

1. Schedule the batch so that it runs on it's own based on a schedule
2. Somehow get SQL or Access (which ever is better) to talk to the "Tag.exe" program that does the actually conversion but on the mp3 server.
3. Completely automate the process and be able to convert multiple mp3 files based on schedule, batch file and software.

Has anyone ever done anything like this and if so which database program did you use and how did you get it to work? Details please as I am still learning SQL Server 2005 and am not a .Net guru either.

Any help is much appreciated. I may be reached at: erico4@.u.washington.eduI wouldn′t automate that from SQL Server. Write an application which can be scheduled and uploads the data to SQL Server. SQL Server is for storing data not external processing.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Jens - thanks but what exactly am I storing in SQL? Am I storing the tags themselves?

Example:
Artist: Company Name
Title: Test Title
Album: News & Information
Year: Copyright 2006 Company Name
Genre: news
Comment: John Smith etc etc

This was suggested by a coworker via email. But I'm not clear on what they mean. How would you interpret this?

If you end up using

Access .... you might be able to get the SQL script out of the Access wizard

http://office.microsoft.com/en-us/access/HP051876301033.aspx?pid=CH063648341033

I'm trying to piece together how all this flows from step one and so on. I need help thinking it through the process so I can have a better understanding of that part of it.

|||

Thats a bit hard to tell for us, as we don′t know the specifics of the TAG program and the process itself. A possible solution for this could be:

1. Grab the Wav file from the folder
2. Extract the Tag information from the Wav file and store it in a flat file
3. Call the MP3 converter
4 Call the tag application using the flat file.
5. Delete the flat file.

This can be all done without any interaction with a SQL Server unless you want to persist the data on the server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You would use Access or SQL Server to store some data related to the process and/or the files that are being processed. It sounds like tag.exe is applying the contents of the data file (Artist, Title, Album, Year, etc.) to the metadata of the mp3 file. If that's correct, you can replace everything that deals with these data files (create the file, parse the file, etc.) with a database and a corresponding query. At it's simplest, you could simply map the data file to a table in the database. If you wanted to start doing more interesting processing (say, all Titles with "Hendrix"), you would want to create separate tables for your query dimensions (e.g. Artist, Genre, Year)...

There are lots of options here for what you can do. There is a good starter link on SQL Server at http://msdn2.microsoft.com/en-us/sql/aa336347.aspx (Look in the section titled "How do I learn more about SQL Server 2005"...specifically the Architecture and Design link and the Programmability link).

Access is a great introductory tool to help get your head wrapped around how you can use a database in this scenario. SQL Server 2005 Express Edition when used in conjunction with Visual Studio 2005 would be another good option.

Hope that helps and good luck!

Any way to use files from separate directories in mgmt studio?

Is there any way to add files from 2 different directories and have them retain their directory identity within the solution? When I attempt to add a file from another directory, mgmt studio makes a copy of the file in the default directory for the solution, which is not what I want. I want to be able to edit the script and have it stay (and update) within the directory from which it came. The only way I see now is to have multiple solutions, but since mgmt studio appears to only allow me to have one solution open at a time, this doesn't work either. Any suggestions?How are you performing this change, DTS or script?

any way to check the duplicated rows in destination before loading data?

Hi. As the title, I am try to figure out how to write script to prevent duplicated rows before loading data from couple csv files to the OLE database table.
Another quick question, when I use Data Conversion to convert data from string to datetime or decimal type, it always return error like potential data loss.

For your first question, probably an easier approach is to use a SORT transform to remove duplicate records before loading data into your destination.

For the other question, I think it's a matter of which format you used in your source strings. Firstly pls be aware we use locale information when doing converting strings to date types or decimals. Secondly, when converting string to date types, you have two options: normal conversion and fast-parse conversion. Normal conversion supports standard oledb formats while fastparse supports ISO 8601. (fastparse option is on the DataConversion output columns)

You'll need to get more detailed helps on this from SQLServer Books On Line. e.g. For fastparse, pls refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/bed6e2c1-791a-4fa1-b29f-cbfdd1fa8d39.htm

thanks

wenyang

|||Thanks for your fast response. My first question is to load date from CSV files to the table, but don't insert the duplicated rows which are already existed in the table.|||

I see. you want to avoid inserting rows which'll duplicate rows in your existing destination table. In that case, you can do a lookup first, then leading only those "failing" rows to destination. Remember to set Lookup's error flow handling to Redirect.

thanks

wenyang

Any way to attach a dual-log-file database without the log files?

Odd problem I ran into today - we'll restore from backups, but I'd
like to know if there's another way around it.
Our problem is that a log file got so big it filled up the drive.
Couldn't even switch it to simple mode. So the thought was to detach
it, then attach it without the log file. However, it appears that we
had two log files in that database, so we get the following when
trying to use sp_attach_single_file_db:
File activation failure. The physical file name "E:\generic
\generic_log.ldf" may be incorrect.
The log was not rebuilt because there is more than one log file.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Generic'. CREATE DATABASE is aborted.
Any thoughts? Thanks.Did you try sp_attach_single_file_db? According to BOL for 2005, this should allow attaching a db
having one data file but multiple log files. Actually, sp_attach* procedures are deprecated so use
CREATE DATABASE ... FOR ATTACH (or in this case FOR_ATTACH__REBUILD_LOG) instead. Also, see the
information on BOL for CREATE DATABASE and ATTACH_REBUILD_LOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"M Bourgon" <bourgon@.gmail.com> wrote in message
news:f5c0e2d4-3530-461d-a2e0-9238074f7c0f@.21g2000hsj.googlegroups.com...
> Odd problem I ran into today - we'll restore from backups, but I'd
> like to know if there's another way around it.
> Our problem is that a log file got so big it filled up the drive.
> Couldn't even switch it to simple mode. So the thought was to detach
> it, then attach it without the log file. However, it appears that we
> had two log files in that database, so we get the following when
> trying to use sp_attach_single_file_db:
> File activation failure. The physical file name "E:\generic
> \generic_log.ldf" may be incorrect.
> The log was not rebuilt because there is more than one log file.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'Generic'. CREATE DATABASE is aborted.
>
> Any thoughts? Thanks.

Any suggestions on how to process very large files?

I am receiving large XML files from an external source. I need to inseert
all the <DATA> elements into a table on SQL Server. These files can be as
small as a couple k or as large as 3 megs. I would like to input all the data
using sprocs on the database server. Can I really really do that or should I
just write a client side app to read the data and insert into the table?
(An example is attached with just 3 data points. Most have tens of thousands
of data points.)
What is the most effiecent approach to handling large XML files? What
technology works the best? If there is information I should be reading on
this I do not seem to be able to locate it in MSDN.
Any help will be appreciated.
AlanS
<?xml version="1.0" encoding="utf-8" ?>
<ORGANIZATION xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://oasis.caiso.com/oasisv003.xsd">
CAISO
<REPORT_ITEM>
<HEADER>
<REPORT>AS_FINAL_MCP</REPORT>
<SYSTEM>OASIS</SYSTEM>
<TZ>PPT</TZ>
<MKT_TYPE>A</MKT_TYPE>
<UOM>US$/MW</UOM>
<INTERVAL>ENDING</INTERVAL>
<SEC_PER_INTERVAL>3600</SEC_PER_INTERVAL>
</HEADER>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>1</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>2</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>3</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
</REPORT_ITEM>
<DISCLAIMER_ITEM>
<DISCLAIMER>The contents of these pages are subject to change without
notice. Decisions based on information contained within the web site are the
visitor's sole responsibility.
</DISCLAIMER>
</DISCLAIMER_ITEM>
</ORGANIZATION>
If you have a dedicated machine with otherwise no load, you should be able
to use OpenXML.
If you have other loads on your machine or the data is often larger than a
couple of 100kB, you may want to consider the XML Bulkload object.
Best regards
Michael
"AlanS" <AlanS@.discussions.microsoft.com> wrote in message
news:BB511473-A60B-4E4F-B492-F050D8024B65@.microsoft.com...
>I am receiving large XML files from an external source. I need to inseert
> all the <DATA> elements into a table on SQL Server. These files can be as
> small as a couple k or as large as 3 megs. I would like to input all the
> data
> using sprocs on the database server. Can I really really do that or should
> I
> just write a client side app to read the data and insert into the table?
> (An example is attached with just 3 data points. Most have tens of
> thousands
> of data points.)
> What is the most effiecent approach to handling large XML files? What
> technology works the best? If there is information I should be reading on
> this I do not seem to be able to locate it in MSDN.
> Any help will be appreciated.
> AlanS
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ORGANIZATION xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
> xsi:noNamespaceSchemaLocation="http://oasis.caiso.com/oasisv003.xsd">
> CAISO
> <REPORT_ITEM>
> <HEADER>
> <REPORT>AS_FINAL_MCP</REPORT>
> <SYSTEM>OASIS</SYSTEM>
> <TZ>PPT</TZ>
> <MKT_TYPE>A</MKT_TYPE>
> <UOM>US$/MW</UOM>
> <INTERVAL>ENDING</INTERVAL>
> <SEC_PER_INTERVAL>3600</SEC_PER_INTERVAL>
> </HEADER>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>1</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>2</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>3</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> </REPORT_ITEM>
> <DISCLAIMER_ITEM>
> <DISCLAIMER>The contents of these pages are subject to change without
> notice. Decisions based on information contained within the web site are
> the
> visitor's sole responsibility.
> </DISCLAIMER>
> </DISCLAIMER_ITEM>
> </ORGANIZATION>
>
|||Hello, Michael!
You wrote on Fri, 29 Oct 2004 18:30:10 -0700:
MRM> If you have other loads on your machine or the data is often larger
MRM> than a couple of 100kB, you may want to consider the XML Bulkload
MRM> object.
Since the original authers xml format is very simple I would suggest to use
SqlXmlBulkLoad object and nothing else.
With best regards, Alex Shirshov.
|||Ok. I have tried to use SQLXmlBulkLoad. But, I am having a problem. First
point though. I am told that this is an invalid connection string.
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=DEVELOPER;packet
size=4096;integrated security=SSPI;data source=ALANS;persist security
info=False;initial catalog=Llama";
The connection string works. I use it earlier to connect to populate a grid.
Here is my using statement;
using SQLXMLBULKLOADLib;
Here is my code
(1) SQLXMLBulkLoad objBL = new SQLXMLBulkLoad();
(2) objBL.ConnectionString = this.sqlConnection1.ConnectionString;
(3) objBL.ErrorLogFile = @."C:\APSES\error.log";
(4)objBL.Execute(@."C:\APSES\XML TestsSampleSchema.xml", @."C:\APSES\XML
TestsSampleXMLData.xml");
(5)objBL = null;
Exception occurs at line (4). Invalid Connect String.
Here is the error log
<?xml version="1.0"?>
<Result State="FAILED">
<Error><HResult>0x80040E21I32</HResult>
<Description><![CDATA[Invalid connection string.]]></Description>
<Source>XML BulkLoad for SQL Server</Source>
<Type>FATAL</Type>
</Error>
</Result State>
What is incorrect and how do I correct it?

Any SQL wizard can help? Reformat the input file and transfer into SQL server

I am trying to transfer 200 txt files into SQL server by using query analyzer.
The command is 'Bulk insert [tableName] from 'path\filename.txt'
However, I need to read and modifiy the txt file.
I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily.

Thank you for the help in advance!

Here is the raw data layout, which is comma delimited.
BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990
Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005
Fq D Fq D Fq D Fq D
Date R P M E D Date R P M E D Date R P M E D Date R P M E D
1/1/90 1 2 3 4 5 1/1/90 2 3 4 5 6 1/1/90 3 4 5 6 7 1/1/90 4 5 6 7 8
2 3 4 5 6 1 2 3 4 5 3 4 5 6 7 6 7 8 9 1
1/1/05 ..... 1/1/05 ... 1/1/05 .... 1/1/05 ....

This is the desired output after load into the table, which is tacking each repeating block on top of each other.
Date R P M E D
1/1/90 1 2 3 4 5
2 3 4 5 6
1/1/05 .....
1/1/90 2 3 4 5 6
2 3 4 5 6
1/1/05 .....
1/1/90 3 4 5 6 7
3 4 5 6 7
1/1/05 .....
1/1/90 4 5 6 7 8
6 7 8 9 1
1/1/05 ....."I am trying to transfer 200 txt files into SQL server by using query analyzer."
--DTS might be more appropriate.

"I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily."
--Faith is a powerful thing.

"Here is the raw data layout, which is comma delimited."
--What you posted is not comma delimited.

"This is the desired output after load into the table, which is tacking each repeating block on top of each other."
--You are going to need to load this data into a staging table and normalize it before loading into your production tables. The process will be complex, involving several passes through the data.

If at all possible, try to get your source data in a better format. Practically any other format would be preferable to what you posted.|||Blindman,
Thank you for your reply.
You are right... I forgot to put "," in my sample file layout.
I am using another source provider to request time series in excel. This is the most efficient way I can utilize excel ability (256 columns and over 65,000 rows). That's why the raw data layout looks wired. However, I have to stick to it.

I was thinking to load these files into a table to normalize but I am not sure if I know SQL well enough to say this is the best solution. I think I got the answer from you.

What is staging db. I assume it is one of defualt DB in in enterprise manager, however, I did not see it. Or this is the name you gave?

Thank you again for the help.
Shiparsons|||Not "Staging DB". "Staging TABLE."

A staging table is basically an table that has the same structure as your input data, with additional columns added as needed to keep track of records as they are being processed. I always add an "Imported" column that defaults to getdate(), and an ImportErrors column that I populate as necessary during processing.

Your staging table should have no Primary Keys or constraints (unless you add a surrogate PKey for processing...), so that your import process never fails because the data does not match what is expected.

Once the data is in the staging table you cleans it and make sure it satisfies all the business rules required by your production tables. Then you make as many passes through the staging table as necessary to update the various production tables it feeds, starting with the top-level tables.|||Thank you for the explanation.
What datatype I should use when I create my staging table? I assume this is nonconstraints type since my raw data contains text, datetime, and float.

Thank you,
Qing|||You should try to match the datatype to the type of the data being entered, though some people just make all staging table columns varchar by default. I don't do this, as a rule, but you may have no other choice since your import file is actually a mix of different layouts. String fields are the only column types that will accept any input type.|||Blindman,
Thank you for the help.

I will try.

shiparsons

Any one knows the location of the SqlServersamples.msi? plz let me konw

Hi i am going through with the videos on reporting server. in the videos they have given files to download from the particular URL. but now they have changed and i cannot find these files on reporting server installations. plz any one know where the location of these files exists.

i need to download four files from the following link

http://msdn2.microsoft.com/en-us/express/bb410792.aspx

but i can find only two from this link they are:

Install Microsoft SQL Server 2005 Express Edition with Advanced Services

Microsoft SQL Server 2005 Express Edition Toolkit

but i dont find remaining two links which says in this video

i cant find SQLserverSample.MSI. any one know the location of this file plz.

plz click for the video link herehttp://www.asp.net/learn/sql-videos/video-112.aspx

SQL Server code samples and sample databases are now hosted on CodePlex

for more information:

https://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

for download:

http://codeplex.com/SqlServerSamples

for the msi files:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

thanks

|||

Try this for the msi:

http://www.codeplex.com/MSFTISProdSamples/Release/ProjectReleases.aspx?ReleaseId=4039

any ideas?

I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
ThanksHi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:
> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go out
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take the
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncronize
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in the
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>

any ideas?

I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
Thanks
Hi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:

> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go out
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take the
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncronize
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in the
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>

any ideas?

I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
ThanksHi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:

> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go o
ut
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take th
e
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncroniz
e
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in th
e
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>

Any embedded tool in win2003 or SSIS for extracting the files from ZIP

Who knows whether exists embedded tool in win2003 or SSIS for extracting the files from ZIP?

Thanks in advance.

SSIS can execute a command line zip utility to extract files.|||

Do u mean that it should use 3rd party tool(like winzip command line)?

I dont wanna do that cuz of commercial purpose.

I just wanna know any tool in windows 2003 server or something banding in the SSIS

|||You're going to have to use a 3rd party utility.

Any blocking when we extend the data files ?

Will there be any blocking ifi grow the data files and Log files from say
5GB to 25GB on a highly transactional system ? Let me know your thoughts.Hi
No locks are taken at data level, but the high I/O does affect performance
on queries.
Do it when usage is lowest and like normal, test it on a non-production
system before you do it.
Regards
Mike
"Hassan" wrote:
> Will there be any blocking ifi grow the data files and Log files from say
> 5GB to 25GB on a highly transactional system ? Let me know your thoughts.
>
>

Any blocking when we extend the data files ?

Will there be any blocking ifi grow the data files and Log files from say
5GB to 25GB on a highly transactional system ? Let me know your thoughts.
Hi
No locks are taken at data level, but the high I/O does affect performance
on queries.
Do it when usage is lowest and like normal, test it on a non-production
system before you do it.
Regards
Mike
"Hassan" wrote:

> Will there be any blocking ifi grow the data files and Log files from say
> 5GB to 25GB on a highly transactional system ? Let me know your thoughts.
>
>

antivirus software question

I currently exclude the directories that contain the sql database files
(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
scans. Actually I do this for any database files (act, quickbooks, ms
access) but I was recently thinking about this and I realized that I'm not
really sure if its necessary?
sticking with SQL server 2000/2005 since that is what this forum is for, is
it true that I should be excluding these directories (the ones with the
database and log files) from my antivirus software's protection?
and just in case someone is also familiar with the needs of any of the other
databases I mentioned, please feel free to comment on them as well. All
those other systems are flat file based database systems. I don't know if
that makes a difference with respect to antivirus software.
Each AntiVirus software scans computers differently. To generally say that
one wont affect your SQL server when anther might would be misleading.
You should test scanning folders with your particular software to see it
affects your SQL server. A decision should be made by the business owner as
to whether scanning that directory is mandated or not.
There are tons of known issues using Antivirus in a clutered envirnoment so
special attention should be givin in this situation.
here are two great articles...
"Guidelines for choosing antivirus software to run on the computers that are
running SQL Server"
http://support.microsoft.com/?kbid=309422
"Antivirus Software May Cause Problems with Cluster Services"
http://support.microsoft.com/kb/250355/
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I currently exclude the directories that contain the sql database files
>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>scans. Actually I do this for any database files (act, quickbooks, ms
>access) but I was recently thinking about this and I realized that I'm not
>really sure if its necessary?
> sticking with SQL server 2000/2005 since that is what this forum is for,
> is it true that I should be excluding these directories (the ones with the
> database and log files) from my antivirus software's protection?
> and just in case someone is also familiar with the needs of any of the
> other databases I mentioned, please feel free to comment on them as well.
> All those other systems are flat file based database systems. I don't know
> if that makes a difference with respect to antivirus software.
>
|||thanks for the reply Warren. Are you refering to stability/corruption
effects or only performance effects? My main concern is if there may be
stability/corruption issues due to the antivirus, not performance. I could
gage the performance easily enough after making a change but its the
possible corruption and/or stability issues that my not be immediately
apparrent (and possibly realized to late!) that worry me. Does this change
anything? or is this still a per product issue that requires individual
testing? ...I'm always looking for that nice, simple, yet broad, rule thats
easy to remember : )
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
> Each AntiVirus software scans computers differently. To generally say
> that one wont affect your SQL server when anther might would be
> misleading.
> You should test scanning folders with your particular software to see it
> affects your SQL server. A decision should be made by the business owner
> as to whether scanning that directory is mandated or not.
> There are tons of known issues using Antivirus in a clutered envirnoment
> so special attention should be givin in this situation.
> here are two great articles...
> "Guidelines for choosing antivirus software to run on the computers that
> are running SQL Server"
> http://support.microsoft.com/?kbid=309422
> "Antivirus Software May Cause Problems with Cluster Services"
> http://support.microsoft.com/kb/250355/
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "djc" <noone@.nowhere.com> wrote in message
> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>
|||I am referring to both stability, performance and security. Installing
AntiVirus on a SQL server increases surface area which has security risks.
But what I am really trying to say is you need to test it with your
particular anti virus software. For me, I dont want anything actively
scanning my SQL server files for a thousand reasons. Antivirus software can
certainly create a bottleneck due to the program requiring too much CPU
time -- especially if the server is also running an e-mail server or program
that requires a lot of antivirus resources (not necessarily a best
practice). It can also create file I/O problems if real-time scanning is
enabled for all files that are opened. I definitely recommend running
malware protection on a SQL Server (or any database server for that matter),
but be sure to exclude your database directories (or specific database
files) from the real-time protection if you can. Also, keep an eye out on
processor utilization to make sure the malware protection is not eating up
all the resources.
I have seen countless forums on how AV software locks up your DB files and
put the database in Suspect Mode or causing SQL server to fail.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:%23QtRDrB3GHA.4484@.TK2MSFTNGP02.phx.gbl...
> thanks for the reply Warren. Are you refering to stability/corruption
> effects or only performance effects? My main concern is if there may be
> stability/corruption issues due to the antivirus, not performance. I could
> gage the performance easily enough after making a change but its the
> possible corruption and/or stability issues that my not be immediately
> apparrent (and possibly realized to late!) that worry me. Does this change
> anything? or is this still a per product issue that requires individual
> testing? ...I'm always looking for that nice, simple, yet broad, rule
> thats easy to remember : )
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
>

antivirus software question

I currently exclude the directories that contain the sql database files
(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
scans. Actually I do this for any database files (act, quickbooks, ms
access) but I was recently thinking about this and I realized that I'm not
really sure if its necessary?
sticking with SQL server 2000/2005 since that is what this forum is for, is
it true that I should be excluding these directories (the ones with the
database and log files) from my antivirus software's protection?
and just in case someone is also familiar with the needs of any of the other
databases I mentioned, please feel free to comment on them as well. All
those other systems are flat file based database systems. I don't know if
that makes a difference with respect to antivirus software.Each AntiVirus software scans computers differently. To generally say that
one wont affect your SQL server when anther might would be misleading.
You should test scanning folders with your particular software to see it
affects your SQL server. A decision should be made by the business owner as
to whether scanning that directory is mandated or not.
There are tons of known issues using Antivirus in a clutered envirnoment so
special attention should be givin in this situation.
here are two great articles...
"Guidelines for choosing antivirus software to run on the computers that are
running SQL Server"
http://support.microsoft.com/?kbid=309422
"Antivirus Software May Cause Problems with Cluster Services"
http://support.microsoft.com/kb/250355/
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I currently exclude the directories that contain the sql database files
>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>scans. Actually I do this for any database files (act, quickbooks, ms
>access) but I was recently thinking about this and I realized that I'm not
>really sure if its necessary?
> sticking with SQL server 2000/2005 since that is what this forum is for,
> is it true that I should be excluding these directories (the ones with the
> database and log files) from my antivirus software's protection?
> and just in case someone is also familiar with the needs of any of the
> other databases I mentioned, please feel free to comment on them as well.
> All those other systems are flat file based database systems. I don't know
> if that makes a difference with respect to antivirus software.
>|||thanks for the reply Warren. Are you refering to stability/corruption
effects or only performance effects? My main concern is if there may be
stability/corruption issues due to the antivirus, not performance. I could
gage the performance easily enough after making a change but its the
possible corruption and/or stability issues that my not be immediately
apparrent (and possibly realized to late!) that worry me. Does this change
anything? or is this still a per product issue that requires individual
testing? ...I'm always looking for that nice, simple, yet broad, rule thats
easy to remember : )
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
> Each AntiVirus software scans computers differently. To generally say
> that one wont affect your SQL server when anther might would be
> misleading.
> You should test scanning folders with your particular software to see it
> affects your SQL server. A decision should be made by the business owner
> as to whether scanning that directory is mandated or not.
> There are tons of known issues using Antivirus in a clutered envirnoment
> so special attention should be givin in this situation.
> here are two great articles...
> "Guidelines for choosing antivirus software to run on the computers that
> are running SQL Server"
> http://support.microsoft.com/?kbid=309422
> "Antivirus Software May Cause Problems with Cluster Services"
> http://support.microsoft.com/kb/250355/
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "djc" <noone@.nowhere.com> wrote in message
> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>>I currently exclude the directories that contain the sql database files
>>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>>scans. Actually I do this for any database files (act, quickbooks, ms
>>access) but I was recently thinking about this and I realized that I'm not
>>really sure if its necessary?
>> sticking with SQL server 2000/2005 since that is what this forum is for,
>> is it true that I should be excluding these directories (the ones with
>> the database and log files) from my antivirus software's protection?
>> and just in case someone is also familiar with the needs of any of the
>> other databases I mentioned, please feel free to comment on them as well.
>> All those other systems are flat file based database systems. I don't
>> know if that makes a difference with respect to antivirus software.
>|||I am referring to both stability, performance and security. Installing
AntiVirus on a SQL server increases surface area which has security risks.
But what I am really trying to say is you need to test it with your
particular anti virus software. For me, I dont want anything actively
scanning my SQL server files for a thousand reasons. Antivirus software can
certainly create a bottleneck due to the program requiring too much CPU
time -- especially if the server is also running an e-mail server or program
that requires a lot of antivirus resources (not necessarily a best
practice). It can also create file I/O problems if real-time scanning is
enabled for all files that are opened. I definitely recommend running
malware protection on a SQL Server (or any database server for that matter),
but be sure to exclude your database directories (or specific database
files) from the real-time protection if you can. Also, keep an eye out on
processor utilization to make sure the malware protection is not eating up
all the resources.
I have seen countless forums on how AV software locks up your DB files and
put the database in Suspect Mode or causing SQL server to fail.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:%23QtRDrB3GHA.4484@.TK2MSFTNGP02.phx.gbl...
> thanks for the reply Warren. Are you refering to stability/corruption
> effects or only performance effects? My main concern is if there may be
> stability/corruption issues due to the antivirus, not performance. I could
> gage the performance easily enough after making a change but its the
> possible corruption and/or stability issues that my not be immediately
> apparrent (and possibly realized to late!) that worry me. Does this change
> anything? or is this still a per product issue that requires individual
> testing? ...I'm always looking for that nice, simple, yet broad, rule
> thats easy to remember : )
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
>> Each AntiVirus software scans computers differently. To generally say
>> that one wont affect your SQL server when anther might would be
>> misleading.
>> You should test scanning folders with your particular software to see it
>> affects your SQL server. A decision should be made by the business owner
>> as to whether scanning that directory is mandated or not.
>> There are tons of known issues using Antivirus in a clutered envirnoment
>> so special attention should be givin in this situation.
>> here are two great articles...
>> "Guidelines for choosing antivirus software to run on the computers that
>> are running SQL Server"
>> http://support.microsoft.com/?kbid=309422
>> "Antivirus Software May Cause Problems with Cluster Services"
>> http://support.microsoft.com/kb/250355/
>> thanks,
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "djc" <noone@.nowhere.com> wrote in message
>> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>>I currently exclude the directories that contain the sql database files
>>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>>scans. Actually I do this for any database files (act, quickbooks, ms
>>access) but I was recently thinking about this and I realized that I'm
>>not really sure if its necessary?
>> sticking with SQL server 2000/2005 since that is what this forum is for,
>> is it true that I should be excluding these directories (the ones with
>> the database and log files) from my antivirus software's protection?
>> and just in case someone is also familiar with the needs of any of the
>> other databases I mentioned, please feel free to comment on them as
>> well. All those other systems are flat file based database systems. I
>> don't know if that makes a difference with respect to antivirus
>> software.
>>
>

antivirus software question

I currently exclude the directories that contain the sql database files
(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
scans. Actually I do this for any database files (act, quickbooks, ms
access) but I was recently thinking about this and I realized that I'm not
really sure if its necessary?
sticking with SQL server 2000/2005 since that is what this forum is for, is
it true that I should be excluding these directories (the ones with the
database and log files) from my antivirus software's protection?
and just in case someone is also familiar with the needs of any of the other
databases I mentioned, please feel free to comment on them as well. All
those other systems are flat file based database systems. I don't know if
that makes a difference with respect to antivirus software.Each AntiVirus software scans computers differently. To generally say that
one wont affect your SQL server when anther might would be misleading.
You should test scanning folders with your particular software to see it
affects your SQL server. A decision should be made by the business owner as
to whether scanning that directory is mandated or not.
There are tons of known issues using Antivirus in a clutered envirnoment so
special attention should be givin in this situation.
here are two great articles...
"Guidelines for choosing antivirus software to run on the computers that are
running SQL Server"
http://support.microsoft.com/?kbid=309422
"Antivirus Software May Cause Problems with Cluster Services"
http://support.microsoft.com/kb/250355/
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I currently exclude the directories that contain the sql database files
>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>scans. Actually I do this for any database files (act, quickbooks, ms
>access) but I was recently thinking about this and I realized that I'm not
>really sure if its necessary?
> sticking with SQL server 2000/2005 since that is what this forum is for,
> is it true that I should be excluding these directories (the ones with the
> database and log files) from my antivirus software's protection?
> and just in case someone is also familiar with the needs of any of the
> other databases I mentioned, please feel free to comment on them as well.
> All those other systems are flat file based database systems. I don't know
> if that makes a difference with respect to antivirus software.
>|||thanks for the reply Warren. Are you refering to stability/corruption
effects or only performance effects? My main concern is if there may be
stability/corruption issues due to the antivirus, not performance. I could
gage the performance easily enough after making a change but its the
possible corruption and/or stability issues that my not be immediately
apparrent (and possibly realized to late!) that worry me. Does this change
anything? or is this still a per product issue that requires individual
testing? ...I'm always looking for that nice, simple, yet broad, rule thats
easy to remember : )
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
> Each AntiVirus software scans computers differently. To generally say
> that one wont affect your SQL server when anther might would be
> misleading.
> You should test scanning folders with your particular software to see it
> affects your SQL server. A decision should be made by the business owner
> as to whether scanning that directory is mandated or not.
> There are tons of known issues using Antivirus in a clutered envirnoment
> so special attention should be givin in this situation.
> here are two great articles...
> "Guidelines for choosing antivirus software to run on the computers that
> are running SQL Server"
> http://support.microsoft.com/?kbid=309422
> "Antivirus Software May Cause Problems with Cluster Services"
> http://support.microsoft.com/kb/250355/
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "djc" <noone@.nowhere.com> wrote in message
> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>|||I am referring to both stability, performance and security. Installing
AntiVirus on a SQL server increases surface area which has security risks.
But what I am really trying to say is you need to test it with your
particular anti virus software. For me, I dont want anything actively
scanning my SQL server files for a thousand reasons. Antivirus software can
certainly create a bottleneck due to the program requiring too much CPU
time -- especially if the server is also running an e-mail server or program
that requires a lot of antivirus resources (not necessarily a best
practice). It can also create file I/O problems if real-time scanning is
enabled for all files that are opened. I definitely recommend running
malware protection on a SQL Server (or any database server for that matter),
but be sure to exclude your database directories (or specific database
files) from the real-time protection if you can. Also, keep an eye out on
processor utilization to make sure the malware protection is not eating up
all the resources.
I have seen countless forums on how AV software locks up your DB files and
put the database in Suspect Mode or causing SQL server to fail.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:%23QtRDrB3GHA.4484@.TK2MSFTNGP02.phx.gbl...
> thanks for the reply Warren. Are you refering to stability/corruption
> effects or only performance effects? My main concern is if there may be
> stability/corruption issues due to the antivirus, not performance. I could
> gage the performance easily enough after making a change but its the
> possible corruption and/or stability issues that my not be immediately
> apparrent (and possibly realized to late!) that worry me. Does this change
> anything? or is this still a per product issue that requires individual
> testing? ...I'm always looking for that nice, simple, yet broad, rule
> thats easy to remember : )
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
>

Anti-Virus on SQL Configuration

Hi:
We have Symantec Enterprise Anti-Virus 10.0 but it is not currently
installed on our new SQL box. I would like to know what files and folders t
o
exclude from scanning before I install. I have search Symantec knowledgebas
e
but no answers. Does MS have a page on this like they do for Exchange or is
it pretty much the same as Exchange?
Thanks,http://support.microsoft.com/?kbid=309422
http://www.sqlservercentral.com/col...rusprograms.asp
Andrew J. Kelly SQL MVP
"Cindy" <Cindy@.discussions.microsoft.com> wrote in message
news:1C577C85-E5D4-48B0-BBE1-B363C06C706B@.microsoft.com...
> Hi:
> We have Symantec Enterprise Anti-Virus 10.0 but it is not currently
> installed on our new SQL box. I would like to know what files and folders
> to
> exclude from scanning before I install. I have search Symantec
> knowledgebase
> but no answers. Does MS have a page on this like they do for Exchange or
> is
> it pretty much the same as Exchange?
> Thanks,|||Thanks Andrew. It would be nice if MS had step by step like with Exchange.
Symantec has nothing but the other article you pointed me to helps.
Cindy
"Andrew J. Kelly" wrote:

> http://support.microsoft.com/?kbid=309422
>
> http://www.sqlservercentral.com/col...rusprograms.asp
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "Cindy" <Cindy@.discussions.microsoft.com> wrote in message
> news:1C577C85-E5D4-48B0-BBE1-B363C06C706B@.microsoft.com...
>
>

Antivirus

I'm looking for a best practice for running antivirus
software on sql servers. Which files to exclude, what
can happen by scanning all files, etc.You'd want to exclude your data and log files. Additionally,
the following article has some guidelines:
INF: Consideration for a Virus Scanner on a Computer That Is
Running SQL Server
http://support.microsoft.com/?kbid=309422
-Sue
On Thu, 18 Mar 2004 10:55:42 -0800, "Thad Molling"
<anonymous@.discussions.microsoft.com> wrote:

>I'm looking for a best practice for running antivirus
>software on sql servers. Which files to exclude, what
>can happen by scanning all files, etc.|||Add don't run it at all on a cluster. Anti-virus can interfere with the
ability of the cluster service to shift disk resources from node to node.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:r9ak50tjjrgb0alt9qa2ali45bk2ame7ia@.
4ax.com...
> You'd want to exclude your data and log files. Additionally,
> the following article has some guidelines:
> INF: Consideration for a Virus Scanner on a Computer That Is
> Running SQL Server
> http://support.microsoft.com/?kbid=309422
> -Sue
> On Thu, 18 Mar 2004 10:55:42 -0800, "Thad Molling"
> <anonymous@.discussions.microsoft.com> wrote:
>
>

Anti Virus scanning and database files

Does Microsoft have an official position on excluding MDF/LDF files from
virus scanning? I searched the KBs but couldnt come up with anything.
Thanks.Neil W. wrote:
> Does Microsoft have an official position on excluding MDF/LDF files
> from virus scanning? I searched the KBs but couldnt come up with
> anything.
You can refer to this kb article
http://support.microsoft.com/kb/309422/en-us
Usually I exclude SQL Server data files from scanning...

> Thanks.
Bye
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com