|
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.
|
|
|
|
|
of cause it can work,table define such as
create table t1(
a varchar(10),
b float
);
create table t2(
a varchar(10),
b float
);
can you test in your computer?
|
|
|
|
|
Hi!
I use the Datetime dataype in SQL-Server 2000 to store date and time eg "2002-09-04 09:18:20". When I insert data in through VB6.0 and ADO 2.6 i set the in-parameter to an adDBTime and it seems to work fine.
Could there be problems in the future and/or is there a better datatype in ADO for this case?
|
|
|
|
|
adDBTime was designed for that purpose. Who knows what could happen in the future though.
You can use adVarChar too, if you like
STL is a religeon. Enquiries to Reverend Christian Graus
|
|
|
|
|
I found a nice matrix over datatypemappings SQL-server 2000 <> ADO 2.6 at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_294j.asp
Refering to the matrix, varchar should be mapped to adChar, but adVarchar seems more logic to me. Is it an printerror?
|
|
|
|
|
dim str as string
str= "INSERT INTO employees Values(@ID,@FN,@LN)"
dim InsCom as new SqlCommand(str,SqlConnection1)
plz tell me what is the error in this code.
MS Visual Studio.NEt shows error in the following SqlCommand function.
when i write this line dim InsCom as new sqlcommand(str,Sqlconnection1)
then after new the window pops up but there is nothing like sqlcommand in there?
OR
plz tell how to execute a query from a MS-ACCESS database?
thnx
Rashmi
|
|
|
|
|
You need to build your sql string differently. The parameters are inside of your quote marks so the values can not be passed in. Depending on your datatypes it should look something more like:
dim str as string
str= "INSERT INTO employees Values(" + @ID + "," + @FN + "," + @LN + ")"
dim InsCom as new SqlCommand(str,SqlConnection1)
If your datatypes are int you may have to do something like str(@ID) as well as add single quotes if your datatypes are char.
|
|
|
|
|
Is there some master chart somewhere that gives me a map on how to relate data types between VB / C# and SQL server?
I have looked all over google and see tons of posts just like mine with regards to VB6 but no one seemes to answer them.
|
|
|
|
|