Help with CREATE TRIGGER syntax

Any help would be appreciated. What's wrong with the following syntax?
CREATE TRIGGER tr_CMR_Client_Status_Confirm
ON [CMR Client Numbers]
FOR INSERT, UPDATE
AS
IF UPDATE [Current Status]
CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
Client Number ID] ELSE [Status Flag] = NULL END1. IF UPDATE should have parentheses around the column name - note: if
any row's [Current Status] is updated, this will evaluate to true.
2. not a syntax error, but for clarity, should put a BEGIN..END after the IF
3. [biggest problem] There's no DML statement to do anything, just a
CASE expression...
4. ... which is incorrectly written
Look up UPDATE, CREATE TRIGGER and CASE in BOL
It is quite unclear from the code given what table the [Status Flag]
column belongs to, otherwise I could give an example of possible correct
trigger code.
Please provide DDL of the table.
mike wrote:

>Any help would be appreciated. What's wrong with the following syntax?
>
>CREATE TRIGGER tr_CMR_Client_Status_Confirm
>ON [CMR Client Numbers]
>FOR INSERT, UPDATE
> AS
> IF UPDATE [Current Status]
> CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
>Client Number ID] ELSE [Status Flag] = NULL END
>
>
>
>|||What are you trying to do? It looks like you want to duplicate a value in
another column of your table, which isn't a good thing to do.
Please post DDL and sample data.
David Portas
SQL Server MVP
--sql

Help with a stored procedure

Hi,

Im very new to this posting business so if Im posting in the wrong place or make a faux pa, I apologise

Ive been stuck for a couple of days with the following error, any help would be great!

I created a simple stored procedure which worked fine, then added parameters and it all fell to pieces.

Incorrect syntax near 'StoredProcedure1'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'StoredProcedure1'.

ALTER PROCEDUREdbo.StoredProcedure1

@.MYValuevarchar(50)

AS

BEGIN

SELECT[User].*FROM[User]WHERE[User].UserName = @.MyValueEND

>>>>>>>>>>>>>>>>>>>>>

And I call the procedure here:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand=StoredProcedure1><SelectParameters><asp:SessionParameterName="MYValue"SessionField="name"/></SelectParameters>

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I've tried fiddling with pretty much everything, but nothing has worked yet.

Cheers

Dan

Can you test this:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT[User].*FROM[User]WHERE[User].UserName = @.MyValue" >

<SelectParameters>

<asp:SessionParameterName="MYValue"SessionField="name" type="string"/>

</SelectParameters>

</asp:SqlDataSource>

Or you can test this to see whether your SP works:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="StoredProcedure1" SelectCommandType="StoredProcedure" >

<SelectParameters>

<asp:SessionParameterName="MYValue"SessionField="name" type="string"/>

</SelectParameters>

|||

Whoah, that second suggestion fixed all! Im so stoked,

Cheers mate

Dan

Help using GetColumnInfo()...

Hi, I'm trying to use the following piece of code to obtain the column names from the table in SQL Server, but somehow I'm getting the wrong number of columns in ul_numColumns (see code), and only the first column name in pstr_stringBuffer. Presumably I should be getting a whole string with all the column names there, but I don't. Any suggestions for what I may be doing wrong?..

Thanks in advance

int GetColumnNames(void)

{

try

{

pCommand.CreateInstance(__uuidof (Command));

pCommand->ActiveConnection = pConn;

pCommand->CommandText = "SELECT * FROM t25_pallet_status"; // SQL Syntax...

pRecordset.CreateInstance (__uuidof (Recordset));

pRecordset->CursorLocation = adUseClient;

pRecordset->Open( (IDispatch *) pCommand, vtMissing, adOpenStatic,

adLockReadOnly, adCmdUnknown);

// Get ADORecordsetConstruction interface from the the ADO Recordset

ADORecordsetConstruction *p_adoRecordsetConstruct;

pRecordset->QueryInterface(__uuidof(ADORecordsetConstruction),

(void **)&p_adoRecordsetConstruct);

// From it, we can get the OLEDB <code>IRowset

IRowset *p_rowset;

p_adoRecordsetConstruct->get_Rowset((IUnknown **)&p_rowset);

p_adoRecordsetConstruct->Release(); // don't need it anymore

// The IColumnsInfo that contains ordinals

CComPtr<IColumnsInfo> spColumns;

// Get the the IColumnsInfo from IRowset interface

p_rowset->QueryInterface(&spColumns);

// At this point, we may now release p_rowset

p_rowset->Release();

// IColumnsInfo will give us the DBCOLUMNINFO structure

ULONG ul_numColumns;

DBCOLUMNINFO *p_columnInfo = NULL;

OLECHAR *pstr_stringBuffer = NULL;

// Now get the DBCOLUMNINFO data

spColumns->GetColumnInfo(&ul_numColumns, &p_columnInfo, &pstr_stringBuffer);

// Clean up

CoTaskMemFree(p_columnInfo);

CoTaskMemFree(pstr_stringBuffer);

}

catch(_com_error & ce)

{

PrintComError(ce);

return 0;

}

}

You can do the following to get all the column names

for(ULONG j=0; j<ul_numColumns; j++)

printf("%S\t", p_columnInfo[j].pwszName);

Hope this helps

|||

Thanks a lot Raj,

I kind of sussed it out for my self a short while after I posted the message. I didn't really understand how GetColumnInfo() worked, and I was expecting pstr_stringBuffer to return a string with ALL of the column names (separated by a common delimitter or something). It became apparent to me that I had to loop through and read from a different variable (pwszName) in order to get those names.

I have to say there's very little on examples out there to use functions like this one. Can anyone recomend me a good website where I can get sample code from in C or C++ for the Visual Studio 2003 environment?

Cheers

Help understanding stored procs

I am having trouble understanding how to correctly use stored procs. Here is what I think the process is. Correct me where I am wrong.

I am developing a read-only program with VB 2005 as a front end and SQL Server back end. The user will enter an ID# for search criteria which will be passed as a parameter to a stored proc. I want to create a stored proc that alters a view by changing the ID# the view filters on. A dataset will be created in the front end from that view.

So in SSMS, I create a new proc like this:

CREATE PROC {blah, blah}

@.IDnum

AS

BEGIN

ALTER VIEW {blah, blah}

AS

SELECT {blah, blah}

FROM {blah}

WHERE blah.ID = @.IDnum

END

I would run the code to create the proc, then modify it to an ALTER PROC. I've tried this without success. What am I missing. Yes, I am new to this.

Thanks for the help

Hi,

why do you want to alter a view, rather than using a filter on the view (although the view isn′t that column rich :-) )

So you could create the View as

CREATE VIEW SomeView
AS

SELECT col1, col2 From SomeTable

and then select using

SELECT * FROM SomeView Where Col1 = 'SomeId'

HTH, Jens Suessmeyer

http://www.sqlserver2005.de

|||

Thanks for the reply. I am entering the world of SQL Server from an Access background. In Access I often change the filtering of a query by changing the sql with code or by referencing a field on a form from the criteria field of a query.

That is the thinking with which I have approached views. The purpose of my post is to check my thinking against the experienced community and hopefully get better ideas and understanding. Your reply helps. Thanks again.

|||You′re welcome :-)

Help Setting Up A Linked Server to Query Directory Services

I'm not sure what I'm doing wrong here but I've been banging my head against
for a few hours now.
Background:
SQL 2000 on Windows 2000 Server SP4 as member server in an Active Directory
Domain
MSSQLSVR service and SQL Agent are running as a Domain Administrator
Ive tried to create a linked server with "OLE DB Provider for Microsoft
Directory Services" as the data source. Ive tried this via Query Analylzer
and via Enterprise Manager. The result is the same:
select cn from openquery(AD,'select cn from "LDAP://DC=dfi-intl,dc=com"
where objectCategory="person" and objectClass="users" ')
Gives this error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
I've tried to create it this way via QA:
sp_addlinkedserver 'AD', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
and then in EM, Ive played with all the security stuff but it's still a no
go. I'm completely at my limit with this. Can anyone offer a suggestion?
Thanks in advance.
Errol NealErrol Neal wrote:
> I'm not sure what I'm doing wrong here but I've been banging my head against
> for a few hours now.
> Background:
> SQL 2000 on Windows 2000 Server SP4 as member server in an Active Directory
> Domain
> MSSQLSVR service and SQL Agent are running as a Domain Administrator
> Ive tried to create a linked server with "OLE DB Provider for Microsoft
> Directory Services" as the data source. Ive tried this via Query Analylzer
> and via Enterprise Manager. The result is the same:
> select cn from openquery(AD,'select cn from "LDAP://DC=dfi-intl,dc=com"
> where objectCategory="person" and objectClass="users" ')
> Gives this error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
> returned 0x80040e14].
> I've tried to create it this way via QA:
> sp_addlinkedserver 'AD', 'Active Directory Service
> Interfaces', 'ADSDSOObject', 'adsdatasource'
> and then in EM, Ive played with all the security stuff but it's still a no
> go. I'm completely at my limit with this. Can anyone offer a suggestion?
> Thanks in advance.
> Errol Neal
Hi
I don't know if it helps, but try to alter your OPENQUERY statement a
little bit.
The following works for me and compared to yours I've only specified the
AD servername and not with DC= as you have.
select * from openquery
(
ADSI,'SELECT name, mail, cn
FROM ''LDAP://dc-1''
WHERE objectCategory = ''Person'' AND objectClass = ''user'''
)
Regards
Steen Schlüter Persson
DBA|||Thanks a lot! Syntax was defintely an issue. Using single quotes instead of a
double quote. I appreciate the reply!
"Steen Persson (DK)" wrote:
> Errol Neal wrote:
> > I'm not sure what I'm doing wrong here but I've been banging my head against
> > for a few hours now.
> > Background:
> >
> > SQL 2000 on Windows 2000 Server SP4 as member server in an Active Directory
> > Domain
> > MSSQLSVR service and SQL Agent are running as a Domain Administrator
> >
> > Ive tried to create a linked server with "OLE DB Provider for Microsoft
> > Directory Services" as the data source. Ive tried this via Query Analylzer
> > and via Enterprise Manager. The result is the same:
> >
> > select cn from openquery(AD,'select cn from "LDAP://DC=dfi-intl,dc=com"
> > where objectCategory="person" and objectClass="users" ')
> >
> > Gives this error:
> >
> > Server: Msg 7321, Level 16, State 2, Line 1
> > An error occurred while preparing a query for execution against OLE DB
> > provider 'ADSDSOObject'.
> > OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
> > returned 0x80040e14].
> >
> > I've tried to create it this way via QA:
> >
> > sp_addlinkedserver 'AD', 'Active Directory Service
> > Interfaces', 'ADSDSOObject', 'adsdatasource'
> >
> > and then in EM, Ive played with all the security stuff but it's still a no
> > go. I'm completely at my limit with this. Can anyone offer a suggestion?
> >
> > Thanks in advance.
> >
> > Errol Neal
> Hi
> I don't know if it helps, but try to alter your OPENQUERY statement a
> little bit.
> The following works for me and compared to yours I've only specified the
> AD servername and not with DC= as you have.
> select * from openquery
> (
> ADSI,'SELECT name, mail, cn
> FROM ''LDAP://dc-1''
> WHERE objectCategory = ''Person'' AND objectClass = ''user'''
> )
>
> --
> Regards
> Steen Schlüter Persson
> DBA
>

Help Setting Up A Linked Server to Query Directory Services

I'm not sure what I'm doing wrong here but I've been banging my head against
for a few hours now.
Background:
SQL 2000 on Windows 2000 Server SP4 as member server in an Active Directory
Domain
MSSQLSVR service and SQL Agent are running as a Domain Administrator
Ive tried to create a linked server with "OLE DB Provider for Microsoft
Directory Services" as the data source. Ive tried this via Query Analylzer
and via Enterprise Manager. The result is the same:
select cn from openquery(AD,'select cn from "LDAP://DC=dfi-intl,dc=com"
where objectCategory="person" and objectClass="users" ')
Gives this error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are
returned 0x80040e14].
I've tried to create it this way via QA:
sp_addlinkedserver 'AD', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
and then in EM, Ive played with all the security stuff but it's still a no
go. I'm completely at my limit with this. Can anyone offer a suggestion?
Thanks in advance.
Errol NealErrol Neal wrote:
> I'm not sure what I'm doing wrong here but I've been banging my head again
st
> for a few hours now.
> Background:
> SQL 2000 on Windows 2000 Server SP4 as member server in an Active Director
y
> Domain
> MSSQLSVR service and SQL Agent are running as a Domain Administrator
> Ive tried to create a linked server with "OLE DB Provider for Microsoft
> Directory Services" as the data source. Ive tried this via Query Analylzer
> and via Enterprise Manager. The result is the same:
> select cn from openquery(AD,'select cn from "LDAP://DC=dfi-intl,dc=com"
> where objectCategory="person" and objectClass="users" ')
> Gives this error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Pr
epare
> returned 0x80040e14].
> I've tried to create it this way via QA:
> sp_addlinkedserver 'AD', 'Active Directory Service
> Interfaces', 'ADSDSOObject', 'adsdatasource'
> and then in EM, Ive played with all the security stuff but it's still a no
> go. I'm completely at my limit with this. Can anyone offer a suggestion?
> Thanks in advance.
> Errol Neal
Hi
I don't know if it helps, but try to alter your OPENQUERY statement a
little bit.
The following works for me and compared to yours I've only specified the
AD servername and not with DC= as you have.
select * from openquery
(
ADSI,'SELECT name, mail, cn
FROM ''LDAP://dc-1''
WHERE objectCategory = ''Person'' AND objectClass = ''user'''
)
Regards
Steen Schlüter Persson
DBA|||Thanks a lot! Syntax was defintely an issue. Using single quotes instead of
a
double quote. I appreciate the reply!
"Steen Persson (DK)" wrote:

> Errol Neal wrote:
> Hi
> I don't know if it helps, but try to alter your OPENQUERY statement a
> little bit.
> The following works for me and compared to yours I've only specified the
> AD servername and not with DC= as you have.
> select * from openquery
> (
> ADSI,'SELECT name, mail, cn
> FROM ''LDAP://dc-1''
> WHERE objectCategory = ''Person'' AND objectClass = ''user'''
> )
>
> --
> Regards
> Steen Schlüter Persson
> DBA
>