Import IIS web log

How can I use SQL server to import IIS web log and performance monitor log?
Thanks
The following articles can help you get started:
HOWTO: Use SQL Server to Analyze Web Logs
http://support.microsoft.com/?id=296085
INF: Importing Performance Monitor Logs into SQL Server
http://support.microsoft.com/?id=275685
-Sue
On Sun, 2 May 2004 12:01:04 -0700, "AnnZ"
<anonymous@.discussions.microsoft.com> wrote:

>How can I use SQL server to import IIS web log and performance monitor log?
>Thanks

Import IIS web log

How can I use SQL server to import IIS web log and performance monitor log?
ThanksThe following articles can help you get started:
HOWTO: Use SQL Server to Analyze Web Logs
http://support.microsoft.com/?id=296085
INF: Importing Performance Monitor Logs into SQL Server
http://support.microsoft.com/?id=275685
-Sue
On Sun, 2 May 2004 12:01:04 -0700, "AnnZ"
<anonymous@.discussions.microsoft.com> wrote:

>How can I use SQL server to import IIS web log and performance monitor log?
>Thanks

Import IIS web log

How can I use SQL server to import IIS web log and performance monitor log
ThanksThe following articles can help you get started:
HOWTO: Use SQL Server to Analyze Web Logs
http://support.microsoft.com/?id=296085
INF: Importing Performance Monitor Logs into SQL Server
http://support.microsoft.com/?id=275685
-Sue
On Sun, 2 May 2004 12:01:04 -0700, "AnnZ"
<anonymous@.discussions.microsoft.com> wrote:
>How can I use SQL server to import IIS web log and performance monitor log?
>Thanks

Import from postgres

I'm trying to work with a web designer to get data copied from their system to ours (SQL Server 2000). She's giving me a file in what she called postgres format, which I've never worked with. I can get the CREATE TABLE statements to work with some tweaking, but I can't get the actual data. She's giving me a text file that looks like:

COPY manager (id, name, username, "password", access_level, affiliate) FROM stdin;
1 SunMie sunmie pw 5
2 Manager manager pw 5
\.

which I can't get Query Analyzer to get happy with. Am I missing something, or does she need to provide me with the data in a different format?if you have all the tables created but no data in them, you might be able to use bcp.exe to get the data in. bcp takes delimited files as input and will bulk insert to the table of your choice. as I recall, you can tell it what line to start on, and what line to end on.

look up "bcp utility" in bol for more info.

one catch may be that you are using a space to delimit the field values. it might make more sense to use some other char, like tab or "|". basically you need to choose a delimiter that won't appear in any of your text fields.

EDIT: i hope those aren't real passwords you posted... you might want to edit them if they are!|||The actual text file appears to be tab delimited. I think this forum condensed it when I posted. I will look at bcp, though my problem will be that the text file she sent contains this type of thing for multiple tables (actually it starts with create table statements). I guess in postgres you could just execute the text, and it would create/populate each table in turn. Given that the start and end point for each table would change, I'm not sure bcp will work without my manipulating the data in some way, which I was hoping to avoid.

Thanks for the help!

Import excel into SQL 2005 working in development but not in production

I have a simple code that uses a the file upload control to read an excel sheet and upload the data into a SQL 2005db.

I'm using Visual Web Developer and Sql's express edition to test it. It works fine when I test. However, when I push it up to the production server and try it via the any other pc it does not. The page loads fine. However, when it starts to upload it errors out.

Any reason why? I've never seen this happen.

Here's the code. Thanks in advance.

Protected Sub BtnUpload2_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles BtnUpload2.Click UploadTextDocument()End Sub Private Sub UploadTextDocument()Dim locationAs String = FileUpload1.PostedFile.FileName.ToString' Connection String to Excel WorkbookTry Dim excelConnectionStringAs String =String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location)' Create Connection to Excel Workbook Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select BuilderID,SeriesID,OptionLevel,CommunityID,PhaseID,PlanID,ElevationID,OptionID,CurrentSalesPrice,LocalComments,Active,DateAdded,DateAvailable,DateInactive,SalesPriceEffective,SalesPriceExpires,PreviousSalesPrice,CutOffNotBefore,CutOffNotAfter FROM [Data$]", connection) connection.Open()' Create DbDataReader to Data Worksheet Using drAs Data.Common.DbDataReader = command.ExecuteReader()' SQL Server Connection StringDim connectionStringAs String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Bulk Copy to SQL Server Using bulkCopyAs SqlBulkCopy =New SqlBulkCopy(connectionString) bulkCopy.DestinationTableName ="ExcelData" bulkCopy.WriteToServer(dr)End UsingEnd Using connection.Close()End Using LBError.Text ="The spreadsheet was successfully uploaded."Catch LBError.Text ="There was an error. Check the spreadsheet for correct format."End Try End Sub

whats the exact error message ur getting?

|||

Hey Karen,

I don't get one. Debugging is off on the live application. I just get the error message at "catch".

I have a prospective client looking at it as we speak and don't want to make any changes to the web.config while they are viewing it...

|||

to catch error

use

try catch.. with and throw ex,.

one good possible reason is excel work sheet contains datatype that are not a part of datatypes of ur columns in tables. ..

there would not be any error, but the writetoserver would simply exit.. that's it..so use the old sheets u used in development server and try again.

Try printing., sheetnames and fieldcount.. in reader..

|||

Actually, I'm using the exact same sheet, same data, everything. I debugged it and still can't find any errors.

It only works from developement... I haven't tried it in a reader.

|||

How bout u try saving that file first and then transferring its contents to the database?

Regards

Karen

|||

Possible problems:

You don't have the correct version of Jet installed on the production server.

The process doesn't have read permissions at the location that the file is being written to.

try turning on remote errors in the web.config file so you can see exactly what the error is.

|||

Please provide the code topcopy the data/rows in the Excel work sheet to the sql server table.

Thanks and Regards

Ravi Shanker Maduri

Hyderabad

|||

Okay. I captured the error message. When I select the excel sheet from the folder on my desk top this message appears. The path is correct, as I said, it works fine when I do it from developent...

'C:\Documents and Settings\rednelo\Desktop\IPAddress_Mapping\test.xls'. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

|||

The problem is this line here:

Dim locationAs String = FileUpload1.PostedFile.FileName.ToString

You are using the filename that was on the client. You need the filename of the temporary file that the server created.

|||

Hi

I want to Import The Excel Data to the sql server using T-Sql Query ,i will run somany PL-sql statements for each project i want to automate it , i want to insert the default data to a table using excel sheet to the sql server table.

Please if any one know the solution for moving the excel data to the sql server using T-Sql Statement only.

Thanks and regards

Ravishanker Maduri

|||

Motley:

You need the filename of the temporary file that the server created.

All that makes sense. How would I get to the name of the temporary file?

|||

strINVSRC = Path.GetFileName(FlInvSrc.PostedFile.FileName)

|||

Hey Karen,

I tried what you have and I'm getting this error when I put in on the server. Did you run into this?

The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
However, the same code in development gives me this:

The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

So, its not finding the file. Let me recap...

Dim locationAsString

'this works in development as I said before. So I know that was finding it at least on my local machine.

location = FileUpload1.PostedFile.FileName.ToString

What you had does not appear to find the file.

location = Path.GetFileName(FileUpload1.PostedFile.FileName)

Its somewhere between the two...

|||

Take a look at this link some one gave it to me when i had the same error message

http://forums.asp.net/t/1034337.aspx

I have done it this way..

First i am getting the name of the file...

Dim strFilePath,location as string

StrFilePath = Path.GetFileName(FileUpload1.PostedFile.FileName)

and then

location = Server.Mappath("The directory where u wanna store the file" & StrFilePath.

hope this helps..

Regards

Karen

import excel file to SQL server

Hi all,
I am trying to import excel file to SQL server using web application. I have been browsing all day long trying to get some helps but I found none that really solves my problem. :(
I am aware that I can use DTS in SQL server, however I want to build a web application for it. I can upload the file on the server, my problem is I want to load the data in excel file dynamically; I will need to create a table in the server dynamically everytime I import an excel file.
Please help!!!
Thanks
IrmaHi tinybunny,
Best solution will be
Create table dynamically at application end
and the through DTS transform the data from excel file to table.
You must follow steps to perform this.
Step 1. Create table dynamically at application end.
Step 2. Then pass the dynamically table name and excel file name from application to DTS through dynamic variables.
You have to create stored procedure for this
CREATE Procedure DtsRun
/* Param List */
@.ServerName varchar(30),
@.UserName varchar(30),
@.Password varchar(30),
@.DtsName varchar(250),
@.FileN varchar(200), --File name for excel
@.TblName varchar(200) --Datatable name


AS
DECLARE @.ERROR int -- For Hold Error Number
DECLARE @.CMD varchar(1000) -- Dts Run Command
DECLARE @.DtsPassword varchar(30)
BEGIN
-- Set as No Error
SET @.ERROR = 0

BEGIN
-- With Globle Variable
SET @.CMD ='dtsrun /S '+@.ServerName+' /U '+@.UserName+' /P '+@.Password+' /N'+@.DtsName +' /A strFileName:8='+ @.FileN ' /A strFileName:8='+ @.TblName
--print @.CMD

END

EXECUTE @.ERROR = master..xp_cmdshell @.CMD


END

RETURN @.ERROR
GO
This way you will be passing values to DTS.
Step 3.Create DTS package .
Create dynamic variables for table name and excel file
and then transform data from excel to data table.

|||Hi Nitish,
Thanks a lot for your reply. THAT'S A SUPER GREAT HELP!.. I will give it a try and see if I can get it right...|||Nitish..
I have a question... on step 1, you said that I have to create the table at application end...
The problem is how can I know the column names and the column types (string, int, etc) in excel files since every excel file can have different structure?
Your reply is much appreciated...
Irma|||Excel from version XP/2003 is tabular you can run a query on it but it uses Access SQL not SQL Server version. Hope this helps.|||

Dear Gift,

Thanks for your kind reply. However, I am doing a project right now, and I have to import an excel file to SQL Server for this project. Also, I am new to DTS SQL Server, do you have any recommendations of a good article on how to create a DTS package that would transform excel file to SQL server?
Your help is greatlky appreciated.

|||

Try these links for all your options and I think there is a complete Excel Asp.net solution in the last link from Microsoft. Hope this helps.

http://www.sqldts.com/default.aspx?243

http://www.dotnetjohn.com/articles.aspx?articleid=54
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_ta/html/Office_Excel_XML_Toolbox.asp
http://blogs.msdn.com/vsto2/

|||Thank you Gift!!!

import data properly from csv file.

I need to extract data from a csv file, validate it, and populate other
tables with that data for a multi user web application.
I am importing a csv file via linked servers as follows:
EXEC('SELECT * into ##temptbl FROM '+@.linked_server + '...['+@.file + '#' +
@.extension + ']')
Once data gets into ##temptbl then I do proper validation and populate other
tables.
This will not work if there are other users importing the file as well
because of global temp table ##temptbl.
Do I create a separate physical table to populate and delete based on
certain criteria for that user?
I tried using table variable inside the dynamic sql but did not work. So my
best bet for now is
to have a physical table, populate it for certain criteria, do validation,
and populate other permanent tables. After
successful population I would go ahead and delete rows this temporary
staging for certain criteria.
Does this make sense or this approach stinks?
TIA...I would really appreciate if any guru/expert could address this.
TIA...
"sqlster" wrote:

> I need to extract data from a csv file, validate it, and populate other
> tables with that data for a multi user web application.
> I am importing a csv file via linked servers as follows:
> EXEC('SELECT * into ##temptbl FROM '+@.linked_server + '...['+@.file + '#' +
> @.extension + ']')
> Once data gets into ##temptbl then I do proper validation and populate oth
er
> tables.
> This will not work if there are other users importing the file as well
> because of global temp table ##temptbl.
> Do I create a separate physical table to populate and delete based on
> certain criteria for that user?
> I tried using table variable inside the dynamic sql but did not work. So m
y
> best bet for now is
> to have a physical table, populate it for certain criteria, do validation,
> and populate other permanent tables. After
> successful population I would go ahead and delete rows this temporary
> staging for certain criteria.
> Does this make sense or this approach stinks?
> TIA...

Import Data on 2005, Many Errors

I have a client who we moved to our new web server and sql 2005 server from a sql 2000 server. I detached the database from sql 2000 and attached it to 2005. I also just set the compatibility to 2005 also.

My client used enterprise manager to import data into the tables on the sql 2000 just fine. Now using the SQL Management Studio, importing the same table produces all kinds of error, truncation errors, etc.

I have played with a bunch of the settings, did the "Suggest Types" options, but I still just get a bunch of errors. It seem to get it to work I have to go in on the columns of the flat file i am importing and change EVERY COLUMN field to match the table i'm importing too. That is just too much work.

I basically have a 2 record text file i easily imported to sql 2000. but importing into sql 2005 proves to be a *** load of work! Aren't products supposed to get better with future releases? What am I doing wrong?

I've tried the sql native client and the oledb sqlserver client and get the same results.

Any ideas?

Hi, so your Launching the Import/Export Wizard from Mgt studio?

Is it a delimted file, fixed width, variable row length...

What are some of the errors you are seeing.

Import Data from SPSS (Statistical Package for the Social Sciences

hi to everyone. I'm developing a web application using C#.NET and MS SQL
Server 2000 database backend. But the user wants to transfer a huge data
from his SPSS application because it will take large amount of time to input
each of the item. My question is: Is there a way to import data from SPSS
application to the database using MS SQL Server 2000.
Specifications:
Import Data From a data file (SPSS Standard Version 11.0.0)
Hope you'll reply as soon as possible. You idea will greatly help me.
Thank you and God Bless.Hi
If this information is in a reasonably formatted file you can use BULK
INSERT command, DTS or the BCP utility to import the information. This can
be scheduled as a job and you can archive the file once loaded.
There is plenty of information on BULK INSERT, DTS and BCP in Books Online,
also check out http://www.sqldts.com/ for more DTS articles such as
http://www.sqldts.com/default.aspx?231
and http://www.sqldts.com/default.aspx?246
You will need some method of loading the data file onto your server or
somewhere accessable from the server. It may be necessary to hold the data
in a staging table(s) if it requires additional work before loading into
your destination table(s).
John
"rolly-hubport" <rollyhubport@.discussions.microsoft.com> wrote in message
news:231A4F51-B185-44D8-AE52-730275EC3C21@.microsoft.com...
> hi to everyone. I'm developing a web application using C#.NET and MS SQL
> Server 2000 database backend. But the user wants to transfer a huge data
> from his SPSS application because it will take large amount of time to
> input
> each of the item. My question is: Is there a way to import data from SPSS
> application to the database using MS SQL Server 2000.
> Specifications:
> Import Data From a data file (SPSS Standard Version 11.0.0)
>
> Hope you'll reply as soon as possible. You idea will greatly help me.
> Thank you and God Bless.
>
>|||Hi,
If u will use BCP or Bulk insert u have to manually define the table
name.
use dts package tranfer data from one server to another and u have
option to select the tables.
hope this helps u
from
doller

Import data from a web site

I'm trying to use 2005 Integration Services to import data from a web address into a SQLServer 2005 database.

The address I want to download data from is http://www.nymerc.com/futures/innf.txt

I'm not sure how I am supposed to access the data on the website. What kind of connection manager do I use? Flat File? HTTP? When I try to use a flat file connection manager, I set the connection string to 'http://www.nymerc.com/futures/innf.txt', but when I click OK, the connection string gets changed to 'c:\documents and settings\....\Temporary Internet Files\Content.IE5\V01H744)\innf.txt'

Is this expected?

What's the best practice for using a web page as a data source?

Hi ECDOK,

Can you get FTP acess to this site? That would be one way to gain access to it using SSIS.

The only other approach I can think of is to use a Script Task and get this data as an HttpStream.

Andy