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 store. Show all posts
Showing posts with label store. 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.
Monday, March 26, 2012
Importing Stored Procedures
Hi,
I have previously exported about 10 store procedures as PRC files, how would
I import them back into a SQL Server database?
Thanks.
Eric
If the files are from generating scripts in Enterprise
Manager, the PRC files are just text files of the scripts
for your stored procedures. You can open them in Query
Analyzer and execute the scripts to recreate your stored
procedures.
-Sue
On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Hi,
>I have previously exported about 10 store procedures as PRC files, how would
>I import them back into a SQL Server database?
>Thanks.
>Eric
|||Thanks, Sue.
Yes, it was created from generating scripts.
Is this the official way to import files that have been created via
generated scripts? It appears that there is an export tool, but no import
tool.
Eric
"Sue Hoegemeier" wrote:
> If the files are from generating scripts in Enterprise
> Manager, the PRC files are just text files of the scripts
> for your stored procedures. You can open them in Query
> Analyzer and execute the scripts to recreate your stored
> procedures.
> -Sue
> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
> <Eric@.discussions.microsoft.com> wrote:
>
>
|||Eric,
When you generate a script or scripts from Enterprise
Manager, it just creates SQL statements in the file or
files. You don't import scripts but you execute them. So
that's how you would do it with scripts. Generating scripts
isn't really an import/export tool.
If you are looking to move the database objects themselves
as opposed to scripting the objects, you would use DTS and
the Copy SQL Server objects tasks. Much of what that task
does under the covers is execute SQL scripts for you.
When you use which one or which one is better depends upon
what you are trying to do. Keep in mind though that most of
the objects you are working with are created using SQL so
that's a safe route to go. If you are use to working with
the database objects in Enterprise Manager, it's really just
executing SQL statements for you.
-Sue
On Mon, 31 Jan 2005 17:23:02 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks, Sue.
>Yes, it was created from generating scripts.
>Is this the official way to import files that have been created via
>generated scripts? It appears that there is an export tool, but no import
>tool.
>Eric
>"Sue Hoegemeier" wrote:
sql
I have previously exported about 10 store procedures as PRC files, how would
I import them back into a SQL Server database?
Thanks.
Eric
If the files are from generating scripts in Enterprise
Manager, the PRC files are just text files of the scripts
for your stored procedures. You can open them in Query
Analyzer and execute the scripts to recreate your stored
procedures.
-Sue
On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Hi,
>I have previously exported about 10 store procedures as PRC files, how would
>I import them back into a SQL Server database?
>Thanks.
>Eric
|||Thanks, Sue.
Yes, it was created from generating scripts.
Is this the official way to import files that have been created via
generated scripts? It appears that there is an export tool, but no import
tool.
Eric
"Sue Hoegemeier" wrote:
> If the files are from generating scripts in Enterprise
> Manager, the PRC files are just text files of the scripts
> for your stored procedures. You can open them in Query
> Analyzer and execute the scripts to recreate your stored
> procedures.
> -Sue
> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
> <Eric@.discussions.microsoft.com> wrote:
>
>
|||Eric,
When you generate a script or scripts from Enterprise
Manager, it just creates SQL statements in the file or
files. You don't import scripts but you execute them. So
that's how you would do it with scripts. Generating scripts
isn't really an import/export tool.
If you are looking to move the database objects themselves
as opposed to scripting the objects, you would use DTS and
the Copy SQL Server objects tasks. Much of what that task
does under the covers is execute SQL scripts for you.
When you use which one or which one is better depends upon
what you are trying to do. Keep in mind though that most of
the objects you are working with are created using SQL so
that's a safe route to go. If you are use to working with
the database objects in Enterprise Manager, it's really just
executing SQL statements for you.
-Sue
On Mon, 31 Jan 2005 17:23:02 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks, Sue.
>Yes, it was created from generating scripts.
>Is this the official way to import files that have been created via
>generated scripts? It appears that there is an export tool, but no import
>tool.
>Eric
>"Sue Hoegemeier" wrote:
sql
Importing Stored Procedures
Hi,
I have previously exported about 10 store procedures as PRC files, how would
I import them back into a SQL Server database?
Thanks.
EricIf the files are from generating scripts in Enterprise
Manager, the PRC files are just text files of the scripts
for your stored procedures. You can open them in Query
Analyzer and execute the scripts to recreate your stored
procedures.
-Sue
On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Hi,
>I have previously exported about 10 store procedures as PRC files, how would
>I import them back into a SQL Server database?
>Thanks.
>Eric|||Thanks, Sue.
Yes, it was created from generating scripts.
Is this the official way to import files that have been created via
generated scripts? It appears that there is an export tool, but no import
tool.
Eric
"Sue Hoegemeier" wrote:
> If the files are from generating scripts in Enterprise
> Manager, the PRC files are just text files of the scripts
> for your stored procedures. You can open them in Query
> Analyzer and execute the scripts to recreate your stored
> procedures.
> -Sue
> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
> <Eric@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have previously exported about 10 store procedures as PRC files, how would
> >I import them back into a SQL Server database?
> >
> >Thanks.
> >Eric
>|||Eric,
When you generate a script or scripts from Enterprise
Manager, it just creates SQL statements in the file or
files. You don't import scripts but you execute them. So
that's how you would do it with scripts. Generating scripts
isn't really an import/export tool.
If you are looking to move the database objects themselves
as opposed to scripting the objects, you would use DTS and
the Copy SQL Server objects tasks. Much of what that task
does under the covers is execute SQL scripts for you.
When you use which one or which one is better depends upon
what you are trying to do. Keep in mind though that most of
the objects you are working with are created using SQL so
that's a safe route to go. If you are use to working with
the database objects in Enterprise Manager, it's really just
executing SQL statements for you.
-Sue
On Mon, 31 Jan 2005 17:23:02 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Thanks, Sue.
>Yes, it was created from generating scripts.
>Is this the official way to import files that have been created via
>generated scripts? It appears that there is an export tool, but no import
>tool.
>Eric
>"Sue Hoegemeier" wrote:
>> If the files are from generating scripts in Enterprise
>> Manager, the PRC files are just text files of the scripts
>> for your stored procedures. You can open them in Query
>> Analyzer and execute the scripts to recreate your stored
>> procedures.
>> -Sue
>> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
>> <Eric@.discussions.microsoft.com> wrote:
>> >Hi,
>> >
>> >I have previously exported about 10 store procedures as PRC files, how would
>> >I import them back into a SQL Server database?
>> >
>> >Thanks.
>> >Eric
>>
I have previously exported about 10 store procedures as PRC files, how would
I import them back into a SQL Server database?
Thanks.
EricIf the files are from generating scripts in Enterprise
Manager, the PRC files are just text files of the scripts
for your stored procedures. You can open them in Query
Analyzer and execute the scripts to recreate your stored
procedures.
-Sue
On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Hi,
>I have previously exported about 10 store procedures as PRC files, how would
>I import them back into a SQL Server database?
>Thanks.
>Eric|||Thanks, Sue.
Yes, it was created from generating scripts.
Is this the official way to import files that have been created via
generated scripts? It appears that there is an export tool, but no import
tool.
Eric
"Sue Hoegemeier" wrote:
> If the files are from generating scripts in Enterprise
> Manager, the PRC files are just text files of the scripts
> for your stored procedures. You can open them in Query
> Analyzer and execute the scripts to recreate your stored
> procedures.
> -Sue
> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
> <Eric@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have previously exported about 10 store procedures as PRC files, how would
> >I import them back into a SQL Server database?
> >
> >Thanks.
> >Eric
>|||Eric,
When you generate a script or scripts from Enterprise
Manager, it just creates SQL statements in the file or
files. You don't import scripts but you execute them. So
that's how you would do it with scripts. Generating scripts
isn't really an import/export tool.
If you are looking to move the database objects themselves
as opposed to scripting the objects, you would use DTS and
the Copy SQL Server objects tasks. Much of what that task
does under the covers is execute SQL scripts for you.
When you use which one or which one is better depends upon
what you are trying to do. Keep in mind though that most of
the objects you are working with are created using SQL so
that's a safe route to go. If you are use to working with
the database objects in Enterprise Manager, it's really just
executing SQL statements for you.
-Sue
On Mon, 31 Jan 2005 17:23:02 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Thanks, Sue.
>Yes, it was created from generating scripts.
>Is this the official way to import files that have been created via
>generated scripts? It appears that there is an export tool, but no import
>tool.
>Eric
>"Sue Hoegemeier" wrote:
>> If the files are from generating scripts in Enterprise
>> Manager, the PRC files are just text files of the scripts
>> for your stored procedures. You can open them in Query
>> Analyzer and execute the scripts to recreate your stored
>> procedures.
>> -Sue
>> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
>> <Eric@.discussions.microsoft.com> wrote:
>> >Hi,
>> >
>> >I have previously exported about 10 store procedures as PRC files, how would
>> >I import them back into a SQL Server database?
>> >
>> >Thanks.
>> >Eric
>>
Importing Stored Procedures
Hi,
I have previously exported about 10 store procedures as PRC files, how would
I import them back into a SQL Server database?
Thanks.
EricIf the files are from generating scripts in Enterprise
Manager, the PRC files are just text files of the scripts
for your stored procedures. You can open them in Query
Analyzer and execute the scripts to recreate your stored
procedures.
-Sue
On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Hi,
>I have previously exported about 10 store procedures as PRC files, how woul
d
>I import them back into a SQL Server database?
>Thanks.
>Eric|||Thanks, Sue.
Yes, it was created from generating scripts.
Is this the official way to import files that have been created via
generated scripts? It appears that there is an export tool, but no import
tool.
Eric
"Sue Hoegemeier" wrote:
> If the files are from generating scripts in Enterprise
> Manager, the PRC files are just text files of the scripts
> for your stored procedures. You can open them in Query
> Analyzer and execute the scripts to recreate your stored
> procedures.
> -Sue
> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
> <Eric@.discussions.microsoft.com> wrote:
>
>|||Eric,
When you generate a script or scripts from Enterprise
Manager, it just creates SQL statements in the file or
files. You don't import scripts but you execute them. So
that's how you would do it with scripts. Generating scripts
isn't really an import/export tool.
If you are looking to move the database objects themselves
as opposed to scripting the objects, you would use DTS and
the Copy SQL Server objects tasks. Much of what that task
does under the covers is execute SQL scripts for you.
When you use which one or which one is better depends upon
what you are trying to do. Keep in mind though that most of
the objects you are working with are created using SQL so
that's a safe route to go. If you are use to working with
the database objects in Enterprise Manager, it's really just
executing SQL statements for you.
-Sue
On Mon, 31 Jan 2005 17:23:02 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks, Sue.
>Yes, it was created from generating scripts.
>Is this the official way to import files that have been created via
>generated scripts? It appears that there is an export tool, but no import
>tool.
>Eric
>"Sue Hoegemeier" wrote:
>
I have previously exported about 10 store procedures as PRC files, how would
I import them back into a SQL Server database?
Thanks.
EricIf the files are from generating scripts in Enterprise
Manager, the PRC files are just text files of the scripts
for your stored procedures. You can open them in Query
Analyzer and execute the scripts to recreate your stored
procedures.
-Sue
On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>Hi,
>I have previously exported about 10 store procedures as PRC files, how woul
d
>I import them back into a SQL Server database?
>Thanks.
>Eric|||Thanks, Sue.
Yes, it was created from generating scripts.
Is this the official way to import files that have been created via
generated scripts? It appears that there is an export tool, but no import
tool.
Eric
"Sue Hoegemeier" wrote:
> If the files are from generating scripts in Enterprise
> Manager, the PRC files are just text files of the scripts
> for your stored procedures. You can open them in Query
> Analyzer and execute the scripts to recreate your stored
> procedures.
> -Sue
> On Mon, 31 Jan 2005 15:31:03 -0800, "Eric"
> <Eric@.discussions.microsoft.com> wrote:
>
>|||Eric,
When you generate a script or scripts from Enterprise
Manager, it just creates SQL statements in the file or
files. You don't import scripts but you execute them. So
that's how you would do it with scripts. Generating scripts
isn't really an import/export tool.
If you are looking to move the database objects themselves
as opposed to scripting the objects, you would use DTS and
the Copy SQL Server objects tasks. Much of what that task
does under the covers is execute SQL scripts for you.
When you use which one or which one is better depends upon
what you are trying to do. Keep in mind though that most of
the objects you are working with are created using SQL so
that's a safe route to go. If you are use to working with
the database objects in Enterprise Manager, it's really just
executing SQL statements for you.
-Sue
On Mon, 31 Jan 2005 17:23:02 -0800, "Eric"
<Eric@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks, Sue.
>Yes, it was created from generating scripts.
>Is this the official way to import files that have been created via
>generated scripts? It appears that there is an export tool, but no import
>tool.
>Eric
>"Sue Hoegemeier" wrote:
>
importing store procedures, How-to
Hi,
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server? Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once. Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.
Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.
Thanks
Pritchie
If someone knows how I can export the procedures then re-import them, it
would be appreciated.
Thanks
"Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server? Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once. Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
> Thanks
> Pritchie
>
>
|||Why not generate the scripts and then load into the new database? If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview. From there, you can grab the scripts. Then
go over to your new database and copy the script into the query window and
load. If in 2005, right click DB & choose Tasks, then Generate Scripts, then
choose what you need, etc.
"Shayaan Siddiqui" wrote:
> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
> Thanks
> "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
>
>
|||Hi CB,
Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them. Is this now the only way to
do it? In my mind its 2 steps back after 3 steps forward... :o(
Thanks for your help CB..
Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!! now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables). Such a shame that little things
like this are missed.
Regards
Steve
"CB" <CB@.discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> Why not generate the scripts and then load into the new database? If
2000,
> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview. From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
then[vbcol=seagreen]
> choose what you need, etc.
> "Shayaan Siddiqui" wrote:
and[vbcol=seagreen]
no[vbcol=seagreen]
|||Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.
If you'd like to use SSIS, you can still transfer objects. When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task". Double-click, then choose your source &
destination connections & databases. Scroll down to the "Objects to Copy"
property & expand it. Then click the StoredProceduresList collection (and
UDF, etc.) and choose the SPs you want to transfer. Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options). Then save & choose Debug, Start Debugging if
you want to run in design mode. Or save the package off to the server &
stick in a job. Good Luck!
"Pritchie" wrote:
> Hi CB,
> Yes, this can be done, however it has a far bigger admin overhead than just
> using the import/export task to transfer them. Is this now the only way to
> do it? In my mind its 2 steps back after 3 steps forward... :o(
> Thanks for your help CB..
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!! now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as in
> separating the system and user tables). Such a shame that little things
> like this are missed.
> Regards
> Steve
>
> "CB" <CB@.discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> 2000,
> Then
> then
> and
> no
>
>
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server? Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once. Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.
Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.
Thanks
Pritchie
If someone knows how I can export the procedures then re-import them, it
would be appreciated.
Thanks
"Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server? Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once. Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
> Thanks
> Pritchie
>
>
|||Why not generate the scripts and then load into the new database? If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview. From there, you can grab the scripts. Then
go over to your new database and copy the script into the query window and
load. If in 2005, right click DB & choose Tasks, then Generate Scripts, then
choose what you need, etc.
"Shayaan Siddiqui" wrote:
> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
> Thanks
> "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
>
>
|||Hi CB,
Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them. Is this now the only way to
do it? In my mind its 2 steps back after 3 steps forward... :o(
Thanks for your help CB..
Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!! now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables). Such a shame that little things
like this are missed.
Regards
Steve
"CB" <CB@.discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> Why not generate the scripts and then load into the new database? If
2000,
> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview. From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
then[vbcol=seagreen]
> choose what you need, etc.
> "Shayaan Siddiqui" wrote:
and[vbcol=seagreen]
no[vbcol=seagreen]
|||Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.
If you'd like to use SSIS, you can still transfer objects. When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task". Double-click, then choose your source &
destination connections & databases. Scroll down to the "Objects to Copy"
property & expand it. Then click the StoredProceduresList collection (and
UDF, etc.) and choose the SPs you want to transfer. Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options). Then save & choose Debug, Start Debugging if
you want to run in design mode. Or save the package off to the server &
stick in a job. Good Luck!
"Pritchie" wrote:
> Hi CB,
> Yes, this can be done, however it has a far bigger admin overhead than just
> using the import/export task to transfer them. Is this now the only way to
> do it? In my mind its 2 steps back after 3 steps forward... :o(
> Thanks for your help CB..
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!! now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as in
> separating the system and user tables). Such a shame that little things
> like this are missed.
> Regards
> Steve
>
> "CB" <CB@.discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> 2000,
> Then
> then
> and
> no
>
>
importing store procedures, How-to
Hi,
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server? Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once. Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.
Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.
Thanks
PritchieIf someone knows how I can export the procedures then re-import them, it
would be appreciated.
Thanks
"Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server? Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once. Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
> Thanks
> Pritchie
>
>|||Why not generate the scripts and then load into the new database? If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview. From there, you can grab the scripts. Then
go over to your new database and copy the script into the query window and
load. If in 2005, right click DB & choose Tasks, then Generate Scripts, the
n
choose what you need, etc.
"Shayaan Siddiqui" wrote:
> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
> Thanks
> "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
>
>|||Hi CB,
Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them. Is this now the only way to
do it? In my mind its 2 steps back after 3 steps forward... :o(
Thanks for your help CB..
Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!! now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables). Such a shame that little things
like this are missed.
Regards
Steve
"CB" <CB@.discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> Why not generate the scripts and then load into the new database? If
2000,
> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview. From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
then[vbcol=seagreen]
> choose what you need, etc.
> "Shayaan Siddiqui" wrote:
>
and[vbcol=seagreen]
no[vbcol=seagreen]|||Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.
If you'd like to use SSIS, you can still transfer objects. When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task". Double-click, then choose your source &
destination connections & databases. Scroll down to the "Objects to Copy"
property & expand it. Then click the StoredProceduresList collection (and
UDF, etc.) and choose the SPs you want to transfer. Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options). Then save & choose Debug, Start Debugging if
you want to run in design mode. Or save the package off to the server &
stick in a job. Good Luck!
"Pritchie" wrote:
> Hi CB,
> Yes, this can be done, however it has a far bigger admin overhead than jus
t
> using the import/export task to transfer them. Is this now the only way t
o
> do it? In my mind its 2 steps back after 3 steps forward... :o(
> Thanks for your help CB..
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!! now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as i
n
> separating the system and user tables). Such a shame that little things
> like this are missed.
> Regards
> Steve
>
> "CB" <CB@.discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> 2000,
> Then
> then
> and
> no
>
>sql
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server? Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once. Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.
Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.
Thanks
PritchieIf someone knows how I can export the procedures then re-import them, it
would be appreciated.
Thanks
"Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server? Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once. Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
> Thanks
> Pritchie
>
>|||Why not generate the scripts and then load into the new database? If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview. From there, you can grab the scripts. Then
go over to your new database and copy the script into the query window and
load. If in 2005, right click DB & choose Tasks, then Generate Scripts, the
n
choose what you need, etc.
"Shayaan Siddiqui" wrote:
> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
> Thanks
> "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
>
>|||Hi CB,
Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them. Is this now the only way to
do it? In my mind its 2 steps back after 3 steps forward... :o(
Thanks for your help CB..
Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!! now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables). Such a shame that little things
like this are missed.
Regards
Steve
"CB" <CB@.discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> Why not generate the scripts and then load into the new database? If
2000,
> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview. From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
then[vbcol=seagreen]
> choose what you need, etc.
> "Shayaan Siddiqui" wrote:
>
and[vbcol=seagreen]
no[vbcol=seagreen]|||Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.
If you'd like to use SSIS, you can still transfer objects. When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task". Double-click, then choose your source &
destination connections & databases. Scroll down to the "Objects to Copy"
property & expand it. Then click the StoredProceduresList collection (and
UDF, etc.) and choose the SPs you want to transfer. Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options). Then save & choose Debug, Start Debugging if
you want to run in design mode. Or save the package off to the server &
stick in a job. Good Luck!
"Pritchie" wrote:
> Hi CB,
> Yes, this can be done, however it has a far bigger admin overhead than jus
t
> using the import/export task to transfer them. Is this now the only way t
o
> do it? In my mind its 2 steps back after 3 steps forward... :o(
> Thanks for your help CB..
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!! now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as i
n
> separating the system and user tables). Such a shame that little things
> like this are missed.
> Regards
> Steve
>
> "CB" <CB@.discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> 2000,
> Then
> then
> and
> no
>
>sql
importing store procedures, How-to
Hi,
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server? Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once. Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.
Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.
Thanks
PritchieIf someone knows how I can export the procedures then re-import them, it
would be appreciated.
Thanks
"Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server? Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once. Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
> Thanks
> Pritchie
>
>|||Why not generate the scripts and then load into the new database? If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview. From there, you can grab the scripts. Then
go over to your new database and copy the script into the query window and
load. If in 2005, right click DB & choose Tasks, then Generate Scripts, then
choose what you need, etc.
"Shayaan Siddiqui" wrote:
> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
> Thanks
> "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > Hi,
> > Can anyone tell me how I can import/export stored procedures in MS SQL
> > 2005
> > from a different SQL server? Used to use the right click menu on a
> > database
> > (tasks, import/export) to do the job using DTS, and update all the SP and
> > UDF at once. Now I cannot seem to see a way of doing it using IS
> > (Integration Services) in 2005, only able to import/export tables and
> > views.
> >
> > Can anyone please advise, there's got to be an easy way, but I've had no
> > luck looking on the web or books online.
> >
> > Thanks
> > Pritchie
> >
> >
> >
>
>|||Hi CB,
Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them. Is this now the only way to
do it? In my mind its 2 steps back after 3 steps forward... :o(
Thanks for your help CB..
Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!! now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables). Such a shame that little things
like this are missed.
Regards
Steve
"CB" <CB@.discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> Why not generate the scripts and then load into the new database? If
2000,
> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview. From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
then
> choose what you need, etc.
> "Shayaan Siddiqui" wrote:
> > If someone knows how I can export the procedures then re-import them, it
> > would be appreciated.
> >
> > Thanks
> >
> > "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> > message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > > Hi,
> > > Can anyone tell me how I can import/export stored procedures in MS SQL
> > > 2005
> > > from a different SQL server? Used to use the right click menu on a
> > > database
> > > (tasks, import/export) to do the job using DTS, and update all the SP
and
> > > UDF at once. Now I cannot seem to see a way of doing it using IS
> > > (Integration Services) in 2005, only able to import/export tables and
> > > views.
> > >
> > > Can anyone please advise, there's got to be an easy way, but I've had
no
> > > luck looking on the web or books online.
> > >
> > > Thanks
> > > Pritchie
> > >
> > >
> > >
> >
> >
> >|||Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.
If you'd like to use SSIS, you can still transfer objects. When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task". Double-click, then choose your source &
destination connections & databases. Scroll down to the "Objects to Copy"
property & expand it. Then click the StoredProceduresList collection (and
UDF, etc.) and choose the SPs you want to transfer. Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options). Then save & choose Debug, Start Debugging if
you want to run in design mode. Or save the package off to the server &
stick in a job. Good Luck!
"Pritchie" wrote:
> Hi CB,
> Yes, this can be done, however it has a far bigger admin overhead than just
> using the import/export task to transfer them. Is this now the only way to
> do it? In my mind its 2 steps back after 3 steps forward... :o(
> Thanks for your help CB..
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!! now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as in
> separating the system and user tables). Such a shame that little things
> like this are missed.
> Regards
> Steve
>
> "CB" <CB@.discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> > Why not generate the scripts and then load into the new database? If
> 2000,
> > right click DB & choose "Generate SQL Script...", then choose stored
> > procedures, and click preview. From there, you can grab the scripts.
> Then
> > go over to your new database and copy the script into the query window and
> > load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
> then
> > choose what you need, etc.
> >
> > "Shayaan Siddiqui" wrote:
> >
> > > If someone knows how I can export the procedures then re-import them, it
> > > would be appreciated.
> > >
> > > Thanks
> > >
> > > "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> > > message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > > > Hi,
> > > > Can anyone tell me how I can import/export stored procedures in MS SQL
> > > > 2005
> > > > from a different SQL server? Used to use the right click menu on a
> > > > database
> > > > (tasks, import/export) to do the job using DTS, and update all the SP
> and
> > > > UDF at once. Now I cannot seem to see a way of doing it using IS
> > > > (Integration Services) in 2005, only able to import/export tables and
> > > > views.
> > > >
> > > > Can anyone please advise, there's got to be an easy way, but I've had
> no
> > > > luck looking on the web or books online.
> > > >
> > > > Thanks
> > > > Pritchie
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server? Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once. Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.
Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.
Thanks
PritchieIf someone knows how I can export the procedures then re-import them, it
would be appreciated.
Thanks
"Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server? Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once. Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
> Thanks
> Pritchie
>
>|||Why not generate the scripts and then load into the new database? If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview. From there, you can grab the scripts. Then
go over to your new database and copy the script into the query window and
load. If in 2005, right click DB & choose Tasks, then Generate Scripts, then
choose what you need, etc.
"Shayaan Siddiqui" wrote:
> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
> Thanks
> "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > Hi,
> > Can anyone tell me how I can import/export stored procedures in MS SQL
> > 2005
> > from a different SQL server? Used to use the right click menu on a
> > database
> > (tasks, import/export) to do the job using DTS, and update all the SP and
> > UDF at once. Now I cannot seem to see a way of doing it using IS
> > (Integration Services) in 2005, only able to import/export tables and
> > views.
> >
> > Can anyone please advise, there's got to be an easy way, but I've had no
> > luck looking on the web or books online.
> >
> > Thanks
> > Pritchie
> >
> >
> >
>
>|||Hi CB,
Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them. Is this now the only way to
do it? In my mind its 2 steps back after 3 steps forward... :o(
Thanks for your help CB..
Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!! now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables). Such a shame that little things
like this are missed.
Regards
Steve
"CB" <CB@.discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> Why not generate the scripts and then load into the new database? If
2000,
> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview. From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
then
> choose what you need, etc.
> "Shayaan Siddiqui" wrote:
> > If someone knows how I can export the procedures then re-import them, it
> > would be appreciated.
> >
> > Thanks
> >
> > "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> > message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > > Hi,
> > > Can anyone tell me how I can import/export stored procedures in MS SQL
> > > 2005
> > > from a different SQL server? Used to use the right click menu on a
> > > database
> > > (tasks, import/export) to do the job using DTS, and update all the SP
and
> > > UDF at once. Now I cannot seem to see a way of doing it using IS
> > > (Integration Services) in 2005, only able to import/export tables and
> > > views.
> > >
> > > Can anyone please advise, there's got to be an easy way, but I've had
no
> > > luck looking on the web or books online.
> > >
> > > Thanks
> > > Pritchie
> > >
> > >
> > >
> >
> >
> >|||Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.
If you'd like to use SSIS, you can still transfer objects. When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task". Double-click, then choose your source &
destination connections & databases. Scroll down to the "Objects to Copy"
property & expand it. Then click the StoredProceduresList collection (and
UDF, etc.) and choose the SPs you want to transfer. Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options). Then save & choose Debug, Start Debugging if
you want to run in design mode. Or save the package off to the server &
stick in a job. Good Luck!
"Pritchie" wrote:
> Hi CB,
> Yes, this can be done, however it has a far bigger admin overhead than just
> using the import/export task to transfer them. Is this now the only way to
> do it? In my mind its 2 steps back after 3 steps forward... :o(
> Thanks for your help CB..
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!! now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as in
> separating the system and user tables). Such a shame that little things
> like this are missed.
> Regards
> Steve
>
> "CB" <CB@.discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD-0CB9C36E4F01@.microsoft.com...
> > Why not generate the scripts and then load into the new database? If
> 2000,
> > right click DB & choose "Generate SQL Script...", then choose stored
> > procedures, and click preview. From there, you can grab the scripts.
> Then
> > go over to your new database and copy the script into the query window and
> > load. If in 2005, right click DB & choose Tasks, then Generate Scripts,
> then
> > choose what you need, etc.
> >
> > "Shayaan Siddiqui" wrote:
> >
> > > If someone knows how I can export the procedures then re-import them, it
> > > would be appreciated.
> > >
> > > Thanks
> > >
> > > "Pritchie" <info2005@.remove-this-including-dot.bigbunker.com> wrote in
> > > message news:zXzWf.38338$u31.6894@.newsfe2-win.ntli.net...
> > > > Hi,
> > > > Can anyone tell me how I can import/export stored procedures in MS SQL
> > > > 2005
> > > > from a different SQL server? Used to use the right click menu on a
> > > > database
> > > > (tasks, import/export) to do the job using DTS, and update all the SP
> and
> > > > UDF at once. Now I cannot seem to see a way of doing it using IS
> > > > (Integration Services) in 2005, only able to import/export tables and
> > > > views.
> > > >
> > > > Can anyone please advise, there's got to be an easy way, but I've had
> no
> > > > luck looking on the web or books online.
> > > >
> > > > Thanks
> > > > Pritchie
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
Monday, March 12, 2012
Importing data using WebService
Hello,
i'm searching a way to update data using a web service.
For example, i want to store the temprature of cities in a database. I have a table with all the cities and an other table storing the temperature.
I have access to à web service that give temperature for the cities...
i hope you understand what i want to do... ;-)
thanks a lotThe web service task can access the web service and store the SOAP envelope as an XML file locally. You can then access the XML file in a data flow task using the XML Source Adapter and use the data in your data-flow as you would any other data.
-Jamie|||Ok !
Where can i find a tutorial for using webservice task ?|||BOL should always be your first port of call. Try ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/5c7206f1-7d6a-4923-8dff-3c4912da4157.htm and if there isn't enough there to help use the "Send Feedback" link.
-Jamie|||It's not clear from the question whether the web service gives you address for a specific city or for all cities. Likely it's the former in which case you'd likely want to call the web service through the script component that wraps the SOAP call and does a little of it's own caching to avoid redundant web service calls.
If the shoe fits, feel free to wear it.|||Hi all,
I am using the web service task with XML source to insert data from a web service into an SQL Server database. But I have a problem with the XML output from the Web Service Task. It looks li this:
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfBooking xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
</ArrayOfBooking>
and because of this xmlns="http://tempuri.org/" that is met almost everywhere I cannot create a proper XSD Schema , that is needed for the XML source. If I remove this xmlns="http://tempuri.org/" , then everything works fine . The web service I am calling has this namespace xmlns="http://tempuri.org/" in the [WebService(Namespace = "http://tempuri.org/")] . I am writing the web service so I can change it as well if this is needed. Do you have any ideas how to solve this xmlns="http://tempuri.org/" problem?
Thanks in advance.
Regards,
pc
Importing data using WebService
Hello,
i'm searching a way to update data using a web service.
For example, i want to store the temprature of cities in a database. I have a table with all the cities and an other table storing the temperature.
I have access to à web service that give temperature for the cities...
i hope you understand what i want to do... ;-)
thanks a lotThe web service task can access the web service and store the SOAP envelope as an XML file locally. You can then access the XML file in a data flow task using the XML Source Adapter and use the data in your data-flow as you would any other data.
-Jamie|||Ok !
Where can i find a tutorial for using webservice task ?|||BOL should always be your first port of call. Try ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/5c7206f1-7d6a-4923-8dff-3c4912da4157.htm and if there isn't enough there to help use the "Send Feedback" link.
-Jamie|||It's not clear from the question whether the web service gives you address for a specific city or for all cities. Likely it's the former in which case you'd likely want to call the web service through the script component that wraps the SOAP call and does a little of it's own caching to avoid redundant web service calls.
If the shoe fits, feel free to wear it.|||Hi all,
I am using the web service task with XML source to insert data from a web service into an SQL Server database. But I have a problem with the XML output from the Web Service Task. It looks li this:
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfBooking xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
</ArrayOfBooking>
and because of this xmlns="http://tempuri.org/" that is met almost everywhere I cannot create a proper XSD Schema , that is needed for the XML source. If I remove this xmlns="http://tempuri.org/" , then everything works fine . The web service I am calling has this namespace xmlns="http://tempuri.org/" in the [WebService(Namespace = "http://tempuri.org/")] . I am writing the web service so I can change it as well if this is needed. Do you have any ideas how to solve this xmlns="http://tempuri.org/" problem?
Thanks in advance.
Regards,
pc
Friday, March 9, 2012
importing data from Oracle - Is there a limitations on the datatypes that can be
> Thanks,
Hard to answer a question that doesn't have more information, but YES there
are limits:
SQL Server can store 8-, 16- and 32-bit integers in a TINYINT, (Short int?)
INT-type column. Other forms of numeric data types (What Oracle calls
Number(x, y) or Numeric(x, y) or whatever it is...) can be stored in SQL
Server as float or decimal.
Dates can be stored. Ditto, on currency types.
Character data that contains text strings can be stored in SQL Server as
CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, or, if pain is something that
you enjoy -- Binary, or VarBinary.
SQL Server also has an IDENTITY type that sort-of fits with whatever Oracle
calls a number-generator-function -- that thing that auto-generates
surrogate keys.
What did you actually have in mind to do?
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSenseiThanks for the info. Need to import to MSSQL from different data source. I
found more info:
Supported data types
The .NET Managed Provider for Oracle supports the Oracle 8i-specific data
types such as CLOB, BLOB, NCLOB, and Bfile. The .NET Managed Provider for
Oracle 2.0 does not support the Oracle 9i XML data type or the Oracle 10g
data types Binary Float and Binary Double. (from
http://support.microsoft.com/kb/322158/en-us)
"Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
news:%23OKLbOlWGHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hard to answer a question that doesn't have more information, but YES
there
> are limits:
> SQL Server can store 8-, 16- and 32-bit integers in a TINYINT, (Short
int?)
> INT-type column. Other forms of numeric data types (What Oracle calls
> Number(x, y) or Numeric(x, y) or whatever it is...) can be stored in SQL
> Server as float or decimal.
> Dates can be stored. Ditto, on currency types.
> Character data that contains text strings can be stored in SQL Server as
> CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, or, if pain is something that
> you enjoy -- Binary, or VarBinary.
> SQL Server also has an IDENTITY type that sort-of fits with whatever
Oracle
> calls a number-generator-function -- that thing that auto-generates
> surrogate keys.
> What did you actually have in mind to do?
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>
Hard to answer a question that doesn't have more information, but YES there
are limits:
SQL Server can store 8-, 16- and 32-bit integers in a TINYINT, (Short int?)
INT-type column. Other forms of numeric data types (What Oracle calls
Number(x, y) or Numeric(x, y) or whatever it is...) can be stored in SQL
Server as float or decimal.
Dates can be stored. Ditto, on currency types.
Character data that contains text strings can be stored in SQL Server as
CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, or, if pain is something that
you enjoy -- Binary, or VarBinary.
SQL Server also has an IDENTITY type that sort-of fits with whatever Oracle
calls a number-generator-function -- that thing that auto-generates
surrogate keys.
What did you actually have in mind to do?
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSenseiThanks for the info. Need to import to MSSQL from different data source. I
found more info:
Supported data types
The .NET Managed Provider for Oracle supports the Oracle 8i-specific data
types such as CLOB, BLOB, NCLOB, and Bfile. The .NET Managed Provider for
Oracle 2.0 does not support the Oracle 9i XML data type or the Oracle 10g
data types Binary Float and Binary Double. (from
http://support.microsoft.com/kb/322158/en-us)
"Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
news:%23OKLbOlWGHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hard to answer a question that doesn't have more information, but YES
there
> are limits:
> SQL Server can store 8-, 16- and 32-bit integers in a TINYINT, (Short
int?)
> INT-type column. Other forms of numeric data types (What Oracle calls
> Number(x, y) or Numeric(x, y) or whatever it is...) can be stored in SQL
> Server as float or decimal.
> Dates can be stored. Ditto, on currency types.
> Character data that contains text strings can be stored in SQL Server as
> CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, or, if pain is something that
> you enjoy -- Binary, or VarBinary.
> SQL Server also has an IDENTITY type that sort-of fits with whatever
Oracle
> calls a number-generator-function -- that thing that auto-generates
> surrogate keys.
> What did you actually have in mind to do?
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>
Wednesday, March 7, 2012
Importing Data from Access 2002 in SQL Express
Hi All,
At the moment i have a piece of software that uses Access to store the data into a number of tables.
I am developing a new piece of software that has been built around SQL express and need to upgrade users that are using access. I have managed to write the installation to install etc and also the start of a script to insert all of the tables. I cant work out how to get the data into SQL express using a script.
Basically the access db (CentralDb) will be stored a a given location C:\Program Files\CentralDB.mdb
The tables within the SQL version are indentical to access but i need a way of pulling out the information stored and putting it into SQL, and as there is no DTS i am becoming stuck on how to automate the process.
Any ideas?
Thanks in advance
Martin
Hi,
here is a good article to refer http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx you may also use SSMA to migrate data from Access to SQL Express.
Hemantgiri S. Goswami
Importing Data from access 2000 in SQL
Hi All,
At the moment i have a piece of software that uses Access to store the data into a number of tables.
I am developing a new piece of software that has been built around SQL express and need to upgrade users that are using access. I have managed to write the installation to install etc and also the start of a script to insert all of the tables. I cant work out how to get the data into SQL express using a script.
Basically the access db (CentralDb) will be stored a a given location C:\Program Files\CentralDB.mdb
The tables within the SQL version are indentical to access but i need a way of pulling out the information stored and putting it into SQL, and as there is no DTS i am becoming stuck on how to automate the process.
Any ideas?
Thanks in advance
Martin
I do not know if you can write it with the help of query. Yes, if you can import the Access data, that would be great.
Well, you might like to write a small application for your data migration. The application should be able to read data from your access database and will push the data in SQL.
|||Create a "linked server" to the access file, and just use TSQL queries to pull the data from the linked server.|||Excuse my ignorance, but where can i find the linked server option ?(i am using SQL 2005 Managment Studio Express) and cant find the option to link servers (the version i am running also has no help!!)
Thanks Again
Martin
|||It is under: ServerName -> Server Objects -> Linked Servers. Right click and select "New Linked Server".Give it a name, like "oldaccess". Then select the MS Jet 4.0 OLEDB driver. Put in the full path of the Access database in the "Data Source".
To insert into your SQL table use:
INSERT INTO tablea
SELECT * FROM oldaccess.tablea
See this tutorial: http://www.databasejournal.com/features/mssql/article.php/3103331
Subscribe to:
Posts (Atom)