Datasource properties while publishing report

Hi,
I am having problem where I am uploading report and setting its datasource
properties using rs.setproperties. I had defined datasource but it says â'data
source in not published on report serverâ' or
â'System.Web.Services.Protocols.SoapException: The required field DataSource
is mi
ssing from the input structure. -->
Microsoft.ReportingServices.Diagnostics.Uti
lities.MissingElementException: The required field DataSource is missing
from th
e input structure.â'
. Any idea how to resolve this problem I am also attaching code for your
reference
Dim reference As New DataSourceReference()
reference.Reference = "/Report Project1/EBS"
Dim dataSources(1) As DataSource
Dim ds As New DataSource()
ds.Item = CType(reference, DataSourceDefinitionOrReference)
ds.Name = "EBS"
dataSources(0) = ds
Try
rs.SetReportDataSources(â'abc.rdlâ', dataSources)
Console.WriteLine("New reference set for the report.")
Catch e As SoapException
Console.WriteLine(e.Detail.InnerXml.ToString())
End Try
Thanks in Advance
JasvinderI know this might not totally make sense, but in the line where you declare
the dataSources variable, use the following syntax:
Dim dataSources(0) As DataSource
instead of:
Dim dataSources(1) As DataSource
and I bet it will work for you! Hope it does. -Aaron
"Jasvinder" wrote:
> Hi,
> I am having problem where I am uploading report and setting its datasource
> properties using rs.setproperties. I had defined datasource but it says â'data
> source in not published on report serverâ' or
> â'System.Web.Services.Protocols.SoapException: The required field DataSource
> is mi
> ssing from the input structure. -->
> Microsoft.ReportingServices.Diagnostics.Uti
> lities.MissingElementException: The required field DataSource is missing
> from th
> e input structure.â'
>
> . Any idea how to resolve this problem I am also attaching code for your
> reference
>
> Dim reference As New DataSourceReference()
> reference.Reference = "/Report Project1/EBS"
> Dim dataSources(1) As DataSource
> Dim ds As New DataSource()
> ds.Item = CType(reference, DataSourceDefinitionOrReference)
> ds.Name = "EBS"
> dataSources(0) = ds
>
> Try
> rs.SetReportDataSources(â'abc.rdlâ', dataSources)
> Console.WriteLine("New reference set for the report.")
> Catch e As SoapException
> Console.WriteLine(e.Detail.InnerXml.ToString())
> End Try
>
> Thanks in Advance
> Jasvinder
>|||Hi Aaron,
Yes i had figured out this problem and i am still not understanding how this
can work. Thought of posting messege to MS.
But anyhow thanks for the solution.
Another problem which is coming is "Unhandled
Exception:System.Security.SecurityPermission" I have added try catch block
everywhere but still in the process of finding this error. Moreover its
frequency of coming is not sure. Sometimes it does not comes and sometimes it
comes and vice versa.
Thanks
Jasvinder
"Aaron Williams" wrote:
> I know this might not totally make sense, but in the line where you declare
> the dataSources variable, use the following syntax:
> Dim dataSources(0) As DataSource
> instead of:
> Dim dataSources(1) As DataSource
> and I bet it will work for you! Hope it does. -Aaron
>
> "Jasvinder" wrote:
> > Hi,
> > I am having problem where I am uploading report and setting its datasource
> > properties using rs.setproperties. I had defined datasource but it says â'data
> > source in not published on report serverâ' or
> >
> > â'System.Web.Services.Protocols.SoapException: The required field DataSource
> > is mi
> >
> > ssing from the input structure. -->
> > Microsoft.ReportingServices.Diagnostics.Uti
> >
> > lities.MissingElementException: The required field DataSource is missing
> > from th
> >
> > e input structure.â'
> >
> >
> >
> > . Any idea how to resolve this problem I am also attaching code for your
> > reference
> >
> >
> >
> > Dim reference As New DataSourceReference()
> >
> > reference.Reference = "/Report Project1/EBS"
> >
> > Dim dataSources(1) As DataSource
> >
> > Dim ds As New DataSource()
> >
> > ds.Item = CType(reference, DataSourceDefinitionOrReference)
> >
> > ds.Name = "EBS"
> >
> > dataSources(0) = ds
> >
> >
> >
> > Try
> >
> > rs.SetReportDataSources(â'abc.rdlâ', dataSources)
> >
> > Console.WriteLine("New reference set for the report.")
> >
> > Catch e As SoapException
> >
> > Console.WriteLine(e.Detail.InnerXml.ToString())
> >
> > End Try
> >
> >
> >
> > Thanks in Advance
> > Jasvinder
> >sql

DataSet.xsd Path in App Config?

Hey all... what is the proper method for setting a Dataset.xsd path in your Settings.settings value:

Here's the app.config from settings.settings value:

<add name="ffgsCRM.My.MySettings.ffgscrmConnectionStringSQLCE"

connectionString="Data Source = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)\ffgsCRM.sdf"

providerName="Microsoft.SqlServerCe.Client" />

I have the above and wondered how to make the path dynamic to C:\Documents and Settings\USERNAME\Local Settings\Application Data\ffgsCRM.sdf in the settings.settings value.

Thanks a ton!!

Bill

I think what you want is to use the |DataDirectory| substitution. Eg "Data Source = |DataDirectory|\ffgsCRM.sdf"|||Thanks... but that only puts the .sdf in the bin and not where I need it. :)|||

To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules are applied to access the database folder:

?

For applications that are put in a folder on the user's computer, the database folder uses the application folder.

?

For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.

Note The .NET Compact Framework does not support the AppDomain.SetData method on Microsoft Windows Mobile-based devices. If an application calls the AppDomain.SetData method on a Windows Mobile-based device, you receive an error message.

(from http://support.microsoft.com/kb/920700)

|||

|||

To be honest I think you missed the point that both Erik and I were making with the DataDirectory in the when you deploy using ClickOnce it will automatically set the path to the users docs & settings folder.

w.r.t making the dataset dynamic - you can always set the connection object on each of the tableadapters that are used to fill the dataset?

|||

Nick, I'm wrong and had an error in the deployment... sorry... I'm going to try and path the Dataset1 to the users c:\drive\somewhere and go from there.

Thanks,

Bill

|||

my bad...

|||Yea, but where to you use the call to AppDomain.SetData? I call it in the load routine of my start up form, and my data is still being directed to a data folder from the click once deployment, and not to the place I want it in my application executable folder. ? what the ?|||

I did a public in the main form that looks like this for my dataset.xsd:

Public CRMConnectionString As String = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "\CRM.sdf"

Now when my app is fired up or deployed the dataset looks for the local db that is in the users program files folder... works all the time as long as you have the local db in the folder.

Hope this helps,

Bill

DataSet.xsd Path in App Config?

Hey all... what is the proper method for setting a Dataset.xsd path in your Settings.settings value:

Here's the app.config from settings.settings value:

<add name="ffgsCRM.My.MySettings.ffgscrmConnectionStringSQLCE"

connectionString="Data Source = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)\ffgsCRM.sdf"

providerName="Microsoft.SqlServerCe.Client" />

I have the above and wondered how to make the path dynamic to C:\Documents and Settings\USERNAME\Local Settings\Application Data\ffgsCRM.sdf in the settings.settings value.

Thanks a ton!!

Bill

I think what you want is to use the |DataDirectory| substitution. Eg "Data Source = |DataDirectory|\ffgsCRM.sdf"|||Thanks... but that only puts the .sdf in the bin and not where I need it. :)|||

To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules are applied to access the database folder:

? For applications that are put in a folder on the user's computer, the database folder uses the application folder. ? For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.

Note The .NET Compact Framework does not support the AppDomain.SetData method on Microsoft Windows Mobile-based devices. If an application calls the AppDomain.SetData method on a Windows Mobile-based device, you receive an error message.

(from http://support.microsoft.com/kb/920700)

|||

|||

To be honest I think you missed the point that both Erik and I were making with the DataDirectory in the when you deploy using ClickOnce it will automatically set the path to the users docs & settings folder.

w.r.t making the dataset dynamic - you can always set the connection object on each of the tableadapters that are used to fill the dataset?

|||

Nick, I'm wrong and had an error in the deployment... sorry... I'm going to try and path the Dataset1 to the users c:\drive\somewhere and go from there.

Thanks,

Bill

|||

my bad...

|||Yea, but where to you use the call to AppDomain.SetData? I call it in the load routine of my start up form, and my data is still being directed to a data folder from the click once deployment, and not to the place I want it in my application executable folder. ? what the ?|||

I did a public in the main form that looks like this for my dataset.xsd:

Public CRMConnectionString As String = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "\CRM.sdf"

Now when my app is fired up or deployed the dataset looks for the local db that is in the users program files folder... works all the time as long as you have the local db in the folder.

Hope this helps,

Bill

sql

Dataset Dynamic Query

I am trying to implement the setting of a parameter to the default value of
'-All-' using the IIF function in the WHERE clause.
I received a processing error then backed off and checked a basic 'SELECT
select list FROM tablename WHERE columnname = value' query. It ran in
Preview just fine.
Then I wrapped the query in ' = " query w/o changes" ' When this runs in
Preview I get the following message in a pop-up box .. "Processing Error ..
An error has occurred during report processing. Query execution failed for
data set 'TestDS'. Line 1: Incorrect syntax error'.' "
The only change is the ' = " ..." '
The Datatset Command type is 'Text'
Any idea what is happening here'
--
Thanks in advance
GHHi Vakar,
Based on my knowledge, the Generic Query Designer Execute button will grey
out when the query begins with an '='
This is expected behavior since dynamic query cannot be executed in Generic
Query Designer, which also means you are not able to Perview the data now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Correct the i cannot execute the query from the Query Designer in the Data tab.
I get the error when I go to the the Preview tab.
Thanks
"Michael Cheng [MSFT]" wrote:
> Hi Vakar,
> Based on my knowledge, the Generic Query Designer Execute button will grey
> out when the query begins with an '='
> This is expected behavior since dynamic query cannot be executed in Generic
> Query Designer, which also means you are not able to Perview the data now.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||If you get an error when you go to Preview it is because something is wrong
with your string. If you have to (and I avoid it) use an expression then do
these steps.
1. Create a report with report parameters and a text box (nothing else)
2. Set the textbox to the expression that will end up being the source for
the dataset. Preview and look at what is in the textbox and make sure that
it is valid SQL (copy and paste into query analyzer and validate you have it
correctly).
3. create a query that will have the same fields returned to get you field
list populated
4. change to the expression that you now know (from #2) is correct.
These are the steps I always take.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> Correct the i cannot execute the query from the Query Designer in the Data
tab.
> I get the error when I go to the the Preview tab.
> Thanks
> "Michael Cheng [MSFT]" wrote:
> > Hi Vakar,
> >
> > Based on my knowledge, the Generic Query Designer Execute button will
grey
> > out when the query begins with an '='
> >
> > This is expected behavior since dynamic query cannot be executed in
Generic
> > Query Designer, which also means you are not able to Perview the data
now.
> >
> > Thank you for your patience and corporation. If you have any questions
or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Get Secure! - http://www.microsoft.com/security
> >
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only, many thanks!
> >
> >|||Great suggustion ... I was going to ask for debugging suggestions ... I
The following testing was just completed.
I created a dataset (query 2) that is a subset of my problem query (query 1)
which is populating another table. The IIF function is the SAME for both
queries (I cut and pasted from Query 2 to Query 1). Query 2 works but Query
1 gets the error. Query 1 runs when commenting out the IIF function and
removing the =" ".
Here are the queries:
Query 1
="SELECT DISTINCT
a.EID
,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
,a.PassageDistrictID
,Region = CASE
WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
WHEN e.PassageRegionFullName = '' THEN 'Unknown'
ELSE e.PassageRegionFullName
END
,a.EventName
,RegionalMgrName = CASE
WHEN b.LastName IS NULL THEN 'N/A'
WHEN b.LastName = '' THEN 'N/A'
ELSE b.LastName
END
,OperationsMgrName = CASE
WHEN d.LastName IS NULL THEN 'N/A'
WHEN d.LastName = '' THEN 'N/A'
ELSE d.LastName
END
,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
FROM dbo.Event a
LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=e.PassageDistrictID "
& IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
Parameters!Region.Value & "")
Query 2
="SELECT a.EID, a.EventName
FROM dbo.Event a
JOIN GeographicDim_PE b
ON a.PassageDistrictID = b.PassageDistrictID "
& IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
Parameters!Region.Value & "")
"Bruce L-C [MVP]" wrote:
> If you get an error when you go to Preview it is because something is wrong
> with your string. If you have to (and I avoid it) use an expression then do
> these steps.
> 1. Create a report with report parameters and a text box (nothing else)
> 2. Set the textbox to the expression that will end up being the source for
> the dataset. Preview and look at what is in the textbox and make sure that
> it is valid SQL (copy and paste into query analyzer and validate you have it
> correctly).
> 3. create a query that will have the same fields returned to get you field
> list populated
> 4. change to the expression that you now know (from #2) is correct.
> These are the steps I always take.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "GH" <vakar@.community.nospam> wrote in message
> news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > Correct the i cannot execute the query from the Query Designer in the Data
> tab.
> >
> > I get the error when I go to the the Preview tab.
> >
> > Thanks
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> > > Hi Vakar,
> > >
> > > Based on my knowledge, the Generic Query Designer Execute button will
> grey
> > > out when the query begins with an '='
> > >
> > > This is expected behavior since dynamic query cannot be executed in
> Generic
> > > Query Designer, which also means you are not able to Perview the data
> now.
> > >
> > > Thank you for your patience and corporation. If you have any questions
> or
> > > concerns, don't hesitate to let me know. We are always here to be of
> > > assistance!
> > >
> > >
> > > Sincerely yours,
> > >
> > > Michael Cheng
> > >
> > > Online Partner Support Specialist
> > > Partner Support Group
> > > Microsoft Global Technical Support Center
> > > ---
> > > Get Secure! - http://www.microsoft.com/security
> > >
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > Please reply to newsgroups only, many thanks!
> > >
> > >
>
>|||I have heard that sometimes there is a problem with put in a hard carriage
return in the expression box. Make sure the iif part is on the same line.
I.e. let any line wrapping in the expression box occur on its own, don't do
a carriage return to put the iif on another line. Just a guess.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> Great suggustion ... I was going to ask for debugging suggestions ... I
> The following testing was just completed.
> I created a dataset (query 2) that is a subset of my problem query (query
1)
> which is populating another table. The IIF function is the SAME for both
> queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
Query
> 1 gets the error. Query 1 runs when commenting out the IIF function and
> removing the =" ".
> Here are the queries:
> Query 1
> ="SELECT DISTINCT
> a.EID
> ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> ,a.PassageDistrictID
> ,Region => CASE
> WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> ELSE e.PassageRegionFullName
> END
> ,a.EventName
> ,RegionalMgrName => CASE
> WHEN b.LastName IS NULL THEN 'N/A'
> WHEN b.LastName = '' THEN 'N/A'
> ELSE b.LastName
> END
> ,OperationsMgrName => CASE
> WHEN d.LastName IS NULL THEN 'N/A'
> WHEN d.LastName = '' THEN 'N/A'
> ELSE d.LastName
> END
> ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
> FROM dbo.Event a
> LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> e.PassageDistrictID "
> & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> Parameters!Region.Value & "")
> Query 2
> ="SELECT a.EID, a.EventName
> FROM dbo.Event a
> JOIN GeographicDim_PE b
> ON a.PassageDistrictID = b.PassageDistrictID "
> & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> Parameters!Region.Value & "")
> "Bruce L-C [MVP]" wrote:
> > If you get an error when you go to Preview it is because something is
wrong
> > with your string. If you have to (and I avoid it) use an expression then
do
> > these steps.
> > 1. Create a report with report parameters and a text box (nothing else)
> > 2. Set the textbox to the expression that will end up being the source
for
> > the dataset. Preview and look at what is in the textbox and make sure
that
> > it is valid SQL (copy and paste into query analyzer and validate you
have it
> > correctly).
> > 3. create a query that will have the same fields returned to get you
field
> > list populated
> > 4. change to the expression that you now know (from #2) is correct.
> >
> > These are the steps I always take.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > Correct the i cannot execute the query from the Query Designer in the
Data
> > tab.
> > >
> > > I get the error when I go to the the Preview tab.
> > >
> > > Thanks
> > >
> > > "Michael Cheng [MSFT]" wrote:
> > >
> > > > Hi Vakar,
> > > >
> > > > Based on my knowledge, the Generic Query Designer Execute button
will
> > grey
> > > > out when the query begins with an '='
> > > >
> > > > This is expected behavior since dynamic query cannot be executed in
> > Generic
> > > > Query Designer, which also means you are not able to Perview the
data
> > now.
> > > >
> > > > Thank you for your patience and corporation. If you have any
questions
> > or
> > > > concerns, don't hesitate to let me know. We are always here to be of
> > > > assistance!
> > > >
> > > >
> > > > Sincerely yours,
> > > >
> > > > Michael Cheng
> > > >
> > > > Online Partner Support Specialist
> > > > Partner Support Group
> > > > Microsoft Global Technical Support Center
> > > > ---
> > > > Get Secure! - http://www.microsoft.com/security
> > > >
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > Please reply to newsgroups only, many thanks!
> > > >
> > > >
> >
> >
> >|||I just tried that ...
I removed all cr/lf making on long line of code ... nastly ... but it
worked ...
My mind thinks TSQL/Query Analyzer and forgets that in RS to think VB!!
Thanks for you help!!
"Bruce L-C [MVP]" wrote:
> I have heard that sometimes there is a problem with put in a hard carriage
> return in the expression box. Make sure the iif part is on the same line.
> I.e. let any line wrapping in the expression box occur on its own, don't do
> a carriage return to put the iif on another line. Just a guess.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "GH" <vakar@.community.nospam> wrote in message
> news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> > Great suggustion ... I was going to ask for debugging suggestions ... I
> >
> > The following testing was just completed.
> >
> > I created a dataset (query 2) that is a subset of my problem query (query
> 1)
> > which is populating another table. The IIF function is the SAME for both
> > queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
> Query
> > 1 gets the error. Query 1 runs when commenting out the IIF function and
> > removing the =" ".
> >
> > Here are the queries:
> > Query 1
> > ="SELECT DISTINCT
> > a.EID
> > ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> > ,a.PassageDistrictID
> > ,Region => > CASE
> > WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> > WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> > ELSE e.PassageRegionFullName
> > END
> > ,a.EventName
> > ,RegionalMgrName => > CASE
> > WHEN b.LastName IS NULL THEN 'N/A'
> > WHEN b.LastName = '' THEN 'N/A'
> > ELSE b.LastName
> > END
> > ,OperationsMgrName => > CASE
> > WHEN d.LastName IS NULL THEN 'N/A'
> > WHEN d.LastName = '' THEN 'N/A'
> > ELSE d.LastName
> > END
> > ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
> > FROM dbo.Event a
> > LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> > LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> > LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> > LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> > e.PassageDistrictID "
> > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > Parameters!Region.Value & "")
> >
> > Query 2
> > ="SELECT a.EID, a.EventName
> > FROM dbo.Event a
> > JOIN GeographicDim_PE b
> > ON a.PassageDistrictID = b.PassageDistrictID "
> > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > Parameters!Region.Value & "")
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > If you get an error when you go to Preview it is because something is
> wrong
> > > with your string. If you have to (and I avoid it) use an expression then
> do
> > > these steps.
> > > 1. Create a report with report parameters and a text box (nothing else)
> > > 2. Set the textbox to the expression that will end up being the source
> for
> > > the dataset. Preview and look at what is in the textbox and make sure
> that
> > > it is valid SQL (copy and paste into query analyzer and validate you
> have it
> > > correctly).
> > > 3. create a query that will have the same fields returned to get you
> field
> > > list populated
> > > 4. change to the expression that you now know (from #2) is correct.
> > >
> > > These are the steps I always take.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "GH" <vakar@.community.nospam> wrote in message
> > > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > > Correct the i cannot execute the query from the Query Designer in the
> Data
> > > tab.
> > > >
> > > > I get the error when I go to the the Preview tab.
> > > >
> > > > Thanks
> > > >
> > > > "Michael Cheng [MSFT]" wrote:
> > > >
> > > > > Hi Vakar,
> > > > >
> > > > > Based on my knowledge, the Generic Query Designer Execute button
> will
> > > grey
> > > > > out when the query begins with an '='
> > > > >
> > > > > This is expected behavior since dynamic query cannot be executed in
> > > Generic
> > > > > Query Designer, which also means you are not able to Perview the
> data
> > > now.
> > > > >
> > > > > Thank you for your patience and corporation. If you have any
> questions
> > > or
> > > > > concerns, don't hesitate to let me know. We are always here to be of
> > > > > assistance!
> > > > >
> > > > >
> > > > > Sincerely yours,
> > > > >
> > > > > Michael Cheng
> > > > >
> > > > > Online Partner Support Specialist
> > > > > Partner Support Group
> > > > > Microsoft Global Technical Support Center
> > > > > ---
> > > > > Get Secure! - http://www.microsoft.com/security
> > > > >
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > > Please reply to newsgroups only, many thanks!
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Great. Wasn't sure if it would solve the problem but I couldn't see anything
wrong (having the second query with the iif work but not the first was the
clue for me).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:FF5CFDA6-8144-482A-BA91-DB35B53ECE25@.microsoft.com...
> I just tried that ...
> I removed all cr/lf making on long line of code ... nastly ... but it
> worked ...
> My mind thinks TSQL/Query Analyzer and forgets that in RS to think VB!!
> Thanks for you help!!
> "Bruce L-C [MVP]" wrote:
> > I have heard that sometimes there is a problem with put in a hard
carriage
> > return in the expression box. Make sure the iif part is on the same
line.
> > I.e. let any line wrapping in the expression box occur on its own, don't
do
> > a carriage return to put the iif on another line. Just a guess.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> > > Great suggustion ... I was going to ask for debugging suggestions ...
I
> > >
> > > The following testing was just completed.
> > >
> > > I created a dataset (query 2) that is a subset of my problem query
(query
> > 1)
> > > which is populating another table. The IIF function is the SAME for
both
> > > queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
> > Query
> > > 1 gets the error. Query 1 runs when commenting out the IIF function
and
> > > removing the =" ".
> > >
> > > Here are the queries:
> > > Query 1
> > > ="SELECT DISTINCT
> > > a.EID
> > > ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> > > ,a.PassageDistrictID
> > > ,Region => > > CASE
> > > WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> > > WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> > > ELSE e.PassageRegionFullName
> > > END
> > > ,a.EventName
> > > ,RegionalMgrName => > > CASE
> > > WHEN b.LastName IS NULL THEN 'N/A'
> > > WHEN b.LastName = '' THEN 'N/A'
> > > ELSE b.LastName
> > > END
> > > ,OperationsMgrName => > > CASE
> > > WHEN d.LastName IS NULL THEN 'N/A'
> > > WHEN d.LastName = '' THEN 'N/A'
> > > ELSE d.LastName
> > > END
> > > ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS
AssignmentLtrSentDt
> > > FROM dbo.Event a
> > > LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> > > LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> > > LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> > > LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> > > e.PassageDistrictID "
> > > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > > Parameters!Region.Value & "")
> > >
> > > Query 2
> > > ="SELECT a.EID, a.EventName
> > > FROM dbo.Event a
> > > JOIN GeographicDim_PE b
> > > ON a.PassageDistrictID = b.PassageDistrictID "
> > > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > > Parameters!Region.Value & "")
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > If you get an error when you go to Preview it is because something
is
> > wrong
> > > > with your string. If you have to (and I avoid it) use an expression
then
> > do
> > > > these steps.
> > > > 1. Create a report with report parameters and a text box (nothing
else)
> > > > 2. Set the textbox to the expression that will end up being the
source
> > for
> > > > the dataset. Preview and look at what is in the textbox and make
sure
> > that
> > > > it is valid SQL (copy and paste into query analyzer and validate you
> > have it
> > > > correctly).
> > > > 3. create a query that will have the same fields returned to get you
> > field
> > > > list populated
> > > > 4. change to the expression that you now know (from #2) is correct.
> > > >
> > > > These are the steps I always take.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "GH" <vakar@.community.nospam> wrote in message
> > > > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > > > Correct the i cannot execute the query from the Query Designer in
the
> > Data
> > > > tab.
> > > > >
> > > > > I get the error when I go to the the Preview tab.
> > > > >
> > > > > Thanks
> > > > >
> > > > > "Michael Cheng [MSFT]" wrote:
> > > > >
> > > > > > Hi Vakar,
> > > > > >
> > > > > > Based on my knowledge, the Generic Query Designer Execute button
> > will
> > > > grey
> > > > > > out when the query begins with an '='
> > > > > >
> > > > > > This is expected behavior since dynamic query cannot be executed
in
> > > > Generic
> > > > > > Query Designer, which also means you are not able to Perview the
> > data
> > > > now.
> > > > > >
> > > > > > Thank you for your patience and corporation. If you have any
> > questions
> > > > or
> > > > > > concerns, don't hesitate to let me know. We are always here to
be of
> > > > > > assistance!
> > > > > >
> > > > > >
> > > > > > Sincerely yours,
> > > > > >
> > > > > > Michael Cheng
> > > > > >
> > > > > > Online Partner Support Specialist
> > > > > > Partner Support Group
> > > > > > Microsoft Global Technical Support Center
> > > > > > ---
> > > > > > Get Secure! - http://www.microsoft.com/security
> > > > > >
> > > > > > This posting is provided "as is" with no warranties and confers
no
> > > > rights.
> > > > > > Please reply to newsgroups only, many thanks!
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Dataming of subsets question

Have not found an active general data mining forum yet so asking here.

I'm new to data mining band have been given the task of setting up a data mining system. The problem is that our setup seems pretty non standard and I'm not sure how to use data mining on it or what my expectations should be. The situation is:

We have a large and growing set of strings which we get requests for (> 100,000). The requests have many, mostly nominal (non-numeric), variables associated with them. We can only handle a subset (probably less than 10,000) or the strings at any one time. We want to use data mining to analyze historic requests so we can figure out which strings we are going to handle under a given set of variables. So given that our variable currently have values X1, ..., Xn, what subset should be use given than a large database of historic string requests.

Anyone know what techniques would work well for this kind of problem? This is a quick and dirty kind of project, no special purpose hardware or expensive software on this one. I've been looking at using rapid miner but not sure that it's a great tool in this case.

thanks in advance,
max

A first ideea is to use Microsoft Clustering Algorithm to cluster your requests and find similare requests- for this situation you don't have to have a predictable attribute.

A second way is to define an objective by selecting one (or more) columns from your requests, let's say Xj and put the question :

What are the links/influences of X1,..., Xn with Xj?

Or create another attribute Xn+1 which same question.

Let see as Microsoft example that is presented in it's tutorials;it have following attributes:

CommuteDistance(X1)

Gender(X2)

HouseOwnerFlag(X3)

MaritalStatus(X4)

...

NumberCarsOwned(Xn)

and a predictible attribute:

BikeBuyer(Xj)

that mean if customer buy or not a bicycle; so in this case the question/objective is in the some way formulated:

What are the links/influences of X1,..., Xn with Xj?

There is a good book presented here

|||

You could try using and classification method with the other fields as input and the target string as output. Then you could rank the likelihood of each string given some input.

Candidate algorithms are

- Naive Bayes

- Logistic Regression

- Neural Nets (slow)

- Association Rules (not traditionally a classification method, but could work in this case)

After creating the model you would do something like

SELECT FLATTENED (SELECT TOP 10000 TargetString FROM PredictHistogram(TargetString) ORDER BY $Probability) FROM MyModel NATURAL PREDICTION JOIN

(SELECT 'Factor1' AS Factor1, 'Factor2' AS Factor2) AS t

HTH

-Jamie

Dataming of subsets question

Have not found an active general data mining forum yet so asking here.

I'm new to data mining band have been given the task of setting up a data mining system. The problem is that our setup seems pretty non standard and I'm not sure how to use data mining on it or what my expectations should be. The situation is:

We have a large and growing set of strings which we get requests for (> 100,000). The requests have many, mostly nominal (non-numeric), variables associated with them. We can only handle a subset (probably less than 10,000) or the strings at any one time. We want to use data mining to analyze historic requests so we can figure out which strings we are going to handle under a given set of variables. So given that our variable currently have values X1, ..., Xn, what subset should be use given than a large database of historic string requests.

Anyone know what techniques would work well for this kind of problem? This is a quick and dirty kind of project, no special purpose hardware or expensive software on this one. I've been looking at using rapid miner but not sure that it's a great tool in this case.

thanks in advance,
max

A first ideea is to use Microsoft Clustering Algorithm to cluster your requests and find similare requests- for this situation you don't have to have a predictable attribute.

A second way is to define an objective by selecting one (or more) columns from your requests, let's say Xj and put the question :

What are the links/influences of X1,..., Xn with Xj?

Or create another attribute Xn+1 which same question.

Let see as Microsoft example that is presented in it's tutorials;it have following attributes:

CommuteDistance(X1)

Gender(X2)

HouseOwnerFlag(X3)

MaritalStatus(X4)

...

NumberCarsOwned(Xn)

and a predictible attribute:

BikeBuyer(Xj)

that mean if customer buy or not a bicycle; so in this case the question/objective is in the some way formulated:

What are the links/influences of X1,..., Xn with Xj?

There is a good book presented here

|||

You could try using and classification method with the other fields as input and the target string as output. Then you could rank the likelihood of each string given some input.

Candidate algorithms are

- Naive Bayes

- Logistic Regression

- Neural Nets (slow)

- Association Rules (not traditionally a classification method, but could work in this case)

After creating the model you would do something like

SELECT FLATTENED (SELECT TOP 10000 TargetString FROM PredictHistogram(TargetString) ORDER BY $Probability) FROM MyModel NATURAL PREDICTION JOIN

(SELECT 'Factor1' AS Factor1, 'Factor2' AS Factor2) AS t

HTH

-Jamie

DataGridViewComboBoxColumn - DROPDOWN STYLE

Dear Sir,

can u tell what command is available for setting the drop down style of a DataGridViewComboBoxColumn. By default it is coming as dropdown list, but i want to make it only drop down, so that i can make the combo blank and populate the list at my will, which we can do with other combo box controls.

with regards,

wilfi

It isn't T-SQL task. You could configure this setting by DataGridView properties, not bu Transact-SQL commands

Data-Driven Subscription Link

Hi,
I'm setting up a data driven subscription for a report. I'm using email as
the delivery option, am NOT including the report in the email, instead using
the hyperlink feature to bring the user to the report on the server. I
tested it out and I received the email with the link but the link displays
the full address of the server, report path and parameters to the recipient.
We designed the system to use URL access and hidden parameters to hide all
paths and parameter options from the user, how can we have the link in the
email display something like: "Click here to view report" instead of:
http://myserver/ReportServer?/Reporting/FullReport&Client=xxx&date=xx/xx/xxxx.
It is very important to my company that we hide this string from the user.
thank you
JerryI'm not sure how you could accomplish this. Even if we put just the text
you suggest and make it a hyperlink, the url would still be visible to the
user. You could always modify the comments section to include html and put
the hyperlink in yourself, but this would not hide the parameter.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry" <idroppeddabomb@.hotmail.com> wrote in message
news:OJUzVIblEHA.2820@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm setting up a data driven subscription for a report. I'm using email as
> the delivery option, am NOT including the report in the email, instead
using
> the hyperlink feature to bring the user to the report on the server. I
> tested it out and I received the email with the link but the link displays
> the full address of the server, report path and parameters to the
recipient.
> We designed the system to use URL access and hidden parameters to hide all
> paths and parameter options from the user, how can we have the link in the
> email display something like: "Click here to view report" instead of:
>
http://myserver/ReportServer?/Reporting/FullReport&Client=xxx&date=xx/xx/xxxx.
> It is very important to my company that we hide this string from the user.
> thank you
> Jerry
>|||We worked around this by displaying the report within a pop-up window that we
instanciated via Javascript. That allowed us to turn off the address bar and
hide the URL. The user could still access it by right-clicking on the page
and going to properties, but at least it's not obvious.
sebring1130
"Daniel Reib [MSFT]" wrote:
> I'm not sure how you could accomplish this. Even if we put just the text
> you suggest and make it a hyperlink, the url would still be visible to the
> user. You could always modify the comments section to include html and put
> the hyperlink in yourself, but this would not hide the parameter.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jerry" <idroppeddabomb@.hotmail.com> wrote in message
> news:OJUzVIblEHA.2820@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > I'm setting up a data driven subscription for a report. I'm using email as
> > the delivery option, am NOT including the report in the email, instead
> using
> > the hyperlink feature to bring the user to the report on the server. I
> > tested it out and I received the email with the link but the link displays
> > the full address of the server, report path and parameters to the
> recipient.
> > We designed the system to use URL access and hidden parameters to hide all
> > paths and parameter options from the user, how can we have the link in the
> > email display something like: "Click here to view report" instead of:
> >
> http://myserver/ReportServer?/Reporting/FullReport&Client=xxx&date=xx/xx/xxxx.
> > It is very important to my company that we hide this string from the user.
> >
> > thank you
> >
> > Jerry
> >
> >
>
>|||In another situation, I didn't care that the URL was visible, I just had one
particular parameter that I couldn't allow the users to see (the username).
I solved this problem by "encrypting" that parameter. I created a simple SQL
function where I pass in the username, it manipulates the string a little and
then returns the SOUNDEX of the manipulated string. I used the function when
creating the data for the report and then again when selecting the parameters
for the data-driven subscription. I published the report, then went to
Report Manager and checked the "Prompt User" checkbox then set the "Prompt
String" to null. That allows me to send out the URL in the subscription
e-mails that are generated because the parameter value is meaningless to the
user (they see 'S165' instead of 'sebring', for example). They can click on
the URL link and the report comes up for them, but because the "Prompt
String" is set to null in the subscription, they cannot change that
parameter. It worked perfectly for what we needed.
sebring1130
"sebring1130" wrote:
> We worked around this by displaying the report within a pop-up window that we
> instanciated via Javascript. That allowed us to turn off the address bar and
> hide the URL. The user could still access it by right-clicking on the page
> and going to properties, but at least it's not obvious.
> sebring1130
>
> "Daniel Reib [MSFT]" wrote:
> > I'm not sure how you could accomplish this. Even if we put just the text
> > you suggest and make it a hyperlink, the url would still be visible to the
> > user. You could always modify the comments section to include html and put
> > the hyperlink in yourself, but this would not hide the parameter.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Jerry" <idroppeddabomb@.hotmail.com> wrote in message
> > news:OJUzVIblEHA.2820@.TK2MSFTNGP15.phx.gbl...
> > > Hi,
> > >
> > > I'm setting up a data driven subscription for a report. I'm using email as
> > > the delivery option, am NOT including the report in the email, instead
> > using
> > > the hyperlink feature to bring the user to the report on the server. I
> > > tested it out and I received the email with the link but the link displays
> > > the full address of the server, report path and parameters to the
> > recipient.
> > > We designed the system to use URL access and hidden parameters to hide all
> > > paths and parameter options from the user, how can we have the link in the
> > > email display something like: "Click here to view report" instead of:
> > >
> > http://myserver/ReportServer?/Reporting/FullReport&Client=xxx&date=xx/xx/xxxx.
> > > It is very important to my company that we hide this string from the user.
> > >
> > > thank you
> > >
> > > Jerry
> > >
> > >
> >
> >
> >

DATABASE= or INITIAL CATALOG=?

I'm setting up a DSN for a new SQL Server install, and had some problems with
an existing Access app. Every query resulted in an "object not found", and
after a little poking about it became clear that it was because the app was
connecting to "master" instead of our database.
The connection string in Access used "Database=[our database name]". Does
this actually work? Or did the app only work in the past because the default
database for that login was set to ours?
Is there a difference between DATABASE (which I inherited) and INITIAL
CATALOG?
Yes, Database=YourDatabase does actually work.
Initial catalog is generally used when connecting with an
OLE DB provider.
Database is generally used when connecting with an ODBC
driver.
The following is a good resource for connection strings:
http://www.able-consulting.com/ADO_Conn.htm
-Sue
On Wed, 24 Nov 2004 07:37:35 -0800, Maury Markowitz
<MauryMarkowitz@.discussions.microsoft.com> wrote:

>I'm setting up a DSN for a new SQL Server install, and had some problems with
>an existing Access app. Every query resulted in an "object not found", and
>after a little poking about it became clear that it was because the app was
>connecting to "master" instead of our database.
>The connection string in Access used "Database=[our database name]". Does
>this actually work? Or did the app only work in the past because the default
>database for that login was set to ours?
>Is there a difference between DATABASE (which I inherited) and INITIAL
>CATALOG?
|||"Sue Hoegemeier" wrote:

> Yes, Database=YourDatabase does actually work.
> Initial catalog is generally used when connecting with an
> OLE DB provider.
Hmmm. That being the case can you suggest any other reason for this problem?
The application logs into the correct server and attaches to master, even
though I say database=mydatabase in the connection string.
|||Using DSNs and connections strings is kind of a waste in my
opinion and can make the issues more convoluted. I have no
idea how the DSN and connection strings are being used but I
would just get rid of the DSN. You introduce maintenance
issues as well as you have your connection string in a DSN
and in code. So I'd eliminate that and just use dsn-less
connections.
Make sure the user exists in the database and can actually
access the database.
-Sue
On Wed, 24 Nov 2004 08:37:04 -0800, Maury Markowitz
<MauryMarkowitz@.discussions.microsoft.com> wrote:

>"Sue Hoegemeier" wrote:
>
>Hmmm. That being the case can you suggest any other reason for this problem?
>The application logs into the correct server and attaches to master, even
>though I say database=mydatabase in the connection string.

DATABASE= or INITIAL CATALOG=?

I'm setting up a DSN for a new SQL Server install, and had some problems wit
h
an existing Access app. Every query resulted in an "object not found", and
after a little poking about it became clear that it was because the app was
connecting to "master" instead of our database.
The connection string in Access used "Database=[our database name]". Doe
s
this actually work? Or did the app only work in the past because the default
database for that login was set to ours?
Is there a difference between DATABASE (which I inherited) and INITIAL
CATALOG?Yes, Database=YourDatabase does actually work.
Initial catalog is generally used when connecting with an
OLE DB provider.
Database is generally used when connecting with an ODBC
driver.
The following is a good resource for connection strings:
http://www.able-consulting.com/ADO_Conn.htm
-Sue
On Wed, 24 Nov 2004 07:37:35 -0800, Maury Markowitz
<MauryMarkowitz@.discussions.microsoft.com> wrote:

>I'm setting up a DSN for a new SQL Server install, and had some problems wi
th
>an existing Access app. Every query resulted in an "object not found", and
>after a little poking about it became clear that it was because the app was
>connecting to "master" instead of our database.
>The connection string in Access used "Database=[our database name]". Do
es
>this actually work? Or did the app only work in the past because the defaul
t
>database for that login was set to ours?
>Is there a difference between DATABASE (which I inherited) and INITIAL
>CATALOG?|||"Sue Hoegemeier" wrote:

> Yes, Database=YourDatabase does actually work.
> Initial catalog is generally used when connecting with an
> OLE DB provider.
Hmmm. That being the case can you suggest any other reason for this problem?
The application logs into the correct server and attaches to master, even
though I say database=mydatabase in the connection string.|||Using DSNs and connections strings is kind of a waste in my
opinion and can make the issues more convoluted. I have no
idea how the DSN and connection strings are being used but I
would just get rid of the DSN. You introduce maintenance
issues as well as you have your connection string in a DSN
and in code. So I'd eliminate that and just use dsn-less
connections.
Make sure the user exists in the database and can actually
access the database.
-Sue
On Wed, 24 Nov 2004 08:37:04 -0800, Maury Markowitz
<MauryMarkowitz@.discussions.microsoft.com> wrote:

>"Sue Hoegemeier" wrote:
>
>Hmmm. That being the case can you suggest any other reason for this problem
?
>The application logs into the correct server and attaches to master, even
>though I say database=mydatabase in the connection string.

Database triggers - SQL Server - Fields only allowed if listed in another field in another

I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).

eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.

Does anyone know the easy way to do this? / Syntax for the trigger?

Why not use PK/FK instead of trigger? I mean you can define the YesNoBlank column as Primary Key in some table, and other columns whose values must exist in the YesNoBlank column as Foreign Key referenceing the YesNoBlank column. For more information about PK/FK, you can take a look at:

Creating and Modifying FOREIGN KEY Constraints

|||

I think Check constraints are my preferance, as I have all of my fields which would potentially be Primary keys in your example in a single reference table (ie multiple columns)

My statement in('Yes','No','') does not work for my check constraint.

Does anyone know what my syntax would be?

|||A quick sample:

CREATE TABLE testConstraint (ID INT, NAME sysname, YESNO VARCHAR(3))

ALTER TABLE testConstraint
ADD CONSTRAINT yesno_check CHECK (UPPER(YESNO) in ('YES','NO'))

--This will succeed
INSERT INTO testConstraint VALUES(1,'Iori','Yes')
--This will fail
INSERT INTO testConstraint VALUES(2,'Kyo','noo')

For mor information, you can refer to:?ALTER?TABLE?