I am using MSDE and WebMatrix. My stored procedure is creating a Dynamic SQL query and is is about 200 lines long.
I am not getting the expected results, but also not generating any errors. I inserted a Print statement to print the resultant SQL query, but I don't know how to see or display that print result.
I do NOT have SQL2000, only MSDE. I am using WebMatrix and VB.net to create my application. Is there some class in asp.net that will help me, or some free utility. One of the problems is that the dynamic SQL is using over 20 parameters to create the query; the end result of the user picking fields on the webform.
i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!
Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file....
i would like to enquire about the Print statement. If I do not want to use the Raiseerror to raise an error as its not an error, but want to just print out in a dialog box, how do i do it?I tried using the Print statement but nothing happened. I am using this inside my trigger, can someone help?Thank You
Hey i have written a constraint trigger that will return an error message, but i want to include values from a count in the error msg. i know how it would be doing in oracle
RaisError('You cannot add more then' ||Bedroom_Count|| 'student tenants into this house', 10, 1)
First, I will apologise in advance for my ignorance, but I am new to the subject of SSIS.
My objective is tbe able to execute a stored procedure from SSIS and have a log of what the stored procedure did - preferably a log file
At the moment the stored procedure diagnostics etc are via T-SQL Print statement .
When I execute the stored procedure from SSIS I don't get any of the print diagnostics.
I've experimented with raiseerror - 1 severity < 11 gives no output, but a severity of 11 results in the print diagnostics being displayed in the output window - but the task goes red.
I have a stored procedure contains a few PRINT statement that return the status to the client. On the client side, I am using vb.net to add handler for it, as follow:
'-------------------------------------------------------------------------------- con = New SqlConnection("Data Source=.SQLEXPRESS;Initial Catalog=IntranetMaster;Integrated Security=True;Persist Security Info=False;Connect Timeout=0")
AddHandler con.InfoMessage, New SqlInfoMessageEventHandler(AddressOf ShowSQLMessage)
Sub ShowSQLMessage(ByVal o As Object, ByVal e As SqlInfoMessageEventArgs) Dim err As SqlError For Each err In e.Errors Console.WriteLine(Now() & " - SQL Message: " & err.Message) Next err End Sub
The result is that the message from several PRINT statements are coming out only at the end of the stored procedure and all in once, so I cannot time the individual processing time.
Is there anything I have done wrong? or anyway to make the PRINT result comes out immediately?
We have some SQL CLR code that was written to go get information from a web service...all works great and the CLR code does what it is supposed to. The problem is, it returns its success or failure status as a string text value (equivalent of a print statement I imagine) instead of as a result set or output parameter that i can check. In the proc I wrote to call the CLR stored proc I do some cleanup that i would like to rollback if the call to the web service that the CLR proc makes fails. Problem is, how can i get there. I have tried every way that I know and I can't seem to get that text into an object (temp table, variable whatever) that i can evaluate. Anyone have any suggestions?
I want to create store procedure which will print out something like this:
Insert into [dbname].dbo.[gameBooks] value (@gameBookID, gameBookTitle, ganeVolumn) But first one print out good, as I expected. I either got nothing or the error message on second piece.
Msg 245, Level 16, State 1, Line xxx Conversion failed when converting the varchar value 'Insert into [dbname].dbo.[cookBooks] value (' to data type int. Any Help will be greatly appreciated.
on SQL 2005 SP1. Here is my example code (not the real one): Create Procedure [dbo].[makeNewString] @myBookID As Declare bookID int Declare newRowID int Declare insertBookInfoString nvarchar(150) select 'Declare @newBookID int' Set insertBookInforString = (Select 'Insert into [dbname].dbo.[gameBooks] value (' + @gameBookID + ',' + gameBookTitle +',' + ganeVolumn +')' from [dbname].dbo.[gameBooks]) where @gameBookID=@myBookID Print insertBookInforString select 'SET @newRowID = Scope_Identity()' print @newRowID select 'Declare @newBookID int' Set insertBookInforString = (Select 'Insert into [dbname].dbo.[cookBooks] value (' + @cookBookID +',' + cookBookTitle +','+cookVolumn+')' from [dbname].dbo.[cookBooks] where @cookBookID=@myBookID) Print insertBookInforString first insertBookInfoString prints out fine. But I kept this error on second part:
------------------------------ DECLARE Declare @newBookID int (1 row(s) affected) Insert into [dbname].dbo.[gameBooks] value (@gameBookID, gameBookTitle, ganeVolumn) (this is what I want) ----------------------------------------- SET @newBookID = Scope_Identity() (1 row(s) affected)
Msg 245, Level 16, State 1, Line xxx Conversion failed when converting the varchar value 'Insert into [dbname].dbo.[cookBooks] value (' to data type int.
I'm trying to cath content of PRINT statements through InfoMessage event. I have subscribed on InfoMessage event through ConnectionEventsVt interface. The problem that event fired only once for statement. For example i have following T-SQL script:
Code Snippet
PRINT 'Message1' PRINT 'Message2' GO
PRINT 'Message3' PRINT 'Message4' GO
When i execute this script InfoMessage event fired twice. First time Error object Description property contains "Message1" text. Second time it contains "Message3" text.
What i'm doing wrong?
Server: MS SQL Sever 2005 OS: Windows XP SP2 IDE: Delphi 7
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
What I would like to do is run a select statement, and then have the result of the select be used/run thru the same select again untill it hits a certain where clause.
This is for a where used search in our BOM (Bill of Material) table.
So for example
BOM 1
level 1 Item# 123 type Finished Good level 2 Item# 45 type subassembly level 3 Item# 56 type purchased item
BOM 2
level 1 Item# 678 type Finished Good level 2 Item# 78 type subassembly level 3 Item#56 type purchased item
So my input into the select statement would be Item# 56, it will then first find Item# 45 and Item# 78, but those I am not interested in. I want the select to then take Item#45 and Item#78 and run it thru the same select so that it will find Item#123 and Item#678.
The select will know to stop there since these are flagged as "Finished Good" in our system, so they will not be in any other BOM. This is what I would imagine being specified in the "where clause".
I can write the first part, where the select will find Item #45 and Item#78, but don't know how to get past that point.
I am using MSSQL 2000, SQL Server Developer Edition and product version is : 8.00.760 (SP3)
Application Server: JBoss v 4.0.4 Driver : sql 2005 driver.
Hi,
I am running my application in JBOSS and hibernate to do all the query to the DB. While we are testing our application, it is working fine for the 1st day of stress testing, however after running for one days, it starts throwing the following exception. This can occur in a number of method call with no fixed pattern. Anyone has any idea what is going on?
used by: org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2148) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029) at org.hibernate.loader.Loader.list(Loader.java:2024) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:392) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:333) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1114) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:756) at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:63) ... 88 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source) at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at org.hibernate.loader.Loader.getResultSet(Loader.java:1669) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2145) ... 97 more
SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'
every times I execute it I get different result.
1st result : 17036986 2nd result : 17037903 3rd result : 17038309
Any idea??? There is no any inserting on TABLEA, so it should not has changes. Is that because of statistic is still updating?? I don't have much knowledge on this so I cannot sure the cause of the problem. Please advise.
Hi everyone,My brain refuses to remember the (undocumented?) stored procedure I'mthinking of. It takes at least two parameters: a sql statement toexecute, and a table name (or something of that nature).Then, for each value in the table, it executes the sql statement andpasses the value as a parameter.Can anybody refresh my memory? The functionality may be slighlydifferent than described, but the principle is the same. Thanks verymuch...-Joe
Hi,I have this code following my signature to SELECT a number.I don't know what am I missing, because it always return the number which the variable banID was initialized (32 in this case).The strangest thing is that if I run the statement inside MS SQL SERVER, I get the right result.Any help would be appreciated.Warm Regards,Mário Gamito--// Get inserted user IDint banID = 32 ;SqlConnection myConn = new SqlConnection("Data Source=192.168.1.6; Initial Catalog=db1; User=sa; Password=secret");try{myConn.Open()}catch (Exception e){Console.WriteLine(e.ToString());}try{SqlCommand myCommand = new SqlCommand("SELECT MAX(PublisherID) FROM table1", myConn);banID = Convert.ToInt32(myCommand.ExecuteScalar);}catch (Exception e){ Console.WriteLine(e.ToString());}Response.Write(banID);
then I try it like this : exec proc_add_comp 'new answer','by me',1
result is : (1 row(s) affected)
but when I look in the db, nothing was changed, comp_answer still has its old value.. comp_answer is nvarchar type column..isnt add operation allowed in update statement? thanks...
I want to export the data from a database and place it on a csv file to be imported to another database. However, I want some columns from the selected data (result of select statement) to be encrypted. What should I do? Is this possible? How do you decrypt the data during import?
The original data is from an MS SQL database to be transferred to a PostgreSQL database.
MS SQL ----- CSV (some columns are encrypted) ---- PostgreSQL (all columns decrypted)
I posted the same question on the mysql category. I wanted to delete it since I just realized today that I posted it on the wrong category but seems like there is no way I can delete.
How to get the CASE results highlighted in BOLD into this equation; "(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked" ? Thank you. CREATE VIEW dbo.vwu_ReportViewASSELECT EmployeeID , LastName , FirstName , LocationCode , UserID , Today , Login , AMBreakOut , AMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS AMBreak , LunchOut , LunchIn , CASE WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 66 THEN '0' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 67 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 81 THEN '15' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96 THEN '30' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 97 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 111 THEN '45' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126 THEN '60' ELSE '75' END AS LunchBreak, PMBreakOut , PMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS PMBreak , Logout , Comments , LoginLogon , AMBreakOutLogon , AMBreakInLogon , LunchOutLogon , LunchInLogon , PMBreakOutLogon , PMBreakInLogon , LogoutLogon ,(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked
Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.
currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.
Problem: i need to call an aggregate count on the results of another aggregate function (sum) with a group by.
*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales
This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.
thanks, Tom Anderson Software Engineer Custom Business Solutions
Hi all, I want to save DBCC's result into a table to execute my statement but I don't know how to, exmple for DBCC OPENTRAN (my database) - how can I put SPID & UID to my variable from this statement? Thanks in advance Happy New Year PTH
Gurus.I do not know if it is possible, but here is what I want to do.I want to allow user to page the SQL result, so he could decides toreturn from row 10 to row 20, or row 100 to 200, without returns thewhole resultset. Every time he sends another request, I do not mind tohit the database again, (I do not want to cache the result in themiddle tier server, scalability issue), and I know that I could achievethis with CURSOR, but unfortunately the FOR XML is not allowed in aCURSOR statement .(I know that I could achieve what I want to do by writing custom codein the middle tier, but I just want to see if there is a way to do thison the database side.)Any comments & suggestion is greatly appreciated.Thanks in advance.(I am using SQL2005)John
I'm trying to migrate to the 2005 JDBC driver against SQL Server 2000, but I'm getting the following exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. This is similar problem to a past thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=413019&SiteID=1 The difference is that my stored procedure uses cursors. This same stored procedure works fine with the 2000 JDBC driver.
Does anybody have any ideas how to resolve this problem?
We are having problems printing Reports (when printing by clicking on the AcitiveX print control), where the font for the fields are set to "C39HrP24DhTt" (barcode).
While viewing the report it displays as Barcodes but while printing, the Barcode does not get printed, but the string gets printed.
Environment: SSRS 2005
Using the ReportViewer Control in a .Net 2.0 Web App to render the reports.
BarCodes print fine in the following situations:
1. When the Report is exported to Excel and when printed from there
2. When you click on the print button on Internet Explorer
3. When saving as html (from view source) and opening that html document and printing.
BarCodes do NOT get printed in the following situation:
1. When printing by clicking on the "Print" (Active X Control) icon. Even the "Print Preview" does not show the Barcode.
I'm new to SQL Server and would like to add a calculated column to this query from the report writer in our ERP system based on the NextFreq case statement result.
Basically, I want to create a column called service with result as follows:
If IV.meter > NextFreq then the result should be 'OVERDUE' If (NextFreq - IV.meter) <50 then the result should be 'DUE SOON' Otherwise the result should be 'NOT DUE'
This is the code from the current report writer query:
Select IV.item, IV.meter, isnull(wt.name,0)as name, case when whh.meterstop is null then 0 end meterstop, whh.rejected, Case when cast(meterstop as int) > 0 then cast(meterstop as int) when meterstop is null then isnull(IV.meter,0) else isnull(IV.meter,0) end EndMeter, ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) as LastFreq, case when whh.rejected = 1 then ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 0 then 100 when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 100
SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO' FROM MPR_portfolio_transactions ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE
This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'
