Re: Calling functions in stored procedures - Database Discussion Boards

First Prev Next

Re: Restore SQl DB On Machine Problem...

Rob Graham1-Feb-05 5:48

Rob Graham

1-Feb-05 5:48

If the database is smaller than 2GB you could use MSDE[^]
and restore it using the command line OSQL utility.

Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg

Re: Restore SQl DB On Machine Problem...

Just Greeky Creek2-Feb-05 22:04

Just Greeky Creek

2-Feb-05 22:04

u mean, u try to give access to any client to MS SQL Server. If so u have to install MS SQL Agent, (Desktop Client Version it is available in MS SQL SERVER CD)

if u dont, is what ,u wanna do, to restore a MS SQL Database to a new Server which doesnt have any licence MS SQL Server??? If so, u DONT.

Hangs when attempting to close the Recordset object

CodingFrenzy1-Feb-05 3:06

CodingFrenzy

1-Feb-05 3:06

I have a VC6 project that access' a visual fox pro database via the use of ADO. When I run the project in DEBUG mode everything works fine. When I run it in RELEASED mode on a development machine it also works fine. But when I try running it on a test machine it blows up.
There are multiple tables that I read in. I connect, create a Recordset, iterate through the DB (reading the field values as I go along), close the Recordset, close the connection, and then repeat the process for all other tables. Each table that gets imported has it's own method.
The results are weird as well. The first table will come in fine with no errors or exceptions.
Usually it will hang in the DisconnectADO() method that gets called at the end of the ImportSecondDB() method. But sometimes an exception is thrown that gets caught in the
catch(...). I'm not really sure what's going on here and have already lost close to a week's worth of development time on this. I have provided some code "snipets" bellow. Any help, comments, or suggestions will be most welcomed and greatly appreciated.

HRESULT ClassName::Run()
{
HRESULT hr = S_OK;

for (int i=0; i<2; ++i)
{
switch(i)
{
case 0:
hr = ImportFirstDB();
break;

case 1:
hr = ImportSecondDB();
break;

default:
ASSERT(FALSE);
hr = E_FAIL;
}
}
}

HRESULT ClassName::ImportFirstDB()
{
ado::_ConnectionPtr spConnection;
ado::_RecordsetPtr spRecSet;
CString ostr;
HRESULT hr = S_OK;
_variant_t ovField;

try
{
if (SUCCEEDED(hr = ConnectADO(spConnection, spRecSet)))
{
if (SUCCEEDED(hr = spRecSet->Open(L"SELECT * FROM daily", _variant_t((IDispatch*)spConnection, true), ado::adOpenStatic,ado::adLockReadOnly, ado::adOptionUnspecified)))
{
if (SUCCEEDED(hr = spRecSet->MoveFirst()))
{
while(SUCCEEDED(hr) && !spRecSet->GetadoEOF())
{
ovField = spRecSet->GetCollect("col name");
if (ovField.vt != VT_NULL)
{
ostr = (_bstr_t)ovField;
}
hr = spRecSet->MoveNext();
}
}
}
}
}
catch(_com_error& oE)
{
hr = ProcessError(&oE);
}
catch(...)
{
hr = ProcessConnectionError(spConnection);
}

DisconnectADO(spConnection, spRecSet);
return hr;
}

HRESULT ClassName::ImportSecondDB()
{
ado::_ConnectionPtr spConnection;
ado::_RecordsetPtr spRecSet;
CString ostr;
HRESULT hr = S_OK;
_variant_t ovField;

try
{
if (SUCCEEDED(hr = ConnectADO(spConnection, spRecSet)))
{
if (SUCCEEDED(hr = spRecSet->Open(L"SELECT * FROM daily", _variant_t((IDispatch*)spConnection, true), ado::adOpenStatic,ado::adLockReadOnly, ado::adOptionUnspecified)))
{
if (SUCCEEDED(hr = spRecSet->MoveFirst()))
{
while(SUCCEEDED(hr) && !spRecSet->GetadoEOF())
{
ovField = spRecSet->GetCollect("col name");
if (ovField.vt != VT_NULL)
{
ostr = (_bstr_t)ovField;
}
hr = spRecSet->MoveNext();
}
}
}
}
}
catch(_com_error& oE)
{
hr = ProcessError(&oE);
}
catch(...)
{
hr = ProcessConnectionError(spConnection);
}

DisconnectADO(spConnection, spRecSet);
return hr;
}

HRESULT ClassName::ConnectADO(ado::_ConnectionPtr& spConnection, ado::_RecordsetPtr& spRecSet)
{
CString ostrConnection;
HRESULT hr = S_OK;

try
{
if (SUCCEEDED(hr = spConnection.CreateInstance(__uuidof(ado::Connection))))
{
ostrConnection = _T("Provider=VFPOLEDB.1;");
ostrConnection += _T("Data Source=");
ostrConnection += ostrInputFile + _T(";");

if (SUCCEEDED(hr = spConnection->Open(_bstr_t(ostrConnection), L"", L"", ado::adModeUnknown)))
{
spRecSet.CreateInstance(__uuidof(ado::Recordset));
spRecSet->CursorLocation = ado::adUseClient;
}
else
{
_com_issue_error(hr);
}
}
catch(_com_error &oE)
{
hr = ProcessError(&oE, _T("AccountView_WINTask::ConnectADO() - InputFile: ") + ostrInputFile);
}
catch(...)
{
TRACE( "*** Unhandled Exception ***" );
hr = E_FAIL;
}

return hr;
}

HRESULT ClassName::DisconnectADO(ado::_ConnectionPtr& spConnection, ado::_RecordsetPtr& spRecSet)
{
ASSERT(spConnection);
ASSERT(spRecSet);
HRESULT hr = S_OK;

try
{
if (spRecSet)
{
if (spRecSet->State == ado::adStateOpen)
{
hr = spRecSet->Close();
}
}

if (spConnection)
{
if (spConnection->State == ado::adStateOpen)
{
hr = spConnection->Close();
}
}

spRecSet.Release();
spConnection.Release();
}
catch(_com_error& oE)
{
hr = ProcessError(&oE);
}
catch(...)
{
hr = ProcessConnectionError(spConnection);
}

return hr;
}

Re: Hangs when attempting to close the Recordset object

Rob Graham1-Feb-05 5:56

Rob Graham

1-Feb-05 5:56

Are you sure you wont get an empty recordset for any of the tables?
calling MoveFirst() on an empty recordset should throw an exception...
as will calling MoveNext when you have reached the end of the recordset.
If I recall correctly, ADo returns the recodset positioned on the first record if there are any, so the movefirst should not be needed, and your while loop should chech that you have not reached EOF. If the recordset is empty, EOF will already be true, as will BOF.

Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg

Calling functions in stored procedures

Majid Shahabfar1-Feb-05 2:29

Majid Shahabfar

1-Feb-05 2:29

Hi There,
I've written an inline table-valued function in SQL such as the following:

ALTER FUNCTION dbo.GetCityByID( @CityID int)<br />
RETURNS TABLE<br />
AS<br />
  RETURN(<br />
         SELECT<br />
               Name,<br />
               Url<br />
           FROM Cities<br />
          WHERE (CityID = @CityID) )<br />

suppose that Cities table includes three fields (CityID, Name, Url).

By the way I wrote a store procedure as follow:

ALTER PROCEDURE MyProcedure ( @MyID int)<br />
AS<br />
   SELECT<br />
         CountryID,<br />
         OriginCityID,<br />
         DestCityID<br />
   FROM<br />
         MyTable<br />
   WHERE (MyID = @MyID)<br />

The OriginCityID and DestCityID are related to CityID in Cities table. I wanna get the name
and url of each city by its ID through this stored procedue by making relation to Cities table.
so I call GetCityByID function in my stored procedure like this:

ALTER PROCEDURE MyProcedure ( @MyID int)<br />
AS<br />
   SELECT<br />
         CountryID,<br />
         dbo.GetCityByID(OriginCityID),<br />
         dbo.GetCityByID(DestCityID)<br />
   FROM<br />
         MyTable<br />
   WHERE (MyID = @MyID)<br />

this procedure dosn't work an returns error.

What's your solution for getting information from Cities table for OriginCityID and DestCityID?
Thank you in advance.

Re: Calling functions in stored procedures

Colin Angus Mackay1-Feb-05 3:28

Colin Angus Mackay

1-Feb-05 3:28

Majid Shahabfar wrote:
I've written an inline table-valued function

That is your answer. If it return a table, you must treat it as a table.

Since it looks like this should only ever return one row, why not re-write the function to return a single value. If you need to Url value as well write a function for that.

However, it would be much more efficient to just join to the cities table in your stored procedure.

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Re: Calling functions in stored procedures

Majid Shahabfar1-Feb-05 4:34

Majid Shahabfar

1-Feb-05 4:34

tnx for reply,
the OriginCityID and DestCityID are both related to CityID in Cities table.
so I cannot have inner join for both of them. as I know it's possible for just
one CityID (either Origin or DestCityID).

Re: Calling functions in stored procedures

Colin Angus Mackay1-Feb-05 4:43

Colin Angus Mackay

1-Feb-05 4:43

Majid Shahabfar wrote:
I cannot have inner join for both of them

Yes you can, you make two inner joins but assign them a different alias - then it is like having two tables with duplicate data. Here:

SELECT CountryID, OCity.Name AS OriginCity, DCity.Name AS DestCity
FROM MyTable
INNER JOIN Cities OCity ON OCity.CityID = OriginCityID
INNER JOIN Cities DCity ON DCity.CityID = DestCityID
WHERE (MyID = @MyID)

Does this help?

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Date stored as varchar required to be sorted

xcavin31-Jan-05 19:49

xcavin

31-Jan-05 19:49

I have a db table column which is of type varchar, but the value stored is date.
eg: 1/23/2005
2/24/2004
3/23/2006 etc.

I want to format this column as follows:

2__1/23/2005
1__1/23/2004
3__2/23/2006

ie, the reformatted value is the sorting index for date. Ie if the column is selected with an order by, the result will be sorted according to the date even though the column type is varchar.
This twisted way is adopted since this column is refered by crystal reports and the report is sorted according to this column.
Any query to format like this.

Re: Date stored as varchar required to be sorted

Ritesh12341-Feb-05 2:04

Ritesh1234

1-Feb-05 2:04

Hi,
Try this............

SELECT *
FROM TableName
ORDER BY CONVERT(DATETIME,DateColumn)

Hope this will help u.................:->

Reagrds,
Ritesh

SQL Replication Conflicts

Daminda31-Jan-05 19:09

Daminda

31-Jan-05 19:09

HI,

I Configured a SQL Server with Merge Replication after few days some tables got some conflicts one table got 10012 conflict Records. Any one there to help me to solve this how to resolve this records or how can I avoid this conflicts?

(That particular table has a Trigger it calculate daily balance)

retrieving datetime from recordset

elephantstar31-Jan-05 8:49

elephantstar

31-Jan-05 8:49

When I select all fields in the table, the datetime does not display. Instead it returns a NULL. Why is that? This is the code I used to populate the database.

recordset->Open("SELECT * from mytable", connection.GetInterfacePtr(),
ADODB::adOpenForwardOnly, ADODB::adLockReadOnly, ADODB::adCmdText);

while(!recordset->ADOEOF)
{
_variant_t vtValue;
vtValue = recordset->Fields->GetItem(L"value")->GetValue();
sprintf(fieldValue,"%s",(LPCSTR)(vtValue.bstrVal));

recordset->MoveNext();
};

Thanks!

Re: retrieving datetime from recordset

WoutL31-Jan-05 10:59

WoutL

31-Jan-05 10:59

A datetime value does not return as a VT_BSTR but as a VT_DATE value. But you can change the type by using vtValue.ChangeType(VT_BSTR) before displaying the value.

Wout Louwers

Re: retrieving datetime from recordset

elephantstar31-Jan-05 11:14

elephantstar

31-Jan-05 11:14

Thanks! That did it!

Trigger Dates

Tom Wright31-Jan-05 7:41

Tom Wright

31-Jan-05 7:41

I have a thread that, every 3 seconds checks a table to see if a record
exists. If it does it retreives it and then deletes the record. is
there a way to use trigger dates in my table to tell me when a record
exists so that i do not have to set a timer to check every three
seconds?

Is there a way to have a SQL table fire an event so that I know if
something has changed and to go check it?

Thanks

Tom Wright
tawright915@yahoo.com

Re: Trigger Dates

Colin Angus Mackay31-Jan-05 9:38

Colin Angus Mackay

31-Jan-05 9:38

Tom Wright wrote:
Is there a way to have a SQL table fire an event

You mean like a "trigger"?

You could write a trigger that fires an event of some kind. You can set SQL server up to send emails if you want. Or you could use xp_cmdshell to execute an exe file which could perform the event you want. Be careful of these however as they are open to abuse if you don't think about the security aspect.

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Re: Trigger Dates

cmk31-Jan-05 9:53

cmk

31-Jan-05 9:53

Use an insert/update trigger on the table.

CREATE TRIGGER TomsTrig
ON TomsTable
FOR INSERT, UPDATE
AS

-- Check if insert/update record matches record you are looking for

-- Do one or more of :
-- 1. Use matching record to insert/update another table
-- 2. Use an existing stored/extended procedure to send a message out (e.g. xp_sendmail, xp_logevent)
-- 3. Write your own stored/extended procedure to send a message out

-- Delete (maybe rollback ?) insert/update of matching record

GO

...cmk

Save the whales - collect the whole set

Re: Trigger Dates

Tom Wright31-Jan-05 11:53

Tom Wright

31-Jan-05 11:53

The probelm is my app will not reside on the same machine as the SQL server. I was wondering if, since I have a connection open to my SQL database, that it would be possible to send an event to my app telling me that something has changed.

Let me ask this. If at the beginning of my app I read in all the records from the table....connections. And someone uses another app to delete one of these records. How will i know that a record has been deleted. Will I have to do a refresh every so many seconds?

Thanks

Tom Wright
tawright915@yahoo.com

Re: Trigger Dates

cmk31-Jan-05 14:23

cmk

31-Jan-05 14:23

Yes, that is the scenario i expected.

Before going farther let me state - i am not a DBA. I am a C++ developer who writes a lot of database code (ODBC and OLE DB), but does not know all the ins and outs of SQL Server.

I had the same issue a couple years ago.
I wanted my server application to be aware of any changes made to certain tables by other applications.

What i ended up doing is writing a couple extended procedures for SQL Server.

Given:
Machine S contains my server program.
Machine D contains SQL Server.
The extended procedures are all contained in x.dll which has been installed on D.

My server on S, using ODBC, connects to MSSQL on D after setting up a listening port on S.
It then executes (via ODBC statement) 'xp_cmkInitSvcMsg <@server> <@port> [@handle OUTPUT]'.
The output handle uniquely identifies the MSSQL connection to S.

The triggers on the tables then call 'xp_cmkBroadcastSvcMsg <@msg>' when modified.
This sends @msg to all connected servers (i.e. those that called xp_cmkInitSvcMsg).

Just before the server on S disconnects from MSSQL on D it executes 'xp_cmkTermSvcMsg <@handle>'.
This just closes the socket connection from MSSSQL to S and cleans up the state.

As long as the server is connected it gets messages from MSSQL on its listening port.
It also allows more than one program to get change messages from MSSQL at the same time.
It's a bit of a round about way of doing things, but i needed a solution and this was the best i came up with.

...cmk

Save the whales - collect the whole set

Re: Trigger Dates

Tom Wright1-Feb-05 11:11

Tom Wright

1-Feb-05 11:11

I thought it would be this difficult. I've never called a sp on SQL2000, can you shoe me a code snippet on how to do this. Also where can I find info on how to create a sp like the one you show...or any that return something back to the calling app. I'm going to assume that they are all the same.

Thanks

Tom Wright
tawright915@yahoo.com

Re: Trigger Dates

cmk1-Feb-05 16:47

cmk

1-Feb-05 16:47

I was afraid you were going to ask this ... start with the following links and then feel free to ask any specific questions.

http://www.codeproject.com/database/mssqltutorial.asp[^]
http://www.codeproject.com/database/xp_md5.asp[^]
http://www.codeproject.com/database/extended_sp.asp[^]
http://www.codeproject.com/database/xp_pcre.asp[^]

...cmk

Save the whales - collect the whole set

Re: Trigger Dates

Tom Wright2-Feb-05 4:39

Tom Wright

2-Feb-05 4:39

Awesome. Thanks for the help.

Tom Wright
tawright915@yahoo.com

Connecting to the remote Sqlserver instance.

RuchirD31-Jan-05 3:05

RuchirD

31-Jan-05 3:05

Hi Firends,
I am trying to access remote Sqlserver instance using program, Can anyone tell me how to achieve this.
I am having difficulty because My machine is having more that one instance of MSDE/Sqlserver instance how to provide exact information of the Sqlserver instance to which to connect.

Please anyone tell me.

Thanks n Regards,

RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.

Re: Connecting to the remote Sqlserver instance.

David Salter31-Jan-05 8:54

David Salter

31-Jan-05 8:54

If you have more than one instance, you need to specify the machine and instance name to connect.

i.e. instead of connecting to "server_name", you would connect to "server_name\instance_name".

Database FAQ

Re: Connecting to the remote Sqlserver instance.

RuchirD31-Jan-05 16:25

RuchirD

31-Jan-05 16:25

Hi,
Since the server is on the remote machine in that case How to do this,
Let me give an example suppose my server name is Foo and the instance name is Bar, IP address of the remote sever is ppp.qqq.rrr.sss
Can anyone tell me what would be the connection string.

RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.

Last Visit: 31-Dec-99 18:00 Last Update: 19-May-24 2:59

Refresh

ᐊ Prev 1...2151 2152 2153 215421552156 2157 2158 2159 2160 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.