creating a view

I'm trying to create a view to check another view for a record that matches to the value in a textbox of a form. It is giving a syntax error.
This is how my SQL looks like

SELECT aql_level, aql_level_info
FROM v_tblPart_cost_info
WHERE cust_part_num = forms!edit_shiping_sched!cust_part_num

Thanks!!What was the error?
Check books online for correct syntax=.

Creating a User Defined Aggregate Function

Does SQL Server allow the user to create user defined aggregate functions?
If so:
What doed the syntax look like?
Where can I find more information on creating user defined aggregate
functions?Sorry, I found the answer.
"Charles" wrote:

> Does SQL Server allow the user to create user defined aggregate functions?
> If so:
> What doed the syntax look like?
> Where can I find more information on creating user defined aggregate
> functions?
>|||In SQL 2005 user-defined functions can be created as CLR functions.
http://msdn2.microsoft.com/en-us/library/ms131051.aspx
ML
http://milambda.blogspot.com/
"Charles" wrote:

> Does SQL Server allow the user to create user defined aggregate functions?
> If so:
> What doed the syntax look like?
> Where can I find more information on creating user defined aggregate
> functions?
>

Creating a table inside a stored procedure

I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:

Error 208: Invalid object name '##OPTIONSEX'

I am using the following SQL script:

CREATE PROCEDURE [dbo].[Test2] AS

CREATE TABLE ##OPTIONSEX
(
OPTION_PLAN VARCHAR(50),
TOT_OPTIONS_EXCHANGED FLOAT NULL
)

GO

INSERT ##OPTIONSEX

SELECT
B.COMPONENT,
TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
WHERE B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

GO

Any help getting this to run correctly would be appreciated.Is that like the type of sex you want...is this mail order?

Anyway, you need to take out the GO...that's a scope terminator

And are you sure you want a GLOBAL Temp table instead of a local one?

Try this

CREATE PROCEDURE [dbo].[Test2]
AS
BEGIN
CREATE TABLE ##OPTIONSEX (
OPTION_PLAN VARCHAR(50)
, TOT_OPTIONS_EXCHANGED FLOAT NULL
)

INSERT ##OPTIONSEX
SELECT B.COMPONENT
, TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A
INNER JOIN TBLCOMPONENT B
ON B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

DROP TABLE ##OPTIONSEX
GO|||Thanks for the reply. Worked fine only after I removed the BEGIN line. For some reason using BEGIN returned a syntax error.

Thanks again Brett.|||dooooooh

I forgot the END

BEGIN
.....some code
END

And you'll need those constructs if you do an control of flow logic

IF some condition
BEGIN
.........some code line 1
.........some code line 2
END

WHILE some Cond
BEGIN
.........some code line 1
.........some code line 2
END

Good luck

creating a table

I have used this syntax to create a table
create emp(emp_id int,staff_Name varchar(10),det_Name varchar(20));
This creates a table but I cannot insert any data in the table.How can i insert data in the table.insert into emp values(100,'sridhar','programmer')

CreateMHTMLBody retuns "Invalid syntax" error

Hi,

I want to add html as body of my mail message. I added CreateMHTMLBody method in my SQL code.

Syntax I used is

Exec @.hr=sp_oamethod @.imsg,'CreateMHTMLBody',NULL,@.MsgFile,0,'',''

This syntax is working fine on my system but whem I implement this on production it gives error.

Needed ASAP as I have to implmenet this on my production server today.

Thanks

GAurav

What is the error message you are getting|||are you running the same version of cdosys.dll on dev and prod?|||

Why use CDO when you have sql database mail.

CreateMHTMLBody expects 4 parameters (url, cdomhtmlflags, user, pwd) so check your @.MsgFile.

|||Check the required COM DLLs are registered properly on your server.|||

Using SQL database mail you can't send the HTML mails where message body is created from the HTML file

And my syntax is correct as it is working perfectly fine on my development env. but it is not working on production

|||

Yes COM dll is properly registerde and it is sending Textbody mail. But when I use this CreateMHTMLBoby it gives me above error. And my code and syntax is also correct as it is working fine on development env.

Thanks

Create View Syntax

Why can't one use a three part naming convention when creating a view?

example:

CREATE VIEW databasename.schemaname.anyview

AS

SELECT * FROM anytable

The following error is returned:

Msg 166, Level 15, State 1, Line 1

'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

You are creating the VIEW in the current database, therefore the three part name is not allowed since that would be creating a view in a different database.

You can, however, use the three part name for the table(s) used in the VIEW definition.

Create View in SQL Server with data types

I need to create a view of a sql table, but change the data types. I know
the syntax below is not correct, and can't figure out if it is wrong or if
you just can't do this. I have only created views before with the same data
type.

CREATE VIEW F0005New(DRKY nchar(3), DRDL01 nchar(30))
INSERT (SELECT rtrim(F0005.DRKY), F0005.DRDL01
FROM F0005 AS F0005
WHERE DRSY = '41' AND DRRT = 'S1')

Thanks!!Aren't those JD Edwards column names a drag? I'm surprised you need
UNICODE (nchar and nvarchar instead of char and varchar), I did not
realize that JDE could handle anything beyond EBCDIC or ASCII.

A VIEW is just a SELECT statement inside. The example has an INSERT
statement. A VIEW can NOT have an INSERT statement.

CREATE VIEW does not allow for specifying the data types in the VIEW
column list. It is not part of the syntax.

When I need to control the data type this way I use CONVERT, though
CASE can do the same thing:

CREATE VIEW F0005New
AS
SELECT CONVERT(nhcar(3), rtrim(F0005.DRKY)) as DRKY,
CONVERT(nchar(30), F0005.DRDL01) as DRDL01
FROM F0005
WHERE DRSY = '41'
AND DRRT = 'S1'

Roy Harvey
Beacon Falls, CT

On Mon, 14 Aug 2006 15:58:01 GMT, "cognosqueen" <u25284@.uwewrote:

Quote:

Originally Posted by

>I need to create a view of a sql table, but change the data types. I know
>the syntax below is not correct, and can't figure out if it is wrong or if
>you just can't do this. I have only created views before with the same data
>type.
>
>CREATE VIEW F0005New(DRKY nchar(3), DRDL01 nchar(30))
>INSERT (SELECT rtrim(F0005.DRKY), F0005.DRDL01
>FROM F0005 AS F0005
>WHERE DRSY = '41' AND DRRT = 'S1')
>
>Thanks!!

|||Roy - thanks so much!!! You are right about the JE Edwards column names!! I
do not enjoy them at all. Regarding the data type - the client already had
it set up - I just have to live with it!! I'll try this and see if it works!

Roy Harvey wrote:

Quote:

Originally Posted by

>Aren't those JD Edwards column names a drag? I'm surprised you need
>UNICODE (nchar and nvarchar instead of char and varchar), I did not
>realize that JDE could handle anything beyond EBCDIC or ASCII.
>
>A VIEW is just a SELECT statement inside. The example has an INSERT
>statement. A VIEW can NOT have an INSERT statement.
>
>CREATE VIEW does not allow for specifying the data types in the VIEW
>column list. It is not part of the syntax.
>
>When I need to control the data type this way I use CONVERT, though
>CASE can do the same thing:
>
>CREATE VIEW F0005New
>AS
>SELECT CONVERT(nhcar(3), rtrim(F0005.DRKY)) as DRKY,
CONVERT(nchar(30), F0005.DRDL01) as DRDL01
FROM F0005
WHERE DRSY = '41'
AND DRRT = 'S1'
>
>Roy Harvey
>Beacon Falls, CT
>

Quote:

Originally Posted by

>>I need to create a view of a sql table, but change the data types. I know
>>the syntax below is not correct, and can't figure out if it is wrong or if

>[quoted text clipped - 7 lines]

Quote:

Originally Posted by

>>
>>Thanks!!

|||Roy - it worked! Thanks for your help.

cognosqueen wrote:

Quote:

Originally Posted by

>Roy - thanks so much!!! You are right about the JE Edwards column names!! I
>do not enjoy them at all. Regarding the data type - the client already had
>it set up - I just have to live with it!! I'll try this and see if it works!
>

Quote:

Originally Posted by

>>Aren't those JD Edwards column names a drag? I'm surprised you need
>>UNICODE (nchar and nvarchar instead of char and varchar), I did not

>[quoted text clipped - 25 lines]

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>
>>>Thanks!!

|||You are welcome, and good luck! My experience with importing JD
Edwards data into a SQL Server data warehouse was quite painful, not
because of F0005 which in our case was quite clean, but the invoice
data that came in lacked any unique key. I hope your life is simpler
than mine was.

Roy

On Mon, 14 Aug 2006 18:37:37 GMT, "cognosqueen" <u25284@.uwewrote:

Quote:

Originally Posted by

>Roy - it worked! Thanks for your help.
>
>cognosqueen wrote:

Quote:

Originally Posted by

>>Roy - thanks so much!!! You are right about the JE Edwards column names!! I
>>do not enjoy them at all. Regarding the data type - the client already had
>>it set up - I just have to live with it!! I'll try this and see if it works!
>>

Quote:

Originally Posted by

>>>Aren't those JD Edwards column names a drag? I'm surprised you need
>>>UNICODE (nchar and nvarchar instead of char and varchar), I did not

>>[quoted text clipped - 25 lines]

Quote:

Originally Posted by

>>>>
>>>>Thanks!!