Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brin
bin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedule
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen
|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>
|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:

> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 databases
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
>
>

Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brinbin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedu
le
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:

> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 database
s
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
>
>sql

Multiple instances of SQL

Hi
I am considering consolidating our environment to a Single SQL enterprise
server. I'm curious about how the instances are handled.. if one instance
gets updated how does this impact the other instances that are running. What
effect does this have as far as the master db goes. Is there a single point
of failure in sql that may crash the whole environment? Any help would be
greatly appreciated.
thanks
D
Hello I have a number of servers that have multiple instances on them. When
patching everything is quite straight forward and works as you expect namely
only the instance that is being upgraded is affected. This includes the DLLs
and EXEs for each instance, master does count because its very obviously
different for each instance and as you run the patch one instance at a time
only one gets the update.
If this is prod server I would think about getting a box with enough CPU and
RAM to divvy up the resources to each instance. You’ll need to look into CPU
affinity.
We run this configuration when wanting to separate 3rd party applications
that are mostly poorly designed and our in house applications. We also use
this for spinning up multiple dev and test environments.
Hope that helps
John Vandervliet
"DS" wrote:

> Hi
> I am considering consolidating our environment to a Single SQL enterprise
> server. I'm curious about how the instances are handled.. if one instance
> gets updated how does this impact the other instances that are running. What
> effect does this have as far as the master db goes. Is there a single point
> of failure in sql that may crash the whole environment? Any help would be
> greatly appreciated.
> thanks
> D

multiple insert call for a table having insert trigger

Hi

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1

ThanksLooks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO

Cheers|||Thanks for reply me back..
I am using cursor to call multiple insert inside that curser.. here is the code..

OPEN DestinationIDList
FETCH NEXT FROM DestinationIDList INTO @.DestinationID

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO Table(ID, DestinationID) VALUES(@.ID, @.DestinationID)

FETCH NEXT FROM DestinationIDList INTO @.DestinationID
END
CLOSE DestinationIDList
DEALLOCATE DestinationIDList

What should I have to do so that trigger fire for each insert ?

Thanks

Originally posted by aldo_2003
Looks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO

Cheers|||What you could do is to write the Insert as a stored procedure.

Then call this proc from your cursor passing the variables that you have defined in your cursor

something like :

create proc Insert_T1
(@.id as int , @.DestinationID as int)
as

INSERT INTO T1(ID, DestinationID) VALUES(@.ID, @.DestinationID)
go

All you do call this proc in your cursor land this should make your trigger fire for every insert you perform

Give it a go and see if it works

Cheers|||No Luck :-(|||Can you post the code for your trigger

Cheers|||Here is the trigger's code

CREATE TRIGGER TR_TD
ON dbo.TRHistory FOR INSERT

AS

DECLARE @.ID int
DECLARE @.DSTID int
DECLARE @.RT decimal (18,4)
DECLARE @.Time datetime
DECLARE @.GTID int
DECLARE @.GTExist int
DECLARE @.intErrorCode INT
SET @.GTID = 0
SET @.GTExist = 0
SET @.ID = 0

-- CHECK ID' S VALIDITY

SELECT @.ID = i.ID, @.DSTID= i.DSTID,
@.RT = i.RT, @.Time = i.Time
FROM Inserted i
INNER JOIN ITSPS ON ITSPS.ID = i.ID

IF @.ID <> 0

BEGIN

-- FIND OUT NO OF GTS FOR SPECIFIC DSTS TO UPDATE FOR CORRESPONDING GT
DECLARE GTList CURSOR FOR
-- SELECT GTS FOR DSTID
SELECT GTs.GTID
FROM GTs INNER JOIN
GTDSTs ON GTs.GTID = GTDSTs.GTID INNER JOIN
ITSPs ON GTs.ID = ITSPs.ID
Where GTs.ID = @.ID AND DSTID = @.DSTID

OPEN GTList

FETCH NEXT FROM GTList INTO @.GTID

-- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
IF (@.GTID = 0)
BEGIN
SELECT @.intErrorCode = 1
CLOSE GTList
DEALLOCATE GTList
GOTO PROBLEM

END
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0

BEGIN

-- CHECK WIEHTER GT ENTERY EXISTS IN TR TABLE
-- IF SO THEN UPDATE ELSE INTER NEW FOR GT

SELECT @.GTExist = GTid
FROM TR
WHERE GTid = @.GTid AND DSTID = @.DSTID

IF @.GTExist > 0

UPDATE TR
SET RT = @.RT,
Time = getdate()

WHERE GTID = @.GTid


ELSE

INSERT INTO TR (GTID, DSTID, RT)
VALUES (@.GTID,@.DSTID,@.RT)
-- IN CASE FOR ANY EXCEPTION GO TO PROBLEM PARA AND CLOSE & DEALLOCATE CURSOR
SELECT @.intErrorCode = @.@.ERROR
IF (@.intErrorCode <> 0)
BEGIN
CLOSE GTList
DEALLOCATE GTList
GOTO PROBLEM
END
FETCH NEXT FROM GTList INTO @.GTID


END
CLOSE GTList
DEALLOCATE GTList
COMMIT TRAN
END
ELSE
-- INVALID ID
BEGIN
RAISERROR (' Invalid ID ', 16, 1)
ROLLBACK TRAN
END


PROBLEM:
IF (@.intErrorCode <> 0)
BEGIN
IF (@.intErrorCode = 1)
RAISERROR (' Insert is rejected due to invalid GT DST info', 16, 1)
ELSE
RAISERROR ('Error occured to udated info for tr' s. Please contact administrator ', @.intErrorCode, 1)
ROLLBACK TRAN


END|||Hi aldo_2003

I am Waiting.. Only problem is that if i use multiple insert with GO then multiple insert works.. but when i call is in cursor for multiple then it add only first insert..|||Just got back from lunch mate

Am goiung to try a couple if things and get back to you

Cheers|||Had a quick look at the trigger

Don't think it is the GO statement as I managed to get a test trigger to fire twice without the GO.

I'm not sure why your trigger is only firing once.

What you might want to do is to try and reduce the complexity of the code within the trigger by putting that logic within the stored proc and calling this from your trigger.

You have cursors within cursors when you take the trigger into account and this may be causing you issues that you are not aware of.

Get the trigger to fire properly without any logic in it i.e

Get the first cursor inserting into your table and the trigger firing a simple insert into a test table. Once you have that then implement your trigger logic in a stored proc and call that from the trigger.

I hope this helps, let me know how you get on.

Cheers|||Well there are just so many things...

first you don't need a cursor...collapse the cursor and the insert in to 1...

second a cursor in a trigger can't be a good idea performance wise...but like I said collapse them

third

-- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
IF (@.GTID = 0)

isn't a check for existance...

Look at @.@.ROWCOUNT

fourth...never mind...fixe the insert first...

Multiple FK or "TableName" column?

Hi!
I have a table "addresses". Address can be associate to a customer, an
employee, a supplier or a consultant. Every of these 5 have a table with an
identity primary key. My question is simple: Which of the 2 ways is the best
to link the "Addresses" table to other tables?
A- By adding 5 foreign keys, assuming that only one will be NOT NULL by reco
rd
B- Adding a column Varchar "ParentTable" and a Int column "ParentID" to make
the relation programmatically?
I need the best way to be faster and also easier to implement...
Thanks in Advance!David Parenteau wrote:
> Hi!
> I have a table "addresses". Address can be associate to a customer, an
> employee, a supplier or a consultant. Every of these 5 have a table
> with an identity primary key. My question is simple: Which of the 2
> ways is the best to link the "Addresses" table to other tables?
> A- By adding 5 foreign keys, assuming that only one will be NOT NULL
> by record B- Adding a column Varchar "ParentTable" and a Int column
> "ParentID" to make the relation programmatically?
> I need the best way to be faster and also easier to implement...
> Thanks in Advance!
Or C - Create 5 tables for CustomerAddress, EmployeeAddress, etc.
Or D - Add an AddressID to the Customer, Employee, etc. tables if they
can only have one address (probably not the case)
Or E - Create a generic Person table and a PersonAddress table. Relate
Person to Customer, Employee, etc.
I don't like option A and option B is not relational.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||First of all, an IDENTITY cannot ever be a key by definition, so the
schema is screwed up from the start. Ignoring that fundamental
problem, start by asking the basic questions:
1) Is an address an attribute of a customer, an employee, a supplier
and a consultant? In which case you need a SAN (Standard Address
Number) or equivalent in a column in those tables. I am assuming that
people fall into one and only one of these roles and that they are
logically different. Otherwise, there would be a People table.
2) Is there a one-to-many relationship between an address and a
customer, an employee, a supplier or a consultant? That case, you will
need tables to model the customer-addresses, the employee-addresses,
the supplier-addresses and the consultant-addresses relationships. I
would also add type codes to the addresses (ship-to, bill-to, etc.).|||Wow... suggestion!
Yes, more than one adress per customers, etc...
For E, does the Person table has 5 FK, one for each table or you mean 5
Person table, one for customer, one for employee, and so on?
Addresses should be the same columns for each 5 entities, so Solution C
seems to be not the quickest way to choose if a change must occur in the
schema... WHat do you think about this?
"David Gugick" wrote:

> David Parenteau wrote:
> Or C - Create 5 tables for CustomerAddress, EmployeeAddress, etc.
> Or D - Add an AddressID to the Customer, Employee, etc. tables if they
> can only have one address (probably not the case)
> Or E - Create a generic Person table and a PersonAddress table. Relate
> Person to Customer, Employee, etc.
> I don't like option A and option B is not relational.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||First create and load this table. might want to look at Melissa Data
software to edit the addresses,
CREATE TABLE Addresses
(address_nbr INTEGER NOT NULL PRIMARY KEY,
street1 CHAR(35) NOT NULL,
street2 CHAR(35),
street3 CHAR(35),
city_name CHAR(20) NOT NULL,
state_code CHAR(2) NOT NULL,
zip4 CHAR(10) NOT NULL
CHECK (zip4 LIKE '[0-9][0-9][0-9][0-9][0-9]'
OR zip4 LIKE
'[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
verification_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);
Next, create each of the tables with the proper key for that kind of
entity. For suppliers, the Dun & Bradsteet codes are common; employees
better have an SSN, etc.
CREATE TABLE Suppliers
(duns_nbr CHAR(9) NOT NULL PRIMARY KEY,
supplier_name CHAR(35) NOT NULL,
.);
Now create a relationship table between each of the entities and the
addresses:
CREATE TABLE SupplierAddresses
(duns_nbr CHAR(9) NOT NULL
REFERENCES Suppliers(duns_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
address_nbr INTEGER NOT NULL,
REFERENCES Addresses(address_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
supplier_address_type CHAR(5) INTEGER NOT NULL
CHECK (supplier_address_type IN ('ship', 'bill', 'HQ', 'serv',
...)),
PRIMARY KEY (duns_nbr, address_nbr));
Make heavy use of DRI actions to maintian data integrity. Take time to
invent good address type codes for each logical entity (i.e. a customer
does not have a service department or a headquarters).|||About the Identity that cannot be a Key, do you mean a "Natural key"? If yes
,
I'm agree.
I used to set a FieldID INT Identity has a Primary key in my tables, to
avoid multiple-field primary keys. This seems to be easier to locate a recor
d
from a web application or application... Does this a very bad thing to do? I
f
yes, why?
David
"--CELKO--" wrote:

> First of all, an IDENTITY cannot ever be a key by definition, so the
> schema is screwed up from the start. Ignoring that fundamental
> problem, start by asking the basic questions:
> 1) Is an address an attribute of a customer, an employee, a supplier
> and a consultant? In which case you need a SAN (Standard Address
> Number) or equivalent in a column in those tables. I am assuming that
> people fall into one and only one of these roles and that they are
> logically different. Otherwise, there would be a People table.
> 2) Is there a one-to-many relationship between an address and a
> customer, an employee, a supplier or a consultant? That case, you will
> need tables to model the customer-addresses, the employee-addresses,
> the supplier-addresses and the consultant-addresses relationships. I
> would also add type codes to the addresses (ship-to, bill-to, etc.).
>|||Celko,
I now have a new example: A table activity. But the "activities" will need
to be linked to all 150 tables in my database... So, is it a reason to avoid
creating 150 link tables and keep the table name in a column of the
"Activity" table?
Thanks!!
"--CELKO--" wrote:

> First of all, an IDENTITY cannot ever be a key by definition, so the
> schema is screwed up from the start. Ignoring that fundamental
> problem, start by asking the basic questions:
> 1) Is an address an attribute of a customer, an employee, a supplier
> and a consultant? In which case you need a SAN (Standard Address
> Number) or equivalent in a column in those tables. I am assuming that
> people fall into one and only one of these roles and that they are
> logically different. Otherwise, there would be a People table.
> 2) Is there a one-to-many relationship between an address and a
> customer, an employee, a supplier or a consultant? That case, you will
> need tables to model the customer-addresses, the employee-addresses,
> the supplier-addresses and the consultant-addresses relationships. I
> would also add type codes to the addresses (ship-to, bill-to, etc.).
>

Multiple DropDowns error

Hi:

I have two drop downs bound to the same data source.. These dropdowns are automatically populated from a database. When I click the button I get some sort of strange query error.

Not sure what I'm doing wrong here.

<%@.ImportNamespace="System.Data" %>

<%@.ImportNamespace="System.Data.SQLClient" %>

<scriptlanguage="VB"runat="server">

Dim sOrderbyasString

Dim sDirectionasString

Dim MySQLAsString

Dim MySQL1AsString

Dim sSubjectAsString

Dim sCategoryAsString

Sub Page_Load(ByVal SourceAsObject,ByVal EAs EventArgs)

IfNot Page.IsPostBackThen

Dim strConnAsString ="server=GAALP-DT-UHABB2\CFW;uid=sa;pwd=removed;database=NetG"

Dim MySQLAsString ="Select DISTINCT [Subject] from dbo_v_netG_courses"

Dim MySQL1AsString ="Select DISTINCT [Category] from dbo_v_netG_courses"

Dim MyConnAsNew SqlConnection(strConn)

Dim objDRAs SqlDataReader

Dim CmdAsNew SqlCommand(MySQL, MyConn)

Dim Cmd1AsNew SqlCommand(MySQL1, MyConn)

MyConn.Open()

objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddl.DataSource = objDR

ddl.DataValueField ="Subject"

ddl.DataTextField ="Subject"

ddl.DataBind()

MyConn.Close()

MyConn.Open()

ddlDir.DataSource = Cmd1.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddlDir.DataValueField ="Category"

ddlDir.DataTextField ="Category"

ddlDir.DataBind()

MyConn.Close()

ddl.Items.Insert(0,"-- Choose --")

ddlDir.Items.Insert(0,"-- Choose --")

EndIf

'ddl.Items.Insert(0, "-- Choose --")

EndSub

' Sub Page_Change(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)

' MyDataGrid.CurrentPageIndex = e.NewPageIndex

' BindData()

'Sub GridOne(ByVal Source As Object, ByVal E As EventArgs)

' MyDataGrid.CurrentPageIndex = 0

'End Sub

'Sub GetData(ByVal Source As Object, ByVal E As EventArgs)

' BindData()

' End Sub

Sub BindData(ByVal SourceAsObject,ByVal EAs EventArgs)

sSubject = ddlDir.SelectedItem.Text

sCategory = ddlDir.SelectedItem.Value

Dim strConnAsString ="server=GAALP-DT-UHABB2\CFW;uid=sa;pwd=removed;database=NetG"

If sSubject =""And sCategory =""Then

MySQL ="Select * from dbo_v_netG_courses"

Else ( THIS LINE IS GIVING ME THE ERROR)

MySQL ="Select * from dbo_v_netG_courses where [Subject] = & sSubject"

EndIf

Dim MyConnAsNew SqlConnection(strConn)

Dim dsAs DataSet =New DataSet()

Dim CmdAsNew SqlDataAdapter(MySQL, MyConn)

Cmd.Fill(ds,"dbo_v_netG_courses")

MyDataGrid.DataSource = ds.Tables("dbo_v_netG_courses").DefaultView

MyDataGrid.DataBind()

EndSub

</script>

<html>

<head>

<metaname="GENERATOR"Content="ASP Express 3.0">

<title>Ad Hoc Sorting with a DataGrid</title>

</head>

<body>

<Formid="form1"runat="server">

<table>

<tr>

<tdalign="Left"valign="Top"><b><i>View Employee Data</i></b></td>

<tdalign="right"valign="Top">

Subject:<asp:dropdownlistid="ddl"runat="server">

</asp:dropdownlist>

Category:<asp:dropdownlistid="ddlDir"runat="server">

</asp:dropdownlist><br/>

<br/>

<asp:Buttonid="btn1"Text="View Records"onclick="BindData"runat="server"/><br/>

</td>

</tr>

<tr>

<tdalign="Left"valign="Top"Colspan="2">

<asp:Datagridrunat="server"

Id="MyDataGrid"

GridLines="Both"

cellpadding="0"

cellspacing="0"

Headerstyle-BackColor="#8080C0"

Headerstyle-Font-Bold="True"

Headerstyle-Font-Size="12"

BackColor="#8080FF"

Font-Size="10"

AlternatingItemStyle-BackColor="#EFEFEF"

AlternatingItemStyle-Font-Size="10"

BorderColor="Black">

</asp:DataGrid><br> </td>

</tr>

</table>

</form>

</body>

</html>

This code works now.. One question though.. How can I return all values by default when the page loads..

Thanks

Working code in VB for multiple dropdown selections and then button click to submit values

<%@.ImportNamespace="System.Data" %>

<%@.ImportNamespace="System.Data.SQLClient" %>

<scriptlanguage="VB"runat="server">

Dim sOrderbyasString

Dim sDirectionasString

Dim MySQLAsString

Dim MySQL1AsString

Dim sSubjectAsString

Dim sCategoryAsString

Sub Page_Load(ByVal SourceAsObject,ByVal EAs EventArgs)

IfNot Page.IsPostBackThen

Dim strConnAsString ="server=LAPTOP;uid=sa;pwd=sa;database=NetG"

Dim MySQLAsString ="Select DISTINCT [Subject] from dbo_v_netG_courses"

' Dim MySQL1 As String = "Select DISTINCT [Category] from dbo_v_netG_courses"

Dim MyConnAsNew SqlConnection(strConn)

Dim objDRAs SqlDataReader

Dim CmdAsNew SqlCommand(MySQL, MyConn)

' Dim Cmd1 As New SqlCommand(MySQL1, MyConn)

MyConn.Open()

objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddl.DataSource = objDR

ddl.DataValueField ="Subject"

ddl.DataTextField ="Subject"

ddl.DataBind()

MyConn.Close()

' MyConn.Open()

' ddlDir.DataSource = Cmd1.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

'ddlDir.DataValueField = "Category"

' ddlDir.DataTextField = "Category"

' ddlDir.DataBind()

'MyConn.Close()

ddl.Items.Insert(0,"-- Choose --")

'ddlDir.Items.Insert(0, "-- Choose --")

EndIf

'ddl.Items.Insert(0, "-- Choose --")

EndSub

Sub fillModel(ByVal SourceAsObject,ByVal EAs EventArgs)

Dim strConnAsString ="server=LAPTOP;uid=sa;pwd=sa;database=NetG"

If ddl.SelectedItem.Text <>"-- Choose --"Then

sSubject = ddl.SelectedItem.Value

Dim MySQL1AsString ="Select DISTINCT Category from dbo_v_netG_courses where [Subject]=" &"'" & sSubject &"'"

Dim MyConnAsNew SqlConnection(strConn)

'Dim objDR As SqlDataReader

Dim Cmd1AsNew SqlCommand(MySQL1, MyConn)

MyConn.Open()

ddlDir.DataSource = Cmd1.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddlDir.DataValueField ="Category"

ddlDir.DataTextField ="Category"

ddlDir.DataBind()

' ddl2.selectedindex=0

' tdmodel.visible = "true"

'lit1.text = "<b>Vehicle Type</b>: " & ddl1.selectedItem.text

' ddl2.items.insert(0, "-- Choose --")

' tdStyle.visible = "false"

MyConn.Close()

EndIf

ddlDir.Items.Insert(0,"-- Choose --")

EndSub

' Sub Page_Change(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)

' MyDataGrid.CurrentPageIndex = e.NewPageIndex

' BindData()

'Sub GridOne(ByVal Source As Object, ByVal E As EventArgs)

' MyDataGrid.CurrentPageIndex = 0

'End Sub

'Sub GetData(ByVal Source As Object, ByVal E As EventArgs)

' BindData()

' End Sub

Sub BindData(ByVal SourceAsObject,ByVal EAs EventArgs)

sSubject = ddl.SelectedItem.Value

sCategory = ddlDir.SelectedItem.Value

Dim strConnAsString ="server=LAPTOP;uid=sa;pwd=sa;database=NetG"

If sSubject =""And sCategory =""Then

MySQL ="Select * from dbo_v_netG_courses"

ElseIf sSubject ="-- Choose --"And sCategory =""Then

MySQL ="Select * from dbo_v_netG_courses"

'Page.IsPostBack = True

Else

MySQL ="Select * from dbo_v_netG_courses where [Subject]=" &"'" & sSubject &"'" &" and [Category] =" &"'" & sCategory &"'"

Dim MyConnAsNew SqlConnection(strConn)

Dim dsAs DataSet =New DataSet()

Dim CmdAsNew SqlDataAdapter(MySQL, MyConn)

Cmd.Fill(ds,"dbo_v_netG_courses")

MyDataGrid.DataSource = ds.Tables("dbo_v_netG_courses").DefaultView

MyDataGrid.DataBind()

EndIf

EndSub

</script>

<html>

<head>

<metaname="GENERATOR"Content="ASP Express 3.0">

<title>Ad Hoc Sorting with a DataGrid</title>

</head>

<body>

<Formid="form1"runat="server">

<table>

<tr>

<tdalign="Left"valign="Top"><b><i>View Employee Data</i></b></td>

<tdalign="right"valign="Top">

Subject:<asp:dropdownlistid="ddl"

runat="server"

onselectedindexchanged="fillModel"AutoPostBack="True"

>

</asp:dropdownlist>

Category:<asp:dropdownlistid="ddlDir"runat="server"DataTextField="Model">

</asp:dropdownlist><br/>

<br/>

<asp:Buttonid="btn1"Text="View Records"onclick="BindData"runat="server"/><br/>

</td>

</tr>

<tr>

<tdalign="Left"valign="Top"Colspan="2">

<asp:Datagridrunat="server"

Id="MyDataGrid"

cellpadding="0"

Headerstyle-BackColor="#8080C0"

Headerstyle-Font-Bold="True"

Headerstyle-Font-Size="12"

BackColor="#8080FF"

Font-Size="10pt"

AlternatingItemStyle-BackColor="#EFEFEF"

AlternatingItemStyle-Font-Size="10"

BorderColor="Black"AllowSorting="True">

<AlternatingItemStyleBackColor="#EFEFEF"Font-Size="10pt"/>

<HeaderStyleBackColor="#8080C0"Font-Bold="True"Font-Size="12pt"/>

</asp:DataGrid><br> </td>

</tr>

</table>

</form>

</body>

</html>

|||I assume this is an .NET 1.1 application?

Multiple databases

Hi
I need to run a masive script on multiple databases (about 20)
I only want to execute the script once to then loop and run on all databases
one by one. Is there a clean way of doing so?
Thankscreate a third party application with dot net
put the server name in array of connection string
with in a for-next loop
call each connection string and then
call the execute method
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Jaco" wrote:

> Hi
> I need to run a masive script on multiple databases (about 20)
> I only want to execute the script once to then loop and run on all databas
es
> one by one. Is there a clean way of doing so?
> Thanks|||Yup
use undocumented sp_MSforeachdb. Google for that
Regards
R.D
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> create a third party application with dot net
> put the server name in array of connection string
> with in a for-next loop
> call each connection string and then
> call the execute method
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Jaco" wrote:
>|||Thanks.
"R.D" wrote:
> Yup
> use undocumented sp_MSforeachdb. Google for that
> Regards
> R.D
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||thats
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"R.D" wrote:
> Yup
> use undocumented sp_MSforeachdb. Google for that
> Regards
> R.D
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>

multiple connection sql server express

HI
I have a win app (always running 24x7) (.net 2 c#) that uses sql server
express 2005.
I use the following connection string to attach it in the app.config
<connectionStrings>
<add name="Browser.Properties.Settings.DataConnectionSt ring"
connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data. mdf;Integrated
Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Every day a win service, using the same MDB updates/imports the data from
another source. This all works as expected. when the windows service has
completed its update, the win app is notified to refresh its data and show
the updates.
My issue, is that though the data has been refreshed and the win app
reconnects to the database to collect the updates (new datasets) it does not
reflect the updates, simply the older version. I think it is the way I have
attached the database (the win app starts first on sys reboot) is there a
better way of 2 apps using the same database without having to attach it.
Thanks
Richard
It doesn't work that way. You are either both using the same mdf or you are
not. If you are using the same one then any committed changes one user makes
are immediately available to the other user. Your app may require refreshing
if it caches the data but SQL Server only has 1 copy of the committed data.
The exception is if you are using one of the snapshot isolation levels. In
that case depending on the level and what you are doing you may see the
original versions. But you have had to explicitly turn this on and I don't
even think Express edition has this capability available in the first place.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Steele" <RichardSteele@.discussions.microsoft.com> wrote in message
news:48E10FFE-E1A2-4318-BFE0-F1E07E077D17@.microsoft.com...
> HI
> I have a win app (always running 24x7) (.net 2 c#) that uses sql server
> express 2005.
> I use the following connection string to attach it in the app.config
> <connectionStrings>
> <add name="Browser.Properties.Settings.DataConnectionSt ring"
> connectionString="Data
> Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data. mdf;Integrated
> Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
> providerName="System.Data.SqlClient" />
> </connectionStrings>
> Every day a win service, using the same MDB updates/imports the data from
> another source. This all works as expected. when the windows service has
> completed its update, the win app is notified to refresh its data and show
> the updates.
> My issue, is that though the data has been refreshed and the win app
> reconnects to the database to collect the updates (new datasets) it does
> not
> reflect the updates, simply the older version. I think it is the way I
> have
> attached the database (the win app starts first on sys reboot) is there a
> better way of 2 apps using the same database without having to attach it.
> --
> Thanks
> Richard