I am working on a DTS package in which one part is executing a SQL Task that's output is being stored in a global variable..or better yet, it WAS storing it. In the beginning it was working and I had an Active X task that would loop through the record set and write the contents in HTML to a flat file.
NOW, out of nowhere, when I press on the parameters button, I recieve the following error:
"The SQL Statement does not contain any parameters"
I made no changes and the funny thing about it is that I can still run the package and it still writes the output to the file, with different data if it applies ( it is calling a stored procedure ), I just cannot change it!
Has anybody ran into this? I have searched other discussion boards and there have been people with the same issue but I have not gotten a clear answer.
Hello, Does anyone have any experience or knowledge of being able to send an unlimited amount of parameters to a stored proc and having the stored proc detect how many parameters were sent?
Example: 1) calling the stored proc with 2 strings: exec sp_myproc "a","b"
2) calling the stored proc with 7 strings: exec sp_myproc "a","b","c","d","e","f","g"
The following sp_myproc would need to be able to support an unlimited amount of parameters as well as detect how many were sent...
Thanks in advance for any help and/or code samples given!
Does anyone have any experience or knowledge of being able to send an unlimited amount of parameters to a stored proc and having the stored proc detect how many parameters were sent?
Example: 1) calling the stored proc with 2 strings: exec sp_myproc "a","b"
2) calling the stored proc with 7 strings: exec sp_myproc "a","b","c","d","e","f","g"
The following sp_myproc would need to be able to support an unlimited amount of parameters as well as detect how many were sent...
Thanks in advance for any help and/or code samples given!
Does anyone have any experience or knowledge of being able to send an unlimited amount of parameters to a stored proc and having the stored proc detect how many parameters were sent?
Example: 1) calling the stored proc with 2 strings: exec sp_myproc "a","b"
2) calling the stored proc with 7 strings: exec sp_myproc "a","b","c","d","e","f","g"
The following sp_myproc would need to be able to support an unlimited amount of parameters as well as detect how many were sent...
Thanks in advance for any help and/or code samples given!
I have a unbound text box that gets its date from the system (date.now). The problem is that when I write it to SQL (via a SQL Insert Command) It throws an error. It transpires that the format is wrong. It accepts the date such as 07/17/2007 just fine but not 17/07/2007 which is automatically generated. My IIS has a locale setting with is correct for the UK. How can I change SQL 2005 so that it accepts DMY for a date/time field Thanks.
Hi DBAs, I am very new in SQL server. I created a table where one column is varchar(8000). But when I am trying to insert value from enterprise manager this column cann't accept a long text value. I counted that its' capacity is 1012 charecters. I have tried a lot but don't know how to solve this. I really need help from you. Pls help.
I have created a stored procedure and when i execute it in sql server2005 it runs fine...the user can put in a parameter or none at all andthe corresponding results are returned.@name varchar(20) = NULLselect person.addressfrom personwhere person.name = @nameMy problem comes when i try using the stored procedure in my report inms reporting services. When i run the report and try not entering avalue for the parameter i get an error saying that it is required thati enter the parameter. Any ideas?
I've got a function which inserts into a database, and has arguments for each item being inserted A couple of the items are integer datatypes (in SQL), but they will accept nulls When I add my parameters, it asks to explicitly use the SQL datatype (which is integer):.Add("@myParam", SqlDbType.Int).Value = myParam In the header of the function, I assumed I could make the argument optional - Optional ByVal myParam as Integer=System.DBNull.Value However, when the function runs, I always get an error:System.InvalidCastException was unhandled by user code Message="Conversion from type 'DBNull' to type 'Integer' is not valid." I make them all Optional (which won't happen, but various arguments may be, at different timesand I get this error, with the last item (which is on a separate line), in red:Constant expression is required. How can I get around this?
I have following simple code in my stored proc. even I have hard coded OFFSET to non zero, but it always return result from starting point 0. End limit "Fetch Next" is working perfect.Only problem is with start.
SELECT * FROM #invoices ORDER BY #invoices.InvoiceDateTime ASC OFFSET @StartRow ROWS Fetch NEXT @EndRow ROWS ONLY;
Display based on customerid display max of item they purchased on a order display only number like cust id pursed 12 items in 3rd order so when i enter customerid it should display 12.
using row number in sql server 2012.creating storeprocedure accepting customer id as input parameter.
cid oid items 1 1 10 1 2 12 1 3 3 1 4 4
so if we enter 1 as custid it got to give us 12 as the result..
Hi!I need how to pass variables struct witt n fields and n recordsto procedures in transac sql in one bbdd sql server 2000,this parameters are in/out.Thanks.
I am trying to figure how to programmatically pass parameters to the report in SQL Reporting Services using web service API and then send this report to the printer. I found code in C# on how to print and it does work very well. I am hoping to add report parameters to this module. Any ideas, samples or help would really be appreciated.
here is the code for printing
using System; using System.Drawing; using System.Drawing.Imaging; using System.Drawing.Printing; using System.IO; using System.Web.Services.Protocols; using PrintReport.reportserver; using System.Runtime.InteropServices; // For Marshal.Copy
namespace PrintReport { /// <summary> /// A simple console application that demonstrates one way to /// print Reporting Services reports to a printer. /// </summary> class app { /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main(string[] args) { PrintExample pe = new PrintExample(); // The name of the printer should be added here; // this could be a local or network printer. pe.PrintReport(@"\jacc-fs120S2500 PCL IT"); } }
class PrintExample { ReportingService rs; private byte[][] m_renderedReport; private Graphics.EnumerateMetafileProc m_delegate = null; private MemoryStream m_currentPageStream; private Metafile m_metafile = null; int m_numberOfPages; private int m_currentPrintingPage; private int m_lastPrintingPage;
public PrintExample() { // Create proxy object and authenticate Console.WriteLine("Authenticating to the Web service..."); rs = new ReportingService(); rs.Credentials = System.Net.CredentialCache.DefaultCredentials; }
// Build device info based on the start page deviceInfo = String.Format(@"<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>", "emf");
//Exectute the report and get page count. try { // Renders the first page of the report and returns streamIDs for // subsequent pages firstPage = rs.Render( reportPath, format, null, deviceInfo, null, null, null, out encoding, out mimeType, out reportHistoryParameters, out warnings, out streamIDs);
// The total number of pages of the report is 1 + the streamIDs m_numberOfPages = streamIDs.Length + 1; pages = new Byte[m_numberOfPages][];
// The first page was already rendered pages[0] = firstPage;
for (int pageIndex = 1; pageIndex < m_numberOfPages; pageIndex++) { // Build device info based on start page deviceInfo = String.Format(@"<DeviceInfo><OutputFormat>{0}</OutputFormat><StartPage>{1}</StartPage></DeviceInfo>", "emf", pageIndex+1); pages[pageIndex] = rs.Render( reportPath, format, null, deviceInfo, null, null, null, out encoding, out mimeType, out reportHistoryParameters, out warnings, out streamIDs); } }
private void pd_PrintPage(object sender, PrintPageEventArgs ev) { ev.HasMorePages = false; if (m_currentPrintingPage <= m_lastPrintingPage && MoveToPage(m_currentPrintingPage)) { // Draw the page ReportDrawPage(ev.Graphics); // If the next page is less than or equal to the last page, // print another page. if (++m_currentPrintingPage <= m_lastPrintingPage) ev.HasMorePages = true; } }
// Method to draw the current emf memory stream private void ReportDrawPage(Graphics g) { if(null == m_currentPageStream || 0 == m_currentPageStream.Length || null ==m_metafile) return; lock(this) { // Set the metafile delegate. int width = m_metafile.Width; int height= m_metafile.Height; m_delegate = new Graphics.EnumerateMetafileProc(MetafileCallback); // Draw in the rectangle Point destPoint = new Point(0, 0); g.EnumerateMetafile(m_metafile,destPoint , m_delegate); // Clean up m_delegate = null; } }
private bool MoveToPage(Int32 page) { // Check to make sure that the current page exists in // the array list if(null == this.RenderedReport[m_currentPrintingPage-1]) return false; // Set current page stream equal to the rendered page m_currentPageStream = new MemoryStream(this.RenderedReport[m_currentPrintingPage-1]); // Set its postion to start. m_currentPageStream.Position = 0; // Initialize the metafile if(null != m_metafile) { m_metafile.Dispose(); m_metafile = null; } // Load the metafile image for this page m_metafile = new Metafile((Stream)m_currentPageStream); return true; }
private bool MetafileCallback( EmfPlusRecordType recordType, int flags, int dataSize, IntPtr data, PlayRecordCallback callbackData) { byte[] dataArray = null; // Dance around unmanaged code. if (data != IntPtr.Zero) { // Copy the unmanaged record to a managed byte buffer // that can be used by PlayRecord. dataArray = new byte[dataSize]; Marshal.Copy(data, dataArray, 0, dataSize); } // play the record. m_metafile.PlayRecord(recordType, flags, dataSize, dataArray);
return true; }
public byte[][] RenderedReport { get { return m_renderedReport; } set { m_renderedReport = value; } } } }
Is there any way to pass a dataset parameter in RS 2000? Basicly, I have some data in my web page and want to print it. But I don't want to create another page to pass parameters to RS. I want to print the page as it is with the data already in the page, so if there is a way I can send the data through a dataset to the report directly, would be great. Is there?
In the data tab of my report (Reporting Services for SQL Server 2000), I'd like to run a query that requires parameters be passed to it. Is there a way to pass the parameters of the report to that query?
I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.
I am having a problem. I know of hidden parameters in SQL 2005 RS, but I need to have this functionality as well for SQL 2000 RS. I read on the Net that MS SQL 2000 RS Service Pack 1 adds this functionality. I have installed SP4 for SQL 2000, then I installed RS SP1 on my laptop, but I don't see that functionality in VS 2003 Enterprise Architect. I even installed RS SP2 on my other machine, but still no joy. Am I missing something here or is Microsoft lying about this functionality? My client will be upgrading to SQL 2005 sometime in the near future (if they don't change their minds again), but I need to implement this kind of functionality now. I can't wait for them to upgrade to SQL 2005. This parameter I need to hide in the reports is necessary to ensure the reports show data by specific department. I'll implement it in such a way that when they call the report from the web application, I'll pass the departmentID to the report via the query string.
P.S. It is not possible for me to achieve this using JOINs in the queries. The parameter lists themselves depend on this value.
People, help me with this? Already broke my brain.
I have some data on Sybase 9.0.2.3198, i'm tryin to render report with MSSQL ReportingServices 2000 Eval SP2.
the report designer is fyiReportDesigner 3.0 - and I can do preview with it.
after deployment I'm tryin to render it, RS gives me an error:
An error has occurred during report processing. (rsProcessingAborted) Get Online Help Query execution failed for data set 'Data'. (rsErrorExecutingCommand) Get Online Help Column '@start_date' not found
I tried connect through ODBC and OLEDB both - same result.
ps I'm gonna kill somebody real soon.
the sql:
WHERE PERIOD.BEGINDATE between CAST(@start_date as timestamp) and CAST(@end_date as timestamp)
Has anyone managed to crack getting multi-select parameters to work from an SSRS 2005 report which is querying an SSAS 2000 cube.
SSRS 2005 does support Multiselect, however SSRS 2000 did not. Given that i am querying an SSAS 2000 cube, i get the impression that i am also limited to SSRS 2000 functionality regarding to multi-select parameters.
Is there anyone out there that ever managed to get a wor around, say by using filters on a table or anything really that coud simulate the same behaviour....
This is probably a really obvious question but I have been struggling with answering it for about two days now. (I may even be in the wrong forum) I work in a company with an internal network set up. I have been trying to connect to our website via FTP and it has worked ... well it did in the beginning. I think the server that our website is hosted on is a Unix server - this is a guess from reading the FTP transcripts of the various programs I've downloaded and tried out in an attempt to connect to it.
What happened:
I was working on an .ASP page in Dreamweaver, hit save without thinking, realised I wanted to make a few additional changes before uploading it onto the server, cancelled the transaction and voila. I haven't been able to connect to the remote server since.
Depending on the FTP program that I am using, I get a connection error along these lines:
- "Connection to the server was reset" "The server may temporarily be down or unavailable or not accepting connections"
I am a big newbie at this. I have turned off the windows firewall and tried turning on and off passive IP. I can connect from home but not from inside the building which has me think this has to do with a stray setting somewhere. My housemate suggested I try using the "telnet" command from DOS and see can I get any sort of connection from work. If I can't, he said, it's probably a firewall on the internal network itself.
Does anyone know of a link or list that has all the parameters for the "rs:" section of the URL access parameter, except for the ones in the Microsoft books?
I'm trying to create reports in RS2005 using AS2000 as my data source. I understand that if I use RS2005 on AS2000, I wont be able to enjoy the OLAP based parameters as in using AS2005. Does anyone know an easy way to easily use Parameters in RS2005 while still using AS2000?
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,
Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).
Am very new to MS SQL adminstration Can anybody help me out how to work on Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A just for the practice.
The activity which am going to workout on MSDE is below.
How to install SQL(on XP) How the layout will be(like if i insall MSDE what are all Application will be and how they depends on each other) How to create/delete tables if so, how can we do it either by GUI or CUI
Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
Hi, Just upgraded some development desktops to Vista Business. However we need to still connect to some older remote windows 2000/SQL 2000 servers.
Trying to setup an ODBC system DSN on our Vista Business local desktop we get the following errors -
-START ERROR WINDOW- Connection Failed: SQLState: '01000' SQL Server Error: 772 [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (SECDoClientHandshake()0. Connection failed: SQLState: '08001' SQL Server Error: 18 [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security Error -END ERROR WINDOW-
Any help greatly appreciated as this is stopping us from making database/table connections etc. We've checked the firewall setup and all is well there.
PS - we can still connect fine using XP or windows 2000 desktops and their local DSNs.
