Sql Auto grow: sql

Showing posts with label sql. Show all posts

Thursday, March 29, 2012

Automation of Excel to SQL Server 2005

Hi all,

I have a task which needs to be automated.

1) Excel spreadsheet load into sql server database as a table.

2) Then , there will be some quering of data joining this table ( imported from excel) with other tables.

3) Then, output should be back into excel spreadsheet.

All these steps 1,2,3 need to be automated.

Could anybody give me the different steps(if not,, any links which guide me doing so), for doing this and any pre setup required for this.

Thanks for all your help..

You can do this using distributed quires...

Here the sample,

Code Snippet

--To fetch the data from the Excel

select * from

OpenDataSource('Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

--To store the data back to the Excel

insert into

OpenDataSource('Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

Values('11','Name11')

Automation of database restoration

We have sql server standard edition , I want to automate the task of data
restoration on a daily basis .
How can I automate copying and restoration of the data on standalone PC .
Kindly suggest me the best option for the same .
Regards,
SwatiI assume that you have two database servers and you want to keep them in
synch.
You have to do two things
BACKUP your database(s) on your primary server
RESTORE your backups to your standby server.
It is simple to set up a job which issues BACKUP commands. In fact you
should be doing that now as part of your backup routine.
The RESTORE is easy. Assuming that the account that SQL Server is running
as on your second server has rights to the machine where your primary
backups are stored you can do something like this: RESTORE DATABASE foo FROM
DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
If you have a share on the server you can access the share:
RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak' WITH
REPLACE
If your database servers have a different disk layout you might have to use
the WITH MOVE option to move the physical files to a location that works on
your secondary machine.
Once you are able to issue the appropriate BACKUP and RESTORE commands the
next thing you have to do is create a couple of jobs which will automate the
process for you. You can start a job (on another server) from your main
server, but the easiest method might be to have the jobs be independent of
each other. Schedule your backup job to run at, say 10pm. Lets say that it
takes 30 minutes. You could schedule the restore job on the other server to
start at 10:45pm.
You may find these articles helpful:
314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
http://support.microsoft.com/?id=314515
323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
Paper)
http://support.microsoft.com/?id=323135
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
http://support.microsoft.com/?id=325220
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
http://support.microsoft.com/?id=821786
321247 HOW TO: Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
Errors
http://support.microsoft.com/?id=329133
Keith
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> We have sql server standard edition , I want to automate the task of data
> restoration on a daily basis .
> How can I automate copying and restoration of the data on standalone PC .
> Kindly suggest me the best option for the same .
>
> Regards,
> Swati
>
>|||Keith,
Thanks for response.
I agree that we can write manual commands for restoring the database on
standby server . Let me explain you our backup stategy
Full backup : 12:00 AM
Diff backup : evry 2 hours
Txn log backup : every 15 minute .this is done using DB maintenance plan .
so every time we get a new file. e.g mydb_db_200410260000.bak
Now I want to restore my full backup and diff backup which is done at
interval of 2 hours . How can I restore my full db backup and diff backup.
Regards,
Swati
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
> I assume that you have two database servers and you want to keep them in
> synch.
> You have to do two things
> BACKUP your database(s) on your primary server
> RESTORE your backups to your standby server.
> It is simple to set up a job which issues BACKUP commands. In fact you
> should be doing that now as part of your backup routine.
> The RESTORE is easy. Assuming that the account that SQL Server is running
> as on your second server has rights to the machine where your primary
> backups are stored you can do something like this: RESTORE DATABASE foo
FROM
> DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
> If you have a share on the server you can access the share:
> RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak' WITH
> REPLACE
> If your database servers have a different disk layout you might have to
use
> the WITH MOVE option to move the physical files to a location that works
on
> your secondary machine.
> Once you are able to issue the appropriate BACKUP and RESTORE commands the
> next thing you have to do is create a couple of jobs which will automate
the
> process for you. You can start a job (on another server) from your main
> server, but the easiest method might be to have the jobs be independent of
> each other. Schedule your backup job to run at, say 10pm. Lets say that
it
> takes 30 minutes. You could schedule the restore job on the other server
to
> start at 10:45pm.
> You may find these articles helpful:
>
> 314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
> http://support.microsoft.com/?id=314515
> 323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
> Paper)
> http://support.microsoft.com/?id=323135
> 325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
> http://support.microsoft.com/?id=325220
> 821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
> http://support.microsoft.com/?id=821786
> 321247 HOW TO: Configure Security for Log Shipping
> http://support.microsoft.com/?id=321247
> 329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
> Errors
> http://support.microsoft.com/?id=329133
>
> --
> Keith
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> > We have sql server standard edition , I want to automate the task of
data
> > restoration on a daily basis .
> > How can I automate copying and restoration of the data on standalone PC
.
> > Kindly suggest me the best option for the same .
> >
> >
> > Regards,
> > Swati
> >
> >
> >
> >
>|||swati
Create a job that does RESTORE full and DIFF database
For more details please refer to the BOL
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:upWyC21uEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Keith,
> Thanks for response.
> I agree that we can write manual commands for restoring the database on
> standby server . Let me explain you our backup stategy
> Full backup : 12:00 AM
> Diff backup : evry 2 hours
> Txn log backup : every 15 minute .this is done using DB maintenance plan .
> so every time we get a new file. e.g mydb_db_200410260000.bak
> Now I want to restore my full backup and diff backup which is done at
> interval of 2 hours . How can I restore my full db backup and diff backup.
> Regards,
> Swati
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
> > I assume that you have two database servers and you want to keep them in
> > synch.
> >
> > You have to do two things
> > BACKUP your database(s) on your primary server
> > RESTORE your backups to your standby server.
> >
> > It is simple to set up a job which issues BACKUP commands. In fact you
> > should be doing that now as part of your backup routine.
> > The RESTORE is easy. Assuming that the account that SQL Server is
running
> > as on your second server has rights to the machine where your primary
> > backups are stored you can do something like this: RESTORE DATABASE foo
> FROM
> > DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
> > If you have a share on the server you can access the share:
> > RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak'
WITH
> > REPLACE
> >
> > If your database servers have a different disk layout you might have to
> use
> > the WITH MOVE option to move the physical files to a location that works
> on
> > your secondary machine.
> >
> > Once you are able to issue the appropriate BACKUP and RESTORE commands
the
> > next thing you have to do is create a couple of jobs which will automate
> the
> > process for you. You can start a job (on another server) from your main
> > server, but the easiest method might be to have the jobs be independent
of
> > each other. Schedule your backup job to run at, say 10pm. Lets say
that
> it
> > takes 30 minutes. You could schedule the restore job on the other
server
> to
> > start at 10:45pm.
> >
> > You may find these articles helpful:
> >
> >
> > 314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
> > http://support.microsoft.com/?id=314515
> >
> > 323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping
(White
> > Paper)
> > http://support.microsoft.com/?id=323135
> >
> > 325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
> > http://support.microsoft.com/?id=325220
> >
> > 821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
> > http://support.microsoft.com/?id=821786
> >
> > 321247 HOW TO: Configure Security for Log Shipping
> > http://support.microsoft.com/?id=321247
> >
> > 329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
> > Errors
> > http://support.microsoft.com/?id=329133
> >
> >
> > --
> > Keith
> >
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> > > We have sql server standard edition , I want to automate the task of
> data
> > > restoration on a daily basis .
> > > How can I automate copying and restoration of the data on standalone
PC
> .
> > > Kindly suggest me the best option for the same .
> > >
> > >
> > > Regards,
> > > Swati
> > >
> > >
> > >
> > >
> >
>|||If you want to use the second server for Disaster Recovery, you should also
write a script to export your logins from the source SQL Server to the
Destination SQL Server.
--
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Uri Dimant" wrote:
> swati
> Create a job that does RESTORE full and DIFF database
> For more details please refer to the BOL
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:upWyC21uEHA.3276@.TK2MSFTNGP15.phx.gbl...
> >
> > Keith,
> > Thanks for response.
> >
> > I agree that we can write manual commands for restoring the database on
> > standby server . Let me explain you our backup stategy
> > Full backup : 12:00 AM
> > Diff backup : evry 2 hours
> > Txn log backup : every 15 minute .this is done using DB maintenance plan .
> > so every time we get a new file. e.g mydb_db_200410260000.bak
> >
> > Now I want to restore my full backup and diff backup which is done at
> > interval of 2 hours . How can I restore my full db backup and diff backup.
> >
> > Regards,
> > Swati
> >
> > "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> > news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
> > > I assume that you have two database servers and you want to keep them in
> > > synch.
> > >
> > > You have to do two things
> > > BACKUP your database(s) on your primary server
> > > RESTORE your backups to your standby server.
> > >
> > > It is simple to set up a job which issues BACKUP commands. In fact you
> > > should be doing that now as part of your backup routine.
> > > The RESTORE is easy. Assuming that the account that SQL Server is
> running
> > > as on your second server has rights to the machine where your primary
> > > backups are stored you can do something like this: RESTORE DATABASE foo
> > FROM
> > > DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
> > > If you have a share on the server you can access the share:
> > > RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak'
> WITH
> > > REPLACE
> > >
> > > If your database servers have a different disk layout you might have to
> > use
> > > the WITH MOVE option to move the physical files to a location that works
> > on
> > > your secondary machine.
> > >
> > > Once you are able to issue the appropriate BACKUP and RESTORE commands
> the
> > > next thing you have to do is create a couple of jobs which will automate
> > the
> > > process for you. You can start a job (on another server) from your main
> > > server, but the easiest method might be to have the jobs be independent
> of
> > > each other. Schedule your backup job to run at, say 10pm. Lets say
> that
> > it
> > > takes 30 minutes. You could schedule the restore job on the other
> server
> > to
> > > start at 10:45pm.
> > >
> > > You may find these articles helpful:
> > >
> > >
> > > 314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
> > > http://support.microsoft.com/?id=314515
> > >
> > > 323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping
> (White
> > > Paper)
> > > http://support.microsoft.com/?id=323135
> > >
> > > 325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
> > > http://support.microsoft.com/?id=325220
> > >
> > > 821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
> > > http://support.microsoft.com/?id=821786
> > >
> > > 321247 HOW TO: Configure Security for Log Shipping
> > > http://support.microsoft.com/?id=321247
> > >
> > > 329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
> > > Errors
> > > http://support.microsoft.com/?id=329133
> > >
> > >
> > > --
> > > Keith
> > >
> > >
> > > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > > news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> > > > We have sql server standard edition , I want to automate the task of
> > data
> > > > restoration on a daily basis .
> > > > How can I automate copying and restoration of the data on standalone
> PC
> > .
> > > > Kindly suggest me the best option for the same .
> > > >
> > > >
> > > > Regards,
> > > > Swati
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>
>sql

Automation Error when called from VB 6

Hi Friend,

I have an application in VB 6 with Crystal Reports 7. Once I deploy the application using Package & Deployment Wizard, I get the error 'Automation Error' when I call the report from VB. I have deployed the .rpt file and not the complied file. I get this error occationally and not always. I also use Selection Formula, and Replace Selection Formula, both in VB code and also in the Report. Other reports function normally but just this one report gives me problems.

Have you come across this problem or do you have any idea of solving this ?

Please let me know if you do.

Bye.You might be missing a required dll.|||omalley might be right...............but it can be about a thousand things in CR - the error traps are so generic...:-(

I've seen this type of error when referencing objects in VB that are nor correctly type, for example.
eg dim myObj as Object
dim myObj as CRAXDRT.Report (which is the correct way to expose a report object and its properties and avoid the iUnknown interface on the COM layer, as I recall.)

It can also occur when automation servers used by CR are not available for some reason. I use some of my own UFLs (user function libraries that I developed in VB), which end up being exposed as "Additional Functions" in the formula editor. Any dll's with prefix UFL MUST be in same location as crpeaut32.dll or they will operate unreliably, even though properly registered using regsvr32.

Check also that your installation doesn't have some distributables on a network drive.

Once you've done that, make sure that you have properly passed your login info to the report in your code, and that you have also passed it to any subreports. This is a trap that bites many CR users - your database security may require you to supply login info to each subreport. It will also vary from OS to OS - standard XP security is much fussier than ME, for example.

As you can isolate it to a particular report that fails, are you doing anything differently to other reports, ie passing a different type of parameter, or maybe a range of parameters instead of a single one, and so on? Or maybe a different user is experiencing the problem.

Failing that, check the event log on the PC and see what information it is returning regarding the automation failure, and post that here.

Dave|||Thank You, omalley and springsoft for your comments. The code I use is given below;

'*****

CR.ReportFileName = App.Path & "\RouteCard.Rpt"

CR.DiscardSavedData = True
CR.Reset
CR.DiscardSavedData = False
CR.WindowShowRefreshBtn = True
CR.WindowShowSearchBtn = True
CR.WindowShowPrintSetupBtn = True

If CmbWoNoFrom.ListCount = 0 Then
MsgBox "No Records", vbInformation
Exit Sub
End If
ab = False
st1 = "({TblSchedulinghdr.Cancelled} = 0 and {TblPartNoWorkOrderNo.Cancelled}=0) and {TblOrderAcceptanceDetail.Move}='MOVE' "

If Trim(CmbWoNoFrom) <> "" Then
st1 = st1 & " AND {TblSchedulinghdr.SequenceWONo} >= " & Val(CmbWoNoFrom)
End If
If Trim(CmbWoNoTo) <> "" Then
st1 = st1 & " and {TblSchedulinghdr.SequenceWONo} <= " & Val(CmbWoNoTo)
End If
If Trim(CmbFinancialYear) <> "" Then
st1 = st1 & " and {TblSchedulinghdr.FinancialYear} = '" & UCase(Trim(CmbFinancialYear)) & "'"

End If
CR.SelectionFormula = st1

CR.Action = 1

'****

I use a database on the network which connects through ODBC and there is no database passwords set. There are 6 sub reports on this report and only one user gets this error at times. This package is compiled on win 2000 and deployed on win98.

Please let me know if you have any clue.

Thanx.
Bye.|||John,
Only potential problem I can see here (and it may not actually be a problem because you have given us a code snippet, not the whole lot), is that you are using combo boxes, by the look of your variable naming notations (which bears some resemblance to Charles Simonyi's Hungarian C Notation, with an indicative type prefix on variable and object names).
If CmbWoNoFrom (for example) is actually a combo box, what are you doing to trap situations where user presses Print button without actually selecting an item from each combo?
Unless you have a default value already set, and it doesn't look like you have, as you are Val'ing the combo's selection, then a failure to select the value may cause the query to fail, because it is passing a value (probably -1) that the query can't handle.

Dave|||Thnak You springsoft for your observations.

I don't think an empty combo box would produce an error as the check for an empty trimmed combo is done before the expression is built. This particular machine had some virus problems and also has a keyboard device driver alert in device manager. Does this pose any potential problem, although I feel it should'nt.

Please let me know of your views.

Thanx.

Bye.

Automation Error

Hello,

Last night was the first night we used Sql Server Agent to run our Warehouse ETL packages. We use the built in logging of SSIS as well as our own event logging mechanism as it goes through all of the packages. Unfortunately, when we checked this morning the agent showed failure but in both logs from the packages no errors were reported. I checked the Windows event log and the Sql Server logs but found nothing that would appear to report an error.

Does anyone have any ideas what I might be missing, any recommendations on things to check, we have manually been running these packages for weeks with no problems. Any help would be greatly appreciated! Thank you in advance!

probably user account problem - have you tried to run manually under the same user account as sql agent is running?

|||In reviewing all of the logs it made it through the entire process before reporting failure. The very last process to run is a DBCC CHECKDB task which shows up in the Server logs. I will double check though. Thank you for your input.

Automation

Is there a way to automation SQL on an SQL Server to performimpports and exports at a certain of the night. The server isalways running.

Thank

Dee

Hi,

In Sql Server2005 we have new featue called Maintenence Plans, it is offering different kinds of operations. If you see your object explorer you can see Management Folder and Under Management you can see Maintenence Plans.

1) Expaand your Object Explorer.

2) Select Management folder and Expand it.

3) You can see Maintenance Plans.

4) Right Click on Maintenance Plan and Select Maintenance Plan Wizard.

5) Follow the Wizard instructions and It will take you to setup Backup plan scheduleing.

let me know if you have any difficulty to schedule Backups.

Note: It Maintenance Plan is not available in Sql 2005 Express Edition.

|||

I have a dumb question, how do I get to the Object Explorer?

Ido have both Sql 2005 Express Edition and Sql 2005 I think standardinstall, because I and running it currently at home on Window XP. I want to test this at home, but at work we have the server version.

Thanks

Dee

|||

Click on F8 (or)

Goto View --> Object Explorer

Are you using Sql Server 2005 ?

|||

Yes, I am using SQL Server 2005.

Dee

|||

When I got to the Management folder and expanded it I did notsee Maintenance plans. Is because I have both SQL 2005 ServerExpress and SQL 2005 Server?

Dee

|||

Hi,

From your description, you want to achieve schedule exporting and importing in your SQLServer, right?

Are you using SQLServer2000 or SQLServer2005? If SQLServer2005, you should use SSIS to create a package for your exporting or importing. And then, in SQLServer Agent, start a Job, and create a job step which execute s the package you just created from SSIS.

Thanks.

sql

Automation

How can I import an xml file to SQL at the same time every night? I will
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
DeeHi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist and
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connection
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:
> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a difference
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
> Hi
> You don't give the version of SQL Server that you are using! You can write a
> stored procedure that will create the database/table if they do not exist and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connection
> properties.
> You could use OPENXML to load the file and compare the two entries (assuming
> the same structure) and FOR XML to produce your output which would not need
> DTS/SSIS.
> John
> "Dee" wrote:
> > How can I import an xml file to SQL at the same time every night? I will
> > need to create a new database first via the import after that I will be
> > appending to the database. Then I need to xport the data into a difference
> > xml file.
> >
> > Do I have to have the orginal xml file on my server or can I point to the
> > location of the xml file?
> >
> > Thank you
> > Dee|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
> > Hi
> >
> > You don't give the version of SQL Server that you are using! You can write a
> > stored procedure that will create the database/table if they do not exist and
> > then pass the database name to a DTS/SSIS package that will load the file.
> > Using this global variable for the package you can then change the connection
> > properties.
> >
> > You could use OPENXML to load the file and compare the two entries (assuming
> > the same structure) and FOR XML to produce your output which would not need
> > DTS/SSIS.
> >
> > John
> >
> > "Dee" wrote:
> >
> > > How can I import an xml file to SQL at the same time every night? I will
> > > need to create a new database first via the import after that I will be
> > > appending to the database. Then I need to xport the data into a difference
> > > xml file.
> > >
> > > Do I have to have the orginal xml file on my server or can I point to the
> > > location of the xml file?
> > >
> > > Thank you
> > > Dee|||But I also have SQL 2005 Standard installed. Can I do an Import/Export from
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> probably the way to go.
> John
> "Dee" wrote:
> > John,
> >
> > I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> > and the standard for Windows XP installed.
> >
> > Will this work for both.
> >
> > Thanks
> > Dee
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You don't give the version of SQL Server that you are using! You can write a
> > > stored procedure that will create the database/table if they do not exist and
> > > then pass the database name to a DTS/SSIS package that will load the file.
> > > Using this global variable for the package you can then change the connection
> > > properties.
> > >
> > > You could use OPENXML to load the file and compare the two entries (assuming
> > > the same structure) and FOR XML to produce your output which would not need
> > > DTS/SSIS.
> > >
> > > John
> > >
> > > "Dee" wrote:
> > >
> > > > How can I import an xml file to SQL at the same time every night? I will
> > > > need to create a new database first via the import after that I will be
> > > > appending to the database. Then I need to xport the data into a difference
> > > > xml file.
> > > >
> > > > Do I have to have the orginal xml file on my server or can I point to the
> > > > location of the xml file?
> > > >
> > > > Thank you
> > > > Dee|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
> But I also have SQL 2005 Standard installed. Can I do an Import/Export from
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:
> > Hi
> >
> > Import/Export and Integration services is not on the feature list for SQL
> > Express see
> > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> > Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> > probably the way to go.
> >
> > John
> >
> > "Dee" wrote:
> >
> > > John,
> > >
> > > I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> > > and the standard for Windows XP installed.
> > >
> > > Will this work for both.
> > >
> > > Thanks
> > > Dee
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You don't give the version of SQL Server that you are using! You can write a
> > > > stored procedure that will create the database/table if they do not exist and
> > > > then pass the database name to a DTS/SSIS package that will load the file.
> > > > Using this global variable for the package you can then change the connection
> > > > properties.
> > > >
> > > > You could use OPENXML to load the file and compare the two entries (assuming
> > > > the same structure) and FOR XML to produce your output which would not need
> > > > DTS/SSIS.
> > > >
> > > > John
> > > >
> > > > "Dee" wrote:
> > > >
> > > > > How can I import an xml file to SQL at the same time every night? I will
> > > > > need to create a new database first via the import after that I will be
> > > > > appending to the database. Then I need to xport the data into a difference
> > > > > xml file.
> > > > >
> > > > > Do I have to have the orginal xml file on my server or can I point to the
> > > > > location of the xml file?
> > > > >
> > > > > Thank you
> > > > > Dee

Automating XML from Web to SQL Server

I have a web service, which returns XML to an http request. I need to automate the insert of this XML into SQL server.
I have already designed a procedure which used OPENXML to insert the XML packet, but I need to figure out a way to automate going to the page and retrieving the XML and calling the procedure.
I lack experience on the application development side of SQL Server, and would appriciate any quick & simple ways to perform this. The simpler the better...
Thanks in advance for your ideas.
Michael D.
"sorengi" <sorengi@.discussions.microsoft.com> wrote in message
news:5EEC9F63-7457-4343-A145-5FDCC3615AB3@.microsoft.com...
>I have a web service, which returns XML to an http request. I need to
>automate the insert of this XML into SQL server.
> I have already designed a procedure which used OPENXML to insert the XML
> packet, but I need to figure out a way to automate going to the page and
> retrieving the XML and calling the procedure.
The easiest way to do this would be to create a DTS package in SQL Server
that retrevies the XML and passes it into your procedure. You could then
schedule the package to execute at a specific interval.
See the SQL Server Books Online for information about DTS packages and
scheduling. A simple example of DTS can be found here:
http://sqlxml.org/faqs.aspx?faq=10
Bryant

Automating the importation of an Oracle table into Sql Server 2000

Hi guys,

I figure this should not be a complex one. I know how to manually pull in data from Oracle 9i into SQL Server 2000 using DTS. However this is my issue....

I simply want to automate the pulling in of data from 1 table in my ORACLE 9i database into another table in my Sql Server 200. I was hoping I could simple write a stored procedure that would sort of utilize a dblink like in ORACLE and then schedule that procedure. Is this feasible in Sql Server, and how would one go about setting this automated import up??

Thanks in Advance all.........

'WaleDid 9i ever come out with an export utility?

Your best bet is to schedule the Oracle export, TRUNCATE the SQL Server table, then do a bcp load or BULK INSERT...

or you can set up a linked server in sql server and do a delete and an insert from the oracle table...

The latter will incur more over head...but will appear to be easier...|||problem is that the ORACLE database is not in my control. So I can't just schedule an export.|||I wonder if you can bcp out data from a linked server...

look into sp_addlinkedserver

You must authority to that box, right?|||yeah I do have authority on the SQL Server box. i'll look into it.|||You'll have to look into sp_addlinkedsrvlogin as well

automating the dumping of databaser in sql server 6.5

I don't happen to have any sql server 6.5 books. I need to know how
to automate the dumping of databases at a preset interval. I can do
it manually and do so each day but it would be nice if it would do it
on its own.
thanksHi,
You can Schedule this using the "Schedule Tasks" available in SQL Server 6.5
Enterprise manager.
THis schedule tasks will be ran by the service "SQL Executive", so ensure
that the service is up all the time.
How to do:-
1. Open enterprise manager
2. COnnect to the server
3. In the menu ,choose Server option and select "Scheduled tasks"
4. Click the first Icon (New Task)
5. Give the name as "Backup" and then select the type as "TSQL".
6. In the command type your "DUMP DATABASE COMMAND"
7. In the schedule option click "Recurring"
8. Click the change command button and select the intervals and days to
schedule
9. CLick ok
Thanks
Hari
MCDBA
<kg7poe@.yahoo.com> wrote in message
news:lcala0t54hin158dlsqtq9bka6k4f5b5ke@.4ax.com...
> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>sql

automating the dumping of databaser in sql server 6.5

I don't happen to have any sql server 6.5 books. I need to know how
to automate the dumping of databases at a preset interval. I can do
it manually and do so each day but it would be nice if it would do it
on its own.
thanks
Hi,
You can Schedule this using the "Schedule Tasks" available in SQL Server 6.5
Enterprise manager.
THis schedule tasks will be ran by the service "SQL Executive", so ensure
that the service is up all the time.
How to do:-
1. Open enterprise manager
2. COnnect to the server
3. In the menu ,choose Server option and select "Scheduled tasks"
4. Click the first Icon (New Task)
5. Give the name as "Backup" and then select the type as "TSQL".
6. In the command type your "DUMP DATABASE COMMAND"
7. In the schedule option click "Recurring"
8. Click the change command button and select the intervals and days to
schedule
9. CLick ok
Thanks
Hari
MCDBA
<kg7poe@.yahoo.com> wrote in message
news:lcala0t54hin158dlsqtq9bka6k4f5b5ke@.4ax.com...
> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>
|||"kg7poe@.yahoo.com" wrote:

> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>

automating the dumping of databaser in sql server 6.5

I don't happen to have any sql server 6.5 books. I need to know how
to automate the dumping of databases at a preset interval. I can do
it manually and do so each day but it would be nice if it would do it
on its own.
thanksHi,
You can Schedule this using the "Schedule Tasks" available in SQL Server 6.5
Enterprise manager.
THis schedule tasks will be ran by the service "SQL Executive", so ensure
that the service is up all the time.
How to do:-
1. Open enterprise manager
2. COnnect to the server
3. In the menu ,choose Server option and select "Scheduled tasks"
4. Click the first Icon (New Task)
5. Give the name as "Backup" and then select the type as "TSQL".
6. In the command type your "DUMP DATABASE COMMAND"
7. In the schedule option click "Recurring"
8. Click the change command button and select the intervals and days to
schedule
9. CLick ok
Thanks
Hari
MCDBA
<kg7poe@.yahoo.com> wrote in message
news:lcala0t54hin158dlsqtq9bka6k4f5b5ke@.
4ax.com...
> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>|||"kg7poe@.yahoo.com" wrote:

> I don't happen to have any sql server 6.5 books. I need to know how
> to automate the dumping of databases at a preset interval. I can do
> it manually and do so each day but it would be nice if it would do it
> on its own.
> thanks
>

Automating the creation of a database

Hi;
We have a program (ASP.NET) that requires a database as it's back end. We
are trying to create an install that requires as little expertise as
possible. In other words, no DBA required.
For creating the database itself we are at that point. We use the registry
to find the location of osql.exe and use that to run the schema that creates
the database. I'ld prefer an API we could call so we can more cleanly handle
errors but this works fine 98% of the time.
The remaining problem is ownership of the created database.
1) Is there a way (in .NET 2.0) to query if the database is mixed mode
authentication.
2) And if it is a way to both get a list of all users
3) And to create a user?
We can already enum all domain users. So with the above we could give them a
list of all users they can choose from as the owner and also let then create
a new one - without the user ever having to run any SqlServer tool or even
have any installed.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
It is SERVER property not a databases
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly
') AS
[IsIntegratedSecurityOnly]

> 2) And if it is a way to both get a list of all users
EXEC northwind..sp_helpuser

> 3) And to create a user?
Create Database mydb
go
use mydb
go
sp_addlogin 'mydbuser','monitor','mydb'
go
sp_adduser 'mydbuser'
go
sp_Addrolemember 'db_datawriter','mydbuser'
go
sp_Addrolemember 'db_datareader','mydbuser'
go
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi David,
I am afraid that the database may have some synchronous problem now. Our
yesterday's replies cannot be seen from Web and we also cannot see your
replies.
So I post it again from Outlook Express and hope you could see it now. Sorry
for bringing you any inconvenience.
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi Dave,
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

automating taking data from sql server 2000 onto excel 2003 spreasheet

I would like to know recommendations on automate the following:

1. I want to know how to take data from various sql server 2000 data queries and load the data onto excel spreadsheets in an automated method. Some of the data loaded into the excel spreadsheets load detail data and some load data into pivot tables.

Is there any way that the data can be taken from sql server to the excel spreadsheets using odbc connections?

2. Some of the data taken from sql server 2000 database is loaded into pdf files. Is there any way this process can be automated?

Thanks!

You can use macros in Excel to do this task.

Have you checked out Reporting Services? This allows you to export to a number of different formats including PDF.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual
data from the old db to the new one, but i would like to take the table
structure. in DB2, i could run through a ddl file that contained CREATE
DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
tables, databases, and the like. how exactly do i do this using MS SQL
server? im thinking along the lines of writing my own file, and then
using something in SQL server to basically read the file and execute
the SQL commands. any ideas? thanks.
qin_23
qin_23
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1835350.html
quin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1835350.html
>
sql

automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual
data from the old db to the new one, but i would like to take the table
structure. in DB2, i could run through a ddl file that contained CREATE
DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
tables, databases, and the like. how exactly do i do this using MS SQL
server? im thinking along the lines of writing my own file, and then
using something in SQL server to basically read the file and execute
the SQL commands. any ideas? thanks.
qin_23
--
qin_23
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message1835350.htmlquin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1835350.html
>

automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual data fr
om the old db to the new one, but i would like to take the table structure.
in DB2, i could run through a ddl file that contained CREATE DATABASE, CREAT
E TABLE, etc. kinds of commands to automatically create tables, databases, a
nd the like. how exactly do i do this using MS SQL server? im thinking along
the lines of writing my own file, and then using something in SQL server to
basically read the file and execute the SQL commands. any ideas? thanks.
qin_23quin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1835350.html
>|||alright. thanks it worked.
qin_23

Thursday, March 29, 2012

Automation of Excel to SQL Server 2005

Automation of database restoration

Automation of database restoration

Automation of database restoration

Automation Error when called from VB 6

Automation Error

Automation

Automation

Automation

Automation

Automating XML from Web to SQL Server

Automating the importation of an Oracle table into Sql Server 2000

automating the dumping of databaser in sql server 6.5

automating the dumping of databaser in sql server 6.5

automating the dumping of databaser in sql server 6.5

Automating the creation of a database

automating taking data from sql server 2000 onto excel 2003 spreasheet

automating table creation

automating table creation

automating table creation

Sql Auto grow

Blog Archive

About Me