Friday, March 23, 2012
Script to detect long running jobs
I don't think so, but you could build a process that reviews the
sysjobhistory table and determine those jobs runs that contain durations
longer then the average. Review the format of the sysjobhistory table in
BOL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:1FCC1245-DF44-4CA6-8856-4AEEC52206C8@.microsoft.com...
> Is there a function/proc to help me detect jobs that are running long, say
twice the normal run time?
Script to detect long running jobs
sysjobhistory table and determine those jobs runs that contain durations
longer then the average. Review the format of the sysjobhistory table in
BOL.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:1FCC1245-DF44-4CA6-8856-4AEEC52206C8@.microsoft.com...
> Is there a function/proc to help me detect jobs that are running long, say
twice the normal run time?
Script to detect long running jobs
wice the normal run time?I don't think so, but you could build a process that reviews the
sysjobhistory table and determine those jobs runs that contain durations
longer then the average. Review the format of the sysjobhistory table in
BOL.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:1FCC1245-DF44-4CA6-8856-4AEEC52206C8@.microsoft.com...
> Is there a function/proc to help me detect jobs that are running long, say
twice the normal run time?
Monday, March 12, 2012
Script generation for Objects - Management Studio
How do I generate a single script for each object - table, view, index, trigger, sp, function etc.... in SQL Server. The script that is generated from SQL Server wizards is in a single file.
I want to have separate file for each of the objects.
In EM, you right-click on the object, select 'all tasks', then 'Generate SQL Script'. I do not have access to Management Studio right now, so this may or may not be much help.
Do you have access to Enterprise Manager? You can run SQL Server 2000 and 2005 side by side, so it may be worthwhile installing if you cannot do this in MS.
Clarity Consulting (http://www.claritycon.com)
|||Well we have the same feature in SQL 2005 but I need one individual file for each object. I do not want the script of say all tables in file. I want this in different files with file name as table name that is getting scripted.
It applies to other objects like procs, funcs, triggers, constraints, FKs etc
|||You might look at Scriptio. I use it to create .sql files off of my db schema. On the 2nd tab I select ONE FILE PER OBJECT. www.sqlteam.com. The only problem I have found is that existing sql files (we re-exporting) can't be read-only.|||Currently, the Generate Script Wizard can only create a single file. We plan to implement file-per-object functionality in SP2.Script generation for Objects - Management Studio
How do I generate a single script for each object - table, view, index, trigger, sp, function etc.... in SQL Server. The script that is generated from SQL Server wizards is in a single file.
I want to have separate file for each of the objects.
In EM, you right-click on the object, select 'all tasks', then 'Generate SQL Script'. I do not have access to Management Studio right now, so this may or may not be much help.
Do you have access to Enterprise Manager? You can run SQL Server 2000 and 2005 side by side, so it may be worthwhile installing if you cannot do this in MS.
Clarity Consulting (http://www.claritycon.com)
|||Well we have the same feature in SQL 2005 but I need one individual file for each object. I do not want the script of say all tables in file. I want this in different files with file name as table name that is getting scripted.
It applies to other objects like procs, funcs, triggers, constraints, FKs etc
|||You might look at Scriptio. I use it to create .sql files off of my db schema. On the 2nd tab I select ONE FILE PER OBJECT. www.sqlteam.com. The only problem I have found is that existing sql files (we re-exporting) can't be read-only.|||Currently, the Generate Script Wizard can only create a single file. We plan to implement file-per-object functionality in SP2.Friday, March 9, 2012
script database - create to file
In the management studio there is a function to dump the DDL of a database, script database create to and then I can choose file.
What I was wondering and can't seem to find is, whether it is possible to somehow call this function from a program.
Any language, and connection will do for now, if at all posible, or if there is some other way I haven't found to dump the DDL for a database this woudl be much appreciated.
btw, I'm using a mssql 2005 database.
Regards
Unfortunately, that isn't possible. But here are several ways to approach the issue.
DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp
DDL –Script Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx?FamilyID=29B4FFD8-AC3A-4481-B352-9B185619A901
Hi,
Just to add a thought here - you could use SMO to achieve the task. For instance, SMO exposes a Database object with a Script() method. It is very well documented in the SQL Books Online.
In addition, you could take an easy shortcut - you could use SSIS (the new DTS) and create a scripting task. Then you could simply kick-off the task from your application.
HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Script Component Task: function that returns last word in a string
I have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task
create a function called CleanString (ByVal CustomerName as String) As String
CleanString Returns the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd
ie parse 'Mr John Tools' and the function returns 'Tools'
ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'
ie parse 'Microsoft Corp' return 'Microsoft Corp'
ie parse 'Digidesign Ltd' return 'Digidesign Ltd'
Any idea of a regular expression or existing piece of existing code I can have
thanks in advance
dave
I believe you'd want something along the lines of:
\s*(\w+(\s+(Corp|Ltd))?)$
Public Function CleanString(ByVal CustomerName As String) As String
Dim re As New Regex("\s*(\w+(\s+(Corp|Ltd))?)$")
Dim m As Match
m = re.Match(CustomerName)
' insert sanity to make sure we had a match
' group 0 would be the full string
' group 1 is the one we want
Dim g As Group = m.Groups(1)
Dim cc As CaptureCollection = g.Captures
CleanString = cc(0).Value
End Function
|||Matt,
Thanks the code almost worked--I had to rewrite it like this. However I need help to tweak the regular expression Regex("\s*(\w+(\s+(Corp|Ltd))?)$") Im getting the following results In/Out
Input: Digidesign Australasia Pty Ltd Output: Pty Ltd --wrong should be Ltd
Input: Ms S Aiten Output: Aiten --correct
Function CleanString(ByVal CustomerName As String) As String
m = re.Match(CustomerName)
' group 0 would be the full string
' group 1 is the one we want
CleanString = m.Groups(1).Value
End Function
'Called by
Public Class ScriptMain
Inherits UserComponent
Dim re As Regex = New Regex("\s*(\w+(\s+(Corp|Ltd))?)$")
Dim m As Match
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Len(Row.CUSTOMERNAME) > 0 Then
Output0Buffer.oCUSTOMERNAME = Row.CUSTOMERNAME.ToString
Output0Buffer.oSearchName = CleanString(Row.CUSTOMERNAME.ToString)
End If
|||I don't think you can easily do this with a single regex. You might want to pre-process the string to remove the Pty first, or have a special case that captures the words you want to the left and right of "Pty" whenever the string appears in your customer name.Wednesday, March 7, 2012
Script code for "Reinitialize All Subscripers" function SQL 2005
We are running Merge replication on SQL 2005 with Pocket PC Clients.
My question is, can i script (T-SQL) the same function as if i rightclick on
the Replication Publication, and select "Reinitialize All Subscripers" and
then click on "Create New SnapShot" + "Upload Changes"?
Which system stored procedures should i use? I need to do the same just with
T-SQL.
Thanks
use sp_reinitsubscription
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"RU Developer" <RUDeveloper@.discussions.microsoft.com> wrote in message
news:30B41C88-47D1-4C19-B580-757A3F40FE77@.microsoft.com...
> Hello All
> We are running Merge replication on SQL 2005 with Pocket PC Clients.
> My question is, can i script (T-SQL) the same function as if i rightclick
> on
> the Replication Publication, and select "Reinitialize All Subscripers" and
> then click on "Create New SnapShot" + "Upload Changes"?
> Which system stored procedures should i use? I need to do the same just
> with
> T-SQL.
> Thanks
|||or sp_reinitpullsubscription if you have pull subscriptions
Hilary Cotter wrote:[vbcol=seagreen]
> use sp_reinitsubscription
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "RU Developer" <RUDeveloper@.discussions.microsoft.com> wrote in message
> news:30B41C88-47D1-4C19-B580-757A3F40FE77@.microsoft.com...
Script / Function ... to find difference b/w 2 similar tables
I m searching for some Script / Function ... to find difference in data
b/w 2 similar tables (exactly same fields structure) in sql 2000.
plz update me asap !
thanks in advance !On 11 Jan 2005 22:34:27 -0800, AzGhanv/. wrote:
>I m searching for some Script / Function ... to find difference in data
>b/w 2 similar tables (exactly same fields structure) in sql 2000.
>plz update me asap !
Hi AzGhanv/.,
SELECT CASE
WHEN a.KeyCol = 1 THEN 'FirstTable'
ELSE 'SecondTable'
END AS Origin,
COALESCE (a.KeyCol1, b.KeyCol1) AS KeyCol1,
COALESCE (a.KeyCol2, b.KeyCol2) AS KeyCol2,
....
COALESCE (a.KeyColN, b.KeyColN) AS KeyColN,
COALESCE (a.DataCol1, b.DataCol1) AS DataCol1,
COALESCE (a.DataCol2, b.DataCol2) AS DataCol2,
....
COALESCE (a.DataColN, b.DataColN) AS DataColN
FROM FirstTable AS a
FULL OUTER JOIN SecondTable AS b
ON a.KeyCol1 = b.KeyCol1
AND a.KeyCol2 = b.KeyCol2
....
AND a.KeyColN = b.KeyColN
AND a.DataCol1 = b.DataCol1
AND a.DataCol2 = b.DataCol2
....
AND a.DataColN = b.DataColN
NOTE: The above assumes that no column may contain NULLS. For each data
column that allow NULLS, you'll have to replace
AND a.DataColX = b.DataColX
with
AND (a.DataColX = b.DataColX
OR (a.DataColX IS NULL AND b.DataColX IS NULL))
or, alternatively
AND NULLIF (a.DataColX, b.DataColX) IS NULL
AND NULLIF (b.DataColX, a.DataColX) IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Assuming by differences you mean rows in A not in B as well as vice
versa, a good solution would be
create table t1 (a int,b int)
create table t2 (a int,b int)
insert into t1 values (1,2)
insert into t1 values(3,4)
insert into t1 values(5,6)
insert into t2 values(1,2)
insert into t2 values(3,7)
insert into t2 values(5,6)
select sum(t) ,a,b from
(select 1 as t,* from t1
union all
select 2 as t,* from t2
) as both
group by a,b
this way you see both kinds of differences as 1 or 2, and 3 means no
difference (you can add having sum(t)<3 not to see them)
hope this helps
Tzvika
Saturday, February 25, 2012
scopeing problem in an aggregate function
select from my boolean parameter and change the scope on my aggregate
function. I already have my boolean parameter setup. the two groups are
called matrix1_Year and matrix1_Hub. i used the following expression in my
textbox
=Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
when i preview the report it gives me the following error
The Value expression for the textbox â'textbox5â' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set.
If i take the IIF statement out and put in either of the group names, the
report works and i get the values i would expect, but i need to be able to do
this by passing in a parameter, i don't want to create two report just for
this one problem.Have you tried adding the IIF to the outside... ie
=IIf (Parameters@.Scope1.Value,Big Expression1, Big Expression2)
I do not know if this will help, but it is possible that the problem is
related to WHEN the expression is evaluated..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"gacar101" wrote:
> I have a matrix with two column groups. I need to be able have the user
> select from my boolean parameter and change the scope on my aggregate
> function. I already have my boolean parameter setup. the two groups are
> called matrix1_Year and matrix1_Hub. i used the following expression in my
> textbox
> =Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
> when i preview the report it gives me the following error
> The Value expression for the textbox â'textbox5â' has a scope parameter that
> is not valid for an aggregate function. The scope parameter must be set to a
> string constant that is equal to either the name of a containing group, the
> name of a containing data region, or the name of a data set.
> If i take the IIF statement out and put in either of the group names, the
> report works and i get the values i would expect, but i need to be able to do
> this by passing in a parameter, i don't want to create two report just for
> this one problem.
Tuesday, February 21, 2012
SCOPE_IDENTITY() Help
Better might be a return value that you get at via a Return Code parameter using ExecuteNonQuery. No reason you could not do a SELECT SCOPE_IDENTITY() at the end of an INSERT SP and then get at it using ExecuteScalar() if you wish.
CREATE PROCEDURE spfoo
@.fooID int
AS
INSERT INTO fooTable(fooID) VALUES(@.fooID)
RETURN SCOPE_IDENTITY()
|||
Hi
Using SQLDataSource and SQL 2005 Stored Procedure I've been successful in creating multiple additional CreateUser fields saving them to "tbl_UserDetails" table.
I pass the UserId to the tbl_UserDetails as well. What I'm struggling with is retreiving the new tbl_UserDetails "Details_Id" - t-sql RETURN SCOPE_IDENTITY()).
I want to integrate it into the Profile data (see Profile creation below in CompleteButton_Click (71)).
I'm not sure if the problem retreiving the Details_Id is in the SQLDSInsert_Inserted (133) code used or if I'm not addressing the retreival in the "correct" stage of "the process". See below.
Thanks
1Imports Telerik.WebControls2Imports SITE.DAL.DBAccess3Imports System.Data4Imports System.Data.SqlClient56PartialClass Site_Pages_Registration7Inherits System.Web.UI.Page8Protected WithEvents Update_DateAs WebControls.HiddenField910Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load11If Session("Record") IsNotNothing Then12 CreateUserWizard1.MoveTo(CompleteWizardStep1)13End If1415 End Sub1617 Protected Sub CreateUserWizard1_CreatedUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles CreateUserWizard1.CreatedUser18Dim userAs MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)1920If userIs Nothing Then21 Throw New ApplicationException("Can't find the user.")22 End If2324 Dim DetailsInsert As SqlDataSource = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("SQLDSInsert"), SqlDataSource)25 Dim UserId As Guid = DirectCast(user.ProviderUserKey, Guid)26 Session("NewUserId") = UserId27 Dim Details_FirstName As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_FirstName"), TextBox)28 Dim Details_LastName As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_LastName"), TextBox)29 Dim Details_MI As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_MI"), TextBox)30 Dim Details_Title As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Title"), TextBox)31 Dim Details_Company As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Company"), TextBox)32 Dim Details_1Comm As Telerik.WebControls.RadMaskedTextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1Comm"), Telerik.WebControls.RadMaskedTextBox)33 Dim Details_1CommType As Telerik.WebControls.RadComboBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1CommType"), Telerik.WebControls.RadComboBox)34 Dim Details_2Comm As Telerik.WebControls.RadMaskedTextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2Comm"), Telerik.WebControls.RadMaskedTextBox)35 Dim Details_2CommType As Telerik.WebControls.RadComboBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2CommType"), Telerik.WebControls.RadComboBox)36 Dim Details_Address As TextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Address"), TextBox)37 Dim Details_City As TextBox = CType(CreateUserWizardStep0.ContentTemplateContainer.FindControl("Details_City"), TextBox)38 Dim Details_State As Telerik.WebControls.RadComboBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_State"), Telerik.WebControls.RadComboBox)39 Dim Details_Zip As Telerik.WebControls.RadMaskedTextBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Zip"), Telerik.WebControls.RadMaskedTextBox)40 Dim Details_UserTypeId As Telerik.WebControls.RadComboBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_UserTypeId"), Telerik.WebControls.RadComboBox)41 Dim Details_ReceiveEmail As CheckBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_ReceiveEmail"), CheckBox)4243 DetailsInsert.Insert()4445 Dim UserType As Telerik.WebControls.RadComboBox = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_UserTypeId"), Telerik.WebControls.RadComboBox)46 Session("UserType") = UserType.SelectedValue.ToString47 End Sub4849 Protected Sub SQLDSInsert_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)50 Dim user As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)5152 e.Command.Parameters("@.UserId").Value = user.ProviderUserKey53 e.Command.Parameters("@.Details_FirstName").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_FirstName"), TextBox).Text54 e.Command.Parameters("@.Details_LastName").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_LastName"), TextBox).Text55 e.Command.Parameters("@.Details_MI").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_MI"), TextBox).Text56 e.Command.Parameters("@.Details_Company").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Company"), TextBox).Text57 e.Command.Parameters("@.Details_Title").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Title"), TextBox).Text58 e.Command.Parameters("@.Details_1Comm").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1Comm"), Telerik.WebControls.RadMaskedTextBox).Text59 e.Command.Parameters("@.Details_1CommType").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1CommType"), Telerik.WebControls.RadComboBox).SelectedValue60 e.Command.Parameters("@.Details_2Comm").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2Comm"), Telerik.WebControls.RadMaskedTextBox).Text61 e.Command.Parameters("@.Details_2CommType").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2CommType"), Telerik.WebControls.RadComboBox).SelectedValue62 e.Command.Parameters("@.Details_Address").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Address"), TextBox).Text63 e.Command.Parameters("@.Details_City").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_City"), TextBox).Text64 e.Command.Parameters("@.Details_State").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_State"), Telerik.WebControls.RadComboBox).SelectedValue65 e.Command.Parameters("@.Details_Zip").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Zip"), Telerik.WebControls.RadMaskedTextBox).Text66 e.Command.Parameters("@.Details_UserTypeId").Value = Int32.Parse(CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_UserTypeId"), Telerik.WebControls.RadComboBox).SelectedValue)67 e.Command.Parameters("@.Details_ReceiveEmail").Value = CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_ReceiveEmail"), CheckBox).Checked68 End Sub697071 Protected Sub CompleteButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)72 Dim user As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)7374 Dim pb As ProfileBase = ProfileBase.Create(user.UserName)75 pb.SetPropertyValue("FirstName", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_FirstName"), TextBox).Text)76 pb.SetPropertyValue("LastName", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_LastName"), TextBox).Text)77 pb.SetPropertyValue("UserName", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox).Text)78 pb.SetPropertyValue("Record", Session("Record"))79 pb.SetPropertyValue("Company", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Company"), TextBox).Text)80 pb.SetPropertyValue("Title", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Title"), TextBox).Text)81 pb.SetPropertyValue("Comm1", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1Comm"), Telerik.WebControls.RadMaskedTextBox).Text)82 pb.SetPropertyValue("Comm1Type", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1CommType"), Telerik.WebControls.RadComboBox).SelectedValue)83 pb.SetPropertyValue("Comm2", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2Comm"), Telerik.WebControls.RadMaskedTextBox).Text)84 pb.SetPropertyValue("Comm2Type", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2CommType"), Telerik.WebControls.RadComboBox).SelectedValue)85 pb.SetPropertyValue("Address", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Address"), TextBox).Text)86 pb.SetPropertyValue("City", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_City"), TextBox).Text)87 pb.SetPropertyValue("State", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_State"), Telerik.WebControls.RadComboBox).SelectedValue)88 pb.SetPropertyValue("Zip", CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Zip"), Telerik.WebControls.RadMaskedTextBox).Text)8990 pb.Save()9192 Dim lblRegisterCompleteName As Label = CreateUserWizard1.CompleteStep.FindControl("lblRegisterCompleteName"), Label93 lblRegisterCompleteName.Text = Profile.FirstName & " " & Profile.LastName94 Dim Muser As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)95 If Muser Is Nothing Then96 Throw New ApplicationException("Can't find the user.")97End If98 Muser.IsApproved =False99 End Sub100101 Protected Sub btnTeamProfile_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)102Dim userAs MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)103104Dim pbAs ProfileBase = ProfileBase.Create(user.UserName)105 pb.SetPropertyValue("FirstName",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_FirstName"), TextBox).Text)106 pb.SetPropertyValue("LastName",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_LastName"), TextBox).Text)107 pb.SetPropertyValue("UserName",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox).Text)108 pb.SetPropertyValue("Record", Session("Record"))109 pb.SetPropertyValue("Company",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Company"), TextBox).Text)110 pb.SetPropertyValue("Title",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Title"), TextBox).Text)111 pb.SetPropertyValue("Comm1",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1Comm"), Telerik.WebControls.RadMaskedTextBox).Text)112 pb.SetPropertyValue("Comm1Type",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_1CommType"), Telerik.WebControls.RadComboBox).SelectedValue)113 pb.SetPropertyValue("Comm2",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2Comm"), Telerik.WebControls.RadMaskedTextBox).Text)114 pb.SetPropertyValue("Comm2Type",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_2CommType"), Telerik.WebControls.RadComboBox).SelectedValue)115 pb.SetPropertyValue("Address",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Address"), TextBox).Text)116 pb.SetPropertyValue("City",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_City"), TextBox).Text)117 pb.SetPropertyValue("State",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_State"), Telerik.WebControls.RadComboBox).SelectedValue)118 pb.SetPropertyValue("Zip",CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Details_Zip"), Telerik.WebControls.RadMaskedTextBox).Text)119120 pb.Save()121 Response.Redirect("Profile.aspx")122End Sub123124 Protected Sub CompleteWizardStep1_PreRender(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles CompleteWizardStep1.PreRender125Dim btnTeamProfileAs Button =CType(CompleteWizardStep1.ContentTemplateContainer.FindControl("btnTeamProfile"), Button)126If Session("UserType") ="2"Then127 btnTeamProfile.Visible =True128 Else129 btnTeamProfile.Visible =False130 End If131 End Sub132133 Protected Sub SQLDSInsert_Inserted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)134Dim DetailsInsertAs SqlDataSource =CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("SQLDSInsert"), SqlDataSource)135Dim RecordAs Integer = DetailsInsert.InsertParameters.Add("@.Details_Id", System.Data.ParameterDirection.ReturnValue)136'Dim Record As Integer = e.Command.ExecuteScalar137 Session("Record") = Record.ToString138139 Response.Redirect("Registration.aspx")140End Sub141142143End Class144
scope_identity()
Code Snippet
create function fillmuon1(jdbc ds)->boolean
as for each muon m
sqlu(ds," Insert into particle (id,eventid,px,py,pz,kf,ee) VALUES (" +
itoa(id(m)) + "," + itoa(id(event(m))) + "," + itoa(px(m)) + "," +
itoa(py(m)) + "," + itoa(pz(m)) + "," + itoa(kf(m)) + "," + itoa(Ee(m)) + ");
Insert into leptonaux(id) VALUES (SCOPE_IDENTITY());
Insert into muonaux(id) VALUES (SCOPE_IDENTITY());");
fillmuon1(:ds);
My problem when i call scope_identity() for the first time works, but when i call it the second time is trying to add a null value, so what can I do to keep the value
Yes. The idenity values will be reset when you call INSERT statement.
Change the code as follow as,
sqlu(ds," Insert into particle (id,eventid,px,py,pz,kf,ee) VALUES (" +itoa(id(m)) + "," + itoa(id(event(m))) + "," + itoa(px(m)) + "," +
itoa(py(m)) + "," + itoa(pz(m)) + "," + itoa(kf(m)) + "," + itoa(Ee(m)) + ");
Declare @.ID as int; Set @.ID=SCOPE_IDENTITY(); Insert into leptonaux(id) VALUES (@.ID);
Insert into muonaux(id) VALUES (@.ID);");
SCOPE_IDENTITY returns decimal from SQL command
Hi,
I have an sqlCommand return the ID from the last inserted row. I use SELECT SCOPE_IDENTITY within the SQL.
When I call the function it returns a Decimal instead of an integer??
So I want to convert this to an integer, however this is not liked by the compiler since the overloaded method for the decimal conversion is obviously a decimal, but the ExecuteScalar parameter is of type object.
iReturnVal = decimal.ToInt32(_sqlCommand.ExecuteScalar()); <-- compiler complains...
So I changed to this:
iReturnValue = decimal.ToInt32((decimal)_sqlCommand.ExecuteScalar());
Can someone tell me if this is the right way to do it? Seems messy code to me...
swaino:
iReturnVal = decimal.ToInt32(_sqlCommand.ExecuteScalar()); <-- compiler complains...
Decimal.toint32? I thought it was convert.toint32?
|||It should already be an integer.
|||
Welldecimal. provides loads of conversion functions.
Also ExecuteScalardoes return an object not an integer..