I need to store log files that are created in XML format so I can query some
of the fields. I have only minor knowledge of XML, but have been SQL DBA fo
r
a while. I just have not had a need to use XML in the SQL environment. We
are using SQL2000. What is the best way to do the following:
1. Create a DB for storing the log files? Some variation on the usual way?
2. Import the individual files into the DB. Eventually I can write a DTS or
something to import?
3. Query the DB. Create some reports on usage.
If somene could post a link to get me started it would be very helpful.DaveK wrote:
> I need to store log files that are created in XML format so I can query so
me
> of the fields.
Why not just use an XQuery implementation to query them direct? I see no
requirement here to involve a database at all.
///Peter
> I have only minor knowledge of XML, but have been SQL DBA for
> a while. I just have not had a need to use XML in the SQL environment. W
e
> are using SQL2000. What is the best way to do the following:
> 1. Create a DB for storing the log files? Some variation on the usual way
?
> 2. Import the individual files into the DB. Eventually I can write a DTS
or
> something to import?
> 3. Query the DB. Create some reports on usage.|||After a five minute web search and SQL help search I don't see how I can
avoid using SQL to build some sort of datbase just so I can address backup,
security, etc. It looks like XPath and XQuery can do some searching, but th
e
other items that I need to cover are not really addressed. This is an
example of one event log item I want to store.
<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>
I have to admit I am not a convert to the XML world, but as I said, I am a
newbie to it as well. In this case it just seems like a fancy way to
eliminate delimited importing. The format is not likely to change.|||DaveK wrote:
> After a five minute web search and SQL help search I don't see how I can
> avoid using SQL to build some sort of datbase just so I can address backup
,
> security, etc. It looks like XPath and XQuery can do some searching, but
the
> other items that I need to cover are not really addressed. This is an
> example of one event log item I want to store.
> <CreateDate>7/31/2007</CreateDate>
> <CreateTime>10:19:25</CreateTime>
> <Logger>xxxxd7yrrt11</Logger>
> <Events>
> <Event>
> <EventID>8001</EventID>
> <Description>Login</Description>
> <Category>Audit</Category>
> <Source>LAN Client</Source>
> <SubSource>xxxx_8</SubSource>
> <UserName>test.name</UserName>
> <UserID>347</UserID>
> <Computer>xxx7YRRT11</Computer>
> <Date>07/31/2007</Date>
> <Time>10:19:49</Time>
> <ObjectType>User</ObjectType>
> <Details></Details>
> </Event>
> I have to admit I am not a convert to the XML world, but as I said, I am a
> newbie to it as well. In this case it just seems like a fancy way to
> eliminate delimited importing. The format is not likely to change.
AFAIK all database systems now offer some kind of "Import XML" plugin.
If yours doesn't, you'll need to turn the XML into CSV or whatever your
system consumes. The easiest way to do this is to write an XSLT script,
and I think there are several quoted or linked in Dave Pawson's XSL FAQ
at http://www.dpawson.co.uk/xsl/
XML is just a fancy way of identifying information: you can see from the
above example that it is much clearer in naming items and positioning
them in the hierarchy than (for example) CSV. If the format is stable,
then a little routine to run XSLT over the data and spit out CSV should
do you just fine.
The following appears to work for the sample above (with the addition of
the missing </Events> end-tag and the enclosing root element
<data>...</data> ):
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output method="text"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/">
<xsl:apply-templates select="data/CreateDate"/>
<xsl:apply-templates select="data/CreateTime"/>
<xsl:apply-templates select="data/Logger"/>
<xsl:apply-templates select="data/Events/Event/*"/>
</xsl:template>
<!-- fields that start a record -->
<xsl:template match="CreateDate|EventID">
<xsl:text>"</xsl:text>
<xsl:value-of select="."/>
</xsl:template>
<!-- fields that occur in mid-record -->
<xsl:template match="*">
<xsl:text>,"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>"</xsl:text>
</xsl:template>
<!-- fields that end a record -->
<xsl:template match="Logger|Details">
<xsl:text>,"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>"
</xsl:text>
</xsl:template>
</xsl:stylesheet>
$ java -jar /usr/local/saxon/b8.5/saxon8.jar -o test.csv test.xml test.xsl
$ cat test.csv
"7/31/2007,"10:19:25","xxxxd7yrrt11"
"8001,"Login","Audit","LAN
Client","xxxx_8","test.name","347","xxx7YRRT11","07/31/2007","10:19:49","Use
r",""
$
This makes the assumption that your import routine can do something
different with record #1...
///Peter
--
XML FAQ: http://xml.silmaril.ie/|||Thank you. This is very helpful. I did not paste in the whole log so misse
d
the ending tags.
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
Importing XML for Newbies
I need to store log files that are created in XML format so I can query some
of the fields. I have only minor knowledge of XML, but have been SQL DBA for
a while. I just have not had a need to use XML in the SQL environment. We
are using SQL2000. What is the best way to do the following:
1. Create a DB for storing the log files? Some variation on the usual way?
2. Import the individual files into the DB. Eventually I can write a DTS or
something to import?
3. Query the DB. Create some reports on usage.
If somene could post a link to get me started it would be very helpful.
After a five minute web search and SQL help search I don't see how I can
avoid using SQL to build some sort of datbase just so I can address backup,
security, etc. It looks like XPath and XQuery can do some searching, but the
other items that I need to cover are not really addressed. This is an
example of one event log item I want to store.
<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>
I have to admit I am not a convert to the XML world, but as I said, I am a
newbie to it as well. In this case it just seems like a fancy way to
eliminate delimited importing. The format is not likely to change.
|||Thank you. This is very helpful. I did not paste in the whole log so missed
the ending tags.
of the fields. I have only minor knowledge of XML, but have been SQL DBA for
a while. I just have not had a need to use XML in the SQL environment. We
are using SQL2000. What is the best way to do the following:
1. Create a DB for storing the log files? Some variation on the usual way?
2. Import the individual files into the DB. Eventually I can write a DTS or
something to import?
3. Query the DB. Create some reports on usage.
If somene could post a link to get me started it would be very helpful.
After a five minute web search and SQL help search I don't see how I can
avoid using SQL to build some sort of datbase just so I can address backup,
security, etc. It looks like XPath and XQuery can do some searching, but the
other items that I need to cover are not really addressed. This is an
example of one event log item I want to store.
<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>
I have to admit I am not a convert to the XML world, but as I said, I am a
newbie to it as well. In this case it just seems like a fancy way to
eliminate delimited importing. The format is not likely to change.
|||Thank you. This is very helpful. I did not paste in the whole log so missed
the ending tags.
Friday, March 23, 2012
importing of XML files into SQL 2000
Hi,
How could i actually import an XML file directly into SQL Server 2000 from
the EM or Query Analyzer
i tried right-click on the tables --> import data but what should the data
source be ?
appreciate ur advise
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1Look into SQLXML. So far it is the best way to insert large amounts of XML
data into a table.
"maxzsim via webservertalk.com" wrote:
> Hi,
> How could i actually import an XML file directly into SQL Server 2000 fro
m
> the EM or Query Analyzer
> i tried right-click on the tables --> import data but what should the dat
a
> source be ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1
>|||Hi ,
I am not able to see SQLXML in the Data sources , how can i get that out ,
wat do i need to install ?
appreciate ur advice
tks & rdgs
Sal Young wrote:
>Look into SQLXML. So far it is the best way to insert large amounts of XML
>data into a table.
>
>[quoted text clipped - 7 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1|||This is one of the examples included in the documentation of SQLXML add-on
component. You'll need your xml file and the schema (xsd) file.
Follow these steps:
1. Go to
http://www.microsoft.com/downloads/...&displaylang=en
and download the SQLXML add-on component. Install it on the machine where
you're going to run the DTS package.
2. Create a new DTS package and add an "ActiveX Script Task"
3. Insert the following code inside the Main() function:
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
with objBL
.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=dev;integrated
security=SSPI"
.ErrorLogFile = "C:\error.log"
.SchemaGen = true
.Execute "Salesorderdetail.xsd", "Salesorderdetail.xml"
end with
set objBL=Nothing
This solution works best when importing large amounts of data, otherwise, I
recommend using an "Execute SQL task" to call a stored procedure that uses
OPENXML.
"maxzsim via webservertalk.com" wrote:
> Hi ,
> I am not able to see SQLXML in the Data sources , how can i get that out
,
> wat do i need to install ?
> appreciate ur advice
> tks & rdgs
> Sal Young wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1
>|||Hi,
search for OPENXML in the SQL Manual.
There you'll get an good example.
Achim
"maxzsim via webservertalk.com" <u14644@.uwe> schrieb im Newsbeitrag
news:5b4210d93706a@.uwe...
> Hi,
> How could i actually import an XML file directly into SQL Server 2000 from
> the EM or Query Analyzer
> i tried right-click on the tables --> import data but what should the data
> source be ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1|||tks u ppl very much =)
Achim Stein [MCT] wrote:
>Hi,
>search for OPENXML in the SQL Manual.
>There you'll get an good example.
>Achim
>
>[quoted text clipped - 7 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1
How could i actually import an XML file directly into SQL Server 2000 from
the EM or Query Analyzer
i tried right-click on the tables --> import data but what should the data
source be ?
appreciate ur advise
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1Look into SQLXML. So far it is the best way to insert large amounts of XML
data into a table.
"maxzsim via webservertalk.com" wrote:
> Hi,
> How could i actually import an XML file directly into SQL Server 2000 fro
m
> the EM or Query Analyzer
> i tried right-click on the tables --> import data but what should the dat
a
> source be ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1
>|||Hi ,
I am not able to see SQLXML in the Data sources , how can i get that out ,
wat do i need to install ?
appreciate ur advice
tks & rdgs
Sal Young wrote:
>Look into SQLXML. So far it is the best way to insert large amounts of XML
>data into a table.
>
>[quoted text clipped - 7 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1|||This is one of the examples included in the documentation of SQLXML add-on
component. You'll need your xml file and the schema (xsd) file.
Follow these steps:
1. Go to
http://www.microsoft.com/downloads/...&displaylang=en
and download the SQLXML add-on component. Install it on the machine where
you're going to run the DTS package.
2. Create a new DTS package and add an "ActiveX Script Task"
3. Insert the following code inside the Main() function:
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
with objBL
.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=dev;integrated
security=SSPI"
.ErrorLogFile = "C:\error.log"
.SchemaGen = true
.Execute "Salesorderdetail.xsd", "Salesorderdetail.xml"
end with
set objBL=Nothing
This solution works best when importing large amounts of data, otherwise, I
recommend using an "Execute SQL task" to call a stored procedure that uses
OPENXML.
"maxzsim via webservertalk.com" wrote:
> Hi ,
> I am not able to see SQLXML in the Data sources , how can i get that out
,
> wat do i need to install ?
> appreciate ur advice
> tks & rdgs
> Sal Young wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1
>|||Hi,
search for OPENXML in the SQL Manual.
There you'll get an good example.
Achim
"maxzsim via webservertalk.com" <u14644@.uwe> schrieb im Newsbeitrag
news:5b4210d93706a@.uwe...
> Hi,
> How could i actually import an XML file directly into SQL Server 2000 from
> the EM or Query Analyzer
> i tried right-click on the tables --> import data but what should the data
> source be ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1|||tks u ppl very much =)
Achim Stein [MCT] wrote:
>Hi,
>search for OPENXML in the SQL Manual.
>There you'll get an good example.
>Achim
>
>[quoted text clipped - 7 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200602/1
importing of XML files into SQL 2000
Hi,
How could i actually import an XML file directly into SQL Server 2000 from
the EM or Query Analyzer
i tried right-click on the tables --> import data but what should the data
source be ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...r-xml/200602/1
Look into SQLXML. So far it is the best way to insert large amounts of XML
data into a table.
"maxzsim via droptable.com" wrote:
> Hi,
> How could i actually import an XML file directly into SQL Server 2000 from
> the EM or Query Analyzer
> i tried right-click on the tables --> import data but what should the data
> source be ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...r-xml/200602/1
>
|||Hi ,
I am not able to see SQLXML in the Data sources , how can i get that out ,
wat do i need to install ?
appreciate ur advice
tks & rdgs
Sal Young wrote:[vbcol=seagreen]
>Look into SQLXML. So far it is the best way to insert large amounts of XML
>data into a table.
>[quoted text clipped - 7 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...r-xml/200602/1
|||This is one of the examples included in the documentation of SQLXML add-on
component. You'll need your xml file and the schema (xsd) file.
Follow these steps:
1. Go to
http://www.microsoft.com/downloads/d...displaylang=en
and download the SQLXML add-on component. Install it on the machine where
you're going to run the DTS package.
2. Create a new DTS package and add an "ActiveX Script Task"
3. Insert the following code inside the Main() function:
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
with objBL
.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=dev;integrated security=SSPI"
.ErrorLogFile = "C:\error.log"
.SchemaGen = true
.Execute "Salesorderdetail.xsd", "Salesorderdetail.xml"
end with
set objBL=Nothing
This solution works best when importing large amounts of data, otherwise, I
recommend using an "Execute SQL task" to call a stored procedure that uses
OPENXML.
"maxzsim via droptable.com" wrote:
> Hi ,
> I am not able to see SQLXML in the Data sources , how can i get that out ,
> wat do i need to install ?
> appreciate ur advice
> tks & rdgs
> Sal Young wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...r-xml/200602/1
>
|||Hi,
search for OPENXML in the SQL Manual.
There you'll get an good example.
Achim
"maxzsim via droptable.com" <u14644@.uwe> schrieb im Newsbeitrag
news:5b4210d93706a@.uwe...
> Hi,
> How could i actually import an XML file directly into SQL Server 2000 from
> the EM or Query Analyzer
> i tried right-click on the tables --> import data but what should the data
> source be ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...r-xml/200602/1
|||tks u ppl very much =)
Achim Stein [MCT] wrote:[vbcol=seagreen]
>Hi,
>search for OPENXML in the SQL Manual.
>There you'll get an good example.
>Achim
>[quoted text clipped - 7 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...r-xml/200602/1
sql
How could i actually import an XML file directly into SQL Server 2000 from
the EM or Query Analyzer
i tried right-click on the tables --> import data but what should the data
source be ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...r-xml/200602/1
Look into SQLXML. So far it is the best way to insert large amounts of XML
data into a table.
"maxzsim via droptable.com" wrote:
> Hi,
> How could i actually import an XML file directly into SQL Server 2000 from
> the EM or Query Analyzer
> i tried right-click on the tables --> import data but what should the data
> source be ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...r-xml/200602/1
>
|||Hi ,
I am not able to see SQLXML in the Data sources , how can i get that out ,
wat do i need to install ?
appreciate ur advice
tks & rdgs
Sal Young wrote:[vbcol=seagreen]
>Look into SQLXML. So far it is the best way to insert large amounts of XML
>data into a table.
>[quoted text clipped - 7 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...r-xml/200602/1
|||This is one of the examples included in the documentation of SQLXML add-on
component. You'll need your xml file and the schema (xsd) file.
Follow these steps:
1. Go to
http://www.microsoft.com/downloads/d...displaylang=en
and download the SQLXML add-on component. Install it on the machine where
you're going to run the DTS package.
2. Create a new DTS package and add an "ActiveX Script Task"
3. Insert the following code inside the Main() function:
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
with objBL
.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=dev;integrated security=SSPI"
.ErrorLogFile = "C:\error.log"
.SchemaGen = true
.Execute "Salesorderdetail.xsd", "Salesorderdetail.xml"
end with
set objBL=Nothing
This solution works best when importing large amounts of data, otherwise, I
recommend using an "Execute SQL task" to call a stored procedure that uses
OPENXML.
"maxzsim via droptable.com" wrote:
> Hi ,
> I am not able to see SQLXML in the Data sources , how can i get that out ,
> wat do i need to install ?
> appreciate ur advice
> tks & rdgs
> Sal Young wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...r-xml/200602/1
>
|||Hi,
search for OPENXML in the SQL Manual.
There you'll get an good example.
Achim
"maxzsim via droptable.com" <u14644@.uwe> schrieb im Newsbeitrag
news:5b4210d93706a@.uwe...
> Hi,
> How could i actually import an XML file directly into SQL Server 2000 from
> the EM or Query Analyzer
> i tried right-click on the tables --> import data but what should the data
> source be ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...r-xml/200602/1
|||tks u ppl very much =)
Achim Stein [MCT] wrote:[vbcol=seagreen]
>Hi,
>search for OPENXML in the SQL Manual.
>There you'll get an good example.
>Achim
>[quoted text clipped - 7 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...r-xml/200602/1
sql
Importing MS Access queries into SQLServer?
Hello,
I have an Access db with several tables and several queries defined. Is
there a way to import the results of a query into SQLServer, or do I first
have to save the result of the query as a table and import that?
Thanks.
You might find it easier to perform a make table and then import the
results.
Another solution that you might be able to look at would involve moving the
query into SQL Server and let SQL Server (not MS Access) combine the data
and create (or populate) a table.
Keith
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:%23TG4NxcwEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have an Access db with several tables and several queries defined. Is
> there a way to import the results of a query into SQLServer, or do I first
> have to save the result of the query as a table and import that?
> Thanks.
>
|||Unfortunately, moving the queries into SQLServer is not an option.
Thanks for the reply.
> You might find it easier to perform a make table and then import the
> results.
> Another solution that you might be able to look at would involve moving
the
> query into SQL Server and let SQL Server (not MS Access) combine the data
> and create (or populate) a table.
|||Hi,
Have you tried "Import and Export Data" in SQL Server? What's your
concerns? Would you please show us more detailed scenario about your issue?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Our application imports the user's data (vendors and customers, for example)
from their datasources into SQLServer for the application to use. The user
can select to import from Access, Excel, or an ODBC datasource. When Access
is selected, we create an OleDbConnection object; the ConnectionString
property is something like:
@."Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data\SrcDataSmall.mdb;User Id=;Password=;""
To get the list of tables in the db, we call:
DataTable schemaTable =
dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.T ables, new object[] {null,
null, null, "TABLE"});
Is there a way to get a list of queries in the db, and import the result set
as though it were a table?
Thanks for your help.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:FQ5ZqQlwEHA.3984@.cpmsftngxa10.phx.gbl...
> Hi,
> Have you tried "Import and Export Data" in SQL Server? What's your
> concerns? Would you please show us more detailed scenario about your
issue?
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Developer,
The following statement returns all the views in an Access database.
DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
object[] {null, null, null, "VIEW"});
HTH.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
|||Thank you, Kevin, I'll try that. As I looked in to GetOleDbSchemaTable(), I
wondered if "View" was what I needed to use.
"Kevin Yu [MSFT]" <v-kevy@.online.microsoft.com> wrote in message
news:$0skStjxEHA.1884@.cpmsftngxa10.phx.gbl...
> Hi Developer,
> The following statement returns all the views in an Access database.
> DataTable dt =
> this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
> object[] {null, null, null, "VIEW"});
> HTH.
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
|||Hi Developer,
I would like to follow up on this issue and see if any progress has been
made. I haven't heard from you in 2 days, were you able to check my reply?
Should you have any questions, please feel free to post here.
Looking forward to your reply!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I will use Kevin's suggestion:
DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
object[] {null, null, null, "VIEW"});
Thanks for your help.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:eRfbpKKyEHA.764@.cpmsftngxa10.phx.gbl...
> Hi Developer,
> I would like to follow up on this issue and see if any progress has been
> made. I haven't heard from you in 2 days, were you able to check my reply?
> Should you have any questions, please feel free to post here.
> Looking forward to your reply!
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi,
Great to hear that, I am just checking whether Kevin's suggestion work fine
for you and it seems it does :-)
Free feel to let us know whenever you have any questions or concnerns, we
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
I have an Access db with several tables and several queries defined. Is
there a way to import the results of a query into SQLServer, or do I first
have to save the result of the query as a table and import that?
Thanks.
You might find it easier to perform a make table and then import the
results.
Another solution that you might be able to look at would involve moving the
query into SQL Server and let SQL Server (not MS Access) combine the data
and create (or populate) a table.
Keith
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:%23TG4NxcwEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have an Access db with several tables and several queries defined. Is
> there a way to import the results of a query into SQLServer, or do I first
> have to save the result of the query as a table and import that?
> Thanks.
>
|||Unfortunately, moving the queries into SQLServer is not an option.
Thanks for the reply.
> You might find it easier to perform a make table and then import the
> results.
> Another solution that you might be able to look at would involve moving
the
> query into SQL Server and let SQL Server (not MS Access) combine the data
> and create (or populate) a table.
|||Hi,
Have you tried "Import and Export Data" in SQL Server? What's your
concerns? Would you please show us more detailed scenario about your issue?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Our application imports the user's data (vendors and customers, for example)
from their datasources into SQLServer for the application to use. The user
can select to import from Access, Excel, or an ODBC datasource. When Access
is selected, we create an OleDbConnection object; the ConnectionString
property is something like:
@."Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data\SrcDataSmall.mdb;User Id=;Password=;""
To get the list of tables in the db, we call:
DataTable schemaTable =
dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.T ables, new object[] {null,
null, null, "TABLE"});
Is there a way to get a list of queries in the db, and import the result set
as though it were a table?
Thanks for your help.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:FQ5ZqQlwEHA.3984@.cpmsftngxa10.phx.gbl...
> Hi,
> Have you tried "Import and Export Data" in SQL Server? What's your
> concerns? Would you please show us more detailed scenario about your
issue?
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Developer,
The following statement returns all the views in an Access database.
DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
object[] {null, null, null, "VIEW"});
HTH.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
|||Thank you, Kevin, I'll try that. As I looked in to GetOleDbSchemaTable(), I
wondered if "View" was what I needed to use.
"Kevin Yu [MSFT]" <v-kevy@.online.microsoft.com> wrote in message
news:$0skStjxEHA.1884@.cpmsftngxa10.phx.gbl...
> Hi Developer,
> The following statement returns all the views in an Access database.
> DataTable dt =
> this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
> object[] {null, null, null, "VIEW"});
> HTH.
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
|||Hi Developer,
I would like to follow up on this issue and see if any progress has been
made. I haven't heard from you in 2 days, were you able to check my reply?
Should you have any questions, please feel free to post here.
Looking forward to your reply!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I will use Kevin's suggestion:
DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
object[] {null, null, null, "VIEW"});
Thanks for your help.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:eRfbpKKyEHA.764@.cpmsftngxa10.phx.gbl...
> Hi Developer,
> I would like to follow up on this issue and see if any progress has been
> made. I haven't heard from you in 2 days, were you able to check my reply?
> Should you have any questions, please feel free to post here.
> Looking forward to your reply!
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi,
Great to hear that, I am just checking whether Kevin's suggestion work fine
for you and it seems it does :-)
Free feel to let us know whenever you have any questions or concnerns, we
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Wednesday, March 21, 2012
Importing from a text file
I am extremly new to the SQL serve environment and am trying to import data
from a text file to a table. I figured out how to write an import query and
make table query but I can't seem to be able to figure out how to import dat
a
from a plain old text file. The fields in the query should be something
similiar to the fields in the make table query. Any suggestions?
CREATE TABLE REP
(REP_NUM CHAR(2) PRIMARY KEY,
LAST_NAME CHAR(15) NOT NULL,
FIRST_NAME CHAR(15) NOT NULL,
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
COMMISSION DECIMAL(7,2),
RATE DECIMAL(3,2));Darrin
It is just an example. I hope you will get an idea
CREATE TABLE TmpStList
(
stFName varchar (10) NOT NULL,
stLName varchar (10) NOT NULL,
stEmail varchar (30) NOT NULL
)
go
The data file (hawk.dat):
"Kelly","Reynold","kelly@.reynold.com"
"John","Smith","bill@.smith.com"
"Sara","Parker","sara@.parker.com"
The format file (hawk.bcp):
8.0
4
1 SQLCHAR 0 1 "\"" 0 first_quote ""
2 SQLCHAR 0 10 "\",\"" 1 stFName ""
3 SQLCHAR 0 10 "\",\"" 2 stLName ""
4 SQLCHAR 0 30 "\"\r\n" 3 stEmail ""
bulk insert TmpStList from 'C:\Staging\hawk.dat'
with (formatfile = 'C:\Staging\hawk.bcp')
select * from TmpStList
"Darrin" <Darrin@.discussions.microsoft.com> wrote in message
news:0C357973-085A-4233-B005-8361E0686B69@.microsoft.com...
>I am extremly new to the SQL serve environment and am trying to import data
> from a text file to a table. I figured out how to write an import query
> and
> make table query but I can't seem to be able to figure out how to import
> data
> from a plain old text file. The fields in the query should be something
> similiar to the fields in the make table query. Any suggestions?
> CREATE TABLE REP
> (REP_NUM CHAR(2) PRIMARY KEY,
> LAST_NAME CHAR(15) NOT NULL,
> FIRST_NAME CHAR(15) NOT NULL,
> STREET CHAR(15),
> CITY CHAR(15),
> STATE CHAR(2),
> ZIP CHAR(5),
> COMMISSION DECIMAL(7,2),
> RATE DECIMAL(3,2));
from a text file to a table. I figured out how to write an import query and
make table query but I can't seem to be able to figure out how to import dat
a
from a plain old text file. The fields in the query should be something
similiar to the fields in the make table query. Any suggestions?
CREATE TABLE REP
(REP_NUM CHAR(2) PRIMARY KEY,
LAST_NAME CHAR(15) NOT NULL,
FIRST_NAME CHAR(15) NOT NULL,
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
COMMISSION DECIMAL(7,2),
RATE DECIMAL(3,2));Darrin
It is just an example. I hope you will get an idea
CREATE TABLE TmpStList
(
stFName varchar (10) NOT NULL,
stLName varchar (10) NOT NULL,
stEmail varchar (30) NOT NULL
)
go
The data file (hawk.dat):
"Kelly","Reynold","kelly@.reynold.com"
"John","Smith","bill@.smith.com"
"Sara","Parker","sara@.parker.com"
The format file (hawk.bcp):
8.0
4
1 SQLCHAR 0 1 "\"" 0 first_quote ""
2 SQLCHAR 0 10 "\",\"" 1 stFName ""
3 SQLCHAR 0 10 "\",\"" 2 stLName ""
4 SQLCHAR 0 30 "\"\r\n" 3 stEmail ""
bulk insert TmpStList from 'C:\Staging\hawk.dat'
with (formatfile = 'C:\Staging\hawk.bcp')
select * from TmpStList
"Darrin" <Darrin@.discussions.microsoft.com> wrote in message
news:0C357973-085A-4233-B005-8361E0686B69@.microsoft.com...
>I am extremly new to the SQL serve environment and am trying to import data
> from a text file to a table. I figured out how to write an import query
> and
> make table query but I can't seem to be able to figure out how to import
> data
> from a plain old text file. The fields in the query should be something
> similiar to the fields in the make table query. Any suggestions?
> CREATE TABLE REP
> (REP_NUM CHAR(2) PRIMARY KEY,
> LAST_NAME CHAR(15) NOT NULL,
> FIRST_NAME CHAR(15) NOT NULL,
> STREET CHAR(15),
> CITY CHAR(15),
> STATE CHAR(2),
> ZIP CHAR(5),
> COMMISSION DECIMAL(7,2),
> RATE DECIMAL(3,2));
Subscribe to:
Posts (Atom)