|
FWIW, I am expriencing a problem similar to the one documented in MS KB Article #Q218751[^], although I am using OleDB classes from within VC++ 6.0.
If I try to call a stored procedure that takes a BLOB parameter as input, and returns an OUTPUT parameter, the output parameter does not get set correctly (in fact, it is not touched at all!) if the length of the BLOB is greater than ~8000 bytes. Manually truncating the BLOB before calling the SP causes the problem to go away. This is with using the MSDASQL driver.
Just a brief warning to those that might be using MDAC 2.7 with OleDB/ADO...
Peace!
-=- James (Sonork:100.21837)
"There is nothing worse than being oblivious to the fact that you do not know what you are doing."
[Get Check Favorites 1.4 Now!]
|
|
|
|
|
Greeting all,
I'm trying to use on OleDbDataAdapter to read data from an Oracle 8.1 database. Ittermitantly, when I call the fill method, I get an OleDbException that has me stumped. The message is: "Oracle error occured, but error message could not be retrieved from Oracle." The error code is -2147467259.
It seems to be ittermittant: sometimes it happens, no matter what's in the SQL and other times not...
All insight appreciated,
Bill
|
|
|
|
|
I haven't tried but I do know that the original OLE adapter had some problems going against Oracle. Oracle never behaves right with normal code  Have you tried the updated Oracle Provider that Microsoft recently released? or perhaps Oracle's own provider? I think it can be found on the Technet site for Oracle.
Mark
|
|
|
|
|
I`m having problems getting data from TEXT field from SQLServer using OLE DB - no data could be fetched.
I`m using DBTYPE_VARIANT and COleVariant.
I work in Visual Studio 6.0.
Can anybody give me any tips or examples for VS C++ and pure OLE DB for SQLServer.
What do I need to do to get data from TEXT field (steps)?
Thank you!
Kind regards... Tau.
|
|
|
|
|
For OleDB, look up BLOB_ENTRY_LENGTH_STATUS in the MSDN help. It works great for me! Although you might want to optimize the given implementation of ISequentialStream...
Peace!
-=- James (Sonork:100.21837)
"There is nothing worse than being oblivious to the fact that you do not know what you are doing."
[Get Check Favorites 1.4 Now!]
|
|
|
|
|
Is it for TEXT or for IMAGE?
`Cause I can fetch IMAGE without a problem...
But TEXT...
Kind regards... Tau.
|
|
|
|
|
taualex wrote:
Is it for TEXT or for IMAGE?
As the article says, it is for BLOBs (usually IMAGE/LONGBINARY/VARBINARY, etc). However, you may be able to obtain a TEXT field by using the BLOB type and grabbing it from the passed IStorageStream object.
Peace!
-=- James (Sonork:100.21837)
"There is nothing worse than being oblivious to the fact that you do not know what you are doing."
[Get Check Favorites 1.4 Now!]
|
|
|
|
|
I have the following query:
(SELECT P_Title AS Title FROM tblProjects WHERE Status='True') UNION ALL (SELECT Title FROM tblProjectDocs WHERE Status='True') UNION ALL (SELECT Title FROM tblLinks WHERE Status='True') ORDER BY Title DESC
This returns roughly 26 or so records. I want to include only the top 6 or so records. Using SELECT TOP 3...etc does not work correctly - as for some reason, this means records are displayed in an odd order, and more than 6 records are returned in total.
I have tried mucking about with the position of brackets, doesn't seem to work. Elsewhere it is suggested that a sub-query might work (e.g. SELECT TOP 6 FROM (query with UNION) ORDER BY Field). This doesn't work either. Any ideas?
|
|
|
|
|
IMO you could do this using one of the following methods:
1) Use a temp table and then select top 6 from the temp table
2) If you use this often, create a view and then select top 6 from the view.
There might be other ways to do this but the problem is that the ordering is done after all the rows have been UNIONed.
|
|
|
|
|
Try this: (tested on SQL 2000)
SELECT TOP 6
Title
FROM
(
SELECT
P_Title As Title
FROM
tblProjects
WHERE
Status='True'
UNION ALL
SELECT
Title
FROM
tblProjectDocs
WHERE
Status='True'
) As Titles
ORDER BY
Title DESC
If you miss out the "As Titles" before the "ORDER BY", you'll get an "Incorrect syntax" error message.
|
|
|
|
|
Anybody got a clue as to how to run a DTS package using SQLDMO?
Also I have just spotted the Jobs interface in SQLDMO and was wondering if that could be a way?
ta
|
|
|
|
|
Paul Watson wrote:
Anybody got a clue as to how to run a DTS package using SQLDMO?
Not sure if this can be considered a clue or if I am just the first lucky soul to *try* and answer your question; you choose. 
'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************
Function Main()
Call Paul_Watson_Fun()
Main = DTSTaskExecResult_Success
End Function
Function Paul_Watson_Fun()
Dim oServer 'DMO Server object.
Dim oResult 'Result set.
Dim sParamValue
'Create a SQLDMO server object.
Set oServer = CreateObject("SQLDMO.SQLServer")
' Make a connection to the local server.
oServer.Connect ".", "sa"
'Select the desired row from the table.
set oResult = oServer.Databases("pubs").ExecuteWithResults_
("select paramvalue from my_param_table")
'Retrieve the first row, first column from the results.
sParamValue = oResult.GetColumnString (1,1)
'Set the global variable.
DTSGlobalVariables("MyGlobalVar").Value = sParamValue
Main = DTSTaskExecResult_Success
End Function
HTH
Nick Parker
|
|
|
|
|
Ok, here's my problem. I've created a SQL Server stored procedure that references an ActiveX DLL I made (yes, in VB) via the sp_OA* procedures in SQL Server. The ActiveX DLL uses the CDO library to send emails (for automated on-site notifications at work) via the Exchange server.
Now, everything's fine except by the time the email hits my inbox I end up with a truncated version of it if the body is more then x amount of characters - which is considerably less than the buffers I'm using to store the body of the email.
Does anyone know of any stack/variable limitations in one of these libraries that would be causing this? I'm about go into a nice debug session, but I'd figure I'd ask first. If I get an answer, I'll post it. If someone knows already, then goody.
Jeremy Falcon
Imputek
<nobr>"C# is the answer to a question nobody asked." - Chris Losinger
Jeremy Falcon
Imputek
<nobr>"C# is the answer to a question nobody asked." - Chris Losinger
|
|
|
|
|
I got it BTW, it's a 255 char limitation with SQL Server's stored procedure params.
Jeremy Falcon
Imputek
<nobr>"C# is the answer to a question nobody asked." - Chris Losinger
|
|
|
|
|
Jeremy,
I see that you have *fixed* your problem, what size data type were you using to define the body of the text within the stored procedure. I have several stored procedures that use a variable amount of text data in which we have often used either varchar (2000) or ntext (16). If you have a SQL Server Administration Book and get a chance you might want to look up how the ntext data is actually stored within SQL Server, interesting none the less . Not sure if this was exactly your problem or not, but a little extra info never hurt anyone either.
Nick Parker
|
|
|
|
|
I'm using a varchar(2000) also for the @Message variable in the procedure. I passed it as a param to my ActiveX DLL via sp_OASetProperty but only ended up with the first 255 chars.
Thanks for the tip, kinda makes more curious about the book now.
Jeremy Falcon
Imputek
<nobr>"In fact it is quite simple, men and women both only want one thing - what they can't have!" - phykell
|
|
|
|
|
I have several string and decimal values that I have to enter into my table through an sql statement. I have one problem. When one of my date fields is null, the table automatically enters the default 1/1/1900. I need to find another way around this. That field needs to stay blank if it is originally blank.
what my code looks like:
string connstr = "my connection to db";
string sSQLCommand = "INSERT INTO t340(cmd_dsg,trns_cd,proc_act_cd,inp_act_cd," + "blk_tkt_no,blk_tkt_dt,seq_no,updt_cd,jono,eor,comt_ref_no,oblg_ref_no_spiin,cost_cen_mgr,docu_dt,act_amt,wi_fed_govt_cd,oblg_stat_cd,oblg_expir_dt,jv_no,oblg_ty_cd,amd_no,qty,wt_lbs,hrs,nomenclature,clin,acrn,mod_no,subjono,ifs_docu_no,lbr_pay_pd_no,prod_ind,reject_cd,correction_cd,load_date)" +
"VALUES('" + cmd_dsg + "','" + trns_cd + "','" + proc_act_cd + "','" + inp_act_cd + "','" + blk_tkt_no + "','" + blk_tkt_dt + "','" + seq_no + "','" + updt_cd + "','" + jono + "','" + eor + "','" + comt_ref_no + "','" + oblg_ref_no_spiin + "','" + cost_cen_mgr + "','" + docu_dt + "'," + act_amt + ",'" + wi_fed_govt_cd + "','" + oblg_stat_cd + "','" + oblg_expir_dt + "','" + jv_no + "','" + oblg_ty_cd + "','" + amd_no + "'," + qty + "," + wt_lbs + "," + hrs + ",'" + nomenclature + "','" + clin + "','" + acrn + "','" + mod_no + "','" + subjono + "','" + ifs_docu_no + "','" + lbr_pay_pd_no + "','" + prod_ind + "','" + reject_cd + "','" + correction_cd + "','" + ldate + "');";
//Create the command object
SqlConnection myConnection = new SqlConnection(connstr);
SqlCommand cmdAdder = new SqlCommand(sSQLCommand, myConnection);
myConnection.Open();
int nNoAdded = cmdAdder.ExecuteNonQuery();
myConnection.Close();
Everything else works. I need to know if there is another way to insert each value and to be able to use an if statement for when the field is blank. I was able to do this in VB but I am having to convert this program.
this is what the VB looked like:
qstr = "Select * from t340 where cmd_dsg = '1111111111111'"
Set temprec = New ADODB.Recordset
temprec.CursorType = adOpenDynamic
temprec.CursorLocation = adUseClient
temprec.Open qstr, cnn1, adOpenKeyset, adLockOptimistic, adCmdText
temprec.AddNew
temprec!cmd_dsg = cmd_dsg
temprec!trns_cd = trns_cd
temprec!proc_act_cd = proc_act_cd
temprec!inp_act_cd = inp_act_cd
temprec!blk_tkt_no = blk_tkt_no
If blk_tkt_dt <> "" Then
temprec!blk_tkt_dt = blk_tkt_dt
End If
temprec!seq_no = seq_no
temprec!updt_cd = updt_cd
temprec!jono = jono
temprec!eor = eor
temprec!comt_ref_no = comt_ref_no
temprec!oblg_ref_no_spiin = oblg_ref_no_spiin
temprec!cost_cen_mgr = cost_cen_mgr
If docu_dt <> "" Then
temprec!docu_dt = docu_dt
End If
temprec!act_amt = act_amt
temprec!wi_fed_govt_cd = wi_fed_govt_cd
temprec!oblg_stat_cd = oblg_stat_cd
If oblg_expir_dt <> "" Then
temprec!oblg_expir_dt = oblg_expir_dt
End If
temprec!jv_no = jv_no
temprec!oblg_ty_cd = oblg_ty_cd
temprec!amd_no = amd_no
temprec!qty = qty
temprec!wt_lbs = wt_lbs
temprec!hrs = hrs
temprec!nomenclature = nomenclature
temprec!clin = clin
temprec!acrn = acrn
temprec!mod_no = mod_no
temprec!subjono = subjono
temprec!ifs_docu_no = ifs_docu_no
temprec!lbr_pay_pd_no = lbr_pay_pd_no
temprec!prod_ind = prod_ind
temprec!reject_cd = reject_cd
temprec!correction_cd = correction_cd
temprec!load_date = load_date
temprec.Update
temprec.Close
Could someone please help?
|
|
|
|
|
Is there any other easy way to import a diagram to an SQL-server 2000 DB than doing a restore of a backup? (Do not want to do a restore...)
|
|
|
|
|
Yes it is pretty simple. All database diagrams are stored in the table dtproperties. This link will show you the query you need to run.
http://www.mssqlcity.com/FAQ/TranMove/MoveDiag.htm
|
|
|
|
|
Hmm
I've tried both examples now and they all resulted in that a copy was created of an already existing diagram with exactly the same name(!). When I deleted one of the diagrams, SQL Enterprise Manager crashed every time I tried to look at a diagram...
insert into
Example..dtproperties (objectid, value, lvalue, version, property)
select
objectid, value, lvalue, version, property
from
Northwind..dtproperties
where objectid =
(select objectid from dtproperties where value = 'Relationships')
You have to add "Northwind.." in the last statement.
I don't see how to transfer diagrams from different servers, only between databases on the same server.
Have I missed something?
|
|
|
|
|
Hi everyone,
I'm having trouble with an Access query.
My program does one connection to the db and several queries, but only the first one is going through, the second one throws Error '-2147417851 (8001015)' which is an automation error.
I'v searched for the cause and it says it's because the server is throwing an exception.
I read in Microsoft's support site and found out that I need to do late binding, but I believe that's what I'm doing.
The code is this:
<br />
int CDBAccess::cargaConfig(char *pszComponente, CConfig *poConfig) {<br />
<br />
_RecordsetPtr rs;<br />
HRESULT hr;<br />
_variant_t conn;<br />
char szQuery[256];<br />
char szTemp[30];<br />
FieldPtr campo;<br />
<br />
try {<br />
hr = rs.CreateInstance("ADODB.Recordset");<br />
if(FAILED(hr)) {<br />
m_nError = 506;
return m_nError;<br />
}<br />
conn = (IDispatch*) m_Conec;<br />
}<br />
catch(_com_error & err) {<br />
MessageBox(NULL,(LPCTSTR) err.Description(), "CargaConfig", MB_OK);<br />
return -1;<br />
}<br />
sprintf(szQuery, <br />
"select * from configuracion where componente = '%s'", pszComponente);<br />
_bstr_t qry(szQuery);<br />
FILE *fd;<br />
fd = fopen("C:\\usr\\sia\\query.txt","w");<br />
fprintf(fd,"%s",(char *)qry);<br />
fclose(fd);<br />
try {<br />
hr = rs->Open(qry, conn, adOpenStatic, adLockOptimistic, adCmdText);<br />
if(FAILED(hr)) {<br />
m_nError = 507;
return m_nError;<br />
}<br />
}<br />
catch(_com_error & err) {<br />
MessageBox(NULL,(LPCTSTR) err.Description(), "CargaConfig", MB_OK);<br />
return -1;<br />
}<br />
<br />
try {<br />
campo = rs->Fields->Item["timeout"];<br />
_bstr_t to = campo->Value;<br />
sprintf(szTemp, "%s", (char *)to);<br />
poConfig->iTimeout = atoi(szTemp);<br />
<br />
campo = rs->Fields->Item["canal_comunicacion"];<br />
_bstr_t can = campo->Value;<br />
sprintf(poConfig->szCanalComm, "%s", (char *)can);<br />
<br />
campo = rs->Fields->Item["id_canal"];<br />
_bstr_t idcan = campo->Value;<br />
sprintf(poConfig->szIdCanal, "%s", (char *)idcan);<br />
<br />
campo = rs->Fields->Item["objeto"];<br />
_bstr_t obj = campo->Value;<br />
sprintf(poConfig->szObjeto, "%s", (char *)obj);<br />
<br />
campo = rs->Fields->Item["prioridad"];<br />
_bstr_t pri = campo->Value;<br />
sprintf(poConfig->szPriori, "%s", (char *)pri);<br />
<br />
hr = rs->Close();<br />
if(FAILED(hr)) {<br />
m_nError = 508;
return m_nError;<br />
}<br />
}<br />
catch(_com_error & err) {<br />
MessageBox(NULL,(LPCTSTR)err.Description(),"CargaConfig",0);<br />
return -1;<br />
}<br />
return 0;<br />
}<br />
Can anyone tell me what might be wrong??
Greetings from México
|
|
|
|
|
Is this the second query that is failing? It looks like your syntax is for SQL Server, using wildcard %. If you are trying to wildcard it, and you are using Access your query needs to say:
select * from configuracion where componente like '*s'
Otherwise the query will never return any records.
|
|
|
|
|
Can someone give me an example of how I can customize by PK? Instead of just a number incrememnted by 1 for every row, I want to change it to NUR1, NUR2, NUR3, etc...basically add a NUS in front of it. How can I accomplish this? I am going to have a different ID for different tables, this one happens to be "New User Request", hence the NUR1, NUR2, NUR3, etc. Any thoughts? Someone mentioned an "INSTEAD OF" trigger, but didn't offer any further details on "HOW" to do it. Thanks
|
|
|
|
|
Maintaing all your PK columns with the same name is extremely bad practise. I would recommend naming the pk as TableNameId.
eg...
CREATE TABLE dbo.Jobs (
JobId int not null identity (1,1),
Jobtitle...
)
it's a nightmare when you start doing joins if all your keys are identically named.
STL is a religeon. Enquiries to Reverend Christian Graus
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
