Can I insert into the same table a new row with the "old" row field value?

I'm trying to insert values into the same database from itself.
Essentially, I want to add another row to the table for each row with
reg_cat_id = 3. But in this row, I want the original registration_id
to show up in the new row.
Here is my syntax below - this generates an error:
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
'05/05/2007')
SELECT REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
FROM Registration_Category t1
WHERE (REG_CAT_ID = 3)
ORDER BY REGISTRATION_ID
Any suggestions?On 30 Mar 2006 14:40:12 -0800, Dee wrote:
(snip)
>Here is my syntax below - this generates an error:
>INSERT INTO Registration_Category
> (REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
>VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
>'05/05/2007')
> SELECT REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
> FROM Registration_Category t1
> WHERE (REG_CAT_ID = 3)
> ORDER BY REGISTRATION_ID
>Any suggestions?
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
SELECT 90, t1.REGISTRATION_ID, 'test',
'20070505', 'Y', '20070505')
FROM Registration_Category AS t1
WHERE REG_CAT_ID = 3
Hugo Kornelis, SQL Server MVP

Can I have SQL server clear a value after a certain amount of time?

I have an application that allows users to "reserve" a certain time and location for practice sessions. I want them to look at a web form that shows which times are available and then allows them to select an open slot and then submit this form with some personal information. My problem is that as soon as they select an open slot, other users should see that this slot is no longer available. On the other hand, if someone selects an open slot and then takes too long to type in their personal information, I want it somehow to kick them out and re-open this slot back up.

I know that I can create a field called 'status' in my database that gets turn to something like 'pending' as soon as the first user clicks on an open slot, but how would I turn this field back to the 'open' state if the user took too long to enter their data?

Any help would be surely appreciated.

Cheers,
AzFyou can store the time the page was requsted in some variable and check against some preset time like 5 min or 10 min...also when do you set the slot as reserved...as soon as the user clicks on the spot or at the end of the page after the user finished entering all the info...? i would suggest doing the latter...

hth

can I get attribute name in SQL XML? thanks

a xml string like

<field name="david" country="ca" age="13" />

in XSL , I can get each attribute name and value by name() and .

So in SQL 2005, if I don't know attribute name in head, how to loop through all attributes and get their name and value?

thanks

declare @.x xml
set @.x = '<field name="david" country="ca" age="13" />'

select x.value('local-name(.)', 'varchar(20)') as Name,
x.value('.', 'varchar(20)') as Value
from @.x.nodes('/field/@.*') as R(x)

Can I do This

Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
AhmedAhmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Sorry,correction
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=C.Code)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahmed,
Try:
select code, name, monthdate, code,
(select sum(amount)
from b
where b.code = a.code
and month(b.date) = a.monthdate) as total
from a;
If you're after applying a table expression querying B and returning a
rowset, to each row from A, there's no set-based way to achieve this in SQL
Server 2000.
You'll have to use itterative logic. SQL Server 2005 solves this by
introducing the APPLY operator, e.g.,
-- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each Store
USE pubs;
SELECT ST.stor_id, CA.*
FROM dbo.Stores AS ST
CROSS APPLY
(SELECT TOP(2) ord_num, title_id, ord_date, qty
FROM dbo.Sales AS SL
WHERE SL.stor_id = ST.stor_id AND qty >= 10
ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on
a value in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON
A.Code=C.Code
thanks in advance
Ahmed|||I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahemd
Yes , you are right , please take a look at Itzik's solution
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=B.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:u5bldm1lFHA.32
88@.TK2MSFTNGP09.phx.gbl...
I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Thanks BG
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OLrJph1lFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Ahmed,
> Try:
> select code, name, monthdate, code,
> (select sum(amount)
> from b
> where b.code = a.code
> and month(b.date) = a.monthdate) as total
> from a;
> If you're after applying a table expression querying B and returning a
> rowset, to each row from A, there's no set-based way to achieve this in
> SQL Server 2000.
> You'll have to use itterative logic. SQL Server 2005 solves this by
> introducing the APPLY operator, e.g.,
> -- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each
> Store
> USE pubs;
> SELECT ST.stor_id, CA.*
> FROM dbo.Stores AS ST
> CROSS APPLY
> (SELECT TOP(2) ord_num, title_id, ord_date, qty
> FROM dbo.Sales AS SL
> WHERE SL.stor_id = ST.stor_id AND qty >= 10
> ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
> news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Dear all
> I need to make a correlated subquery that has the where clause depend
> on a value in the master query, ex
> Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
> I want to perform SQL like this
> SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
> B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C
> ON A.Code=C.Code
>
> thanks in advance
> Ahmed
>

Can I configure a SQLDataSource object to use.....

.. the Membership ProviderUserKey value as the parameter of a stored procedure ?

It seems obvious to me that you would want to do this, but I can only see that it allows you to pass Profile parameters.

You can pass any value as a parameter. It has some built-in parameter types, such as ProfileParameter, FormParameter, ControlParameter etc, but you can just use <asp:Parameter>. Beyond the most basic of usage, the SqlDataSource isn't the most intuitive of controls, I'm afraid. This should help you unpick it a bit though:http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

|||You can always write your own parameter:

1using System;2using System.ComponentModel;3using System.Globalization;4using System.Web;5using System.Web.Security;6using System.Web.UI;7using System.Web.UI.WebControls;89public class MembershipParameter : Parameter10{11public MembershipParameter(string name, TypeCode typeCode,string propertyName) :base(name, typeCode)12 {13this.PropertyName = propertyName;14 }1516public MembershipParameter(string name,string propertyName) :base(name)17 {18this.PropertyName = propertyName;19 }2021public MembershipParameter()22 {23 }2425protected MembershipParameter(MembershipParameter original) :base(original)26 {27if (null == original)throw new ArgumentNullException("original");28this.PropertyName = original.PropertyName;29 }3031public string PropertyName32 {33get34 {35object value = ViewState["PropertyName"];36if (null !=value)return (string)value;37return string.Empty;38 }39set40 {41if (null !=value && 0 ==value.Length)value =null;42if (!string.Equals(value, (string)ViewState["PropertyName"], StringComparison.OrdinalIgnoreCase))43 {44 ViewState["PropertyName"] =value;45 OnParameterChanged();46 }47 }48 }4950protected override Parameter Clone()51 {52return new MembershipParameter(this);53 }5455protected override object Evaluate(HttpContext context, Control control)56 {57object result =null;58string prop =this.PropertyName;59if (!string.IsNullOrEmpty(prop))60 {61 MembershipUser user = Membership.GetUser();62if (null != user)63 {64 result = DataBinder.Eval(user, prop);65 }66 }6768return result;69 }70}

Can datetime type store milliseconds

Hi,

I tried entering this value "8/24/2006 1:35:00.127 PM" with 127 as the milliseconds in a datetime field, but encountered error saying inconsistent datatype ...

Anyone knows how to store datetime value with milliseconds in the SQL database?

Thanks

All --

FYI, I found the issue that was causing me trouble.

It is a simple matter of display in Enterprise Manager.

When viewing data in Enterprise Manager using the GUI by right-clicking on a Table name and choosing "Return all rows" we see the data...

11/09/2006 15:38:14

...but, when we view the same data using Query Analyzer we see the data...

2006-11-09 15:38:13.907

...which is something quite different.

So, the value is stored with milliseconds but the value is NOT displayed in the same way using the GUI table-browser and Query Analyzer.

That was the crux of my issue.

(Imagine my surprise when I found out my code was actually working.)

:-)

Thank you.

-- Mark Kamoski

|||

If you want to store milliseconds you cannot use SmallDateTime because of limited resolution. The link below should take you in the right direction. Post again if you still have questions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187819.aspx

|||

Hi Caddre,

The datatype i am using is datetime and It's doesn't seem to allow me to add milliseconds to it. I am using sql 2000, but i don't think this have to do with SQL version.

|||

There are some rules with DateTime maybe you are doing something wrong so I hsve included the link for DateTime guide in SQL Server read that then try using the DatePart function with MS which means millisecond. Post again if you still have questions.

http://msdn2.microsoft.com/en-us/library/ms186724.aspx
http://www.karaszi.com/SQLServer/info_datetime.asp

|||

thanks,

I managed to store milliseconds

INSERT INTO Table VALUES ({ts '2003-11-05 13:02:43.296'})

|||

The PM was your problem. The format that accepts milliseconds uses a 24-hour clock.

|||

Motley:

The PM was your problem. The format that accepts milliseconds uses a 24-hour clock.

I see that milliseconds can be inserted using SQL; but, what about inserting that value from .NET code into a SQL Server database, using ADO.NET?

I know that a SQL Sever 2000 datetime column can store 3 places of time accuracy, such as 12/30/2006 12:01:03.123, as noted here...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.asp

However, what is not clear is how to get a VS.NET 2003 DateTime variable value to insert into SQL Server datetime column with 3-places of accuracy.

I have tried several things, such as...

DateTime myDateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fffffff"));

...but SQL Server always seems to trim the milliseconds.

What is the way to accomplish this?

Please advise.

Thank you.

-- Mark Kamoski

|||

Caddre:

There are some rules with DateTime maybe you are doing something wrong so I hsve included the link for DateTime guide in SQL Server read that then try using the DatePart function with MS which means millisecond. Post again if you still have questions.

http://msdn2.microsoft.com/en-us/library/ms186724.aspx
http://www.karaszi.com/SQLServer/info_datetime.asp

I see that milliseconds can be inserted using SQL; but, what about inserting that value from .NET code into a SQL Server database, using ADO.NET?

The .NET data type DateTime has milliseconds but after I insert them into SQL Server the datetime column does not contain the milliseconds.

I am using ADO.NET, and OLEDB DataAdapter, and simply call Update passing a DataTable.Therefore, I just set the value in the DataTable as a .NET DateTime datatype with high millisecond precision and then call update. Unfortunately, the milliseconds are trimmed.

How can this be done?

|||

It is in the same area with the link you gave Motley. Read carefully and spend time with Katy kam’s blog she does this stuff for the BCL(base class library) team. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_1ckz.asp

http://blogs.msdn.com/kathykam/archive/2006/09/29/773041.aspx

|||

Mark,

If you tried the first post the link is to the general topic because the urls did not change, I have the SQL Server 2005 version but run a search for the topic in SQL Server 2000 BOL(books online) . Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms190234.aspx

|||

Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)

Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)

cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now

conn.open

cmd.executenonquery

conn.close

|||

Motley:

Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)

Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)

cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now

conn.open

cmd.executenonquery

conn.close

Motley --

OK. that might work.

I appreciate the clarification.

However, now that I think about it, I want to have as much resolution as possible to the date-time value being stored.

As such, if I want to store the full 7-digits to the right of the decimal point, (the resolution that .NET provides), then I think that I need to store the date as an nvarchar in some known format that can easily be parsed. When retrieving such values, I will have to cast from the SqlServer nvarchar to a .NET DateTime. That will make SQL-based comparisons/mining tricky. However, once the data is in-memory, in .NET code, and properly cast back to a DateTime, such comparison/mining will be simple. That seems to be a decent solution, at least in my case. And so on.

If anyone has a better solution, then please post it.

Thank you.

-- Mark Kamoski

|||

Motley:

Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)

Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)

cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now

conn.open

cmd.executenonquery

conn.close

Please help.

When I go to SQL Server 2000 Enterprise Manager, select a table, and choose "Return All Rows", and then try to enter...

01/01/2001 01:01:01.123

...I get the following error...

The value you entered is not consistent with the data type or length of the column, or over grid buffer limit

...which is strange.

I can enter the data...

01/01/2001 01:01:01.123

...but it will NOT allow entry of milliseconds.

Why?

Oddly enough, it will allow...

update ProgramLog set DateTimeLogged = '01/01/2001 01:02:03.456'

...and it enters the milliseconds but (another interesting point) it rounded-up ".456" to ".457".

SideBar -- Note that SQL Server 2000 Books Online says: "datetime - Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table."

Please advise.

Thank you.

-- Mark Kamoski

Can anyone tell me why this returns an empty value?

@.Names is a query string passed in, I need to count the number of records as a result of the below query/

Dim

testAsStringDim sqlConnection3AsNew SqlConnection("data Source=EQ-520-WEB\SQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")Dim cmdAsNew SqlCommand

Dim returnValueAsObject

cmd.CommandText =

"SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @.Names)"

cmd.CommandType = Data.CommandType.Text

cmd.Connection = sqlConnection3

cmd.Parameters.Add(

"@.Names", Data.SqlDbType.NVarChar)

sqlConnection3.Open()

cmd.Parameters(

"@.Names").Value = testIf test =""ThenResponse.Write("An error occured")ExitSubElsereturnValue = cmd.ExecuteScalar()

sqlConnection3.Close()

Label6.Text =

"Number " & returnValue

Hi.

your steps is right but you must to be sure the (test) value that have the correct value.

also you must be sure if there is data in the specific value.for example if you trace that test=7 check if there is data must retrive in the query

SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager =7)

Hopes that help.

Can anyone help me with this? Its giving me a bad headache!

Hi All,

I have a table called Prizes. Here's how it looks in design view with some value placed inside for Illustration purposes.

PrizeID 1, 2, 3, 4, 5
PromotionID 1, 1, 1, 2, 1
PrizeName 10 Cash, 5 cash, 10 cash, 15 cash, 20 cash

My challenge is that I need to write a stored procedure for example, that will find the PrizeID associated with the 4th count of the PromotionID that equals 1 . So in this example, counting to the 4th PromotionID that equalls 1 give us a PrizeID of 5.

I hope I've made myself clear! Can anyone write out a mini SP on how to do this.

Many many thanks in advance,
BradTry creating a temporary table that has an identity column(tempID), a column for PrizeID. Then use an insert statement that selects from Prizes where PromotionID=1 and order it by PrizeID ASC. Then, selecting from your temporary table where tempID=4 (or whatever count you are looking for) will return the corresponding PrizeID. The query to populate the temporary table should look something like this...

DECLARE @.temp TABLE(tempID INT IDENTITY(1,1) PRIMARY KEY,PrizeID INT);

INSERT INTO @.temp (PrizeID) SELECT PrizeID FROM Prizes WHERE PromotionID=1 ORDER BY PrizeID ASC;|||Wow, thank you! I'll give it a try tonight.

Brad

can an INSERT statement RETURN a value?

I only know a little bit of SQL for Access databases, so sorry if this is a silly question!

Situation: I've to INSERT a new company in a SQL server table named 'companies'. This new company will automatically receive a unique ID (autonumber in Access terminology, don't know how to call it in SQL server)

Question: Can this insert statement return the ID it gave to the company? Or how do I get this ID to use it in an other table?

Thanks in advance!create proc <blah-blah> (@.blah1 varchar(blah), @.blah2 varchar(blah-blah) )
as
declare @.RetVal int, @.Error int
begin tran
insert <blah> (blah1, blah2) values (@.blah1, @.blah2)
select @.Error = @.@.error, @.RetVal = scope_identity()
if @.Error != 0 begin
raiserror ('failed to insert into blah', 16, 1)
rollback tran
return 1
end
commit tran
select NewIdentityValue = @.RetVal
return 0

OR, you can define @.RetVal as output parameter, this way you won't have to do a final SELECT. It's all up to your taste and preference.|||...in other words, NO, it can't, but you can put your Insert statement in a procedure that will return a value or an Output parameter.

blindman|||it actually appears that the answer is YES. explanation blindman?|||We're splitting hairs... yes you can get the ID... from the SELECT statement, no... from a stored procedure... yes...

ClipChips asked if the statement could return the ID. The statement itself cannot. But if you use a SP, you can retrieve it either as a SELECT to a recordset, or an OUTPUT parameter.|||It sounds like the answer is really our favorite "yes and no". Yes, @.@.error acts as a sort of return value, but in the strict definition of a return value, you can not have
exec @.retvalue = "insert into table values (...)" Does that explain it better?|||Yes, No?

You can get Id back without stored procedure!

create table test(id int identity primary key
,code varchar(10))
go
create trigger ins_test on test
for insert
as
select id from inserted
go
insert test values('A')
go
id
----
1

Just get recordset from command object...|||True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)|||Originally posted by Seppuku
True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)

But it is possible... ;)|||you can do it even without a trigger, but from a command object.

create table test1 (f1 int identity(1,1) not null, f2 char(1) not null)
go

Just assign the following command to it:
insert test1 (f2) values ('A') select RetVal=@.@.identity -- or scope_identity() for sql2k

Can a value be assigned to an Output parameter and be used in a where clause?

I need to update a table using a stored procedure that "UPSERTS". In this case, @.OfficeId is passed with ParameterDirection.Output

So when I update I assign the value to OfficeId like this (the office id value is being correctly assigned):

SqlParameter OfficeIdParam = new SqlParameter("@.OfficeId", SqlDbType.Int, 4);
if (_officeId > 0) { OfficeIdParam.Value = _officeId; }
OfficeIdParam.Direction = ParameterDirection.Output;

The following segment of the stored procedure is throwing an error that the OfficeId does not exist:

Update -- @.OfficeId int output

if not exists (select 1 from ORG_Office where OfficeId=@.OfficeId)
begin
RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist.
return -1
end

May this approach work, and if the operation turns out to be an Insert I may do this:

select @.error_code = @.@.ERROR, @.OfficeId= scope_identity()

Thank you,

jspurlin

I am thinking you need to add the parameter conditionally, instead of adding it and then setting the direction conditionally. (you might need to fix syntax errors as I dont do .NET code and definetely not in C# )

If (_officeId > 0) {

SqlParameter OfficeIdParam = new SqlParameter("@.OfficeId", SqlDbType.Int, 4);
OfficeIdParam.Direction = ParameterDirection.Output;

}

else {

SqlParameter OfficeIdParam = new SqlParameter("@.OfficeId", SqlDbType.Int, 4);

}

can a textbox hold the value of more than 1 field? maybe use funct

Hello,
My question is if a textbox in a report can contain values from multiple
fields from the data source:
txt1.value
=Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
If this is doable, what is the method/correct method?
I can add multiple fields to one textbox in an MS Access Report. Can this
be done in a Reporting Services Report? I am thinking I could use a function
which would return the concatenated values of these fields as a string. What
would the code for that function look like?
Thanks,
RichI figured out my problem. I added some new fields to my dataset, but not to
the report. Gotta do that for them to compile without complaining.
"Rich" wrote:
> Hello,
> My question is if a textbox in a report can contain values from multiple
> fields from the data source:
> txt1.value
> =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> If this is doable, what is the method/correct method?
> I can add multiple fields to one textbox in an MS Access Report. Can this
> be done in a Reporting Services Report? I am thinking I could use a function
> which would return the concatenated values of these fields as a string. What
> would the code for that function look like?
> Thanks,
> Rich|||You are correct, your format looks correct to me.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
> Hello,
> My question is if a textbox in a report can contain values from multiple
> fields from the data source:
> txt1.value
> =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> If this is doable, what is the method/correct method?
> I can add multiple fields to one textbox in an MS Access Report. Can this
> be done in a Reporting Services Report? I am thinking I could use a
> function
> which would return the concatenated values of these fields as a string.
> What
> would the code for that function look like?
> Thanks,
> Rich|||Thank you. I am still learning. Learn by doing. BTW, if I notice a bug,
who can I report that too?
My actual project is using the reportviewer control that comes with VS2005
(it is almost the same as RS except doesn't require a server - and a few
other things). It works pretty good, but when I select a tractor feeding
printer (one of those older wide paper - dotmatrix like printers) if I tell
the layout to print landscape when using US STD Fanfold paper , the little
icon in the dialog display portrait and it prints portrait. Then if I tell
it Portrait when using the US STD Fanfold papter with tractor feed printer -
the icon displays landscapte and prints landscape. It is pretty obvious that
someone mixed up the options.
So I am not trying to be mr. picky, but when the end user uses my product,
it needs to work according to the standards. Who can I report this too?
Thanks,
Rich
"Steve MunLeeuw" wrote:
> You are correct, your format looks correct to me.
> Steve MunLeeuw
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
> > Hello,
> >
> > My question is if a textbox in a report can contain values from multiple
> > fields from the data source:
> >
> > txt1.value
> >
> > =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> >
> > If this is doable, what is the method/correct method?
> >
> > I can add multiple fields to one textbox in an MS Access Report. Can this
> > be done in a Reporting Services Report? I am thinking I could use a
> > function
> > which would return the concatenated values of these fields as a string.
> > What
> > would the code for that function look like?
> >
> > Thanks,
> > Rich
>
>|||http://connect.microsoft.com/SQLServer/Feedback
Yeah, dealing with different page sizes can be tricky from what I gather.
Luckily I haven't had to deal with that much. Adobe allows you to have
pages in both landscape and portrait in the same document I was asked if I
could do that the other day. I don't think I could.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D2173E9D-E9AF-410D-AFEE-E919AF360951@.microsoft.com...
> Thank you. I am still learning. Learn by doing. BTW, if I notice a bug,
> who can I report that too?
> My actual project is using the reportviewer control that comes with VS2005
> (it is almost the same as RS except doesn't require a server - and a few
> other things). It works pretty good, but when I select a tractor feeding
> printer (one of those older wide paper - dotmatrix like printers) if I
> tell
> the layout to print landscape when using US STD Fanfold paper , the little
> icon in the dialog display portrait and it prints portrait. Then if I
> tell
> it Portrait when using the US STD Fanfold papter with tractor feed
> printer -
> the icon displays landscapte and prints landscape. It is pretty obvious
> that
> someone mixed up the options.
> So I am not trying to be mr. picky, but when the end user uses my product,
> it needs to work according to the standards. Who can I report this too?
> Thanks,
> Rich
> "Steve MunLeeuw" wrote:
>> You are correct, your format looks correct to me.
>> Steve MunLeeuw
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
>> > Hello,
>> >
>> > My question is if a textbox in a report can contain values from
>> > multiple
>> > fields from the data source:
>> >
>> > txt1.value
>> >
>> > =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
>> >
>> > If this is doable, what is the method/correct method?
>> >
>> > I can add multiple fields to one textbox in an MS Access Report. Can
>> > this
>> > be done in a Reporting Services Report? I am thinking I could use a
>> > function
>> > which would return the concatenated values of these fields as a string.
>> > What
>> > would the code for that function look like?
>> >
>> > Thanks,
>> > Rich
>>

Can a script prompt for a value to be inputed?

Hello all,
Is there any way to get a script to prompt for a value (input box or
something else) or to use a value from a variable using SQLCMD?
I have these SELECT scripts that are essentially Crosstab/Pivot queries
where each column is a month of data and is created by a CASE statement.
When updates are done, the CASE statements for the new months need to be
created.
We want to automate this process for a client so they can have one of their
non-DBA employees do the work.
Now I have come up with some Dynamic SQL that will create the same script
and run it based on just a start date and an end date. Start date will be
fixed, the end date will change from update to update. Using the Dynamic
SQL script is alot easier--all we need to do is change one single end date,
rather than adding several CASE statements to the non-Dynamic SQL script.
What I would like to do is create a Dynamic SQL script that will prompt for
an end date to be entered. Now I have figured out how to get SQLCMD to run
a script file and save results to a file, so if I could get the Dynamic SQL
script to accept a variable from SQLCMD to set the end date, that would work
too.
Thanks for any help anyone can provide,
Conan KellyEnd users, dynamic crosstab queries...have you heard of Rac? :)
www.rac4sql.net
We did the dirty work, you take the credit.
www.beyondsql.blogspot.com
Putting logic back in application development|||There's no prompting in the SQL Server tools. But SQLCMD accepts a variable (though the -v switch
and $(varname) literal inside the script file).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:a21Ei.500477$p47.262287@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is there any way to get a script to prompt for a value (input box or something else) or to use a
> value from a variable using SQLCMD?
> I have these SELECT scripts that are essentially Crosstab/Pivot queries where each column is a
> month of data and is created by a CASE statement. When updates are done, the CASE statements for
> the new months need to be created.
> We want to automate this process for a client so they can have one of their non-DBA employees do
> the work.
> Now I have come up with some Dynamic SQL that will create the same script and run it based on just
> a start date and an end date. Start date will be fixed, the end date will change from update to
> update. Using the Dynamic SQL script is alot easier--all we need to do is change one single end
> date, rather than adding several CASE statements to the non-Dynamic SQL script.
> What I would like to do is create a Dynamic SQL script that will prompt for an end date to be
> entered. Now I have figured out how to get SQLCMD to run a script file and save results to a
> file, so if I could get the Dynamic SQL script to accept a variable from SQLCMD to set the end
> date, that would work too.
> Thanks for any help anyone can provide,
> Conan Kelly
>|||Tibor,
Thank you for your help.
That works EXACTLY the way I want it to.
Thanks again,
Conan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23j$uliR8HHA.1168@.TK2MSFTNGP02.phx.gbl...
> There's no prompting in the SQL Server tools. But SQLCMD accepts a
> variable (though the -v switch and $(varname) literal inside the script
> file).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
> news:a21Ei.500477$p47.262287@.bgtnsc04-news.ops.worldnet.att.net...
>> Hello all,
>> Is there any way to get a script to prompt for a value (input box or
>> something else) or to use a value from a variable using SQLCMD?
>> I have these SELECT scripts that are essentially Crosstab/Pivot queries
>> where each column is a month of data and is created by a CASE statement.
>> When updates are done, the CASE statements for the new months need to be
>> created.
>> We want to automate this process for a client so they can have one of
>> their non-DBA employees do the work.
>> Now I have come up with some Dynamic SQL that will create the same script
>> and run it based on just a start date and an end date. Start date will
>> be fixed, the end date will change from update to update. Using the
>> Dynamic SQL script is alot easier--all we need to do is change one single
>> end date, rather than adding several CASE statements to the non-Dynamic
>> SQL script.
>> What I would like to do is create a Dynamic SQL script that will prompt
>> for an end date to be entered. Now I have figured out how to get SQLCMD
>> to run a script file and save results to a file, so if I could get the
>> Dynamic SQL script to accept a variable from SQLCMD to set the end date,
>> that would work too.
>> Thanks for any help anyone can provide,
>> Conan Kelly
>

Can a data region...

reference a parameter? For instance, I have a table control and in the detail field I have an expression.

=IIf(Parameters!Consignee.Value = True, "True","False")

But it doens't work.....any suggestions?

Michael

That should work - what happens when you try it? Did you make the data type Boolean?|||

I get "#Error" in the field.

All I did to get around this was to make a derived table in my main query that filters for the specific parameter, and then joined it back in appropriately. This made it a field instead of a parameter reference and it worked fine. Thanks for the response.

Michael

Can a ControlParameter be used to supply a parameter for a stored procedure?

The code below is an attempt at using the value from a dropdownlist to feed into stored procedure outlined in the Select command. I have seen examples where the control parameter is used with a select command but nothing where the parameter has to be fed into a stored procedure.

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString=".."
SelectCommand="procCAGetCustomerKPIs"SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameterName="iCustomerGroupId"ControlID="CustomerFilterList"PropertyName="SelectedValue"/>
</SelectParameters>
</asp:SqlDataSource>

cheers-jim.

Yes. Normally, I would use the designer, and hit "refresh parameters", it will then generate a list of all the parameters the stored procedure is expecting. From that point, change the parameters from type "None" to "Control", etc.|||Forgot to add autoPostBack=true to the dropdownlist control!!

can a column be named as a variable?

i have declared a variable...lets say @.month
can i have a column named as value of this variable..ie any value this variable returns when executed?
let me know asap
regards
Nikhil
refer to this url:
http://www.algonet.se/~sommar/dynamic_sql.html
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Can a CASE Staement be used in the WHERE clause

I have myself and need some help clarifing how to weite the
following statement so that 3 difierent results can come from the value of
the passed Parameter. The parameter can have 0,1 or 2 as the values
What I'm trying to get back is the records that are automated if the value
is 1 or the records if the value is manual 0 other wise return both types of
records
SELECT Info.ID,
Info.Automated
FROM Info
WHERE (Info.Automated LIKE @.Auto))
Can a case statement be used in the where clause?
I want to change the Like to = if possible.
Thanks for the helpJim Abel (JimAbel@.discussions.microsoft.com) writes:
> I have myself and need some help clarifing how to weite the
> following statement so that 3 difierent results can come from the value
> of the passed Parameter. The parameter can have 0,1 or 2 as the values
> What I'm trying to get back is the records that are automated if the
> value is 1 or the records if the value is manual 0 other wise return
> both types of records
> SELECT Info.ID,
> Info.Automated
> FROM Info
> WHERE (Info.Automated LIKE @.Auto))
> Can a case statement be used in the where clause?
> I want to change the Like to = if possible.
There is no CASE statement in T-SQL, but there is a CASE expression. And,
yes, it can be used in WHERE clauses. I'm not sure that I understood
exactly what you wanted to do, but here is an example:
SELECT ..
FROM Info
WHERE Automated LIKE CASE WHEN @.param = 1 THEN @.Auto
WHEN @.param = 2 THEN @.Bil
WHEN @.param = 3 THEN @.Macchina
END
You find more information in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks this is helping. Let me see know if I can get you to understand what
I'm trying to achive with the query because I'm not sure if a CASE expressio
n
is the right thing to do.
SELECT Info.ID, Info.Automated
FROM Info
WHERE (info.Automated = CASE
WHEN @.Auto = 0 THEN
audPolicyDetail.Auyomated = 0
WHEN @.Auto = 1 THEN
audPolicyDetail.Auyomated = 1
WHEN @.Auto = 2 THEN
audPolicyDetail.Auyomated in(0,1)))
END
"Erland Sommarskog" wrote:

> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> There is no CASE statement in T-SQL, but there is a CASE expression. And,
> yes, it can be used in WHERE clauses. I'm not sure that I understood
> exactly what you wanted to do, but here is an example:
> SELECT ..
> FROM Info
> WHERE Automated LIKE CASE WHEN @.param = 1 THEN @.Auto
> WHEN @.param = 2 THEN @.Bil
> WHEN @.param = 3 THEN @.Macchina
> END
> You find more information in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Thanks this is helping. Let me see know if I can get you to understand
> what I'm trying to achive with the query because I'm not sure if a CASE
> expression is the right thing to do.
> SELECT Info.ID, Info.Automated
> FROM Info
> WHERE (info.Automated = CASE
> WHEN @.Auto = 0 THEN
> audPolicyDetail.Auyomated = 0
> WHEN @.Auto = 1 THEN
> audPolicyDetail.Auyomated = 1
> WHEN @.Auto = 2 THEN
> audPolicyDetail.Auyomated in(0,1)))
> END
I don't know what you are trying to achieve, but the above is not
correct. A CASE expression must return one of the data types in SQL Server,
and boolean is not a data type in SQL Server.
If also looks funny with audPolicyDetail, as there is no such table in
the query. Is this a CLR UDT column?
The general recommendation for query problems is that you post:
o CREATE TABLE statement(s) for your table(s).
o INSERT statements with sample data.
o The desired result given the sample.
Including this sort of information often helps to clarify what you are
looking for, and reduces the amount of guessing needed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You missed one of the fundamental principels of programming, not just
SQL.
SQL has a CASE **expression** and not a CASE **statement**; expressions
return a single value of a known data type. It does not change the
control flow and in fact since SQL is a declarative language, there is
no control flow concept.
Did you mean something like this? A bit weird, but valid:
SELECT I.vague_id, automated_status
FROM Info AS I. AudPolicyDetail AS A
WHERE A.automated_status
= CASE @.auto
WHEN 0 THEN 0
WHEN 1 THEN 1
ELSE I.automated_status END;|||Ok, Ill try once more then we can drop it. I have a page that passed the
@.Auto parameter. On the page the parameter can have one of three options
(All, True or False). In the SQL Statement I think of these options as 3
different possible WHERE conditions as shown below.
1) WHERE Automated = 0 -- Returns records that are not automated
2) WHERE Automated = 1 -- Returns records that are automated
3) -- does not use the Automated field -- returns all the records
It is in the 3rd option that I get stuck with the syntax of the statement
how do I write a WHERE clause that can both use the Automated field and then
not use it if all records are to be returned?
I appreciate your patients with me on this.
Thanks for all the help
"Erland Sommarskog" wrote:

> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> I don't know what you are trying to achieve, but the above is not
> correct. A CASE expression must return one of the data types in SQL Server
,
> and boolean is not a data type in SQL Server.
> If also looks funny with audPolicyDetail, as there is no such table in
> the query. Is this a CLR UDT column?
> The general recommendation for query problems is that you post:
> o CREATE TABLE statement(s) for your table(s).
> o INSERT statements with sample data.
> o The desired result given the sample.
> Including this sort of information often helps to clarify what you are
> looking for, and reduces the amount of guessing needed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Ok, Ill try once more then we can drop it. I have a page that passed the
> @.Auto parameter. On the page the parameter can have one of three options
> (All, True or False). In the SQL Statement I think of these options as 3
> different possible WHERE conditions as shown below.
> 1) WHERE Automated = 0 -- Returns records that are not automated
> 2) WHERE Automated = 1 -- Returns records that are automated
> 3) -- does not use the Automated field -- returns all the records
> It is in the 3rd option that I get stuck with the syntax of the
> statement how do I write a WHERE clause that can both use the Automated
> field and then not use it if all records are to be returned?
The most common way to write it would be
WHERE (Automated = @.Auto OR @.Auto IS NULL)
But if you prefer to pass 2 meaning "don't matter, this works too:
WHERE (Automated = @.Auto OR @.Auto = 2)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Calumn autocomplete

Hello.
It is possible to set autocomplete(?) for table column. For example I have column nvarchar(10) and I insert to this field value 'test' witch is 4 chars long. If I select, I select 'test' - but I need for example 'aaaaaatest'. Is it possible on sql2005?

Best regards,
Radek

Hi,

Just give you a suggestion, maybe not the easiest one. Caculate the length of the value which gets from database, make a compare with the length of nvarchar and deside how many extra chars should be added.

string value;// The value you stored in databasestring extra_char_value="";// The extra-valueint nvarchar_length=10;// The length of the data fieldint extra_char_length = nvarchar_length -value.Length;for (int i = 0; i < extra_char_length; i++) { extra_char_value +="a"; }value += extra_char_value;

Thanks.

Calling User Defined Function in VC++ using SQLDMO

Hi all,
I had a user defined function by name dbo.GetContact (dbo is
owner),the function contains a select statement and returns a value and
i am using this function in a View. when i calling this function in
Query Analyzer it is working fine... but when i call this i use View
programtically,it is telling that "InValid Object dbo.GetContact".
View :
select dbo.getcontact('Address', '_332', 'Email')
Can anyone provide me the solution...
Looking forward for ur reply..
Thanz in advance...
Regards,
PrinceHi
Are you calling it in the view the same way or as part of a select statement
against a table?
Please post DDL and DML.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Prince" wrote:

> Hi all,
> I had a user defined function by name dbo.GetContact (dbo is
> owner),the function contains a select statement and returns a value and
> i am using this function in a View. when i calling this function in
> Query Analyzer it is working fine... but when i call this i use View
> programtically,it is telling that "InValid Object dbo.GetContact".
> View :
> select dbo.getcontact('Address', '_332', 'Email')
> Can anyone provide me the solution...
> Looking forward for ur reply..
> Thanz in advance...
> Regards,
> Prince
>|||hai mike..
i am calling it in a view like this :
pDatabase->ExecuteWithResults("that view",&pResults))
Mike Epprecht (SQL MVP) wrote:
> Hi
> Are you calling it in the view the same way or as part of a select stateme
nt
> against a table?
> Please post DDL and DML.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Prince" wrote:
>|||On 23 Aug 2005 02:48:47 -0700, Prince wrote:

>Hi all,
> I had a user defined function by name dbo.GetContact (dbo is
>owner),the function contains a select statement and returns a value and
>i am using this function in a View. when i calling this function in
>Query Analyzer it is working fine... but when i call this i use View
>programtically,it is telling that "InValid Object dbo.GetContact".
>View :
> select dbo.getcontact('Address', '_332', 'Email')
Hi Prince,
Try:
SELECT Column1, Column2, ...
FROM dbo.getcontact('Address', '_332', 'Email')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Prince, Please try this Visual C++ code. It runs okay for me;
_ConnectionPtr pConn = NULL;
_bstr_t strCon("Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=;Initial Catalog=Enterprise;Data Source=(local)");
HRESULT hr = S_OK;
CoInitialize(NULL);
try
{
hr = pConn.CreateInstance((__uuidof(Connection)));
if (FAILED(hr))
{
cout << "Error instantiating Connection object" << endl;
CoUninitialize();
return 0;
}
//Open the SQL Server connection
hr = pConn->Open(strCon,"","",0);
if (FAILED(hr))
{
cout << "Error opening Database Object using ADO _ConnectionPtr" << endl;
CoUninitialize();
return 0;
}
}
catch (_com_error& ce)
{
cout << "Error= " << ce.ErrorInfo << endl;
}
_RecordsetPtr pRst = NULL;
pRst.CreateInstance(__uuidof(Recordset));
_bstr_t strSQL("select dbo.DBCreationDate('Enterprise')");
VARIANT* ptr = NULL;
IADORecordBinding *picRs = NULL; // Interface Pointer declared
CCustomRs rs;
try {
pRst->Open(strSQL, variant_t((IDispatch *)pConn,true), adOpenStatic,
adLockOptimistic, adCmdText);
if(SUCCEEDED(pRst-> QueryInterface(__uuidof(IADORecordBindin
g), (void
**)&picRs)))
{
TESTHR(picRs->BindToRecordset(&rs));
}
}
catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
// Print Com errors.
cout << "Error" <<endl;
cout << "Code = " << e.Error();
cout << "Code meaning = " <<
e.ErrorMessage();
cout << "Source = " << (LPCSTR) bstrSource;
cout << "Description = " << (LPCSTR)
bstrDescription;
}
Please email me at frankchang91@.gmail.com if you have any questions.
"Prince" wrote:

> Hi all,
> I had a user defined function by name dbo.GetContact (dbo is
> owner),the function contains a select statement and returns a value and
> i am using this function in a View. when i calling this function in
> Query Analyzer it is working fine... but when i call this i use View
> programtically,it is telling that "InValid Object dbo.GetContact".
> View :
> select dbo.getcontact('Address', '_332', 'Email')
> Can anyone provide me the solution...
> Looking forward for ur reply..
> Thanz in advance...
> Regards,
> Prince
>|||Prince, I use your user defined function in a view. When i execute the view
programmatically using the SQLOLEDB COM object , it still runs okay. Do you
have to use the SQLDMO COM object or can you switch to the SQLOLEDB COM
object? Thank you.
"frank chang" wrote:
> Prince, Please try this Visual C++ code. It runs okay for me;
> _ConnectionPtr pConn = NULL;
> _bstr_t strCon("Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=Enterprise;Data Source=(local)");
> HRESULT hr = S_OK;
> CoInitialize(NULL);
> try
> {
> hr = pConn.CreateInstance((__uuidof(Connection)));
> if (FAILED(hr))
> {
> cout << "Error instantiating Connection object" << endl;
> CoUninitialize();
> return 0;
> }
> //Open the SQL Server connection
> hr = pConn->Open(strCon,"","",0);
> if (FAILED(hr))
> {
> cout << "Error opening Database Object using ADO _ConnectionPtr" << end
l;
> CoUninitialize();
> return 0;
> }
> }
> catch (_com_error& ce)
> {
> cout << "Error= " << ce.ErrorInfo << endl;
> }
>
> _RecordsetPtr pRst = NULL;
> pRst.CreateInstance(__uuidof(Recordset));
>
> _bstr_t strSQL("select dbo.DBCreationDate('Enterprise')");
> VARIANT* ptr = NULL;
> IADORecordBinding *picRs = NULL; // Interface Pointer declared
> CCustomRs rs;
> try {
> pRst->Open(strSQL, variant_t((IDispatch *)pConn,true), adOpenStatic,
> adLockOptimistic, adCmdText);
> if(SUCCEEDED(pRst-> QueryInterface(__uuidof(IADORecordBindin
g), (void
> **)&picRs)))
> {
> TESTHR(picRs->BindToRecordset(&rs));
> }
> }
> catch(_com_error &e)
> {
> _bstr_t bstrSource(e.Source());
> _bstr_t bstrDescription(e.Description());
> // Print Com errors.
> cout << "Error" <<endl;
> cout << "Code = " << e.Error();
> cout << "Code meaning = " <<
> e.ErrorMessage();
> cout << "Source = " << (LPCSTR) bstrSource;
> cout << "Description = " << (LPCSTR)
> bstrDescription;
> }
> Please email me at frankchang91@.gmail.com if you have any questions.
> "Prince" wrote:
>