I need to programmatically backup a database in SQL Server Express. I actually also need to programmatically restore it from a backup file. How can I do this programmatically? I know how to do simple ADO commands for simple db operations, but backup and restore sound like "meta" commands to me, and I don't know where to begin from.
View 15 RepliesHi ,Can we make a backup from a SQL-server 2000 database programmatically, using asp.net and then restore this backup programmatically ?
View 4 Replies View Related
Hi there
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Thanks
Jane
I'm building SSIS packages through code and I would like to set the properties of some custom tasks (not data flow tasks) to expressions. I've done some searches but turned up nothing. This is the only thing I'm hitting a brick wall on at the moment; Books Online has been excellent in detailing how to create packages via code up to this point.
For the sake of argument, let's say I want to set the SqlStatementSource property of an Execute SQL task to this value:
"INSERT INTO [SomeTable] VALUES (NEWID(), '" + @[User:omeStringVariable] + "')"
What would the code look like?
Ok. So I have this ASP.NET page and I've programmatically taken a report from the report server and rendered it in PDF. Now I would like to take this a step further and save the report as a pdf document on the local machine.
So at this point I have a byte array representing the document, now how would I save this as a pdf on the local machine? I'm unaware of an ASP Response method to allow this and I'm unaware of a SSRS ReportingService method, but as I said I'm unaware...
Any ideas, thoughts, resources are all welcome.
I've seen several post asking for that possibility, but all 've read, didn't help me.Some sing SQLDMO, other suggest to use SQLSMO, others only explaining to connect to a server and then do "CREATE DATABASE".I will do this within .Net. Connecting to a SQL Server 2005 and execute "CREATE DATABASE" I could realize.But how doing this with SQLExpress? Trying to do SqlConnection.Open with a non existing DB does not work., says "file not exists".Or do I only have the wrong connection string? Can someone post here an excample connection string, which works with a non existing database?Some hints I've read make me considering to use SQLSMO. But I don't have it on my computer. Where do I get it from? Any links would be nice.
View 6 Replies View Relatedhi all... how can i change the collation of a table that i dynamically created during runtime??i wanna change the default collation to Arabic
View 2 Replies View RelatedHi Guys, I have this SqlDataSource, that counts some records and sets it in "NotStartedBugs". How do I retrive "NotStartedBugs" programmatically?
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT (SELECT COUNT(*) AS Expr1 FROM tickets WHERE (TicketType = 'Bug') AND (TicketStatus = 'Not Started')) AS NotStartedBugs"></asp:SqlDataSource>
Hi,
I am using Visual Web Developer 2005 Express Edition.
I am trying to SELECT three information fields from a table when the Page_Load take place (so I select the info on the fly). The refering page, sends the spesific record id as "Articleid", that looks typically like this: "http://localhost:1424/BelaBela/accom_Contents.aspx?Articleid=2". I need to extract the "Article=2" so that I can access record 2 (in this example).
How do I define the SelectParameters or QueryStingField on the fly so that I can define the WHERE part of my query (see code below). If I remove the WHERE portion, then it works, but it seem to return the very last record in the database, and if I include it, then I get an error "Must declare the scalar variable @resortid". How do I programatically set it up so that @resortid contains the value that is associated with "Articleid"?
My code is below.
Thank you for your advise!
RegardsJan/*******************************************************************************
* RETRIEVE INFORMATION FROM DATABASE
*******************************************************************************/
// specify the data source
string connContStr = ConfigurationManager.ConnectionStrings["tourism_connect1"].ConnectionString;
SqlConnection myConn = new SqlConnection(connContStr);
// define the command query
String query = "SELECT resortid, TourismGrading, resortHits FROM Resorts WHERE ([resortid] = @resortid)";
SqlCommand myCommand = new SqlCommand(query, myConn);
// open the connection and instantiate a datareader
myConn.Open();
SqlDataReader myReader = myCommand.ExecuteReader();
// loop thru the reader
while (myReader.Read())
{
Label5.Text = myReader.GetInt32(0).ToString();
Label6.Text = myReader.GetInt32(1).ToString();
Label7.Text = myReader.GetInt32(2).ToString();
}
// close the reader and the connection
myReader.Close();
myConn.Close();
Hello. Im trying to create an SQLDataSource control programmatically. I need to do this because I want to do some stuff on my MasterPage's 'Page_Init' event.
heres my code (Master.master.vb): Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
lblUser.Text = Page.User.Identity.Name
Dim PUser As New ControlParameter
PUser.ControlID = "lblUser"
PUser.Name = "LoginName"
PUser.PropertyName = "Text"
PUser.Type = TypeCode.String
PUser.DefaultValue = Page.User.Identity.Name
Dim SQLDS_Login As New SqlDataSource
SQLDS_Login.ID = "SQLDS_Login"
SQLDS_Login.ConnectionString = "I put conection string here. How do I use the one on my web.config?"
SQLDS_Login.SelectCommand = "SELECT [LoginID], [LoginName], [Role], [Status] FROM [myLogin] WHERE ([LoginName] = @LoginName)"
SQLDS_Login.SelectParameters.Add(PUser)
SQLDS_Login.SelectCommandType = SqlDataSourceCommandType.Text
GridView1.DataSource = SQLDS_Login
GridView1.DataBind()
End Sub
When i run, i get this error message:
The SqlDataSource control 'SQLDS_Login' does not have a naming container. Ensure that the control is added to the page before calling DataBind.
I never had any problem with Inserts, Updates and Deleting, but I have never made it work for Select when doing it programmatically.
Can you help me with this?
I am trying to add a number of dates into a Sql database. Basically I want the user to add the start and end date and then all the dates in between are are added to a database in unique records. I can create an ArrayList but I don't know how to bind it to an SqlDataSource Dim startdate As DateTime = tbstartdate.Text Dim enddate As DateTime = tbenddate.Text Dim datediff As TimeSpan = enddate.Subtract(startdate) Dim noofdays As Integer = datediff.Days Dim ar As New ArrayList Dim i For i = 0 To noofdays ar.Add(startdate.AddDays(i)) Next Sorry if this is a total noob question....
View 1 Replies View RelatedI am trying to customize my update statement and this MUST happen in codebehind, otherwise I will be overwriting data. The following is updating the data that is should be. The problem is that eventhough my UpdateCommand is clear in my .aspx and there are no parameters set... It is STILL running it's own update and overwriting the information it isn't supposed to. From what I can tell, it is using a default. What can I do to prevent this?
SqlDataSource1.UpdateCommand = "UPDATE MyTable SET MyField1=@MyField1 WHERE MyField2=@MyField2 AND MyField3=@MyField3"SqlDataSource1.UpdateParameters.Add("MyField1", "CustomText")
SqlDataSource1.UpdateParameters.Add("Task_ID", "Parameter")SqlDataSource1.UpdateParameters.Add("Comments", "Parameter")
SqlDataSource1.Update()
I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find is http://forums.asp.net/thread/1233258.aspx but no answer was posted.
What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0])
Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user?
Thank you very much for your help.
Great Monday Morning to one and all,
Setup: SQL7 w/SP4 running on W2K Pro
Table in Question:
I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).
The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.
For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.
Great Monday Morning to one and all,
Setup: SQL7 w/SP4 running on W2K Pro
Table in Question:
I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).
The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.
For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.
Is there a way to schedule tasks to run from an ASP script? If not ASP, how about VB? What function is called if any? Thanks!
View 1 Replies View RelatedHowdy,
Is the any easy way to clone a database programatically?
I've searched the web but did not come up with anything.
At the moment I create a new blank database (based on the model database)
and create tables, indexes, etc. via asp. (The databases are always exactly
the same)
It seems to me that it would be a better option to
create one database with tables, indexes, etc and copy
it to a new database (on the same server).
One of the options I'm considering would be to create a database,
fill it with tables and indexes, etc. and then detach it.
Whenever I need a new database all I would have to do is use the
file system object to copy and rename the mdf file and then reattach the
new mdf file.
I realise that I could also make the changes to the model
database so that all new databases have the same structure
but that would be my last resort.
Is there any easier way to do this? A stored procedure perhaps?
You're opinions and advice would be welcomed.
I recently had to find ways to programmatically backup and restore anSQL Server Express database.For backup, I found a one-liner stored procedure that does the job(BACKUP DATABASE Toy to disk = 'c:ProjectsToy.bak'.My question is, is there something as simple for a restore operation?Thanks in Advance-Kostas
View 4 Replies View RelatedHi,The following script does not return any resultset against a test dbwhile I know for a fact tables with letter "aaa" has columns thatcontains "ccc".What's wrong? the the inner cursor?Thanks.-- get all tbls with letter aaadeclare @tbl varchar(8000)declare tblCursor cursor forSELECT nameFROM sysobjectsWHERE xtype = 'U'AND name LIKE '%aaa%'open tblCursorfetch next from tblCursorinto @tblwhile (@@fetch_status = 0)begin-- get all columns with letter ccc and replace it with nothing /remove itdeclare @tbuffer varchar(4000)declare @cbuffer varchar(8000)declare abnormal_cols cursor forSELECT o.name, c.nameFROM sysobjects oJOIN syscolumns c ON o.id = c.idWHERE o.xtype = 'U'AND c.name LIKE '%ccc%'and o.id = object_id('+@tbl')-- ORDER BY c.nameopen abnormal_colsfetch next from abnormal_colsinto @tbuffer,@cbufferwhile (@@fetch_status = 0)begin-- EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%ccc%','')','COLUMN';-- testprint @tbuffer + ', ' + @cbuffer;fetch next from abnormal_colsinto @tbuffer,@cbufferendclose abnormal_colsdeallocate abnormal_cols;fetch next from tblCursorinto @tblendclose tblCursordeallocate tblCursor;
View 1 Replies View RelatedHi!
I have a rdl file and would like to publish it programmatically? Is there any API or Web Service available for this.
Does anybody know can you programmatically instantiate a control at run-time?
I need to make a dynamic number of images appear on a report and programmatically grab an image name based on data in the report.
vbwrangler@yahoo.com
In the old DTS, we can use the ActiveX Script to change any task's property programmatically.
Can we still do it in SSIS? Using the Script task? It seems changing the value of variables then use a expression can do some of the work, but what if a task has no expression defined?
Say, I want to change the Fuzzy look up reference table name.
Can we do it?
All,
I'm wondering if there are any methods to set the permissions for a specific user on a specific report within SQL Server Reporting Services.
I got how to get permission from them programmatically, but not how to set them but the user interface..
Please apologize me if there was a similar thread in the recent past; I tried to find it out, I didn't succeed.
Thanks a lot in advance for your timely response (as usual deadlines are by "yesterday").
-G
I wrote a custom IIS log reader windows service that reads and parses log data and periodically uploads it to a SQL server table which I run my reports from later. Currently I read the file by giving the absolute physical path name for the log directory followed by the log file name I anticipate depending on date/time stamps.
My question is: Is there a programmatic way to get the directory path which is configured in IIS to store the IIS log files to? We use non default location e.g. D:DATALOGSIISW3SVC1 or something similar, which might change from server to server. Also, I am not worried about the individual log file names but the directory. I would prefer a C# code example.
Any help is appreciated!
KC
Hi there,
I got a user who is requesting a weekly report to be exported in csv (comma delimited) format. But this process will run weekly using schedule job and he wants the file to save to a certain directory on the network. Two part questions...
1. Is there a way to create a .csv file programmatically after runing the query?
2. How would I save the .csv file to a specified directory on the network?
TIA
SQL Server 2005 Reporting Services
From the Report Manager home page, if you click on the Properties tab, you get to a security page where you can add new role assignments for users or groups. We need to add role assignments programmatically (not manually using Report Manager).
How can we programmatically add new role assignments?
Can a package executed from code be aborted in code? The Package class has an Execute() method but no Abort() method. Clearly the debugger can stop a package at any point, so it must be possible somehow.
Hi,
I built a small application that helps me create new packages by loading a template and modifying some parameters, and eventually save the updated package to a user defined location. This application uses the SSIS programming API.
I know all the packages created this way from the same template will have the same package ID. Is there a way to regenerate the package ID programmatically? I don't want to use dtutil.exe, but rather a nice package level method in the API.
Is there such?
Thanks.
Hi,
I want to drop users programmatically, is this possible? I tried
create PROCEDURE [dbo].[sp_DelAllUnusedLogins] AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserName nvarchar(128)
DECLARE user_cursor CURSOR FOR
select name from sys.sysusers where status=12 and name
not in ('NT AUTHORITYSYSTEM','dbo')
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
DROP USER @UserName
FETCH NEXT FROM user_cursor INTO @UserName
END
CLOSE user_cursor
DEALLOCATE user_cursor
END
But @UserName isn't accepted..
i can Buil a Packages Programmatically in a script task. and also i can change the variable value .
but how can i save the value i assigned in the script task？
I would like to evaluate expressions from within my execute function in a custom task. I saw this post from about 8 months ago detailing how it would be possible:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=117564&SiteID=1
Is there an updated response (or more detailed example) to the question? Am I really risking a lot by using this undocumented feature?
Thanks!
Hi,
I have metadata that stored my table structure and relationship. I would like to know is it possible to create table relationship programatically? Any sample?
Thank you
Hi!
I am trying to write a script in VB.NET that will run a report that already exists in the system and export the results to my local machine. We are using MS Reporting Services to manage and manipulate the reports. So here's my question:
Is it possible to programmatically create a report in VB.NET based on an existing report? I noticed that crystal reports has a nice export method, but I have not been able to find anything similar for my situation. Basically I believe I would need some sort of reporting services object in .NET that would allow me to run the report and export the results. Does anyone know of such a structure, or if this is even possible? Thanks!!
Best regards,
Josh