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)
how do i achieve the same result in MS SERVER
regards
QUISH
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
View 1 Replies View RelatedI 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.
How to write query with PRINT Statement for below query.
WITH CTE1 AS (SELECT * FROM table1),
CTE2 AS (SELECT * FROM table2),
CTE3 AS (SELECT * FROM table3)
In between to use PRINT statement seeing where my current query is.
So once we process "SEELCT * FROM table1" and gets completed I want to print as "Table table1 completed".
Hi,
Mine Below Query is not Printing Anything though there is a matching record in table "gpcb_proj_paras"
Can any one help me??
declare @catg as char(1)
set @catg = '''A'''
declare @qry as nvarchar(3000)
declare @paras as varchar(200)
Set @paras = (Select convert(nvarchar(200), app_paras) from gpcb_proj_paras t
where t.act = @catg)
print @paras
-- Regards
Prashant Hirani
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.
Could someone help,
Thanks In advance.
Pete
I want to insert attibute att1 in field F1 . Value for this attribute is content of another field in this table (F2).
My query :
update MyTable
set F1.modify
('insert attribute att1 {sql:column("F2")} into (/ROOT/Node1)[1]')
Where F1 Is not null
But I get this error :
XML well-formedness check: Duplicate attribute 'att1'. Rewrite your XQuery so it returns well-formed XML.
How do I check the douplicate attribute ?
HI,
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
thanks
MJ
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 stored procedures as follows:
BEGIN
SET NOCOUNT OFF;
PRINT 'Remove Sales Data in Current Year'
EXEC uspMRT_RemoveSalesDataInCurrentYear @dbName
PRINT 'Remove Reference Tables'
EXEC uspMRT_RemoveRefTablesData @dbName
PRINT 'Import Data'
EXEC uspMRT_ImportData @dbName, @folder
END
'---------------------------------------------------------------------------------
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?
Thanks
Raymond
After running a select statement in vb 6, how do i send the results to the printer. Here is the code i've written so far:
Private Sub ProcSelectRecords()
Dim MyConn As ADODB.Connection
Set MyConn = New ADODB.Connection
Dim MyRecSet1 As New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:DocumentsA2 Computing Courseworkdb1.mdb"
MyConn.Open
Set MyRecSet1 = MyConn.Execute("SELECT * FROM tblItem_Sales")
MyConn.Close
End Sub
Im using MS Access as my backend
any help would me much appreciated.
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.
Hello all,
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
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.
UPDATE #2: When it said "Do you want to install Microsoft SQL Server" I said "yes" and that caused it to work. I exited and re-ran and now the print runs w/o the "install SQL Server" (If the prompt had said "Do you want to install the print dialog" we wouldn't be having this discussion...)
UPDATE: After posting this i discovered that the same thing occurs when attempting to print the report direct from IE6: First a dialog pops up "Do you want to install this software?" Name: Microsoft SQL Server. When I click "Don't Install" I get the dialog "unable to load client print control." Since this happens direct from IE6 I suspect it's browser settings. I'll resume tomorrow and post a followup.
My WinForm C# app integrates Reporting Services by calling them from WebBrowser controls. The problem is attempts to print cause a dialog: "unable to load client print control."
I've read prior posts that say "enable Active-X in your browser" - I don't know how to do that from a WebBrowser control.
Any ideas how to support Reporting Services "Print" from within a WebBrowser control?
RELATED THREADS
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=332145&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=264478&SiteID=1
Does someone could explain me the NOCASE attribute in the order by line?
I cant find that anywhere.
Thanks a lot,
venusgirrl
Hello !
Can somebody help me with the following error while deploying my cube:
The attribute key cannot be found : dbo_fact_table, Column: datetime Value 25/10/1901 4:18:00 pm...
The year 1901 is included in the time period of the time dimension.
The calendar includes the following dates :
1/1/1753 - 31/12/2007 (Time binding)
Why this referential integrity error occurs ??
Please help me because it is urgent and I cannot find a solution..
Thanks
What happens if a column which has been defined so that it should not be Nullable is passed a value which is null?
i.e. IDNumber - Primary Key and Nullable = No.
Thanks
can someone please tell me if you can implement a drop down list within a table attribute.
If anyone could show me an example please do.
Kind Regards
Rob
Hi:I need to store MAC Addresses. What is the standard way of storing thistype of attribute? Datatype? I can't find any discussions about thisanywhere.Thx for any pointers.
View 1 Replies View RelatedHi!I have a following problem: I need to select some attributes fromtable, i.e.first, third, fifth...Can anyone give me the hint how to do it. Is there some method to saysomthing like this:SELECT $1, $3, $5 which will refer to the 1st, 3rd and 5th attribute?Thanks!Mario.
View 5 Replies View RelatedI have the following as part of a stored procedure that generates an HTML report based on the data inserted into the table variable "@tblTemp":
SET @html =
N'<table border="1" width="0" style="font-family: Verdana, sans-serif;">' +
N'<tr><th>Name></th><th>ID</th></tr>' +
N'<col align="left"></col><col align="right"></col>' +
CAST ( ( SELECT td = [Name], '',
td = IDNumber
FROM @tblTemp
ORDER BY ID
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
SET @html = @html + N'<br /><br /><img src="muuscreenshot.jpg" width="452" height="507" alt="sample.aspx" />'
This generates a 2 column table that later in the stored procedure is sent out via sp_send_dbmail. The problem I'm having is that I want the right (2nd) column of the table to be right-aligned. For some reason, the line "
N'<col align="left"></col><col align="right"></col>' +" doesn't right-align the 2nd column when the report is sent through email (althought it does work if I take the generated string a use it to manually create an HTML file). So I want to add something like:
SET @html = REPLACE(@html, '<td', '<td align="right"')
but I still want the left column to be left-aligned, and this right-aligns everything. Is it possible for me to use REPLACE (or some other string function) to replace every other (alternating) "<td", or should I be looking at parsing this string using some 2005 XML features (unfortunately, using a CLR function to do this is not an option, since I am not allowed to turn that on on the server I'm using)? Thanks in advance.
-Dave
I am trying to create a linear regression model. The model is skipping lot of columns for regression. But I want model to use all the columns even if they are not valid. I use Forced_Regressor attribute while creating the model.
add mining model Regressiontableinput2_Model
(
[id] ,
[Reading] PREDICT_ONLY,
[Const] regressor,
[HCDD] regressor,
[HHDD] regressor,
[Hr1] regressor,
[Hr2] regressor,
[Hr3] regressor,
) USING MICROSOFT_LINEAR_REGRESSION (FORCED_REGRESSOR=1)
I get the error
Error (Data mining): The 'FORCED_REGRESSOR' data mining parameter is not valid for the 'Regressiontableinput2_Model' model.
Can somedbody tell me what is the syntax for using FORCED_REGRESSOR
Thanks,
DMNovice
I am trying to generate an output using Productname from below xml which will look like:
B/C/
B/D/E
demo xml:
DECLARE @myDoc xml
DECLARE @ProdID nvarchar(10)
SET @myDoc = '<Root>
<ProductDescription Productname="A" Productd="Road Bike">
[code]...
I have a query which works perfect but with nodes. similar way I am looking for the value column. I want a similar result from the function on "Value" column(value column is defined inside the below query) : B/D/E ....
with
CTE_xpath as (
select
T.C.value('local-name(.)', 'nvarchar(max)') as Name,
T.C.query('./*') as elements,
T.C.value('text()[1]', 'nvarchar(max)') as Value
[code]...
i have a column in my table that will store the sum of 3 other column, e.g the data in total_book column will be equal to the sum of the number in the romance, fiction and thriller column. how do i do this?
I am trying to retrive some values from and XML data colum , along with that i need other normal column data also . how can i do that ?
here is my problem
I have a table with 2 colum .
Column Data Type
ID Int
Data XML
The XML data looks like following
<A>
<B a = 'sun' b = 'Red' >
</B>
<A>
I want to get each id and the 'b' attribute of the B node of the xml data for each row ?
Please help me if there is any simple way to do this .
Hi everyone,
I heard that it is possible to make more than one attribute(coloumn) primary key. Is this really possible, logical, productive and beneficial ?
Thanks
How can I set an attribute of unsigned zerofill in SQL? Like for example, there is an option in MySQL phpmyadmin to set the attribute of a specific column to unsigned zerofill, it will be based on the length of the integer. For example, the lenght of my int is 5, so if i'll set the attribute of the column to unsigned zerofill, the value that will be auto incremented will have zeroes before that actual value, for example000010000200003....00010and so on and so forth...But, how can i do this in SQL? I'm using MS SQL Server Management Studio Express, but I can't find the field attributes to set it to unsigned zerofill.. I hope somebody will reply in this post.. :(
View 5 Replies View RelatedHi,I actualy have a problem with the connection to the databasefile that is stored within the websitetree by visual webdeveloper.I'm told that the attribute AttachDbFile is not understood. How have I to define which database (.mdf-file) should be taken that contains thetables that are needed in the later processing.Here the actual code:Imports Microsoft.VisualBasicImports SystemImports System.DataImports system.Data.SqlClientImports System.WebImports System.XmlPartial Class admin_news Inherits System.Web.UI.Page Protected Sub DetailsView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles DetailsView1.DataBound If DetailsView1.CurrentMode = DetailsViewMode.Insert Then DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text = Now End If If DetailsView1.CurrentMode = DetailsViewMode.Edit Then DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text = Now End If End Sub 'Prozedur wird bei Update ausgeführt Public Sub DetailsView1_Update(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Dim conn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFile=C:Pferdeservice KarleApp_dataPferde.mdf;Integrated Security=True Connection Timeout=30;User Instance=True") conn.Open() Dim cmd As New SqlCommand() Dim cmd1 As New SqlCommand() cmd.Connection = conn Dim sqlUpdate As String Dim sqlUpdate1 As String sqlUpdate = "UPDATE News_Kultur set Header = " sqlUpdate &= DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text sqlUpdate &= "Ueberschrift_D =" sqlUpdate &= DirectCast(DetailsView1.FindControl("Ueberschrift_D"), TextBox).Text sqlUpdate &= "Ueberschrift_E =" sqlUpdate &= DirectCast(DetailsView1.FindControl("Ueberschrift_E"), TextBox).Text sqlUpdate &= "Text_D" sqlUpdate &= DirectCast(DetailsView1.FindControl("Text_D"), TextBox).Text sqlUpdate &= "Text_E" sqlUpdate &= DirectCast(DetailsView1.FindControl("Text_E"), TextBox).Text sqlUpdate &= "WHERE (((News_Kultur.ID)=" sqlUpdate &= DirectCast(DetailsView1.FindControl("ArtikelID"), TextBox).Text cmd.CommandText = sqlUpdate sqlUpdate1 = "UPDATE News set Datum=" sqlUpdate1 &= DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text sqlUpdate1 &= "WHERE ID=" sqlUpdate1 &= DirectCast(DetailsView1.FindControl("NewsID"), TextBox).Text cmd.CommandType = CommandType.Text cmd1.CommandType = CommandType.Text cmd.ExecuteNonQuery() cmd1.ExecuteNonQuery() conn.Close() End SubEnd Class
View 1 Replies View RelatedIf I want to store day of the week (i.e. Mon), do I use datetime field or char? Also if I update #25/12/2004# to a datetime field. What is the value is the time port of the field? Thx.
View 10 Replies View RelatedHello, first time here, first time messing with SQL.
When creating a table in SQLServer2005 can you specify a column to be for a derived attribute. example a column monthlysalary and create a column that is a derived attribute - yearly salary. Is this done when creating the table and how is the syntax ?
My company is working on a bond derivative portfolio analysis tool andwe're facing a problem that I did not see adequately addressed anywhere in literature. I really did RTFM. I'm very experienced inrelational modelling (10+ years) so this is not a case of notunderstanding the principles. Here is the problem stripped ofirrelevant context. The problem below is simplified for the sake of theexample so don't sweat the details.THE PROBLEM1. There are many types of bonds, each type has a different set ofattributes, different attribute names, different attribute datatypes.For example, bond A has two variables: a yearly interest rate anddate of issue, B has five variables: an interest rate and 4 specificdates on which various portions of principal need to be paid, bond Chas a set of 4 variables: interest rate in period 1, interest rate inperiod 2, the date on which the bond can be put back to the issuer,and two dates on which the bond can be called by the issue. And so on.So, on the first attempt I could represent each bond type as its owntable. For example,create table bond_type_a (rate INTEGER, issue_date DATE)create table bond_type_b (rate INTEGER, principle_date1 DATE,principle_date2 DATE, principle_date3 DATE, principle_date4 DATE)create table bond_type_c (rate1 INTEGER, rate2 INTEGER, put_date DATE,call_date DATE)This is the nice relational approach but it does not work because:2. There are many thousands of bond types thus we would have to havemany thousands of tables which is bad.3. The client needs to be able construct the bond types on the flythrough the UI and add it to the system. Obviously, it would be bad ifeach new type of bond created in the UI resulted in a new table.4. When a user loads the bond portfolio it needs to be very fast. Inthe table per type approach if a user has a 100 different types if bondin the portfolio you would have to do 100 joins. This is a heavilymulti user environment so it's a non-starter. It's impossibly slow.THE SOLUTIONSSo now that we ditched the table per bond type approach we can considerthe followiing solutions (unpleasant from the relational point ofview):1. Name-Value pairs.create table bonds (bond_id INTEGER, bond_type INTEGER, attribute_idINTEGER, value VARCHAR(255))Comment: The client does not like this approach because they want torun various kinds of reports and thus they doe not want the values tobe stored as VARCHAR. They want the DB to enforce the datatype.2. Typed Name-Value pairs.create table bonds (bond_id INTEGER, bond_type INTEGER, attribute_idINTEGER, int_val INTEGER, string_val VARCHAR(255), date_val DATE_Comment: The client does not like this because the table is sparse.Every row has two empty fields.3. Link table with table per data type.create table bonds (bond_id INTEGER)create table bond_int_data (bond_id INTEGER REFERENCES bonds(bond_id),value INTEGER)create table bond_string_data (bond_id INTEGER REFERENCESbonds(bond_id), value VARCHAR(255))create table bond_date_data (bond_id INTEGER REFERENCES bonds(bond_id),value DATE)Comment: This meets most of the requirements but it just looks ugly.4. Dynamic Mappingcreate table (bond_id INTEGER, int_val1 INTEGER, int_val2 INTEGER,date_val1 DATE, date_val2 DATE, string_val1 VARCHAR(255), string_val2VARCHAR(255))Then you have to add some dynamic mapping in your code which willprovide bond specific mapping (say, stored in an XML file). Forexample,For bond_A: yearly_rate maps to int_val1, issue_date maps to date_val1For bond_C: rate1 maps to int_val1, rate2 maps to int_val2, put_datemaps to date_val1, call_date maps to date_val2)Comment: This is very good for performance because when I load aportfolio of different bond types I can pull them all in in one SELECTstatement. However this approach has a problem that the table issparse. The number of fields of each type has to be as high as toaccmodate the most complex bond while simple bonds will only be usingtwo or three.THE QUESTIONS:Are the four approaches I described above exhaustive? Are there anyother that I overlooked?
View 12 Replies View RelatedHello All,
I am going through the Data Mining Web Control sample and have that working beautifully. I now want to add in some more functionality by allowing the end user to select the: database, model and tree to view in the web viewer (similar to the parameters in SSRS). I am trying to figure out a way to get all this data programatically. So far this is what I have:
Code Snippet
For Each newDB As Microsoft.AnalysisServices.Database In myserver.Databases
dbName = newDB.Name.ToString
myLog.AppendText("DATABASE NAME - " + dbName + vbCrLf)
For Each newStructure As Microsoft.AnalysisServices.MiningStructure In newDB.MiningStructures
structureName = newStructure.Name.ToString
myLog.AppendText(" STRUCTURE NAME [ " + structureName + " ]" + vbCrLf)
For Each newModel As Microsoft.AnalysisServices.MiningModel In newStructure.MiningModels
modelName = newModel.ToString
modelAlgorithm = newModel.Algorithm.ToString
myLog.AppendText(" MODEL NAME -" + modelName + " " + modelAlgorithm + vbCrLf)
This works beautifully but I am having a problem getting the tree name which i believe its called the "attribute_name".
I can run the following SQL against Analysis Services and get the attribute name but I would like to get all this programatically and insert into a table.
Code Snippet
SELECT attribute_name
FROM [model].content
WHERE node_type=2
I plan to run this daily and update a table. This will ultimately let me pull the structure into my data mining viewer page. This way, I will only have to have one viewer page for all models, instead of a new page for each.
Thanks,
Cam