Hi,
I'm working with visual studio 2008 and a sql server database. I'm also a bit of a beginner. I've written a stored procedure as below. My aim is to try and retrieve the data from field 'caption' through a paramater being passed.
I'm using the function as below to call this procedure through objectdatasource.
i'm getting the following error:
Compiler Error Message: BC30455: Argument not specified for parameter 'caption' of 'Public Sub New(id As Integer, count As Integer, caption As String, ispublic As Boolean)'.
Source Error:
Line 98: Using reader As SqlDataReader = command.ExecuteReader()
Line 99: Do While (reader.Read())
Line 100: Dim temp2 As New Album(CStr(reader("Caption")))
Line 101: list2.Add(temp2)
Line 102: Loop
Any help will be apreciated, a lot of my code is copy and pasted and i don't fully understand every line which is where i assume the error is being made!
Thanks
FUNCTIONSPublic Shared Function GetAlbumName(ByVal AlbumID As Integer) As Generic.List(Of Album)
Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("Personal").ConnectionString)Using command As New SqlCommand("GetAlbumName", connection)
connection.Open()Dim list2 As New Generic.List(Of Album)()
Using reader As SqlDataReader = command.ExecuteReader()Do While (reader.Read())Dim temp2 As New Album(CInt(reader("AlbumID")), CStr(reader("Caption")))
I try to replace the following statement : CmdPuzzle.Parameters.Append CmdPuzzle.CreateParameter("@Length",adTinyInt,adParamInput,,6) with: Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, , 6) it highlighted the retLengthParam saying: Argument not specified for parameter 'size' of 'Public Sub New(parameterName As String, dbType As System.Data.SqlDbType, size As Integer, sourceColumn As String) Logic:
I am trying to extract a value out of a cookie and then use that as a parameter to a SQL Select...Where function but I am getting the Argument not specified for parameter error. I assume the value from the cookie is not in the variable that I created but I could be wrong.
What is the proper method for populating a variable from a cookie and using that value as part of a SQL Select....Where function?
After I get a value out of the Select...Where function, I need to use the dataset results in a DropDownList as well as perform the DataBind().
here's some of the code:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) if Not Page.IsPostBack then dim Code as String Code = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code")) label1.Text = Code GetPropertyCodes(Code) ddlPropertyCode.DataBind() end if End Sub
Function GetPropertyCodes(ByVal Code As String) As System.Data.DataSet Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Property_Details_db].[Prop_Code] FROM [Property_Details_db] WHERE ([Property_Details_db].[Code] = @Code)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_code.ParameterName = "@Code" dbParam_code.Value = Code dbParam_code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_code)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
I am using the following code in my SQL stmt in my OLE BD Source stmt:
WHERE ICINVENTORY.ICINVLastChgAt > ? AND ICINVENTORY.ICINVLastChgAt <= DATEADD(mi,?,?)
My parameters are as follows: 0 - User:LastSalesLoadDate DateTime variable 1 - User:Load Interval Int16 (or Int32) 2 - User:LastSalesLoadDate
When I try to close the program I get the following error:
"Argument data type datetime is invalid for argument 2 of dateadd function. If I can't use a datetime data type for the date time part of the dateadd, what can I use?
The exact same code runs without error in an EXECUTE SQL task.
I have an SqlDataSource control on my aspx page, this is connected to database by a built in procedure that returns a string dependent upon and ID passed in. I have the followinbg codewhich is not complet, I woiuld appriciate any help to produce the correct code for the code file Function GetCategoryName(ByVal ID As Integer) As String sdsCategoriesByID.SelectParameters("ID").Direction = Data.ParameterDirection.Input sdsCategoriesByID.SelectParameters.Item("ID").DefaultValue = 3 sdsCategoriesByID.Select() <<<< THIS LINE COMES UP WITH ERROR 1End Function ERROR AS FOLLOWS argument not specified for parameter 'arguments' of public function Select(arguments as System.Web.DatasourceSelect Arguments as Collections ienumerable
Help I have not got much more hair to loose Thanks Steve
Is it possible to define an argument as optional for a UDF? I have a financial calculation that may or may not require a defined date range depending on the status of an individual item. Is there a way to avoid requiring the date range where it's not necessary?
Hi, nice to meet you all. I need to return a parameter value from a store procedure, i already done insert(), update(), delete() to return parameter back. Just only select() can't return parameter and it need argument, I try to write in Sqldatasource1.Select(Argument.empty), it can't work and return null. How should i solve the problem? Or I wirte the wrong argument? Can anyone help me? Thank in advance.
Hi i get the above error whenever i try and run my page, what i am trying to do is embed a repeater within a datalist, here is my code;public void Page_Load(object sender, EventArgs e) {string strID = Request.QueryString["id"]; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_Users", conn); command.CommandType = CommandType.StoredProcedure;command.Parameters.Add("@userID", SqlDbType.Int).Value = Request.QueryString["id"]; SqlDataAdapter cmd1 = new SqlDataAdapter(command); //Create and fill the DataSet.DataSet ds = new DataSet(); cmd1.Fill(ds, "userName"); //Create a second DataAdapter for the Titles table.SqlDataAdapter cmd2 = new SqlDataAdapter("select * from UserSpecialties", conn); cmd2.Fill(ds, "specialty"); //Create the relation bewtween the Authors and Titles tables.ds.Relations.Add("myrelation", ds.Tables["userName"].Columns["userID"],ds.Tables["specialtyName"].Columns["userID"]); //Bind the Authors table to the parent Repeater control, and call DataBind.DataList1.DataSource = ds.Tables["userName"]; Page.DataBind(); //Close the connection. conn.Close(); }
Hey, friends, i have a problem on my bank transaction page now: Procedure or function has too many argument specified.people can log in by their usernames, 1 username can have many accounts, there are 2 account types:'c', 's'. after people login, they are only allowed to withdraw money from their own account: here is some of my codes: 1 else if (DropDownList3.SelectedItem.Text == "withdraw")2 {3 SqlConnection sqlCon = new SqlConnection("Data Source=bandicoot.cs.rmit.edu.au;Initial Catalog=shuli;User ID=test;Password=test");4 SqlCommand cmd = new SqlCommand("usp_withdraw", sqlCon);5 string spResult = "";6 cmd.CommandType = CommandType.StoredProcedure;7 cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = Session["username"].ToString();8 cmd.Parameters.Add("@AccountFrom", SqlDbType.VarChar).Value = TextBox3.Text;9 cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = TextBox5.Text;10 cmd.CommandType = CommandType.StoredProcedure;11 sqlCon.Open();12 spResult = cmd.ExecuteScalar().ToString();13 if (string.Compare(spResult, "Execute successfully") == 0)14 {15 SqlDataSource with = new SqlDataSource();16 with.ConnectionString = ConfigurationManager.ConnectionStrings["shuliConnectionString"].ToString();17 18 with.InsertCommand = "insert into Transactions(TransactionType,AccountNumber,DestAccount,Amount,Comment,ModifyDate) values ('W','" + TextBox3.Text + "','" + TextBox3.Text + "','" + TextBox5.Text + "','" + TextBox2.Text + "','" + DateTime.Now.ToLocalTime() + "')";19 int inertRowNum = with.Insert();20 Server.Transfer("~/deposit_withdraw_confirm.aspx");21 }22 else23 {24 Server.Transfer("~/deposit_withdraw_error.aspx");25 }26 here is my store procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER Procedure [dbo].[usp_withdraw]( @AccountFrom Varchar(20), @Amount Decimal(10,2))AsDECLARE @ReturnMsg AS VARCHAR(20)DECLARE @balance AS Decimal(10,2)DECLARE @username AS nvarchar(50)SELECT @balance = balanceFROM Account,LoginWHERE Account.CustomerID=Login.CustomerID AND Login.UserID= @username and AccountNumber = @AccountFromIF ( SELECT AccountType FROM Account, Login Where Account.CustomerID=Login.CustomerID AND Login.UserID= @username and AccountNumber = @AccountFrom ) ='c'BEGIN BEGIN TRAN IF (@balance - @Amount) > 199 BEGIN UPDATE Account SET balance= balance-@Amount WHERE AccountNumber=@AccountFrom And (balance - @Amount) > 199 SET @ReturnMsg = 'Execute successfully' COMMIT TRAN END ELSE BEGIN SET @ReturnMsg = 'Execute with error' ROLLBACK TRAN END SELECT @ReturnMsg ENDIF ( SELECT AccountType FROM Account Where AccountNumber = @AccountFrom ) ='s'BEGIN BEGIN TRAN IF (@balance - @Amount) > 0 BEGIN UPDATE Account SET balance= balance-@Amount WHERE AccountNumber=@AccountFrom And (balance - @Amount) > 0 SET @ReturnMsg = 'Execute successfully' COMMIT TRAN END ELSE BEGIN SET @ReturnMsg = 'Execute with error' ROLLBACK TRAN END SELECT @ReturnMsg ENDI think the problem is in Line 7cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = Session["username"].ToString();this is to get the current login user's uername.
hello, I am doing some tutorials to learn SQL reporting services. One of the tutorials is using asp to call a web services and blah blah etc... Basically the report opens with two calenders and an execute button. the user would pick two dates and then hit execute.
the code is this:
Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click Dim report As Byte() = Nothing
'Create an instance of the Reporting Services 'Web Reference Dim rs As localhost.ReportingService = New localhost.ReportingService 'Create the credentials that will be used when accessing 'Reporting Services. This must be a logon that has rights 'to the Axelburg Invoice-Batch Number Report. '***Replace "LoginName", "Password", and "Domain" with 'the appropriate values. *** rs.Credentials = New _ System.Net.NetworkCredential("Administrator", _ "password", "localhost") rs.PreAuthenticate = True
'The Reporting Services virtual path to the report. Dim reportPath As String = _ "/Galactic Delivery Services/Axelburg/Invoice-Batch Number"
' The rendering format for the report. Dim format As String = "html4.0"
'The devInfo string tells the report viewer 'how to display with the report. Dim devInfo As String = _ "<DeviceInfo>" + _ "<Toolbar>False</Toolbar>" + _ "<Parameters>False</Parameters>" + _ "<DocMap>True</DocMap>" + _ "<Zoom>100</Zoom>" + _ "</DeviceInfo>"
'Create an array of the values for the report parameters Dim parameters(1) As localhost.ParameterValue Dim paramValue As localhost.ParameterValue _ = New localhost.ParameterValue paramValue.Name = "StartDate" paramValue.Value = calStartDate.SelectedDate parameters(0) = paramValue paramValue = New localhost.ParameterValue paramValue.Name = "EndDate" paramValue.Value = calEndDate.SelectedDate parameters(1) = paramValue
'Create variables for the remainder of the parameters Dim historyID As String = Nothing Dim Credentials() As localhost.DataSourceCredentials = Nothing Dim showHideToggle As String = Nothing Dim encoding As String Dim mimeType As String Dim warnings() As localhost.Warning = Nothing Dim reportHistoryParameters() As _ localhost.ParameterValue = Nothing Dim StreamIDs() As String = Nothing
Dim sh As localhost.SessionHeader = _ New localhost.SessionHeader rs.SessionHeaderValue = sh
Try 'Execute the report. report = rs.Render(reportPath, format, historyID, _ showHideToggle, encoding, mimeType, _ reportHistoryParameters, warnings, _ StreamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
'Flush any pending responce. Response.Clear()
'Set the Http headers for a PDF responce. HttpContext.Current.Response.ClearHeaders() HttpContext.Current.Response().ClearContent() HttpContext.Current.Response.ContentType = "text/html" ' filename is the default filename displayed 'if the user does a save as. HttpContext.Current.Response.AppendHeader( _ "Content-Disposition", _ "filename=""Invoice-BatchNumber.HTM""")
'Send he byte array containing the report 'as a binary response.
Catch ex As Exception If ex.Message <> "Thread was being aborted." Then HttpContext.Current.Response.ClearHeaders() HttpContext.Current.Response.ClearContent() HttpContext.Current.Response.ContentType = "text/html" HttpContext.Current.Response.Write( _ "<HTML><BODY><H1>Error</H1><br><br>" & _ ex.Message & "</BODY></HTML>") HttpContext.Current.Response.End()
End If End Try
End Sub End Class
This is the area underlined as failing: report = rs.Render(reportPath, format, historyID, _ showHideToggle, encoding, mimeType, _ reportHistoryParameters, warnings, _ StreamIDs)
The errors all pretty much go like this: c:inetpubwwwrootAxelburgFrontEndReportFrontEnd.aspx.vb(95): Argument not specified for parameter 'ParametersUsed' of 'Public Function Render(Report As String, Format As String, HistoryID As String, DeviceInfo As String, Parameters() As localhost.ParameterValue, Credentials() As localhost.DataSourceCredentials, ShowHideToggle As String, ByRef Encoding As String, ByRef MimeType As String, ByRef ParametersUsed() As localhost.ParameterValue, ByRef Warnings() As localhost.Warning, ByRef StreamIds() As String) As Byte()'.
I searched around, but didn't really understand what these errors mean. Any help is greatly appreciated.
I have a table with over 11,000 records and I need to do a find and replace using SET and Where conditions. Basically I have one column in the table called RealAudioLink. It contains entries like: wkdy20070416-a.rm and wkdy20070416-b.rm and conv20070416.rm.
I need the select statement to find all wkdy entries and replace those characters with Weekday. I also need it to find all dashes and small a's and b's and replace with null or nothing. Then I need it to insert a capital letter A or B in the wkdy20070416-a.rm filename so that when it's all said and done that entry would read:
Here is the code I am working with. It needs help. I'm close but I'm not knowledgeable with using SET or with removing dashes and inserting capital letters all in the same select statement.
Code Snippet
UPDATE T_Programs_TestCopy (SET RealAudioLink = REPLACE(RealAudioLink, '-a', '') AND (SET RealAudioLink = REPLACE(RealAudioLink, 'wkdy', 'WeekdayA') WHERE (RealAudioLink LIKE 'wkdy%'))
I've never done anything like this before so I would be very appreciative of any assistance with the select statement. I am reading up on it but it would be great to get another perspective from a more experienced sql developer.
I am using the LIKE function combined with a CASE WHEN to change a long list of words, but the list is too long... Is there any posibility to insert more than one argument into one like function...? Any other good ideas? Below an example of the code I am using..
Thanks in advance, Aldo.
Code Snippet Case WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument01%' THEN 'Result' WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument02%' THEN 'Result' WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument03%' THEN 'Result' WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument04%' THEN 'Result' ELSE '' END AS 'Result'
Hello frend...i have a problem with my application when i'm trying to delete a certain data from gridview...i have a store procedure that already create in my mssql server...and in datasource i'm using command that i've already created in my mssql server...the problem is i dont know how i can send my value to the parameter in datasource.....and one more thing what is exaclty the error with 'Store procedure has <my function> too many argument" occur? Please help me....so i paste my code below to easier and detect my problem....(sorry my english are no good).... 1) This is my code in asp.net <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <asp:SqlDataSource ID="SqlDSRole" runat="server" ConnectionString="<%$ ConnectionStrings:PVMCCon %>" DeleteCommand="Roles_delete" DeleteCommandType="StoredProcedure" SelectCommand="Roles_view" SelectCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="roleName" Type="String" /> </DeleteParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="Role_application_id" DataSourceID="SqlDSRole" ForeColor="#333333" GridLines="None" PageSize="4"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <Columns> <asp:CommandField ShowDeleteButton="True" /> <asp:TemplateField HeaderText="Role Application Id" Visible="False"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:Label ID="roleApplicationIdLabel" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label> </EditItemTemplate> <AlternatingItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label> </AlternatingItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Role Name" > <ItemTemplate> <asp:Label ID="roleName" runat="server" Text='<%# Eval("Role_name") %>' CssClass="LabelInfo" ></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="roleNameTxt" runat="server" Text='<%# Bind("Role_name") %>' Width="200px"></asp:TextBox> </EditItemTemplate> <AlternatingItemTemplate> <asp:Label ID="Label5" runat="server" Text='<%# Eval("Role_name") %>' CssClass="LabelInfo"></asp:Label> </AlternatingItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Role Description"> <ItemTemplate> <asp:Label ID="Label6" runat="server" Text='<%# Eval("Role_description") %>' CssClass="LabelInfo"></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="roleDescriptionTxt" runat="server" Height="30px" Text='<%# Bind("Role_description") %>' TextMode="MultiLine" Width="300px"></asp:TextBox> </EditItemTemplate> <AlternatingItemTemplate> <asp:Label ID="Label7" runat="server" Text='<%# Eval("Role_description") %>' CssClass="LabelInfo"></asp:Label> </AlternatingItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <EditRowStyle BackColor="#E0E0E0" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </ContentTemplate> </asp:UpdatePanel> 2) And this is my Store procedure that i created in mssql server Store Procedure: Roles_view set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Roles_view] AS BEGIN SET NOCOUNT ON; SELECT * FROM Role END Store Procedure: Roles_delete set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Roles_delete] @roleName varchar(50) AS BEGIN SET NOCOUNT ON; DELETE FROM Role WHERE Role_name LIKE @roleName END 3) The problem is when i'm trying to delete my certain data, the message box appear and say "Procedure or function Roles_delete has too many argument specified".what should i do????anabosy please help me..
Hey Guys, I'm having problems with my SQL statement and Im wondering if anyone could help? My code is below. The Label6 is filled up using a previous SQL Statement, and when Ive had a fiddle around it is using the KnowledgeID It works in reading in the date, it just doesnt appear to want to work for the second statement as I am getting the error shown in the subject. Thanks in advance =) C# Codeprotected void Button2_Click(object sender, EventArgs e) {KnowledgeID = Convert.ToInt32(Label5.Text); string path = Server.MapPath(" ") + "\X_Drive\";FileLocation = path + Convert.ToString(FileUpload1.FileName);SqlConnection myConnection = new SqlConnection(myConnectionString); SqlCommand myCommand2 = new SqlCommand("select * from Knowledge WHERE KnowledgeID = '" + KnowledgeID + "'", myConnection); myConnection.Open(); SqlDataReader myReader = myCommand2.ExecuteReader();if (myReader.HasRows) {while (myReader.Read()) {Add = myReader["DateAdded"].ToString(); } myReader.Close(); } else { myConnection.Close(); } SqlTransaction trans = myConnection.BeginTransaction(); { try { SqlCommand myCommand3 = new SqlCommand("Select ISNULL(MAX(Version,0)+1 FROM Archive WHERE KnowledgeID = "+KnowledgeID+"",myConnection); myCommand3.Transaction=trans;int nextVersion =(int)myCommand3.ExecuteScalar();if (File != null) {myCommand3 = new SqlCommand("INSERT INTO Archive (FixName, Description, Location, DateAdded, DateArchived, Version, KnowledgeID) SET (@FixName,@Description,@File,@Add,@AddDate,@SAPPS,@Version,'" + KnowledgeID + "')", myConnection); } else {myCommand3 = new SqlCommand("INSERT INTO Archive (FixName, Description, DateAdded, DateArchived, Version, KnowledgeID) SET (@FixName,@Description,@Add,@AddDate,@SAPPS,@Version,'" + KnowledgeID + "')", myConnection); }myCommand3.Parameters.AddWithValue("@FixName", TextBox1.Text); myCommand3.Parameters.AddWithValue("@Description",TextBox2.Text);myCommand3.Parameters.AddWithValue("@File",FileLocation); myCommand3.Parameters.AddWithValue("@Add",Add);myCommand3.Parameters.AddWithValue("@AddDate",AddDate);myCommand3.Parameters.AddWithValue("@Versions",nextVersion); myCommand3.ExecuteNonQuery(); trans.Commit(); }catch (Exception ex) { //TextBox2.Text = ex.Message; trans.Rollback(); myConnection.Close(); } //}
I have a stored procedure which accepts 3 arguments that are used in the WHERE clause of a SELECT Statement
CREATE PROCEDURE [dbo].[MyProcedure] @argYear INT, @argMonth INT, @argDay INT
AS
SELECT *
FROM MyData
WHERE MyData.Year = @argYear AND MyData.Month = @argMonth AND MyData.Day = @argDay
The problem that I am having is @argDay is an "optional" argument. If @argDay is NULL then I want to basically ignore the "AND MyData.Day = @argDay" condition.
Is there an easier way to do this than:
IF @argDay is NULL
SELECT *
FROM MyData
WHERE
MyData.Year = @argYear AND MyData.Month = @argMonthELSE
SELECT *
FROM MyData
WHERE
MyData.Year = @argYear AND MyData.Month = @argMonth AND MyData.Day = @argDay
hai <br> <p>i have a procedure where in which i want to access a specific table whose name will be passed into procedure as argument. Here is the peice of code i have made.</p> CREATE PROCEDURE outputtable @tablename nchar(10) AS<br> begin<br> .........................<br> insert into @tablename (classno) values (@cno)<br> .................................<br> END<br>
<p> I get the following error message :</p> Msg 1087, Level 15, State 2, Procedure................<br> Must declare the table variable "@tablename".<br> <p> Is there anyway i can get around this one. I will be grateful if anyone can help me</p>
For a few days now I have a discussion with a colleague about shrinking the transaction log as a daily maintenance job on an OLTP database. The problem is I cant figure out a way to convince her she is doing something really wrong. Its not the first discussion.. Maintenance Plans.
She implemented this "solution" with a lot of customers as a solution against VLFs fragmentation and huge transaction log sizes. My thoughts about doing this is very clear and I have used the following arguments without success to convince her:
- To solve too many VLFs you have to focus on the actual size of the transaction log and the autogrowth settings in combination with regularly transaction log backups. Check the biggest transaction and modify the transaction log size based on this. Not use shrinking as a solution for solving many VLFs.
- Shrinking the transaction log file on a daily basis that is disk I/O intensive. When the transaction log file is too small for new transactions, the transaction log needs to grow and this will cause disk I/O, this can cause performance problems.
- It looks unprofessional.
These steps are used every morning at 6:00 AM and a transaction log backup is made every 30 minutes.
Step 1 DBCC SHRINKFILE (N'' , 0, TRUNCATEONLY); go
Step 2 ALTER DATABASE MODIFY FILE (NAME = N'', SIZE = 4098MB); GO
My main purpose is making sure the customers have the best possible configuration and I cant accept this is being implemented. Are there any more arguments available for this issue?
I've been scouring the forums and the web for anything that would substantiate an argument for database application developers to keep developing in SQL 8.0 when we have migrated to SQL 2005.
I read somewhere that compatibility 8.0 mode is an intern stepping stone for migrating from 8 to 9, but it seems 2005 will handle and run compat 8.0 databases just fine.
So am finding it really diffucult to substantiate my argument against running 2005 with all databases in compatibility 8 mode indefinitly.
Any suggestions or links that could help me with this.
Is "Encrypt" not support in Compact Framework 2.0 SqlConnection string?
Trying to connect to sql2005 db using SSL from WM5/.net 2.0 pda and am receiving the below error. I can connect fine w/o the "encrypt" argument and the same connection string with "encrypt" connects fine from XP workstation to sql db w/SSL.
"Unknown connection option in connection string: encrypt."
Connection String I used:
sqlConnection = new SqlConnection("Data Source=mysqlserver;Initial Catalog=sometable;UID=username;PWD=Password;Encrypt=true;");
I wonder if someone could suggest a way to obtain the following. Using SQL Server 2005 I want to create some stored procedures. I want to query the DB with various filter arguments and combinations of these. One way would be to create one stored procedure for each function signature. However, as the number of combinations of filter is large, if possible I'd rather have a generic input to the each stored procedure that corresponds to the entire WHERE clause' search condition.
The stereotype behavior I'm looking for is:
SELECT myField FROM myTable WHERE @mySearchCondition
Does any one have some good suggestion, code samples and/or links?
Hi All, I have the following issue, i want to make dynamic the Argument property of the Execute Process Task but i couldn't able to do so, i have posted several times , hopefully i will get a solution today. I am using the following Argument to execute (winzip) program. Argument: /e C:FTPFile1.zip C:FTPUnzipped This Argument works but since File1.zip gets change every time, i mean it will be File2.zip, thus i have to change it manually on the Execute Process Task every time, however i want it to change by itself. To make it clear, the /e mean winzip usage for extract file, the C:FTPFile1.zip is where the zipped file is, and the C:FTPUnzipped is where i dumped the unzipped files. as you see the file name will change every time like File1.zip, File2.zip, File3.zip, etc... Thus i want to make it daynamic, for example if i execute today File1.zip, then i have to execute tomorrow File2.zip automatically, and then File3.zip etc, that means the source file is changing every time, how do i make it then to be Dynamic. Please help, i need your input badly. Thank you in advance,
how to solve this problem .... I want to select te sum of nvarchar column and this error always happen
what is the solution this is my query
"SELECT Datein AS Date,SUM(Durm) AS DurationMin, COUNT (*) AS 'Number of Hits' FROM Rayyan_Log WHERE (DNIS = '"+DropDownList1.SelectedItem.Text +"') AND (Datein between '"+TextBox2.Text+"' and '"+TextBox3.Text+"') GROUP BY Datein ORDER BY Datein"
