Adding Procedure To SQL Server Express
May 18, 2006
I am adding (trying to add) a procedure (written by a friend of mine and someone who is experienced). I am adding it via the SQL Server Mgt. Studio Express. When I run it, it says that the command completed successfully, but it doesn't appear in the Procedure folder.
When I run my ASP 2.0 form that calls it, it says that it can't find the procedure.
Can anyone help me determine what to look for or what I might be doing wrong?
Thank you.
View 3 Replies
ADVERTISEMENT
Dec 6, 2005
When adding records containing a date field in SQL Server 2005 Express I get an error Input string was not in a correct format. Do datefields have to be converted when adding or editing into strings?
View 2 Replies
View Related
Feb 19, 2007
Hi, I have installed a trial version of VC++ Express 2005 with thebudled SQL Server. After creating a C++ Forms prototype, with sometables into a database file named Test1.mdb, now I want to make ODBCavailable this local database.What are the steps required to add and ODBC entry so any ODBC-capableprogram may access my prototype?I know in advance the location of the database file and log file, knowit is local (so the server is localhost or just (local) ) and also thedriver name and the port number.What is unknown to me is the user id and password as the installlationwas done to auth with OS credentials.Could be "sa" without password?Thanks in advance, Carlos
View 1 Replies
View Related
Feb 20, 2006
Hi there,
I'm new to programming and trying to learn as much as I can about SQL Server Express and VB Express.
I'm trying to write some values from a textbox to a SQL database, can someone point me to a sample of this?
Thanks
View 3 Replies
View Related
Mar 26, 2008
I am sure that I am missing something which is why I am in need of the communities assistance...
I have installed SQL Server 2005 Express Edition. I can log in with the Windows Authenication and the SQL Server Authentication using the 'sa' account. This is all good...
What I want to be able to do is add 1 to 3 more users so they can gain access to the database I have created to run queries and add data. This is the part I am confused about. How do I get additional users added??
Thanks in advance for your time and assistance!
View 3 Replies
View Related
Aug 4, 2006
Can I add images to a table with SQL Server Express Management Studio? If so, how, if not, what alternative methods other than the point-and-click can I use?
Thanks in advance,
Fedor Steeman
View 1 Replies
View Related
Jul 21, 2015
I have made this defination for a stored procedure:
PROCEDURE EP_Conterbalances
@Start_Date_For_Totals_Date DATETIME,
@EmpFilterAddDuty VARCHAR(500),
@CounterBalanceType_id INT,
@dateFrom DATETIME,
[Code] .....
The value of @EmpFilterAddDuty could be:
'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID '
If i Replace @EmpFilterAddDuty with this in a QUERY, it gives me the expected result, but if i try to execute the stored procedure.:
DECLARE@return_value int
EXEC@return_value = [dbo].[EP_Conterbalances]
@Start_Date_For_Totals_Date = N'20120831',
@EmpFilterAddDuty = 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B
[Code] .....
I get this error code:
Conversion failed when converting the varchar value 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ' to data type int.
I really do not understand why SQL 2012 tries to convert the value to an int, and I want to know how to pass the text string.
View 4 Replies
View Related
Feb 16, 2007
Hi all,
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
Bill
View 7 Replies
View Related
Sep 5, 2006
Hi everybody, I created several stored procedure in a local sql server 2005 express database, now when I call/execute them in the asp.net 2.0 web page, it returns an error message of "Cannot find the stored procedure '<proc_name>'", this is funny, when I execute the commandin a sql query window "exec sp_GetAllArticles", it also returns cannot find stored procedure, but when I execute the "Use <DBName>" and thenexecute the statement, it works. The sql connection used in codes is ok. So, what I have missed to set or where I made mistake?Code:Inside web.config, <add name="Connectstring" connectionString="Data Source=DENSIASQLExpress;Initial Catalog=DbName;Integrated Security=True" providerName="System.Data.SqlClient" /> inside Load event, DAL.ConnectionString = ConfigurationManager.ConnectionStrings["Connectstring"].ConnectionString; private void DisplayLatestResources() { DAL.CreateCommand("sp_GetLatestFiveTrainingResources"); DAL.ExecuteReader(); SqlDataReader dr = DAL.DataReader; ..... } Try supplying with user id and password, same result...also try using aspnet_regsql.exe, same result also... Thanks in advanced. den2005
View 1 Replies
View Related
Sep 10, 2006
I am new to SQL Server 2005 and Visual C++ 2005 Express.
When I try and add a connection in Visual C++:
DataSource: SQL Server Database File
File Name: c:.......file.mdf
Windows Authentication
I get the following message:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under default settings SQL Server does not allow remote connections. (provider: Share Memeory Provider, error:36 - The Shared Memory dll used to connect to SQL Server 2000 was not found)
Any ideas?
View 5 Replies
View Related
Feb 28, 2008
Hi i m using vwd2005 express and sql express.1. I want to write stored procedure in sql express using sql server management studio express. I m not sure where to write and how to execute the stored procedure in sql server management studio express. Can any one explain the steps required to achieve this? 2.And what is the difference between creating database using:- a. right click the project in solution explorer-> add new item->SQl database(creating .mdf file)and b.clicking a database explorer->data connection->Add connection .... thanks. jack.
View 4 Replies
View Related
Jun 5, 2007
I have created an extended stored procedure (that executes a DLL file) in SQL Server 2005 Express. When I execute this procedure the execution enters an infinite loop and hangs there. When I execute the same procedure on SQL Server 2000 it executes correctly. I wonder if the problem relates to SQL Server 2005, since no problem occurs with SQL Sever 2000. And if so, how could I repair it?
Best Regards,
Ramy
View 2 Replies
View Related
Apr 6, 2005
calculate age:CREATE PROCEDURE selectage ASSELECT Age = datediff(yy, birthday, getdate()) + case when datepart(dy, birthday) <= datepart(dy, getdate()) then 0 else -1endFROM nopaymy ? is how do fix this problem:I add inWHERE (((Age)>= @age1 And (Age)<= @age2)) and it will complain about Age is not a column, so how would i store the underlined and bold Age aboves value?
View 6 Replies
View Related
Mar 27, 2006
Currently I add a new stored procedure in Enterprise Manager by right-clicking Stored Procedure and clicking New Stored Procedure then copy/paste text of procedure into window displayed. Whenever we set up new customer, this process is repeated several times to get all of our stored procedures loaded.
Is there a way to automate this process?
Thanks
View 2 Replies
View Related
Jan 31, 2008
Hello,
What is the easiest way to add a strored prodedure in SQL- server? Sounds like silly question but it doesn´t seem that obvious to me.
BTW, I´m using SQL-server 2005.
Thanks in advance!
View 6 Replies
View Related
Dec 5, 2006
I have wrriten many stored procedures in the past without issue, but this is my first time using SQL Server Management Studio Express. I am having trouble creating a new stored procedure. Here is what's happening:
I am opening my database, right clicking on "Stored Procedures" and selecting "New Stored Procedure". A new window opens with a template for creating a stored procedure. The window is called: "SQLEXPRESS.DBName - sqlquery1.sql". I then type up my stored procedure without an issue.
However, when I go to save the stored procedure it wants to save it as a separate file in a projects folder. It does not become part of the DB (as far as I can tell).
When I used to use Enterprise Manager (not an option anymore) this never happened.
I'm sure I'm doing something dumb. Can someone enlighten me.
Thanks,Chris
View 4 Replies
View Related
Jan 21, 2006
Could somebody tell me how do we create a .NET Stored Procedure in Sql Server 2005 Express Edition and deploy and debug it against the database from Visual Studio 2005 or Visual Web Developer? Can some one also let me know which approach is faster among .NET stored procedure or T-SQL stored procedure?
Regards...
Shashi Kumar Nagulakonda.
View 1 Replies
View Related
Feb 17, 2006
How many Stored Procedure can I add to a database in SQL SERVER 2005 Express edition?
I remember that only 32 Stored Procedure can be added to a database in SQL SERVER 2005 Express edition,but now I can add over 32 Stored Procedure to a database from SQL Server Management Studio Express CTP! How many Stored Procedure can I add to a database in SQL SERVER 2005 Express edition?
View 1 Replies
View Related
Apr 13, 2007
hi everyone,
I have a db based on the Tracking_Schema.sql / Tracking_Logic.sql (find in &windir%/Microsoft.NET/Framework/v3.0/Windows Workflow Foundation/SQL/EN), so after executing both of them I get several stored procedures, especially dbo.GetWorkflows. And I have a solution in VS05 which when executed is filling this stored procedure with Instance-Id´s. My question is: how is the working command (like exec, truncate,..) to empty my st.procedure, not to drop/delete it?
Thanks in advance, best regards
bg
View 1 Replies
View Related
Apr 12, 2007
Hi all,I am creating a ASP.Net 2.0 website and in it I had to create a CLR stored procedure to do a complex sql procedure. Coming to the problem I created the CLR stored procedure as a different database project . I wanted to know whether its possible to add the CLR managed code into my exisiting ASP.Net project in which case I should get the dll for this stored procedure in order to deploy the stored proc in the SQL Server.Or is there some simplified approach to using clr stored procedures in an ASP.Net project.
View 2 Replies
View Related
Jul 19, 2007
Hi,I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.Help!!!I want to add @OrderBy at the end of :-----------------SELECT *FROM AnnoncesWHERE CategoryID = @CategoryID AND DateOnCreate >= @DateFinale-----------------My declaration :Declare @OrderBy nvarchar(50)If @Sort = 'date_de' set @OrderBy = ' ORDER BY DateOnCreate DESC'Else set @OrderBy = ' ORDER BY DateOnCreate ASC'
View 8 Replies
View Related
Apr 30, 2008
Hello,
I am trying to create an ASPNET Login for a SQL Server express Database (As Installed via Visual Studio) so my ASP Web Applications can invoke the Database. But SQLServer Managemnt Tool is not recognising any ASPNET user names I try.
I am trying to add via Microsoft SQL Server Management Studio Express, but get an error
" An Object (User or Built-in Security principal) with the following name cannot be found "mycomputerASPNET". "
Any obvious reason why ASPNET is not being recognised as a Login object object
Unfortunately I am on VISTA and IIS7 ( I have enabled, and checked that ASP Applications Run.)
- The process works OK on my other XP machine, ASP.NET via IIS 5.1, which seems more straightforward to set up.
Any help Appreciated, Since I am really stumped
Cheers
View 1 Replies
View Related
May 27, 2007
Hi,
I'm a complete novice concerning SQL Server (Express Edition)
I'm trying to Add or Delete rows froma VB 2005 Express Function or Sub. While the program is running everything is ok. Except when restarted added records are gone and deleted records are back.
Have i missed an option during installation?
Thx,
Steven
View 5 Replies
View Related
May 15, 2007
Hi,I'm trying to create a page where a user can search the database according to some criteria and get back the result in the form of a GridView. Also, the user has the option of saving the criteria to another table in the database by assigning it a name so that it can be retrieved easily in the future.I have the search and display part working, however, saving the criteria to the database is giving problems for some reason.Given below is my stored procedure to add the info to the db. SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddToReport]
(@ReportName varchar(100), @ProjID varchar(300), @DeptCode varchar(20), @ProjType varchar(20), @ProjectTitle varchar(300),
@ProjectManagerID int, @DateRequested datetime, @DueDate datetime, @ProjectStatusID int)
AS
SET NOCOUNT ON
DECLARE @Dept varchar(50)
DECLARE @err int
BEGIN TRANSACTION
IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN
IF @DeptCode IS NOT NULL
BEGIN
SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','')
END
SET @err = @@ERROR
INSERT INTO dbo.tbl_Report (ReportName, ProjID, DeptCode, ProjType, ProjectTitle, ProjectManagerID, DateRequested, DueDate, ProjectStatusID)
VALUES (@ReportName, @ProjID, @Dept, @ProjType, @ProjectTitle, @ProjectManagerID, @DateRequested, @DueDate, @ProjectStatusID);
IF @err<>0
BEGIN
ROLLBACK TRANSACTION
RETURN @err
END
END
COMMIT TRANSACTION Given below is the relevant codebehind. This is how the values are initialized: Dim newManager As New ListItem
newManager.Text = "Choose a Manager"
newManager.Value = 0
projectManagerDDL.Items.Add(newManager)
Dim newDept As New ListItem
newDept.Text = "Choose a Department"
newDept.Value = ""
deptCodeDDL.Items.Add(newDept)
Dim newID As New ListItem
newID.Text = "Choose a Project"
newID.Value = ""
projIDDDL.Items.Add(newID)
Dim newStatus As New ListItem
newStatus.Text = "Choose a Status"
newStatus.Value = 0
projectStatusDDL.Items.Add(newStatus)
Dim newDateRequestedMonth As New ListItem
newDateRequestedMonth.Text = "Month"
newDateRequestedMonth.Value = 0
dateRequestedMonthDDL.Items.Add(newDateRequestedMonth)
Dim newDateRequestedDay As New ListItem
newDateRequestedDay.Text = "Day"
newDateRequestedDay.Value = 0
dateRequestedDayDDL.Items.Add(newDateRequestedDay)
Dim newDateRequestedYear As New ListItem
newDateRequestedYear.Text = "Year"
newDateRequestedYear.Value = 0
dateRequestedYearDDL.Items.Add(newDateRequestedYear)
Dim newDueDateMonth As New ListItem
newDueDateMonth.Text = "Month"
newDueDateMonth.Value = 0
dueDateMonthDDL.Items.Add(newDueDateMonth)
Dim newDueDateDay As New ListItem
newDueDateDay.Text = "Day"
newDueDateDay.Value = 0
dueDateDayDDL.Items.Add(newDueDateDay)
Dim newDueDateYear As New ListItem
newDueDateYear.Text = "Year"
newDueDateYear.Value = 0
dueDateYearDDL.Items.Add(newDueDateYear) This is the submit code: Protected Sub saveButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim connString As String
Dim con As SqlConnection
Try
connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString
con = New SqlConnection(connString)
Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = con
If (([String].IsNullOrEmpty(reportNameTextBox.Text) = False) Or reportNameTextBox.Text <> "Enter Report Name") Then
cmd.Parameters.Add("ReportName", SqlDbType.VarChar, 300).Value = reportNameTextBox.Text
End If
If ([String].IsNullOrEmpty(projIDDDL.SelectedItem.Value)) = False Then
cmd.Parameters.Add("ProjID", SqlDbType.VarChar, 30).Value = projIDDDL.SelectedItem.Value
End If
If ([String].IsNullOrEmpty(deptCodeDDL.SelectedItem.Value)) = False Then
cmd.Parameters.Add("DeptCode", SqlDbType.VarChar, 20).Value = deptCodeDDL.SelectedItem.Value
End If
If (typeRBL.SelectedItem.Value <> "All") Then
cmd.Parameters.Add("ProjType", SqlDbType.VarChar, 20).Value = typeRBL.SelectedItem.Value
End If
If ([String].IsNullOrEmpty(projectTitleTextBox.Text)) = False Then
cmd.Parameters.Add("ProjectTitle", SqlDbType.VarChar, 300).Value = projectTitleTextBox.Text
End If
If CInt(projectManagerDDL.SelectedItem.Value) <> 0 Then
cmd.Parameters.Add("ProjectManagerID", SqlDbType.Int).Value = CInt(projectManagerDDL.SelectedItem.Value)
End If
If (dateRequestedDayDDL.SelectedItem.Value = 0 Or dateRequestedMonthDDL.SelectedItem.Value = 0 Or dateRequestedYearDDL.SelectedItem.Value = 0) Then
Dim dateRequested As New DateTime
dateRequested = Nothing
Else
Dim dateRequested As New DateTime(dateRequestedYearDDL.SelectedValue, dateRequestedMonthDDL.SelectedValue, dateRequestedDayDDL.SelectedValue)
If (dateRequested) <> Nothing Then
cmd.Parameters.Add("DateRequested", SqlDbType.DateTime).Value = dateRequested
End If
End If
If (dueDateDayDDL.SelectedItem.Value = 0 Or dueDateMonthDDL.SelectedItem.Value = 0 Or dueDateYearDDL.SelectedItem.Value = 0) Then
Dim dueDate As New DateTime
dueDate = Nothing
Else
Dim dueDate As New DateTime(dueDateYearDDL.SelectedValue, dueDateMonthDDL.SelectedValue, dueDateDayDDL.SelectedValue)
If (dueDate) <> Nothing Then
cmd.Parameters.Add("DueDate", SqlDbType.DateTime).Value = dueDate
End If
End If
If (projectStatusDDL.SelectedItem.Value) <> 0 Then
cmd.Parameters.Add("ProjectStatusID", SqlDbType.Int).Value = CInt(projectStatusDDL.SelectedItem.Value)
End If
cmd.CommandText = "dbo.AddToReport"
cmd.CommandType = CommandType.StoredProcedure
Try
con.Open()
cmd.ExecuteNonQuery()
Response.Write("Report Saved")
Catch ex As Exception
Response.Write(ex)
Finally
con.Close()
con.Dispose()
End Try
Catch ex As ApplicationException
Response.Write("Could not load the database")
End Try
End Sub The only absolute requirement when saving to the table is the ReportName. All the other criteria can be NULL. If I don't select and values and try to save the values, I get an error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects
parameter '@ProjID', which was not supplied. at
System.Data.SqlClient.SqlConnection.OnError... etc If I choose the ProjID (thus giving it a value), I get the following error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects
parameter '@DeptCode', which was not supplied. at
System.Data.SqlClient.SqlConnection.OnError... etc and so forth. I'm guessing it's a problem of NULLs somewhere, but I'm not sure. Thanks.
View 6 Replies
View Related
Dec 20, 2007
Hi...
Am working with asp.net with vb for a MusicProject..
In this I have playlist for particular User..ie User Selects Some Songs and clicks on AddToMyPlayList Button....
Here He can insert those songs into a new playlist or update the earlier..
the StroedProcedure is as follws: 'N' means NewPlayList and 'E' means Existing
***************************************************************************************
CREATE PROCEDURE MUSIC_ADD_PLAYLIST ( @PLAYLIST_NAME VARCHAR(255), @USER_ID VARCHAR(255), @ItemList NVARCHAR(4000), @delimiter CHAR(1), @FOLDERNAME VARCHAR(255) ,@PLAYLISTTYPE CHAR(1)) AS SET NOCOUNT ON
DECLARE @IDENT INT
IF @PLAYLISTTYPE = 'N'
BEGIN INSERT INTO MUSIC_PLAYLIST ( MUSIC_PLAYLIST_NAME, MUSIC_PLAYLIST_USER ) VALUES ( @PLAYLIST_NAME, @USER_ID )
SELECT @IDENT=@@IDENTITY FROM MUSIC_PLAYLIST END IF @PLAYLISTTYPE='E'
BEGIN
SELECT @IDENT=MUSIC_PLAYLIST_ID FROM MUSIC_PLAYLIST WHERE MUSIC_PLAYLIST_USER=@USER_ID AND MUSIC_PLAYLIST_NAME=@PLAYLIST_NAME
END
DECLARE @tempItemList NVARCHAR(4000) SET @tempItemList = @ItemList DECLARE @i INT DECLARE @Item NVARCHAR(4000) SET @tempItemList = REPLACE (@tempItemList, ' ', '') SET @i = CHARINDEX(@delimiter, @tempItemList) WHILE (LEN(@tempItemList) > 0) BEGIN IF @i = 0 SET @Item = @tempItemList ELSE SET @Item = LEFT(@tempItemList, @i - 1) -- INSERT INTO @IDTable(Item) VALUES(@Item)
INSERT INTO MUSIC_SONGSLIST (Music_PlayList_Id,Music_SongName,Music_Song_Location) VALUES (@IDENT,@ITEM,@FOLDERNAME+''+@ITEM) IF @i = 0 SET @tempItemList = '' ELSE SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) SET @i = CHARINDEX(@delimiter, @tempItemList) END
GO
***************************************************************************************
Here the problem is am not getting the exact result means if i add 6 songs to a playlist it is adding only 4 songs to that particular playlist..
Please help me out
Thanks in Advance,
Madhavi
View 1 Replies
View Related
Jan 7, 2004
Is there a way to add a field or a stored procedure to a server running MSDE? Like a script on the command line or?? how can this be done.
Thank you,
View 5 Replies
View Related
Jan 10, 2007
I am trying to figure out how to add a time datestamp to my xp_sendmail procedure:
use master;
go
CREATE PROC pr_sendmail
AS
DECLARE @DT DATETIME
SET @DT=GETDATE()
BEGIN
EXEC xp_sendmail @recipients = 'me@work.com',
@message = 'send email from SQL Server Stored Procedure.',
@copy_recipients = 'me@work.com',
@subject = 'Job Started at ', @DT
END
How do I get this to work? Thanks!
View 7 Replies
View Related
Nov 15, 2007
The stored procedure below was working fine and I have added a inner join to it and it stopped working. I have highlighted the new code I have added to the stored procedure in red. Any suggestions on how to solve this issue?
I am getting the below error
Server: Msg 209, Level 16, State 1, Procedure AIG_GetRECON_TRANSACTION, Line 53
Ambiguous column name 'REINS_TYPE_CD'.
below is the query i changed.
ALTER PROCEDURE [dbo].[AIG_GetRECON_TRANSACTION]
@RECON_TRNSCTN_ID int=NULL,
@RECON_ITEM_ID int=NULL,
@DIVISION_ID int=NULL,
@REINS_TYPE_CD int=NULL,
@TRANSACTION_NO char(10)=NULL,
@TRANSACTION_NAME varchar(100)=NULL,
@REF_NUMBER varchar(20)=NULL,
@POLICY_CRS_REF_NO varchar(20)=NULL,
@GL_DTFrom datetime=NULL,
@GL_DTTo datetime=NULL,
@TRANSACTION_CD int=NULL,
@AMOUNT money=NULL,
@FILE_STATUS_CD int=NULL,
@TRNSCTN_OWR_CD int=NULL,
@ISSUE_CD int=NULL,
@SUPP_ISSUE_CD int=NULL,
@IRC_CLASSIFICATION_CD int=NULL,
@UNDER_90_DAYS money=NULL,
@OVER_90_DAYS money=NULL,
@AGING_DAYS_CNT int=NULL,
@VOCHER_NO varchar(50)=NULL,
@LOADED_DTFrom datetime=NULL,
@LOADED_DTTo datetime=NULL,
@SPUsageMode TINYINT = 0 -- This should be the last parameter
AS
-------------------------------------------------------------------------------
-- SP Usage Audit Info -- DO NOT REMOVE
-- All Stored Procedure code MUST be placed between Section 1 and 2 of
-- SP Usage Audit code
-------------------------------------------------------------------------------
-- SP Usaged Section 1 - Declare
-------------------------------------------------------------------------------
DECLARE @SPStartTime DATETIME
SELECT @SPStartTime = GETDATE()
DECLARE @SPEndTime DATETIME
DECLARE @AuditCount INT
-------------------------------------------------------------------------------
SELECT @GL_DTFROM = ISNULL(@GL_DTFROM, CONVERT(DATETIME,'1/1/1900'))
SELECT @GL_DTTO = ISNULL(@GL_DTTO, CONVERT(DATETIME,'12/31/9999'))
SELECT @LOADED_DTFROM = ISNULL(@LOADED_DTFROM, CONVERT(DATETIME,'1/1/1900'))
SELECT @LOADED_DTTO = ISNULL(@LOADED_DTTO, CONVERT(DATETIME,'12/31/9999'))
Begin
SELECT
RECON_TRNSCTN_ID,
RECON_ITEM_ID,
DIVISION_ID,
REINS_TYPE_CD,
TRANSACTION_NO,
TRANSACTION_NAME,
REF_NUMBER,
POLICY_CRS_REF_NO,
GL_DT,
TRANSACTION_CD,
AMOUNT,
FILE_STATUS_CD,
TRNSCTN_OWR_CD,
ISSUE_CD,
SUPP_ISSUE_CD,
IRC_CLASSIFICATION_CD,
UNDER_90_DAYS,
OVER_90_DAYS,
AGING_DAYS_CNT,
VOCHER_NO,
LOADED_DT,
REI.REINS_TYPE_DS
FROM AIGNET.dbo.RECON_TRANSACTION AS RE
INNER JOIN REINSURANCE_TYPE REI ON RE.REINS_TYPE_CD = REI.REINS_TYPE_CD
WHERE
(@RECON_TRNSCTN_ID IS NULL OR @RECON_TRNSCTN_ID=RE.RECON_TRNSCTN_ID)
AND
(@RECON_ITEM_ID IS NULL OR @RECON_ITEM_ID=RE.RECON_ITEM_ID)
AND
(@DIVISION_ID IS NULL OR @DIVISION_ID=RE.DIVISION_ID)
AND
(@REINS_TYPE_CD IS NULL OR @REINS_TYPE_CD=RE.REINS_TYPE_CD)
AND
(@TRANSACTION_NO IS NULL OR @TRANSACTION_NO=RE.TRANSACTION_NO)
AND
(@TRANSACTION_NAME IS NULL OR @TRANSACTION_NAME=RE.TRANSACTION_NAME)
AND
(@REF_NUMBER IS NULL OR @REF_NUMBER=RE.REF_NUMBER)
AND
(@POLICY_CRS_REF_NO IS NULL OR @POLICY_CRS_REF_NO=RE.POLICY_CRS_REF_NO)
AND
((RE.GL_DT IS NULL) OR (RE.GL_DT BETWEEN @GL_DTFrom AND @GL_DTTo))
AND
(@TRANSACTION_CD IS NULL OR @TRANSACTION_CD=RE.TRANSACTION_CD)
AND
(@AMOUNT IS NULL OR @AMOUNT=RE.AMOUNT)
AND
(@FILE_STATUS_CD IS NULL OR @FILE_STATUS_CD=RE.FILE_STATUS_CD)
AND
(@TRNSCTN_OWR_CD IS NULL OR @TRNSCTN_OWR_CD=RE.TRNSCTN_OWR_CD)
AND
(@ISSUE_CD IS NULL OR @ISSUE_CD=RE.ISSUE_CD)
AND
(@SUPP_ISSUE_CD IS NULL OR @SUPP_ISSUE_CD=RE.SUPP_ISSUE_CD)
AND
(@IRC_CLASSIFICATION_CD IS NULL OR @IRC_CLASSIFICATION_CD=RE.IRC_CLASSIFICATION_CD)
AND
(@UNDER_90_DAYS IS NULL OR @UNDER_90_DAYS=RE.UNDER_90_DAYS)
AND
(@OVER_90_DAYS IS NULL OR @OVER_90_DAYS=RE.OVER_90_DAYS)
AND
(@AGING_DAYS_CNT IS NULL OR @AGING_DAYS_CNT=RE.AGING_DAYS_CNT)
AND
(@VOCHER_NO IS NULL OR @VOCHER_NO=RE.VOCHER_NO)
AND
((RE.LOADED_DT IS NULL) OR (RE.LOADED_DT BETWEEN @LOADED_DTFrom AND @LOADED_DTTo))
End
-------------------------------------------------------------------------------
-- SP Usage Audit Info -- DO NOT REMOVE
-- SP Usage Section 2 - INSERT Audit Info
-------------------------------------------------------------------------------
SELECT @AuditCount = @SPUsageMode +
(SELECT ParameterFlag FROM DBPerfMon.dbo.SPUsageParameters WITH (NOLOCK)
WHERE Parameter = 'SPUsageByPass')
IF @AuditCount < 1
Begin
SELECT @SPEndTime = GETDATE()
INSERT DBPerfMon.dbo.SPUsage (
DatabaseName
, Duration
, ObjectID
, ObjectName
, UserName
)
SELECT
DB_NAME()
, DATEDIFF(ms, @SPStartTime, @SPEndTime)
, OBJECT_ID(OBJECT_NAME(@@PROCID))
, OBJECT_NAME(@@PROCID)
, dbo.fncGetLastUpdatedBy ()
End
-------------------------------------------------------------------------------
-- Absolutely NO Stored Procedure code written beyond this point
-------------------------------------------------------------------------------
View 5 Replies
View Related
Dec 20, 2007
(6th attempt to post this on the forum - problems with the forum?)
Hello
Assume that you have SQL Server 2005 Standard Edition installed but only the Database Services and you patched it with SP2. Now assume that you need to install all the other components like Notification Services and Reporting Services, what are the correct steps to follow?
I tried this once this failed miserably and ended up in completely having to uninstall SQL Server 2005 and completely reinstalling it. Luckily for me this was a demo machine.
See:
http://gabriel.lozano-moran.name/blog/PermaLink,guid,6a81a3a9-45d3-4221-8b16-ba4e4863949c.aspx
Thanks
Gabriel
View 3 Replies
View Related
Aug 18, 2015
I'm trying to write a stored procedure that performs a select statement of the RequestID column and the total of the disk size for that row. ie the values on RequestAdditionalDisk1Size + RequestAdditionalDisk2Size + RequestAdditionalDisk3Size where the Requester equals a certain value. I can perform the select statement fine on the individual values, how to add the values of the Disk sizes together and present that back in the select statement.So far the code looks like but is giving me an error around the line performing a SUM.
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spMyRequests]
-- Add the parameters for the stored procedure here
[code]....
View 2 Replies
View Related
Jul 20, 2005
Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott
View 1 Replies
View Related
Apr 24, 2015
I have a SQL server instance being used as our data warehousing environment. The instance consists of several databases that I am snapshotting as part of our high availability strategy for data. I've created a stored procedure that takes the source database as an argument and that will create a new snapshot when a new one needs to be created and will also automatically remove the old snapshot. It also updates some synonym tables that point to the new snapshot but that might not be an important detail.
I would like to have the stored procedure stored some place global to all of the databases that I am routinely snapshotting, but that would mean putting it in the master database. Although having it there makes things significantly better in terms of usability, it seems like there's something wrong with putting any stored procedures in the master database. Am I wrong? Is it OK to put stored procedures there in situations like this?
View 4 Replies
View Related
Dec 22, 2006
In trying to upgrade my sql express install from express to developer edition, I ended up having to delete all things express from my computer and do a clean install of developer edition (as i installed developer edition initially over sql express but this didnt seem to work; express edition still appeared in add remove programs etc). Anyway so i blew away the install and have a clean install of developer edition. Now when I create a new website in Visual Studio 2005 and try to add a database to the project, it gives me an error stating that i need to have sql server express 2005 installed to function properly... Do i need to have express AND developer edition of sql installed to do this? I would think developer edition of sql would enable me this functionality...http://img277.imageshack.us/img277/4228/sqlerrorie2.jpgThanks!It is also worth noting that I get the dreaded remote connection error when trying to connect to a db that I used to be able to connect to when I was using sql express (the aspnetdb database that is created when you user their membership stuff)"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) "However, I did the following successfully:1. create a database using the sql management studio2. added a table and some data to it3. threw a dataset into the website4. added a gridview to a page in the website, pulling data from the dataset5. and the website runs successfully populating the page with data...fyi: My sql services: http://img158.imageshack.us/img158/2020/sqlserviceshj6.jpg
View 15 Replies
View Related
