Tuesday, March 27, 2012
COMSysApp DCOM got error
Thank You,
DCOM got error "The service cannot be started, either because it is disabled
or because it has no enabled devices associated with it. " attempting to
start the service COMSysApp with arguments "" in order to run the server:Joe,
This is a SQL Server group - you are more likely to get an answer if you
post in a DCOM related group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> How can I resolve the error listed below.
> Thank You,
>
> DCOM got error "The service cannot be started, either because it is disabl
ed
> or because it has no enabled devices associated with it. " attempting to
> start the service COMSysApp with arguments "" in order to run the server:
COMSysApp DCOM got error
Thank You,
DCOM got error "The service cannot be started, either because it is disabled
or because it has no enabled devices associated with it. " attempting to
start the service COMSysApp with arguments "" in order to run the server:
Joe,
This is a SQL Server group - you are more likely to get an answer if you
post in a DCOM related group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> How can I resolve the error listed below.
> Thank You,
>
> DCOM got error "The service cannot be started, either because it is disabled
> or because it has no enabled devices associated with it. " attempting to
> start the service COMSysApp with arguments "" in order to run the server:
COMSysApp DCOM got error
Thank You,
DCOM got error "The service cannot be started, either because it is disabled
or because it has no enabled devices associated with it. " attempting to
start the service COMSysApp with arguments "" in order to run the server:Joe,
This is a SQL Server group - you are more likely to get an answer if you
post in a DCOM related group.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> How can I resolve the error listed below.
> Thank You,
>
> DCOM got error "The service cannot be started, either because it is disabled
> or because it has no enabled devices associated with it. " attempting to
> start the service COMSysApp with arguments "" in order to run the server:sqlsql
Thursday, March 22, 2012
Computed Column - Error Validating Formula
I want to create a computed column with this formula:
ISNULL(NULLIF (tot_mnc, 0) / NULLIF (repl_value, 0), 0)
It works in a straight select query, but when I put it in the formula of the table design window, I get an error "Error validating the formula for column 'test_fci'"
I don't know if it's relevant but repl_value is itself a computed column with the formula:
(repl_value_e_g + repl_value_aux)
Is it possible to use the system functions in a computed column? If not, how would I pass those values into a udf and use it for the formula?
Thanks.
You are correct: you are not allowed to create a computed column that is based on a different computed column. You can use the built-in functions, so just create your new computed column based on the calculations from the base columns.
If you were to run an ALTER statement to create your computed column based on a computed column you would potentially get an error message like:
Msg 1759, Level 16, State 0, Line xxxx
Computed column 'ccccc' in table 'tttttt' is not allowed to be used in another computed-column definition.
Kent
|||That was it, thank you. The table designer gui hid the details of the error message and I didn't know which of several possiblities were causing the error.
Thanks again.
Monday, March 19, 2012
components installation
I am just a novice, and am curious.I
I was trying subscription, but an error occured: "Microsoft Server Management Studio is un able to accessreplication components because replication is not installed on this instance.
I went to change, located my setup exe, installed. but at the end, I get this message: No selected features can be installed or upgraded. Setup cannot proceed since no effective change is being made to the machine......
I downloaded the Express edition via MSDN download.
May somebody please help? What wrong did I do? What did I miss to do?
SQL Server 2005 Express does not include Microsoft SQL Server Management Studio. But SQL Server 2005 SQLEXPRCOMP does.|||There is also a separate download for the Management Express Studio. See this link here for more details:http://msdn.microsoft.com/vstudio/express/sql/download/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Thursday, March 8, 2012
complex sql statement, need help
SELECT A.*, B.Name, C.SIName, D.IID,
(Select [LastName] , [FirstName]
FROM E INNER JOIN F ON E.SID =
F.SID , A
WHERE F.Emp='Service' AND E.Lead=1 AND E.ID=[A].[D])
AS Service,
(Select [LastName] , [FirstName]
FROM E INNER JOIN F ON E.SID =
F.SID ,A
WHERE F.Emp='Industry' AND E.Lead=1 AND E.ID=[A].[D])
AS Industry
FROM A , B, C
WHERE (1=1) AND B.SID = C.SID AND A.ID = B.ID
i always get this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
does anyone know how to fix this query?Whats this syntax?
FROM E INNER JOIN F ON E.SID =
F.SID , A|||im not entirely sure, im in the process of converting this from access to sql server. i would assume that they are going ahead and assigning the table from which they will be calling on later. i usually dont use the inner join method myself, i will just type what fields i want, then the tables and then the criteria, so im guessing its along the same lines by calling table A and then specifying criteria on the next line
AND E.ID=[A].[D])|||You are getting the error because the subqueries in your select clause return more than one value (LastName and FirstName), and you are trying to assign the results to a single field in your result set (Service).
You can probably avoid the error by concatenating the two values into one:
[LastName] + ' ' + [FirstName]
but this is not a good way to write a sql statement. There are a lot of problems with the code you posted. We can probably give you some help cleaning it up, but you will need to tell us a bit more about what you want to do, and post a description of the tables and their columns.|||Are the tables really called A, B, C?
Can you post the DDL for these tables?
Do you know what the expected result is suppose to be?
I'm guessing they're trying to coorelate to table A for the names...I think I'd make it just part of the join...with a derived table
Alos you should start using the JOIN syntax...it'll be easier for you in the long run
(My Own Opinion)
MOO|||thanks for the info guys, i think i got it fixed, i used blindmans suggestion and it worked. also ill keep your advice in mind brett|||only problem i have now, is it returns each record 15 times|||This is just a guess, and it would really help if we had the DDL for tables A,B,C,E and F (are the tables really called this?) along with some sample data...
But here's a shot
SELECT A.*
, B.Name
, C.SIName
, D.IID
, N1.*
, N2.*
FROM B
INNER JOIN C ON B.ID = C.ID
INNER JOIN A ON B.ID = A.ID
INNER JOIN ( SELECT [LastName] , [FirstName] FROM E
INNER JOIN F ON E.SID = F.SID
WHERE F.Emp='Service' AND E.Lead=1) AS N1
ON N1.ID=A.D
INNER JOIN ( SELECT [LastName] , [FirstName] FROM E
INNER JOIN F ON E.SID = F.SID
WHERE F.Emp='Industry' AND E.Lead=1) AS N2
ON N2.ID=A.D|||You can avoid the duplicated records by using the SELECT DISTINCT syntax, but this and the other suggestion I gave you are really just band-aid solutions for more serious problems.
For instance, your subqueries appear to use cartesian joins to tables that are not reference in the select list or the criteria, and your subqueries in SELECT clauses are not a good idea to begin with. They should be dropped in favor of direct joins or moved to the FROM clause.|||yea i know it would help, but i got it figured out now. i dont have a lot of experience with converting from access to sql and i assumed that a lot of complex changes were neccessary when come to find out, there was really only a small change to convert it. the code i showed you guys obviously included my f'ed up alterations. but the only problem was the &'s from access had to be switched to +'s in sql. so sorry for taking up your time guys.
Wednesday, March 7, 2012
complex query!
I have Below query with me. but it's showing me error i.e "[Microsoft]
[ODBC SQL Server Driver]Syntax error or access violation"
I am not able to understand what to do exactly ' Please help
Query:
----
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM (((incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>={27/12/2006)
AND incident.date_logged<{29/12/2006}) OR
(incident.inc_close_date>={27/12/2006}
AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status = 'n')or(incident.inc_status = 'c'))
ORDER BY sla.sla_sc"
---Instead of braces, enclose your date literals in single quotes. Braces are
used as the ODBC escape sequence.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status => 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>|||Try this:-
Some of the braces and { braces are not required:-
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id INNER JOIN sla ON
incident.sla_id=sla.sla_id INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>='27/12/2006')
AND incident.date_logged<'29/12/2006') OR
(incident.inc_close_date>='27/12/2006'
AND incident.inc_close_date<'27/12/2006')OR(incident.inc_status ='n')
or(incident.inc_status = 'c')
ORDER BY sla.sla_sc
Thanks
Hari
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status => 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>
complex query!
I have Below query with me. but it's showing me error i.e "[Microsoft]
[ODBC SQL Server Driver]Syntax error or access violation"
I am not able to understand what to do exactly ? Please help
Query:
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM (((incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>={27/12/2006)
AND incident.date_logged<{29/12/2006}) OR
(incident.inc_close_date>={27/12/2006}
AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
'n')or(incident.inc_status = 'c'))
ORDER BY sla.sla_sc"
Instead of braces, enclose your date literals in single quotes. Braces are
used as the ODBC escape sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegr oups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ? Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
>
|||Try this:-
Some of the braces and { braces are not required:-
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id INNER JOIN sla ON
incident.sla_id=sla.sla_id INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>='27/12/2006')
AND incident.date_logged<'29/12/2006') OR
(incident.inc_close_date>='27/12/2006'
AND incident.inc_close_date<'27/12/2006')OR(incident.inc_status ='n')
or(incident.inc_status = 'c')
ORDER BY sla.sla_sc
Thanks
Hari
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegr oups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ? Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
>
complex query!
I have Below query with me. but it's showing me error i.e "[Microsoft]
[ODBC SQL Server Driver]Syntax error or access violation"
I am not able to understand what to do exactly ' Please help
Query:
----
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM (((incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>={27/12/2006)
AND incident.date_logged<{29/12/2006}) OR
(incident.inc_close_date>={27/12/2006}
AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
'n')or(incident.inc_status = 'c'))
ORDER BY sla.sla_sc"
---Instead of braces, enclose your date literals in single quotes. Braces are
used as the ODBC escape sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>|||Try this:-
Some of the braces and { braces are not required:-
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id INNER JOIN sla ON
incident.sla_id=sla.sla_id INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>='27/12/2006')
AND incident.date_logged<'29/12/2006') OR
(incident.inc_close_date>='27/12/2006'
AND incident.inc_close_date<'27/12/2006')OR(incident.inc_status ='n')
or(incident.inc_status = 'c')
ORDER BY sla.sla_sc
Thanks
Hari
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>
Saturday, February 25, 2012
complex query in sp
Background:
1. The innermost query (using the dynamic statements) will run fine on its own.
2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors.
3. Also, the entire query runs fine as a view (with hard coded vals for column and value).
4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.
Here's the code:
CREATE PROCEDURE [dbo].[sp_ninetydayavgtotals]
@.column nvarchar(100),
@.value smallint
AS
DECLARE @.SelectString nvarchar(500)
SET @.SelectString = 'SELECT AVG(total_attacks) AS avg_attacks ' +
'FROM (SELECT TOP 100 PERCENT thedate AS [day], COUNT(total) AS total_attacks, SUM(enemyKIA) AS EKIA, SUM(enemyWIA) AS EWIA, SUM(coalitionKIA) AS CKIA, SUM(coalitionWIA) AS CWIA ' +
'FROM SELECT(TOP 100 PERCENT CONVERT(nvarchar, dateOccured, 11) AS thedate, txtCategory AS total, enemyKIA, enemyWIA, coalitionKIA, coalitionWIA ' +
'FROM dbo.v_maindata ' +
'WHERE (CONVERT(nvarchar, dateOccured, 11) > CONVERT(nvarchar, (getdate()-90), 11)) AND (CONVERT(nvarchar, dateOccured, 11) != CONVERT(nvarchar, getdate(), 11)) AND ' + @.column + ' = @.value ' +
'ORDER BY dateOccured DESC) DERIVEDTBL ' +
'GROUP BY thedate ' +
'ORDER BY thedate DESC) DERIVEDTBL'
EXEC sp_executesql @.SelectString, N'@.column nvarchar(100), @.value smallint', @.column, @.value
GO
----------------------
Owen Eustice
MNC-I Webmaster
Victory Base SouthI'm struggling with a syntax error in my sp. I'm hoping someone can give it a fresh look and straighten me out. Thanks.
Background:
1. The innermost query (using the dynamic statements) will run fine on its own.
2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors.
3. Also, the entire query runs fine as a view (with hard coded vals for column and value).
4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.
Here's the code:
CREATE PROCEDURE [dbo].[sp_ninetydayavgtotals]
@.column nvarchar(100),
@.value smallint
AS
DECLARE @.SelectString nvarchar(500)
SET @.SelectString = 'SELECT AVG(total_attacks) AS avg_attacks ' +
'FROM (SELECT TOP 100 PERCENT thedate AS [day], COUNT(total) AS total_attacks, SUM(enemyKIA) AS EKIA, SUM(enemyWIA) AS EWIA, SUM(coalitionKIA) AS CKIA, SUM(coalitionWIA) AS CWIA ' +
'FROM SELECT(TOP 100 PERCENT CONVERT(nvarchar, dateOccured, 11) AS thedate, txtCategory AS total, enemyKIA, enemyWIA, coalitionKIA, coalitionWIA ' +
'FROM dbo.v_maindata ' +
'WHERE (CONVERT(nvarchar, dateOccured, 11) > CONVERT(nvarchar, (getdate()-90), 11)) AND (CONVERT(nvarchar, dateOccured, 11) != CONVERT(nvarchar, getdate(), 11)) AND ' + @.column + ' = @.value ' +
'ORDER BY dateOccured DESC) DERIVEDTBL ' +
'GROUP BY thedate ' +
'ORDER BY thedate DESC) DERIVEDTBL'
EXEC sp_executesql @.SelectString, N'@.column nvarchar(100), @.value smallint', @.column, @.value
GO
----------------------
Owen Eustice
MNC-I Webmaster
Victory Base South
The problem is that the variable @.SelectString is too short for the SELECT statement you assigning to it so your SQL is being truncated. Use this declaration instead:
DECLARE @.SelectString nvarchar(1000)|||The task is only as difficult as you make it, I guess.
Thanks!|||I can personally assure you that you are at least the second person to make this mistake...|||I've never made such a misteak :eek: in my life, but I've read about them in this book once ;)
On a slightly more serious note, if you don't run into a wall at full speed every now and then, you forget how good it feels when you quit!
-PatP|||I'm supposed to quit? Nobody ever told me that part.
OUCH!
Sunday, February 19, 2012
completed successfully, but not sending and no error.
the code and setup is the same on both dev and live database. but
soon after i restored a live backup to dev. the queue stopped
working on dev, live is ok thou. after some trouble shooting, i
found that the server is not sending the message at all, but it says
"Command(s) completed successfully" without any error messages.
setup:
--
create message type TestQueryMessage validation = none
create contract TestQueryContract (TestQueryMessage sent by initiator)
create queue TestSenderQueue
create service TestSenderService on queue TestSenderQueue
create queue TestQueueReceiver
create service TestServiceReceiver on queue TestQueueReceiver (TestQueryContract)
send message:
-
declare @.conversationhandle uniqueidentifier;
begin dialog @.conversationhandle
from service [TestSenderService]
to service 'TestServiceReceiver'
on contract [TestQueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [TestQueryMessage] ('blah blah blah');
result:
-
Command(s) completed successfully.
but when i do "select * from TestQueueReceiver", there's nothing. and i sure nothing else had picked up the messages.
please advise. thanks a lot.
Look in sys.transmission_queue on the sender's database. The transmission_status column should indicate the problem. Most likely the broker was not enabled back after the restore operation.
HTH,
~ Remus
Complete list of error codes
Does anyone know where to obtain a complete list of error codes which can occur in SQL Server ? For example : errror number 547 is a foreign key violation - where can I get a complete list of these numbers and there descritions ?
I can't find it in BOL or anywhere
In BOL type in 'errors-SQL Server' in the search box in the index tab and they are listed starting with Error 10008 (DB-Library)
you can also take a look here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_syserrors1a_10mx.asp
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Friday, February 17, 2012
Compile Error: Redefinition of IRowsetBookmark (VS 2005)
actually i try to create a native c++ desktop-application that will have to access a SQL CE 3.1 db-file over OLE DB. For that purpose i want to use the same self written wrapper class(for the OLE DB stuff) that i use for a native WM2003 application that creates the db files to be accessed on the desktop application.
The WM2003 application including my wrapper class compiles fine with eVC++ 4.0.
But now with VS 2005 including the same class i got this compile error: "Redefinition of class IRowsetBookmark". I have to add that i used the application wizard to generate a mfc sdi application with database support(ODBC is used in this app, too).
I figured out that in the following 2 inlcuded files IRowsetBookmark is defined:
1)The manually included "ssceoledb30.h" in my wrapper class.
2)The probably from the application wizard included "oledb.h" (in "\Microsoft Visual Studio 8\VC\PlatformSDK\Include")
My workaround for the moment is to comment the class definition in the file "ssceoledb30.h" out because i don't use this OLE DB interface in my wrapper class. But i don't think that this is the way that microsoft supposed to go.
Does anybody had this problem and solved it other way than simply comment one of the class definition out?
I would appreciate it when someone out there knows the answer and could tell me.
Kind regards,
Andre
The ssceoledb30.h file contains both the standard oledb.h and the SQL Compact Edition-specific definitions. Theoretically, you should be able to use just this file. In my products, I'm using a sqlmobile.h file that was published by Microsoft but does not seem to be available now. This file contains only the SQL Compact Edition stuff and was designed to be used with the oledb.h file.
|||Thanks for your answer.The problem will be to find the place where the oledb.h is included. I've done a search over the complete project and found no include line for that header. So it is inlcluded within some of the other header files.
It's not my intention to modify any system header files. So i would also prefer a header you mention that only contains the SQL Compact Edition-specific definitions.
Maybe someone knows where this header file from microsoft is still available or knows another way to get this working without the need of modifying standard header files.
Kind regards,
Andre
|||
I am sorry but the sqlmobile.h file was never released by Microsoft. Would you believe that I built this file myself from ssceoledb30.h, wrote an article about this and completely forgot about it?
I'm getting old...
|||
Jo?o Paulo Figueira wrote:
I am sorry but the sqlmobile.h file was never released by Microsoft. Would you believe that I built this file myself from ssceoledb30.h, wrote an article about this and completely forgot about it?
I'm getting old...
Never mind!
With your help i got a solution without the need to modify the standard headers. That's the main thing. Now with dependent include of ssceoledb30.h or sqlmobile my wrapper class can be used both for native mobile and native desktop developement without the need of adaption.
In difference to your article i have additionally had to comment the complete class IRowsetBookmark out as stated in the early post, not just the GUID definition of IID_IRowsetBookmark.
Kind regards,
Andre
Compile Error: Redefinition of IRowsetBookmark (VS 2005)
actually i try to create a native c++ desktop-application that will have to access a SQL CE 3.1 db-file over OLE DB. For that purpose i want to use the same self written wrapper class(for the OLE DB stuff) that i use for a native WM2003 application that creates the db files to be accessed on the desktop application.
The WM2003 application including my wrapper class compiles fine with eVC++ 4.0.
But now with VS 2005 including the same class i got this compile error: "Redefinition of class IRowsetBookmark". I have to add that i used the application wizard to generate a mfc sdi application with database support(ODBC is used in this app, too).
I figured out that in the following 2 inlcuded files IRowsetBookmark is defined:
1)The manually included "ssceoledb30.h" in my wrapper class.
2)The probably from the application wizard included "oledb.h" (in "\Microsoft Visual Studio 8\VC\PlatformSDK\Include")
My workaround for the moment is to comment the class definition in the file "ssceoledb30.h" out because i don't use this OLE DB interface in my wrapper class. But i don't think that this is the way that microsoft supposed to go.
Does anybody had this problem and solved it other way than simply comment one of the class definition out?
I would appreciate it when someone out there knows the answer and could tell me.
Kind regards,
Andre
The ssceoledb30.h file contains both the standard oledb.h and the SQL Compact Edition-specific definitions. Theoretically, you should be able to use just this file. In my products, I'm using a sqlmobile.h file that was published by Microsoft but does not seem to be available now. This file contains only the SQL Compact Edition stuff and was designed to be used with the oledb.h file.
|||Thanks for your answer.The problem will be to find the place where the oledb.h is included. I've done a search over the complete project and found no include line for that header. So it is inlcluded within some of the other header files.
It's not my intention to modify any system header files. So i would also prefer a header you mention that only contains the SQL Compact Edition-specific definitions.
Maybe someone knows where this header file from microsoft is still available or knows another way to get this working without the need of modifying standard header files.
Kind regards,
Andre
|||
I am sorry but the sqlmobile.h file was never released by Microsoft. Would you believe that I built this file myself from ssceoledb30.h, wrote an article about this and completely forgot about it?
I'm getting old...
|||
Jo?o Paulo Figueira wrote:
I am sorry but the sqlmobile.h file was never released by Microsoft. Would you believe that I built this file myself from ssceoledb30.h, wrote an article about this and completely forgot about it?
I'm getting old...
Never mind!
With your help i got a solution without the need to modify the standard headers. That's the main thing. Now with dependent include of ssceoledb30.h or sqlmobile my wrapper class can be used both for native mobile and native desktop developement without the need of adaption.
In difference to your article i have additionally had to comment the complete class IRowsetBookmark out as stated in the early post, not just the GUID definition of IID_IRowsetBookmark.
Kind regards,
Andre
compile a store proc again
Hi there!!
Can we force stored procs in a database to recompile and see if there is any compile time error in the stored procs then and there.
Something like sp_recompile which marks Stored Proc for recompilation and compilation occurs later on.
I am looking for something which will force the existing stored procs to compile and let me see the error if any.
Best Regards
Rahul Kumar, Software Engineer, India
SP_RECOMPILE is helpful to recompile the next time they are run.
It will remove all the chache Plan for the current object when you run the SP it will compile it.
You can do the following step to get the compile errors...
Exec Sp_recompile 'MySp'
Exec @.R = MySp
If @.R = 0
Print 'Success'
Else
Print 'Failed on Compile'
But the drawback here is you can't find the error is occured by compiler or runtime execution.
|||
This can be done for one stored proc, but we cant have this approach if we want to recompile all the strored procs in the database- as they may be having different parameters.
|||You can try calling DBCC FREEPROCCACHE
This will flush the procedure cache and the cached plans. Next time you execute the procedures, they will compile and you should be able to hit the compilation error.
|||It all depends on what you mean by "recompile". I know that I was really keen that the meant what it sounded like, which would be to take the source code, build an executable module, then a plan, and get things ready.
When you run sp_recompile, all it does is bump an internal value that is used to tell objects to recompile because something has changed. It will not take existing source code and rebuild the executable.
What is the purpose you are trying to achieve? Find where structures have changed? This is something you need to do with source control. Either by having documentation you can search (using a tool, or just manually searching) or just rebuild your project from scratch. Even recompiling is not foolproof unfortunately because of delayed name resolution, meaning that, in a procedure, if it comes to a table name that does not exist, it assumes that you know what you are doing and that the object will be created later. Man, I hate delayed name resolution, except when I need it (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490)
|||Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"
I have a database in Sql Server 2000 and I am trying to copy it on Sql server 2005.Now we have many changes like sort of =* join which we have to do in stored procs.So I am trying to get list of stored procs which wont compile successfully on sql server 2005.
One way to get this list is with SQL Server 2005 Ugrade Advisor, I am surprise but it didnt give me an exhaustive list.
So I thought I would be better if I could recompile all the stored procs, and that what i am trying to achieve.
Best Regards
Rahul
|||For that you would need to script out the stored procedures, and try to compile them. As long as object names are still the same (mitigating the whole question of delayed name resolution) then that will work.
If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:
DECLARE @.value nvarchar(128)
SET @.value = '=*'
SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0
--http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry
Object_definition is a new function in 2005 that gives you the text of the object in a nice package.
But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.
|||
Thanks Louis
this '=*' was just one issue I cited for example, there are and can (which i dont know) many more, which give error on sql server 2005 and even SQL Server 2005 Ugrade Advisor didnt catch.
Can we rewrite your given code as
select object_name(id) from syscomments where text like '%=*%'
@. Louis -- But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.
Does this mean that we can not identify affected stored proc if Upgrade Advisor misses them, expect by executing them.
Regards
Rahul
|||Install the SQL Server Best Practices Analyzer and run it against your SQL Server 2000 database. This has a rule to check for the older-style outer joins.|||
Yeah, anything that the program Umachandar mentions or the upgrade advisor miss will not be found. I was just giving an alternative for searching through your code if you find things you need to.
And no, syscomments cannot be relied upon for a precise search, since it is chunked into 4000 character chunks. Object_definition returns the full text as a varchar(max) that you can use the like on.
My experience iwth the Upgrade Advisor was pretty pleasant. We had very little trouble taking our 2000 databases from 2005, but then again, we try to keep up and usually make those kind of changes ahead of time (not that that helps you :)
|||Just to site one example which made me wondering--
Here is a line of code from my stored proc
tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))
Now the irony is my stored proc is got compiled in sql server 2000 and when i moved it to sql server 2005 and compiled it ther it gave me following error:-
Msg 102, Level 15, State 1, Procedure rsp_UpdPolSchdTmStp, Line 43
Incorrect syntax near 'TSEQUAL'.
Regards
Rahul Kumar
|||Yep, TSEQUAL is gone for good in 2005 (http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534) but it wasn't documented for quite a while.
It isn't necessary, so you can change:
tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))
to
TmStp = convert(varbinary(8), convert(bigint, @.xTmStp)))
though I am not sure why you are doing all of the converting. if @.xTmStp is of varbinary(8) type (or rowversion/timestamp type) you can just do:
TmStp = @.xTmStp
compile a store proc again
Hi there!!
Can we force stored procs in a database to recompile and see if there is any compile time error in the stored procs then and there.
Something like sp_recompile which marks Stored Proc for recompilation and compilation occurs later on.
I am looking for something which will force the existing stored procs to compile and let me see the error if any.
Best Regards
Rahul Kumar, Software Engineer, India
SP_RECOMPILE is helpful to recompile the next time they are run.
It will remove all the chache Plan for the current object when you run the SP it will compile it.
You can do the following step to get the compile errors...
Exec Sp_recompile 'MySp'
Exec @.R = MySp
If @.R = 0
Print 'Success'
Else
Print 'Failed on Compile'
But the drawback here is you can't find the error is occured by compiler or runtime execution.
|||
This can be done for one stored proc, but we cant have this approach if we want to recompile all the strored procs in the database- as they may be having different parameters.
|||You can try calling DBCC FREEPROCCACHE
This will flush the procedure cache and the cached plans. Next time you execute the procedures, they will compile and you should be able to hit the compilation error.
|||It all depends on what you mean by "recompile". I know that I was really keen that the meant what it sounded like, which would be to take the source code, build an executable module, then a plan, and get things ready.
When you run sp_recompile, all it does is bump an internal value that is used to tell objects to recompile because something has changed. It will not take existing source code and rebuild the executable.
What is the purpose you are trying to achieve? Find where structures have changed? This is something you need to do with source control. Either by having documentation you can search (using a tool, or just manually searching) or just rebuild your project from scratch. Even recompiling is not foolproof unfortunately because of delayed name resolution, meaning that, in a procedure, if it comes to a table name that does not exist, it assumes that you know what you are doing and that the object will be created later. Man, I hate delayed name resolution, except when I need it (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490)
|||Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"
I have a database in Sql Server 2000 and I am trying to copy it on Sql server 2005.Now we have many changes like sort of =* join which we have to do in stored procs.So I am trying to get list of stored procs which wont compile successfully on sql server 2005.
One way to get this list is with SQL Server 2005 Ugrade Advisor, I am surprise but it didnt give me an exhaustive list.
So I thought I would be better if I could recompile all the stored procs, and that what i am trying to achieve.
Best Regards
Rahul
|||For that you would need to script out the stored procedures, and try to compile them. As long as object names are still the same (mitigating the whole question of delayed name resolution) then that will work.
If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:
DECLARE @.value nvarchar(128)
SET @.value = '=*'
SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0
--http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry
Object_definition is a new function in 2005 that gives you the text of the object in a nice package.
But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.
|||
Thanks Louis
this '=*' was just one issue I cited for example, there are and can (which i dont know) many more, which give error on sql server 2005 and even SQL Server 2005 Ugrade Advisor didnt catch.
Can we rewrite your given code as
select object_name(id) from syscomments where text like '%=*%'
@. Louis -- But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.
Does this mean that we can not identify affected stored proc if Upgrade Advisor misses them, expect by executing them.
Regards
Rahul
|||Install the SQL Server Best Practices Analyzer and run it against your SQL Server 2000 database. This has a rule to check for the older-style outer joins.|||
Yeah, anything that the program Umachandar mentions or the upgrade advisor miss will not be found. I was just giving an alternative for searching through your code if you find things you need to.
And no, syscomments cannot be relied upon for a precise search, since it is chunked into 4000 character chunks. Object_definition returns the full text as a varchar(max) that you can use the like on.
My experience iwth the Upgrade Advisor was pretty pleasant. We had very little trouble taking our 2000 databases from 2005, but then again, we try to keep up and usually make those kind of changes ahead of time (not that that helps you :)
|||Just to site one example which made me wondering--
Here is a line of code from my stored proc
tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))
Now the irony is my stored proc is got compiled in sql server 2000 and when i moved it to sql server 2005 and compiled it ther it gave me following error:-
Msg 102, Level 15, State 1, Procedure rsp_UpdPolSchdTmStp, Line 43
Incorrect syntax near 'TSEQUAL'.
Regards
Rahul Kumar
|||Yep, TSEQUAL is gone for good in 2005 (http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534) but it wasn't documented for quite a while.
It isn't necessary, so you can change:
tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))
to
TmStp = convert(varbinary(8), convert(bigint, @.xTmStp)))
though I am not sure why you are doing all of the converting. if @.xTmStp is of varbinary(8) type (or rowversion/timestamp type) you can just do:
TmStp = @.xTmStp
compile [really link] question
I'm having trouble linking a simple example using the bcp functions...
The error:
****************************************
********
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
****************************************
********
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER 1
#define DSN 2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV; //Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC; //Connection Handle
main()
{
int ret; // internal return codes
RETCODE retcode; // ODBC return codes
UCHAR szDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL", //DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHAR connectionStringIn[MAXBUFLEN] = ""; //SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN]; //SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
int myType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
return EXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE
=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect( hcn, // Connection handle
NULL, // Window handle
connectionStringIn, // Input connect string
SQL_NTS, // Null-terminated string
connectionStringOut, // Address of output buffer
MAXBUFLEN, // Size of output buffer
&lengthOfConnectionStringOut, // Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle ( SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr( henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle( SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetsonNevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
--
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
--
thanks in advance,
georgejetson
compilation error while adding web reference RS05
if i try to add a web reference in my c# application to reporting services
2005 i got a compilation error.
a part of the displayed message:
--
source error:
line 4: <%@. Import Namespace="System.Xml" %> <-- is a red line
error message:
CS0234: The type or namespace name 'Xml' does not exist in the namspace
'System' (are you missing an assembly reference?
do anybody know a solution for this problem?
thx
MartinTake a look at this forum:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=10847
particularly the post from AkhilaL MSFT
Jens Konerow
Compilation error on store procedure
Hi all,
Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10
Syntax error converting the varchar value'The account type is already exist' to a column of data type int.
Here is my procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN @.message
END
BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)
--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN @.message
END
ELSE
BEGIN
COMMIT TRANSACTION
END
RETURN @.@.ROWCOUNT
END
GO
--execute the procedure
DECLARE @.message VARCHAR (40);
EXEC NewAcctTypeSP 'CDs', @.message;
I am not quite sure where I got a type converting error in my code and anyone can help me solve it?
(p.s. I want to return the @.message value to my .aspx page)
Thanks.
Marcie|||Hi Marcie,
I have get rid of the @.@.RowCount, however, I didn't get value from my @.message, it just returned 'undefined'.
do u know why, and how can i fix it?
Thanks.|||Where is it undefined, from your code or still running your test proc?
Marcie|||
when I executed my .aspx page, I input a duplicated value in a field intentionally and since it should return an error message that I have defined in my dtore procedure, however, I just got 'undefined' instead of my error message...
so, here is my .aspx code:
public function SubmitClick (sender:Object, e:EventArgs) : void
{
if (Page.IsValid)
{
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));
var acctTypeDA : SqlDataAdapter = new SqlDataAdapter ("select * from AcctTypeCatalog", myConnection);
acctTypeDA.InsertCommand = new SqlCommand("NewAcctType", myConnection);
acctTypeDA.InsertCommand.CommandType = CommandType.StoredProcedure;
acctTypeDA.InsertCommand.Parameters.Add(new SqlParameter("@.acctType", SqlDbType.VarChar, 20)).Value = accountType.Text;
var myParm : SqlParameter = acctTypeDA.InsertCommand.Parameters.Add("@.message", SqlDbType.VarChar, 40);
myParm.Direction = ParameterDirection.Output;
<%--try to get value from @.message and assign it to the variable --%>
var msg : String = acctTypeDA.InsertCommand.Parameters("@.message").Value;
msgLabel.Text = msg;
BindGrid();
}
else
{
msgLabel.Text = "The Page contains error!"
}
}
my store procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN
END
BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)
--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
SET @.message = 'Insertion Successful!'
COMMIT TRANSACTION
END
RETURN
END
GO
I have tested my procedure, it works fine in SQL server, however, don't know why I couldn't get the value from @.message on my .aspx page.
Any idea?
Thanks
Marcie|||hi Marcie,
I used JScript to create my page with using the store procedure. (JScipt is very similar with C#)
I have reviewed the tutorial from the ASP.net and it doesn't have any ExecuteNonQuery statement on the following example:
<script language="JScript" runat="server">
publicfunction GetEmployees_Click(sender : Object, e : EventArgs) :void
{
var myConnection : SqlConnection =new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("NWString"));
var myCommand : SqlDataAdapter =new SqlDataAdapter("SalesByCategory", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.CategoryName", SqlDbType.NVarChar, 15));
myCommand.SelectCommand.Parameters("@.CategoryName").Value = SelectCategory.Value;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.OrdYear", SqlDbType.NVarChar, 4));
myCommand.SelectCommand.Parameters("@.OrdYear").Value = SelectYear.Value;
var ds : DataSet =new DataSet();
myCommand.Fill(ds, "Sales");
MyDataGrid.DataSource=ds.Tables("Sales").DefaultView;
MyDataGrid.DataBind();
}
</script>
So, should I use the ExecuteNonQuery statement to get my output parameter? any example you could show me for getting back the output parameter value with using store procedure(it's ok if that's written in VB or C#)?
i have tried to find some material about this problem, but can't get any of it ...
Thanks again.
|||In the example you show here, the Command gets executed in the myCommand.Fill line, your code didn't have anything like that.
Since you have your command object set up as the InsertCommand of a DataAdapter object, I believe your command would automatically fire when the .Update method is called.
Marcie|||The problem is that return parameters in T-SQL arealways integers. That is whay you got the compilation error, and why you are not getting the value you expect in your client-side code.
Even though you have @.message defined as an output parameter, the RETURN @.message is causing you to error out.|||
Thanks pjmcb,
I already got my problem fixed.
compilation error
While compiling my ADF I am getting the foll. error:
Notification Services could not obtain an identifier needed to construct a Transact-SQL statement
I am trying to create an scheduled subscription with a user-defined rule. I followed the instructions on Shyam Pather's book on SQLNS and was able to create the event-trigerred subscription with user-defined logic. But when I tries to extend this to a scheduled subscription I got this error.
I have searched the net for this but could find no other info on this error?
Any idea what this could mean?
Thanks
DK
Detailed Error:
===================================
An attempt to run a Transact-SQL CREATE PROCEDURE statement failed.
Instance Name: ContentMonitorAlerts
Application Name: ContentAlerts
Stored Procedure Name: NSFireDeveloperDefined%RuleId%
SQL Command Text:
DECLARE @.ruleId INT
SELECT @.ruleId=RuleId
FROM [ContentMonitorAlerts].[ContentAlerts].[NSRules]
WHERE RuleName=N'MatchNewContentsForSubscription'
IF @.ruleId IS NULL
BEGIN
RAISERROR (N'9714: Notification Services could not obtain an identifier needed to construct a Transact-SQL statement.', 16, 1)
RETURN
END
DECLARE @.commandText NVARCHAR(MAX)
SET @.commandText = N'
CREATE PROCEDURE [ContentAlerts].[NSFireDeveloperDefined' + CAST(@.ruleId AS NVARCHAR(MAX)) + N']
AS
BEGIN
I noticed that the error says there was an error while running NSFireDeveloperDefined%RuleId%
I am not sure why NS is trying to run that SP - since I am creating a user-defined rule and not a developer defined rule.
Any light on this issue would be greatly appreciated.
Thanks
DK
I solved it by making some changes to the ADF file.
Thanks
|||What changes you have done to resolve your problem ?