|
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
|
|
|
|
|
I have to tables
1: Main table
2: Text table
the relation is one to many.
If there isent any matches in the text-table i want the post from the
main-table
My query: (Swedish columname´s)
------------------------------------
SELECT arrProdKat.prod,arrProdKat.lev,arrProdKatText.sprak,
rubrik =CASE
WHEN(arrProdKatText.sprak is null) THEN arrProdKat.rubrik
ELSE arrProdKatText.rubrik END,
info =CASE
WHEN(arrProdKatText.sprak is null) THEN arrProdKat.info
ELSE arrProdKatText.info END
FROM arrProdKat
LEFT OUTER JOIN arrProdKatText ON
arrProdKat.arr=arrProdKatText.arr AND
arrProdKat.lev=arrProdKatText.lev AND
arrProdKat.prod=arrProdKatText.prod AND
arrProdKat.arrKat=arrProdKatText.arrKat
WHERE arrProdKat.arr=@arr AND arrProdKat.arrKat=@arrKat
AND(arrProdKatText.sprak=@sprak OR arrProdKatText.sprak is null)
-------------------------------------------
The result dont find the post in the main-table if ther are
child-posts in the text-table, even if they dont equals @sprak.
|
|
|
|
|
i want to use ,such as :
select t1.a,sum(t1.b),sum(t2.b) from
(Select a,b from t1) t1 left outer join
(select a,b from t2) t2 on t1.a=t2.a
group by t1.a
some rows exist in t1,but it not exist in t2
so in the result the sum(t2.b) is NULL
my database is Microsoft Access,so can't use function isnull(t2.b,0),---the function isnull(column,replacevalue) only used in Microsoft SQL Server
How can i do?
but can't use such as :
create table table1(
column2 float DEFAULT 0,
|
|
|
|
|
use select count(b) from table1 first.It retreive the number of rows in that table.So you can figure out if it is 0 or not.I have never used it in Access but it works in SQL Server.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
sorry , i don't know what's you meaning yet.
can you write a demo use sql?
|
|
|
|
|
First you run this query:
select count(columnname) from tablename
It will return only one record and its number of rows in that column and table,so if there is no row in that table the returning record value will be 0 so there is no problem if it is null or not,if it is bigger that 0 then you can run your query.
Clear?
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Not exactly sure what it is you are trying to find, if you only want to find the entries in one table that aren't in another then you simply need to do an outer join against the table like such:
SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.a = t2.a
This should grab everything that is in table1 that is not in table2 where there is a relation between t1 and t2 on field a. Does this help, or are you trying to do something else?
Nick Parker
|
|
|
|
|
in fact i used it,such as :
select t1.a,sum(t1.b-t2.b) from
(Select a,b from t1) t1 left outer join
(select a,b from t2) t2 on t1.a=t2.a
group by t1.a
so ,the sum(t1.b-t2.b) will return NULL as long as t2.b is NULL.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
