I am trying to use parameters in an sql statement, that runs inside a loop. every cycle I have to change the parameters values, and I'm not sure if I do it right.
Hello, I'm trying to do something simple but can't make it work. I want to use the result of this sql "Select MAX(Stoptime) AS LatestStop FROM Stoptime" and use it in "Delete from Stoptime Where Stoptime > ?" but here with just the date. How do I set this up with parameters in the Control Flow in Integration Services?
I have a report which measures supplier performance for the previous month. It takes an age to generate so I am trying to cache a copy to a null location first thing in the morning to speed up the process. The problem I'm having is in getting the report to select the first day and last day of the month for the two parameters that the report needs to run.
In BIDS the report runs perfectly when previewed, however, when it is deployed to the report server I get the following error: 'Error during processing of €˜RP2€™ report parameter. (rsReportParameterProcessingError)'
I have used the following two statements for the default values of the parameters, which work in BIDS, so I can't understand why they don't when it's deployed. I'm also sure there is an easier way of doing this, but after about an hour searching yesterday and not finding anything it only took me about half that time to use these statements:
The only caviate to using these statements is that it wont recognise when a leap year occurs, other than that, if it would work when deployed to the report server it would work perfectly for the purposes of what we need.
If anyone can see the flaw, or knows of a better and easier way of doing this please let me know.
Ok, I'm a long time reader of this forum and just about everytime I come here looking for an answer its allready been posted and answered. But I can't seem to find this one anywhere. Sorry if its allready been posted...Just point me in the right direction.
Ok, Ive been coding in VB2005 for a while but i'm new to SQL stored procedures. This is prolly going to sound stupid but I don't know where to start. Heres what I need to do.
I have a stored procedure that requires a parameter. I can pass that parameter to the stored procedure and execute it just fine. But I need the result thats returned when the stored procedure is executed. I can't seem to find anyway to return the Result back to me. DataReaders keep blowing up and I have no Idea Why????? Here is how i'm passing the param to the stored proc.
I'm running MS SQL BTW.
Dim myConnection As New SqlClient.SqlConnection("yadayada;Database=BlaBlaBla;Trusted_Connection=True;")
Dim cmd As New SqlClient.SqlCommand("NameOfStoredProc")
sqlParam.Value = Address 'declared higher up.....its just an int
myConnection.Open()
cmd.ExecuteNonQuery()
myConnection.Close()
Thats all great, but I need to read back the results the stored proc returns. If I execute this in server management studio, it returns a result. But nothing I try in my asp page works. Any help would be Great. Code samples are good too.
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General: Result Set: None Connection Type: OLE DB SourceType: Direct Input IsQueryStoredProcedure: False (this is greyed out and cannot be changed) Bypass Prepare: True
When I use the following execute statement where I am "Hard Coding" in the parameters, the stored procedure runs successfully and it places the data into the table per the stored procedure.
However, the @system_cd parameter can change, so I wanted to set these parameters up as variables and use the parameter mapping in the Execute SQL Task.
I have set this up as follows and it runs the package successfully but it does not put the data into the table. The only thing I can figure is either I have the variables set up incorrectly or the parameter mapping set up incorrectly.
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load] (@system_cd nvarchar(10), @location_type_cd nvarchar(10)) AS BEGIN .....................
Here is my set up, what is wrong here:
I Created these Variables:
Name Scope Data Type Value system_cd Locations String '03' location_type_cd Locations String Store
I added these parameter mappings in the Execute SQL Task
Variable Name Direction Data TypeParameter NameParameter Size User::system_cd Input NVARCHAR@system_cd -1 User::location_type_cd Input NVARCHAR@location_type_cd -1
I used this SQLStatement: EXEC dbo.sp_ml_location_load ?,
It runs the package successfully but it does not put the data into the table.
It's well known issue, that one can't use any dataset fields in a report header/footer directly. One of the approach is to create query-based parameter that basically equals =First(Fields!@FieldName@.Value, "@DataSetName@") and use that parameter value instead. But it doesn't work in my case!
My report displays some entity description and is parametrized with EntityID param. Its header contains entity name that, according to the approach, is queried from the data source through the EntityName report parameter. There's important issue: the report is displayed in ReportViewer control, that is embedded into my application and entity ID parameter isn't ser by user in ReportViewer parameters area. Its default value is changed by the application with SetReportParameters() web method every time a user wants to view the report according to the entity the user is exploring in the application. But after the report has been rendered, its header always contains not actual (outdated) entity name. Nevertheless, the report body contains actual data (including entity name). If I alter entity ID parameter in ReportViewer or in web-based Report Manager and refresh report, header displays correct entity name.
In my SQL Server Management Studio Express (SSMSE), pubs Database has a Stored Procedure "byroyalty":
ALTER PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
And Table "titleauthor" is: au_id title_id au_ord royaltyper
172-32-1176 PS3333 1 100
213-46-8915 BU1032 2 40
213-46-8915 BU2075 1 100
238-95-7766 PC1035 1 100
267-41-2394 BU1111 2 40
267-41-2394 TC7777 2 30
274-80-9391 BU7832 1 100
409-56-7008 BU1032 1 60
427-17-2319 PC8888 1 50
472-27-2349 TC7777 3 30
486-29-1786 PC9999 1 100
486-29-1786 PS7777 1 100
648-92-1872 TC4203 1 100
672-71-3249 TC7777 1 40
712-45-1867 MC2222 1 100
722-51-5454 MC3021 1 75
724-80-9391 BU1111 1 60
724-80-9391 PS1372 2 25
756-30-7391 PS1372 1 75
807-91-6654 TC3218 1 100
846-92-7186 PC8888 2 50
899-46-2035 MC3021 2 25
899-46-2035 PS2091 2 50
998-72-3567 PS2091 1 50
998-72-3567 PS2106 1 100
NULL NULL NULL NULL //////////////////////////////////////////////////////////////////////////////////////////// I try to do an ADO.NET 2.0-VB 2005 programming in my VB 2005 Express to get @percentage printed out in the VB Form1. I read some articles in the websites and MSDN about this task and I am very confused about "How to Work with Output Parameters & Report their Values in VB Forms": (1) Do I need the Form.vb [Design] and specify its properties of the object and classes I want to printout? (2) After the SqlConnectionString and the connection.Open(), how can I bring the value of @percentage to the Form.vb? (3) The following is my imcomplete, crude draft code:
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI;"
Dim connection As SqlConnection = New
SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("byroyalty", connection)
command.CommandType = CommandType.StoredProcedure ................................................................... .................................................................. etc. //////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From the above-mentioned (1), (2) and (3), you can see how much I am lost/confused in attempting to do this task. Please help and give me some guidances and good key instructions for getting the output parameter printed out in the FORM.vb in my VB 2005 Express project.
Hello all, I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType from Product order by ProductType
Hi all, From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
Hi, I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId', N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question... Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too. The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like (@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Hello all, Given: string commandText = "Categories_Delete";SqlCommand myCommand = new SqlCommand(commandText, connection);myCommand.CommandType = CommandType.StoredProcedure; Is there a reason NOT to use myCommand.Parameters.AddWithValue("@CategoryID",CategoryID); I'd prefer to use that over myCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = CategoryID; as I have these functions being created dynamically and hope to get away from a big lookup to try to convert System.Types into SqlDbTypes. [shudder] It seems that ADO.NET makes an implicit conversion to the valid type. If this is correct then I can move on fat dumb and happy. Anyone have any good insight? Thanks,
My error is that 'Name tr is not declared' tr.Rollback() I tried moving the 'Dim tr As SqlTransaction' outside the try but then I get 'Variable tr is used before it si assinged a value'. What is the correct way? Try conn.Open() Dim tr As SqlTransaction tr = conn.BeginTransaction() cmdInsert1.Transaction = tr cmdInsert1.ExecuteNonQuery() cmdInsert2.Transaction = tr cmdInsert2.ExecuteNonQuery() cmdInsert3.Transaction = tr cmdInsert3.ExecuteNonQuery() tr.Commit() Catch objException As SqlException tr.Rollback() Dim objError As SqlError For Each objError In objException.Errors Response.Write(objError.Message) Next Finally conn.Close() End Try
I have the below procedure that will not work- I must be losing my mind, this is not that difficult - mental roadblock for me. Using SQL Server 2000 to create SP being called by ASP.Net with C# code behind stored procedure only returns if input exactly matches L_Name PROCEDURE newdawn.LinkNameLIKESearch @L_Name nvarchar(100)AS SELECT [L_Name], [L_ID], [C_ID], [L_Enabled], [L_Rank], [L_URL] FROM tblContractorLinkInfo WHERE L_Name LIKE @L_Name RETURN I tried: WHERE L_Name LIKE ' % L_Name % ' no luck. What am I missing? Thank you
SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle,C.CategoryTitle FROM HomePageImage H JOIN shortlist SL on H.StoryId = SL.id (INNER JOIN category C on H.CategoryId = C.CategoryId) order by date DESC
I have a textbox and a checkbox on a form and I'd like to add both values to a db. The textbox value gets inserted fine but I'm having trouble with the checkbox. Any ideas would be greatly appreciated.
Beside working right from the server how else someone can perform the SQL admistration job, I guess my question is how do most SQL DBA perform their administration without going to the server directly. Anyone --- can help please??
Declare @StartDate datetime Declare @EndDate Datetime Set @StartDate = dateadd(day, datediff(day, 0, getdate()), 0) Set @EndDate = getdate()
The job runs at 11:30 pm so I want the start date to be the same but the time to be equal to 00:00:00.0 When I run the getdate does it also return a time stamp?
I had a problem where some users were experiencing timeouts when trying to add a single record to a table with 2.3 million records. It's not a very wide table; only 10 columns and the biggest column in varchar 500. The rest are guid, datetime, tinyint...
There is also an old VB app that inserts about 3000 records a day into this table during office hours while users occasionally try and insert a record into this table.
Something said to me that the problem could be indexes but I wasn't quite sure because I though indexes only have an impact on select, delete & update. And not particulary on insert. But I checked it out anyway and noticed that the 3 indexes (1 column PK, 1 column Clustered & 1 column non-clustered) weren't padded. So I changed that (Fill Factor 95) and the problem has gone away. But why? I thought the insert would just have appended it to the end of the index before I made this change? Why would that time out?
I have the following queries. The first returns the 'Unknown' row, the second works the way I would expect. Are my expectations wrong? Can someone describe for me what is going on?
Thanks
Code Snippet select * FROM SynonymComFinancialCategory b LEFT JOIN TT_FinancialCategory a ON a.FinanceGroup = b.FinanceGroup AND a.FinanceCode = b.FinanceCode AND a.Finance = b.Finance AND b.Finance <> 'Unknown' WHERE a.FinanceGroup IS NULL AND a.FinanceCode IS NULL AND a.Finance IS NULL
Code Snippet select * FROM SynonymComFinancialCategory b LEFT JOIN TT_FinancialCategory a ON a.FinanceGroup = b.FinanceGroup AND a.FinanceCode = b.FinanceCode AND a.Finance = b.Finance WHERE a.FinanceGroup IS NULL AND a.FinanceCode IS NULL AND a.Finance IS NULL AND b.Finance <> 'Unknown'
Hi,I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.Mobile Business PrivateNULL 345 NULL4646 65464 65765NULL 564654654 564 6546I want the following as my results:Number3454646564654654Select COALESCE(Mobile,Business,Private) as Number from Table returns:3454646654654 (this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)select mobile,business,private where private is not null returns:657655646546thanks
Hello,I created a formview in a web page. The data are in a sql server express database.With this form, I can to create a new data, I delete it but I can't to modify the data.The app_data folder is ready to write data; the datakeynames element in formview web control declared. I replace the automated query created by VS 2005 by a strored procedure to see if the problem solved.The problem is the same with an update query or a update stored procedure...Have you an idea, please.Than you for your help.Regards.
Im working with a detailsview and when I try to edit something and then update, the changes are not saved. I have 2 tables ("[etpi.admin].Ocorrencias" and "[etpi.admin].SMS") that store the data that Im trying to change. Since Im having problems with the name of tables, Im coding it manually, using SQL server management studio and VWD. I believe my code can be wrong (Im new to vwd and C# world), so here it is: UpdateCommand="UPDATE [etpi.admin].Ocorrencias SET [Status_Ocor] = @Status_Ocor, [Percentual] = @Percentual, [IDPriori] = @IDPriori, [Abertura] = @Abertura, [IDTecRes] = @IDTecRes, [Area] = @Area, [CodEquip] = @CodEquip, [Descricao] = @Descricao, [Destinatario] = @Destinatario, [Data_Implanta] = @Data_Implanta WHERE [etpi.admin].Ocorrencias.IDOcorre = @IDOcorre UPDATE [etpi.admin].SMS SET [idSMS] = @idSMSWHERE [etpi.admin].SMS.IDOcorre = @IDOcorre"> <UpdateParameters><asp:Parameter Name="idSMS" Type="Int32" /><asp:Parameter Name="Status_Ocor" Type="String" /><asp:Parameter Name="Percentual" Type="Int32" /><asp:Parameter Name="IDPriori" Type="Int32" /><asp:Parameter Name="Abertura" Type="DateTime" /><asp:Parameter Name="IDTecRes" Type="Int32" /><asp:Parameter Name="Area" Type="String" /><asp:Parameter Name="CodEquip" Type="Int32" /><asp:Parameter Name="Descricao" Type="String" /><asp:Parameter Name="Destinatario" Type="String" /><asp:Parameter Name="Data_Implanta" Type="DateTime" /><asp:Parameter Name="IDOcorre" Type="Int32" /></UpdateParameters> Thx.
My rollback does not work In my SP I want any of cmdS,cmdS2,cmdS3,cmdS4 produces error Rollback must be executed for all of cmdS,cmdS2,cmdS3,cmdS4 . I tested for error producing situation but no rollbak occured. How can I solve this problem. Thanks. Below is my SP . .......... .......... .......... begin transaction .......... ..........If Len(ltrim(rtrim(@cmdS)))>0 execute(@cmdS) If Len(ltrim(rtrim(@cmdS2)))>0 execute(@cmdS2) If Len(ltrim(rtrim(@cmdS3)))>0 execute(@cmdS3) If Len(ltrim(rtrim(@cmdS4)))>0 execute(@cmdS4) If Len(ltrim(rtrim(@cmdS)))>0 or Len(ltrim(rtrim(@cmdS2)))>0 or Len(ltrim(rtrim(@cmdS3)))>0 or Len(ltrim(rtrim(@cmdS4)))>0 Beginif @@ERROR <>0 begin rollback transaction set @Hata = 'Error !' end Else Beginset @Hata = 'Sucessfully executed :)' End End commit transaction RETURN
I can test it in query builder but when i preview the page and go to do the search i get nothing Here is my statement SELECT Employees.Last_Name, Employees.First_Name, Job_Transaction.Name, Seq_Descript.Seq_DescriptionFROM Call_List INNER JOIN Call_Group ON Call_List.Group_ID = Call_Group.Group_ID AND Call_List.Group_ID = Call_Group.Group_ID INNER JOIN Employees ON Call_List.Clock = Employees.Clock INNER JOIN Job_Transaction ON Call_Group.Group_ID = Job_Transaction.Group_ID INNER JOIN Seq_Descript ON Call_List.Sequence = Seq_Descript.SequenceWHERE (Call_List.Clock = @Clock) Mike
Hi all.I have used a SqlDataSource in my page with this delete command:DELETE FROM tblPersonnel WHERE (ID = @original_ID)and the "OldValueParameterFormatSring" property of the datasource is "original_{0}".and i also have a GridView and a button for delete in rows.(it's CommandName is "Delete"). But when i try to delete a record, the record does not get deleted and this button only makes a PostBack on the page! Why doesn't it work? Thanks in advance.
hi all, i have created a gridview with the select,delete and insert commands working properly. but the update command does not work. when i edit a column and click the update button, it generates no errors but does not save the changes. it just brings back the original values. i dont know wats missing. can anyone help me? this is part of my code:UpdateCommand="UPDATE [test101] SET Surname=@Surname, Names=@Names,Registration=@Registration,[Course code]=@Course_code,Grade=@Grade WHERE ID=@ID1 "<UpdateParameters> <asp:Parameter Name="Surname" Type=String /> <asp:Parameter Name="Names" Type=String /> <asp:Parameter Name="Registration" Type=String /> <asp:Parameter Name="Course_code" Type=String /> <asp:Parameter Name="Grade" Type=Int32 /> <asp:Parameter Name="ID1" Type=Int32 /> </UpdateParameters>
Hi,I am having trouble with a WHERE clause:WHERE (([A] = @A) AND ([B] >= @B) AND (([C] < [D])) OR ([C] = 0 AND [D] = 0)) It's meant to only select data where A = @A, and B is more than or equal to @B, and one of the next two are true: C is less than D or C and D are both 0 Thanks,Jon
I am trying to get the id of last entered record and I used select ID command but it doesn't give me the ID of last record enteredProtected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click Dim name, address, nice As String Dim sConnString, sSQL, sID As String Dim u As MembershipUsername = Me.name.Textaddress = Me.address.Text
u = Membership.GetUser(User.Identity.Name) nice = u.ToString()sSQL = "INSERT into test ([Address],[Name], [username]) values ('" & _ address & "', '" & _name & "', '" & _ nice & "'); SELECT ID FROM test WHERE (ID = SCOPE_IDENTITY())" MsgBox(sID) MsgBox(sSQL) sConnString = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString1").ConnectionString.ToString()Using MyConnection As New System.Data.SqlClient.SqlConnection(sConnString)Dim MyCmd As New System.Data.SqlClient.SqlCommand(sSQL, MyConnection) MyConnection.Open() MyCmd.ExecuteNonQuery() MyConnection.Close() End UsingResponse.Redirect("transferred.aspx?value=2") End Sub End Class
I am using the below WHERE clause and it will not filter out records where Companies.CompanyEmail = b@c.com WHERE (Companies.CompanyEmail <> 'a@a.com') this does not work, is the @ causing a problem? also tried 'a@a.com%' with no success. WHERE (tblCompanies.C_CompanyEmail <> 'none') this works fine. Thank you