i want to automate the exporting of data from an excel database to sql using dts.i don't want this to do manually.can u help me out in solving this.what are the things i have to do for this ?expecting u'r earliest reply
View 2 RepliesBoth using SSMS 2012 Enterprise on Windows 2012
I am able to import - export manually, how can you schedule this import export in a job? I want the table to drop and be rebuilt each time it runs. Since linking servers isn't recommended I want go with that approach.
hi everybody,iam soory maybe my question is easy, i heard about a product that cansolve such problems, but i forgetted.the problem:we have several foxpro6 systems, each one with seperated DB.Now we build a new complete DB for all systems using MS_SQL Server2000, we need to transfer/export old Data to new DB taking allsituations such as the data for one system (SALARY system) thatworking on employee table is not comaptible with other data from othersystem (emplyees system) that worked on the same table employee. Howwe can solve such issue using MS products or other products ifavailable.thanks inadvance for any advice.best regardseng thaarCNS
View 1 Replies View RelatedHello, I have a report that I am creating in sql reporting services, I want my report to be exported in landscape PDF file format. I Went to the report properties then set the width = 11 in and height 8.5.
When i export my report to a PDF file it did not display it in landscape format!
Do i need to make the report go sideways? if yes how! for example
how would i write a textbox which has the label Time sideways?
Thanks
Can anyone give me an idea where I can find the equivalent SQL scripts for the GUI process(DTS) for importing or exporting any DB from a given ODBC source.
Thanks!
Hello!
Can someone help me writting a query to export data in following fashion :
Old Structrure: I've a single table (Countries) with columns CountryID, CountryName, CityName
New Structure: I want to break this single table into two tables now (Countries) and (Cities). New Structure should be like following:
Countries Table:
- CountryID
- CountryName
Cities Table:
- CityID (Auto Generated)
- CountryID (FK from Countries)
- CityName
I would really appreciate if someone can help me to write the query which can export the Data from Countries Table to the new Cities Table.
Thanks.
' File: Test.rss
Dim format as string = "Excel"
Dim fileName as String = "C:Export2.xls"
Dim reportPath as String = "/TimeByEmployee"
Public Sub Main()
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string = "ms-excel"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim parameters(3) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "Employee"
parameters(0).Value = " "
parameters(1) = New ParameterValue()
parameters(1).Name = "Departments"
parameters(1).Value = " "
parameters(2) = New ParameterValue()
parameters(2).Name = "Week Number"
parameters(2).Value = WeekNumber
results = rs.Render(reportPath, format, _
Nothing, Nothing, parameters, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub
'End of script
here is how i run it
rs -i RunReport.rss -s http://localhost/reportserver -v WeekNumber=90 -e mgmt2000
this is the error i get:
The value of parameter 'Parameters' is not valid. also how can i cgange this line to pdf
Dim mimeType as string = "ms-excel" i tried this but it did not
work
Dim mimeType as string = "ms-pdf"
Thanks
' File: Test.rss
Dim format as string = "Excel"
Dim fileName as String = "C:Export2.xls"
Dim reportPath as String = "/TimeByEmployee"
Public Sub Main()
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string = "ms-excel"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim parameters(3) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "Employee"
parameters(0).Value = " "
parameters(1) = New ParameterValue()
parameters(1).Name = "Departments"
parameters(1).Value = " "
parameters(2) = New ParameterValue()
parameters(2).Name = "Week Number"
parameters(2).Value = WeekNumber
results = rs.Render(reportPath, format, _
Nothing, Nothing, parameters, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub
'End of script
here is how i run it
rs -i RunReport.rss -s http://localhost/reportserver -v WeekNumber=90 -e mgmt2000
this is the error i get:
The value of parameter 'Parameters' is not valid. also how can i cgange this line to pdf
Dim mimeType as string = "ms-excel" i tried this but it did not
work
Dim mimeType as string = "ms-pdf"
Thanks
I am trying to create a process to automate the FTP process and then IMPORT to a table. Once Import is complete, I would like to move the file from the FTP server and rename it. I tested the "commands" via CMD and it works. I then created a store procedure with the DOS commands to rename/move the source file. The command executes and deletes the file from the source, however, the copied files to a different destination disappeared. I did a search in my local hard drive(testing using my local folders), but can't find where the copied/renamed file went.
HEre is the portion of the commands:
SET @cmd ='Copy "C:Documents and SettingsegllareMy DocumentsBoxtestData.txt" "C:Documents and SettingsegllareMy DocumentsArchiveCopybox.%random%.bak"'
EXEC master..xp_cmdshell @cmd
GO
Can you please tell me what's wrong with this,
or direct me to a sample store procedure I can model..
TIA
Willie Llarena
Hi team
Could anyone tell me if and how can i automate processing in sql server. Example, i would like to automate reading from a source file, populating it into the respective tables and then running a view to process desired tables. I already did all the above manually. I just want to know if there is a way of automating all this process.
I was wondering if anyone can share the procedure(s) used in setting up an automated MS SQL database backup through tivoli Storage Manager
( 5.2.7 )..........?
The powers at be have decided that they would like to automatically run a trace on one of our analysis servers when it processes a cube in the early hours of the morning. Now I have no problem creating a SQL Profile to run and store the results in a database table for them, but I have no idea how to automate it so that it runs everyday, any help would be greatly appreciated. Apologise for not putting this in the correct forum.
View 1 Replies View RelatedI am looking for the best method of automating an INSERT query in SQL Server 2005
Code:
--------------------------------------------------------------------------------
'INSERT INTO SaltInvWhOpen (StockCode, Warehouse, TrnMonth, TrnYear, OpenBalQty, OpenBalCost)SELECT StockCode, Warehouse, Month(GETDATE()) AS TrnMonth, YEAR(GETDATE()) AS TrnYear, OpenBalQty12, OpenBalQty12 * UnitCost AS Expr1FROM InvWarehouse
--------------------------------------------------------------------------------
In older versions I would have used a DTS package (It's been a long time and I'd have had to use a book to guide me through it), but as that is gone now, I am unsure where to even begin looking.
If someone could assist by giving me the current method, and dare I ask, walk me through it, the help would be much appreciated.
Thank you!
Julia
Isn't there any way to automate Windows defrag? Isn't there any undocumented parameters for DEFRAG.EXE?
Canada DBA
Is there a way that I can Automate SQL Setup via C#, PERL or VB?
View 2 Replies View RelatedAfter SQL Server Express 2012 is installed, is there a way to automate setting up a few options? I need to enable the option for both windows and sql server authentication. Can this be done with Powershell or other tool? This is a situation where the user has absolutely no computer skills and there is no one available in their office that can set the options manually. I thought some sort of script would be useful for this.
View 2 Replies View RelatedIs there a way to automate (schedule) backups of the databases in SQL Express? Similar to Maintenance Plans in SQL2000....
View 8 Replies View RelatedWe're trying to put a view of data maintained in desktop Access databases online and into SQL Server.The desktop Access system uses separate databases instead of tables within one database, It's a strange design, but it can't be changed.We have been importing all of the separate databases into a single, new Access database, then upsizing the new databse to SQL Server, then uploading it.This is not going to work long term, because we are stuck with a 250 mB Access database to upsize and upload, when we never need to update more than 2 or 3 of the tables and upload more than 2 mB.We'd like to be able to upload only the tables -- preferably the Access *.mdb's -- that have changed, and then replace the SQL Server tables with the new information. And we'd like to automate it as much as possible, without upsizing Wizardy.I don't know where to even begin looking for information about how this might be done.Any suggestions would be deeply appreciated.- Tinker
View 4 Replies View Relatedhi I would appreciate if someone demonstrate how to automate ftp in a command line from within a batch file. I do want to move certain files from one server to another via ftp command line in an automatic fashion via running the batch periodically.
thanks
Ali
I'd like to build a process that will identify all the tables in a database with a scan density less than 100% and generate 'dbcc dbreindex' statements for them. The 'dbcc showcontig' command displays the information I want but I don't know how to access this information from within a script. Any ideas out there?
View 5 Replies View RelatedI am testing a procedure to automate the transaction log dump. I am following the steps located in Chapter 22 of the Microsoft SQL Server Administrator's Companion ("Automatic Transaction Log Dumps Using Performance Monitor"). The alert in Performance Monitor appears to be starting when the log is 75% full, but the alert is not firing off the file that contains the dump transaction sql command. For the 'Run Program on Alert' box this is what I have: isql -Ssvrname -Usa -P -id:appsmssqlinndump.sql
The dump.sql file contains: 'dump transaction pubs with no_log'
I have also tried the following 4 steps: 1) Created a SQL Alert Messsage, 2) Created an NT Performance Monitor Threshold Alert to run sqlalrtr to issue a certain error when the pubs log is 75% full, 3) Created a TSQL Task, and 4) Created a SQL Server Alert to run the Task created in step 3. This appears to do the same thing. The Alert is fired off, but the Task is never executed. Note: I am able to execute the task from within the Schedule Tasks Window.
I am using Standard Security with SQL Server 6.5 (sp5a) running on NT4.
Thanks for you help in advance.
question... i heard a bit about Control Macro Generator but have not found much reading material on it. here at our company we have employees fill out a weekely timesheet and typically have clerks enter the information into the TM.PTA.00 (Project Timesheet with Rate/Amount Entry) and submit it that way. say we have this timesheet in a nice little excel sheet with columns that match the input boxes on the form, how can we get it to automatically input the data instead of having a person sit there and type it all in?
View 3 Replies View RelatedI would like to automate a simple process which involves trucating a table and importing records back in. What would be the simplest solution to automate this kind of process? I'd like to run this process twice a day.
Thanks!
How can I do this with vbscript, or C# ?
- Copy backup files down from a network share, into the data directory of my local sql 2005 instance
- perform a restore using the files copied from above
- Execute a dts package
More info:
Our databases are scripted and exist on the typical development, and testing enviroments. So as I get ready to start a new application, I want my local sql instance to be updated based on structure changes as well as data. So I have to apply the changes from the scripted sources and pull over the data. I would naturally like to automate this.
ideas / suggestions welcome
Hi,
View 3 Replies View RelatedCan I automate a stored procedure I made myself?
If so could someone kindly tell me how as I have tried to no avail.
Thanks in advnace
1) Is it possible to run stored procedures at specified intervals without
using the job system (through T-SQL)? I want the schedule to be
independent of the MSDB database in case of temporary failures, etc.
2) Would extended stored procedures be helpful in this scenario?
Thanks
ziggy
Hi,
I am new to SQL Server7. I need to populate some tables from an SQL Server7
database at the end of the day. How can I automate this process?
I also need to export these populated tables to a text file on daily basis.
I know I can use "DTS" to do this. But is there any way to make these
automated also? Or is there any third party tool to do all these?
Thanks in advance.
Mkhan
In order to automate regular maintenance on SQL Server (6.5 SP3) I'm trying
to automate the process of putting the database into single user mode without
having to stop/start the process.
The sp_dboption won't allow single user mode while users are still connected
to the database, so I was trying the following script:
DECLARE @to_kill int
/* Declare Cursor */
DECLARE spid_cursor CURSOR FOR
SELECT spid FROM sysprocesses WHERE dbid = 6
/* Open Cursor */
OPEN spid_cursor
/* Loop Through All Table Names */
FETCH NEXT FROM spid_cursor INTO @to_kill
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
KILL @to_kill
END
FETCH NEXT FROM spid_cursor INTO @to_kill
END
This, however gives a sytax error for the KILL command (it seems that KILL
will not allow a parameter to be passed). I've tried making the @to_kill
variable smallint, int, and varchar without any success.
Is there any automated way way to kill user connections without cycling
the server?
Thanks
Gord T.
I'm really puzzled with this. I am new to using MS SQL and need help with the primary key. When I was using Access, I would open the database table and just enter values for the various columns, each time a new column was begun, the computer would insert a unique number there. OK, so now I am using visual studio to work on a SQL database. I created a new table and the first field I specified was "ID", I then chose it as the primary key. For default value, I tried both auto number and auto increment. These don't seem to work, as when I type values into fields and try to move to the next row, having left the ID field blank, I get an error which reads "String or Binary data may be truncated". What is going on, what do I need to do so the field automatically populates with an ID number... Some research sort of led me to the concept of a "trigger" to do this. Is this the approach. I was able to r-click on the table and I saw an option for "create trigger" or "new trigger"... How do I learn to do this, is there a generic trigger to do an auto increment of a primary key ID field?
View 2 Replies View RelatedHi,
I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?
Thanks,
Maria
Hi,I need some advise on how to automate record selection on a storedproc. Here is my situation. I have a stored proc that I used onCrystal reports with two parameters - Acctcode and Subacct. When auser enters ' *' on these parameter, it means to report on allaccounts otherwise, report only on specific account.Here is my select statement with line numbers:Create proc rb_SubledgerRpt@Acctcode varchar(4), @SubAcct varchar(3)As3 Select AcctCode, SubAcct4 From GLDetails5 Where SubAcct <> ' '6 and AcctCode = @Acctcode -- for specific acctcode7 and SubAcct = @SubAcct -- for specific subacct8 Go-- If a user wants to see all Acctcode, and all Subacct, how do Idisable lines 6 and 7?Thank you in advance for your help.Edgar
View 6 Replies View Related
Is there anyway to automate the configuring Reporting Server after is installed. I have many servers that I need to install SQL 2005 with Analysis servcies and Reporting Server and it will be good if I could run a tool to configure reporting server with some default values.
Thanks