Database backup!....................

Hi All,
Does anyone know how to write an xp_ Stored Procedure or
dll to make to normal sql backup run faster.
Thanks and please point me to the right direction or
source for this.
tony
trying to issue a SQL Server backup thru a self created DLL will most likely
take longer and have more overhead. I would suggest you look at SQL
LiteSpeed from http://www.imceda.com/ to speed up your backups. They will
also be much smaller as well.
Andrew J. Kelly SQL MVP
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:14f501c499ba$829e8490$a301280a@.phx.gbl...
> Hi All,
> Does anyone know how to write an xp_ Stored Procedure or
> dll to make to normal sql backup run faster.
> Thanks and please point me to the right direction or
> source for this.
> tony
|||You can also use more than one backup device - this may help your
situation. You can find some other suggestions and things to monitor
for bottlenecks in the books online topic:
Optimizing Backup and Restore Performance
-Sue
On Mon, 13 Sep 2004 10:53:00 -0700, "tony"
<anonymous@.discussions.microsoft.com> wrote:

>Hi All,
>Does anyone know how to write an xp_ Stored Procedure or
>dll to make to normal sql backup run faster.
>Thanks and please point me to the right direction or
>source for this.
>tony
|||Have a look at our article at http://www.yohz.com/articles_01.html. Amongst
other things, it describes how Microsoft provides a standard interface for
third party software vendors to implement their own backup/restore solution.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:14f501c499ba$829e8490$a301280a@.phx.gbl...
> Hi All,
> Does anyone know how to write an xp_ Stored Procedure or
> dll to make to normal sql backup run faster.
> Thanks and please point me to the right direction or
> source for this.
> tony

Database Backup in a Maintenance Plan

I want to write a SQL query to find if a perticular database is backed up in
any Maintenance plan or not.
Is there any way to write this query.
I can surely open all the Plans and view it, but I need to find it out using
a query.
Please let me know if it is possible.
Thanks in advance, Steven
Which version are you talking about? If it is 2000 then have a look at the
sysdbmaintplan_databases table in msdb.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Steven" <Steven@.nospaml.com> wrote in message
news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>I want to write a SQL query to find if a perticular database is backed up
>in any Maintenance plan or not.
> Is there any way to write this query.
> I can surely open all the Plans and view it, but I need to find it out
> using a query.
> Please let me know if it is possible.
> Thanks in advance, Steven
>
|||Thanks for the suggestion.
I'll take it from her.
Thanks again, Steven
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exZYoUZiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> ... or perhaps the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
>

Database Backup in a Maintenance Plan

I want to write a SQL query to find if a perticular database is backed up in
any Maintenance plan or not.
Is there any way to write this query.
I can surely open all the Plans and view it, but I need to find it out using
a query.
Please let me know if it is possible.
Thanks in advance, StevenWhich version are you talking about? If it is 2000 then have a look at the
sysdbmaintplan_databases table in msdb.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Steven" <Steven@.nospaml.com> wrote in message
news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>I want to write a SQL query to find if a perticular database is backed up
>in any Maintenance plan or not.
> Is there any way to write this query.
> I can surely open all the Plans and view it, but I need to find it out
> using a query.
> Please let me know if it is possible.
> Thanks in advance, Steven
>|||... or perhaps the backup history tables in msdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
> Which version are you talking about? If it is 2000 then have a look at the
> sysdbmaintplan_databases table in msdb.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Steven" <Steven@.nospaml.com> wrote in message news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>>I want to write a SQL query to find if a perticular database is backed up in any Maintenance plan
>>or not.
>> Is there any way to write this query.
>> I can surely open all the Plans and view it, but I need to find it out using a query.
>> Please let me know if it is possible.
>> Thanks in advance, Steven
>>
>|||Thanks for the suggestion.
I'll take it from her.
Thanks again, Steven
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exZYoUZiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> ... or perhaps the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
>> Which version are you talking about? If it is 2000 then have a look at
>> the sysdbmaintplan_databases table in msdb.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Steven" <Steven@.nospaml.com> wrote in message
>> news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>>I want to write a SQL query to find if a perticular database is backed up
>>in any Maintenance plan or not.
>> Is there any way to write this query.
>> I can surely open all the Plans and view it, but I need to find it out
>> using a query.
>> Please let me know if it is possible.
>> Thanks in advance, Steven
>>
>

DataAdapter Update Problems

Hi, I am populating a datagrid with data from one sql table, I add additional columns and then wish to write it back out to another table. I have written the following code to acheive this, however it keeps throwing the follwing exception - "Update requites a valid UpdateComman when passed a DataRow collection with modified rows"

I've looked and I can see what the problem is, can anybody help please?

Private Sub FillData()'SQL connectionMe.sqlconnection =New System.Data.SqlClient.SqlConnectionMe.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")Dim sqlAs String If Request("action") ="gen"Then sql ="select item from tblPickList where GroupName ='xxx'AND Category = '" & Category2 & "' ORDER BY item ASC" ElseIf Request("action") = "fav" Then sql = "select identifiertext as item from tblfavourites where HRID =" & Session("hrid") & " and type = 3 ORDER BY identifiertext ASC" ElseIf Request("action") = "spec" Then sql = "select item from tblPickList where GroupName ='" & GroupID & "' AND Category = '" & Category2 & "' ORDER BY item ASC"End If ds =New DataSet da =New SqlClient.SqlDataAdapter(sql, sqlconnection)Try sqlconnection.Open() da.Fill(ds,"tblPickListTemp") sqlconnection.Close()Catch exAs Exception sqlconnection.Close()End Try ds.Tables(0).Columns.Add("HRID") ds.Tables(0).Columns.Add("Flag")'may possibly add a favourites column here at a later date to ensure no duplicationDim drAs DataRowFor Each drIn ds.Tables("tblPickListTemp").Rows dr("HRID") = Session("hrid") dr("Flag") = 0Next DgrdFavourites.DataSource = ds DgrdFavourites.DataBind()End Sub Public Sub CreateTempRecords()'SQL connectionMe.sqlconnection =New System.Data.SqlClient.SqlConnectionMe.sqlcommand =New System.Data.SqlClient.SqlCommandMe.da =New SqlClient.SqlDataAdapterMe.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")Dim sqlAs String sql ="Insert into tblPickListTemp (Item, HRID, Flag) values (@.Item, @.HRID, @.Flag)" sqlcommand.CommandText = sql sqlcommand.Connection = sqlconnection sqlcommand.Parameters.Add("@.Item","Item") sqlcommand.Parameters.Add("@.HRID","HRID") sqlcommand.Parameters.Add("@.Flag","Flag") da.InsertCommand = sqlcommand da.Update(ds.Tables(0))End Sub

Hello Naimh,

Your program retrieves records from the database, you modify them and try to update the records in the database.

In that case you need to add an UpdateCommand to your SqlDataAdapter.

The Update statement will than handle new rows and modified rows.

Jeroen Molenaar.

|||I thought that was what I was doing with the da.InsertCommand on the 2nd to last line.I'm not sure I've really got to grips with the dataadapter syntax, could you show me please?|||

The InsertCommand you use is correct. But you are not adding new rows to the table, so you need also the UpdateCommand.

sql = "Update tblPickList Set HRID = @.HRID, Flag = @.Flag where Item = @.Item"

// Add the parameters here ...

sqlcommand.CommandText = sql

da.UpdateCommand = sqlcommand

// etc.

Hope you get it working now.

Jeroen Molenaar.

|||

jeroenm:

The InsertCommand you use is correct. But you are not adding new rows to the table, so you need also the UpdateCommand.

Okay really confused now, as that is exactly what I want to do, I populate the dataset from one table, manipulate it in the dataset and then Insert entirely new row's into a completely different table, erk! I shall give your suggestion a try but I have to fess up to being none the wiser.|||

jeroenm:

sql?= "Update tblPickListTemp Set HRID = @.HRID, Flag = @.Flag where Item = @.Item"

See now this is never going to work for me, tblPickListTemp ( the table I want to insert rows into) at the start of this process is empty, I cannot update and set fields to new values when no records currently exist.Ah well back to the drawing board, thanks anyway.|||

Do you have an AcceptChanges statement somewhere before the data is saved to the database?

When you modify the dataset after AcceptChanges, the tableadapter thinks it needs an Update statement in stead of an Insert statement.

Jeroen Molenaar.

|||No I am afraid I don't, all the code is in the snippet above. It's most confusing. I have also ensured that the primary key is set in the recipient table, which is another common problem which throws this error. I am at a real loss and almost at the point of scrapping the whole thing and taking a different approach, it's just that if I could have got this to work it would have been less trips to the Db and a lot less spaghetti code.|||

You can add some ASP.NET code. Perhaps this gives another clue.

|||

<form id="Form1" method="post" runat="server"><TABLE id="Table2" cellSpacing="0" cellPadding="0" width="280" align="center" border="0"><TR height="24"><TD class="fntmed" style="WIDTH: 280px" background="../images/header.jpg">         PickList</TD></TR><TR><TD class="fntnorm" style="WIDTH: 280px; HEIGHT: 36px"><IMG height="6" src="../images/6pixspacer.gif"><br><asp:image id="imgLine" runat="server" Width="260px" ImageUrl="../Images/line.jpg"></asp:image></TD></TR><TR><TD><div style="OVERFLOW: auto; WIDTH: 270px; POSITION: static; HEIGHT: 190px"><asp:DataGrid id="DgrdFavourites" autogeneratecolumns="False" BackColor="#ffffff" showheader="False"OnItemCommand="DgrdFavourites_ItemCommand" runat="server" Width="256px" Height="25px" BorderWidth="0"CssClass="fntsmall" AlternatingItemStyle-CssClass="fntsmall"><AlternatingItemStyle BackColor="#e2eaee" /><Columns><asp:BoundColumn DataField="Item" /><asp:templatecolumn ItemStyle-HorizontalAlign="Right"><ItemTemplate><asp:button CssClass="buttonlike" Text="Select" commandname="Select" runat="server" ID="btnSelect" /></ItemTemplate></asp:templatecolumn><asp:templatecolumn ItemStyle-HorizontalAlign="Right"><ItemTemplate><asp:button CssClass="buttonlike" Text="Favourite" runat="server" commandname="Add" ID="btnAdd" /></ItemTemplate></asp:templatecolumn></Columns></asp:DataGrid></div></TD></TR></TABLE><asp:Label ID="oops" Runat="server"></asp:Label></form></body>

That the entirety of the front end code, basically at page load inside a "If not Is Postback" the Filldata() method is invoked followed by the CreateTempData() method. Not a lot to it really.|||

Hello Naimh,

Sorry, but I've been moving you into the wrong direction. My first idea was correct, but I didn't read your code properly.

When you call FillData the dataset ds is filled from the table tblPickList. In FillData you add 2 columns and modify all rows with the session HRID. At this stage your rows have the status Modified in the dataset ds. (This generates the error message when calling da.Update())

Before "DgrdFavourites.DataSource = ds" you can enter the command ds.AcceptChanges(). All modifications are accepted and no update statement will be generated by the dataadapter.

The second thing is make some changes to CreateTempRecords. Create a new dataset dstemp as a copy of ds. After that insert the rows from ds into the new dataset dstemp, for example with a foreach construction. Now the new dataset is ready for the insert statement that you have written in CreateTempRecords(). Change da.Update(ds.Tables(0)) into da.Update(dstemp.Tables(0)).

Now your code should do the job!

Jeroen Molenaar.

Data Warehousing:Best way to write SP?

I'm writing a set of queries designed to test the data quality of our data warehouse at the fact level. The intent is to ensure that there are no keys at the fact level that do not exist at the dimension level.

I'm dealing with 8 fact tables, 8 dimension tables and 9 keys. I'm currently doing:

SELECT COUNT(DISTINCT key)
FROM t_fact
WHERE key NOT IN
(
SELECT DISTINCT key FROM t_dimension
)

If I do one of those for each key-fact table combo, there are about 50 queries in total. Not every key exists in every fact table.

I'm a Stored Procedure novice. What is the best way to check all of the fact tables, aside from running 50 counts with subqueries? If I run the queries one fact table at a time, it will take about 30 minutes. I've tried to run one query per fact table, by counting all keys, and doing a subselect to each dimension table, but got misleading results.

Any tips will be greatly appreciated. Abandoning data warehousing isn't a current option!

MikeIn Oracle I would find missing keys like this:

SELECT key FROM t_fact
MINUS
SELECT key FROM t_dimension

BTW, why not use a foreign key constraint to ensure all fact keys are based on dimension keys?