ODBC connection to SQL Server

I use Business Objects XI R2 (BOXI) to report against a SQL Server 2000 database using an ODBC connection.
When I query the database using Enterprise Manager on the server I can make use of all functions available within SQL Server, yet when I access it via the ODBC connection I only get a very small subset of SQL Server functions available.

I can use the inbuilt BOXI functions, which work on the data locally, but would like the functions processed on the server instead.

Is this constraint a property of the ODBC driver, and if so are there other drivers which would expose all of the available SQL Server functions.

Any suggestions appreciated.

Hi Farengi,

When you refer to "functions available within SQL Server", do you mean stored procedures and user-defined functions? Or you mean the GUI-related features like graphic query design?

If we are talking about the stored procs and user-defined functions, there's no limitation per client. In fact, Enterprise Manager uses ODBC under the hood. :-)

However, the stored procs and functions are just database objects - they have permissions granted to them for the appropriate user. Are you using the same login/user in Enterprise Manager as well as in BOXI? In addition, don't forget that the names of the procs and functions is also related to their owner, thus dbo.usp_MyProc is different from JivkoD.usp_MyProc.

If you are referring to the GUI-related features, unfortunately they are specific to the Enterprise Manager and are not exposed to different client applications.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Jivko,

Thank you for your extensive answer.

Sorry about the delay in responding to you, but I have been attending some advanced BO courses and my question, which I may not have stated correctly, was answered.

The functions I wanted to refer to (eg. max, min, stdev, stdevp, var, varp etc.) in my question, are displayed in the Business Objects sql builder interface only if they are contained in a .prm (parameter) file. This file is supplied with a frequently used subset of sql server functions, but it can be edited and added to. All that this file does is to present a list of functions to the interface.

The functions, which don't appear in the interface, can still be used in a sql statement as long as they are valid functions.

Thanks

ODBC connection to SQL Server

I use Business Objects XI R2 (BOXI) to report against a SQL Server 2000 database using an ODBC connection.
When I query the database using Enterprise Manager on the server I can make use of all functions available within SQL Server, yet when I access it via the ODBC connection I only get a very small subset of SQL Server functions available.

I can use the inbuilt BOXI functions, which work on the data locally, but would like the functions processed on the server instead.

Is this constraint a property of the ODBC driver, and if so are there other drivers which would expose all of the available SQL Server functions.

Any suggestions appreciated.

Hi Farengi,

When you refer to "functions available within SQL Server", do you mean stored procedures and user-defined functions? Or you mean the GUI-related features like graphic query design?

If we are talking about the stored procs and user-defined functions, there's no limitation per client. In fact, Enterprise Manager uses ODBC under the hood. :-)

However, the stored procs and functions are just database objects - they have permissions granted to them for the appropriate user. Are you using the same login/user in Enterprise Manager as well as in BOXI? In addition, don't forget that the names of the procs and functions is also related to their owner, thus dbo.usp_MyProc is different from JivkoD.usp_MyProc.

If you are referring to the GUI-related features, unfortunately they are specific to the Enterprise Manager and are not exposed to different client applications.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Jivko,

Thank you for your extensive answer.

Sorry about the delay in responding to you, but I have been attending some advanced BO courses and my question, which I may not have stated correctly, was answered.

The functions I wanted to refer to (eg. max, min, stdev, stdevp, var, varp etc.) in my question, are displayed in the Business Objects sql builder interface only if they are contained in a .prm (parameter) file. This file is supplied with a frequently used subset of sql server functions, but it can be edited and added to. All that this file does is to present a list of functions to the interface.

The functions, which don't appear in the interface, can still be used in a sql statement as long as they are valid functions.

Thanks

Objects related to DB

Hi Folks,
I suppose should exist a way to find the relationship between all external
objects (logins, dts, jobs, ...) with one database. I mean, the objects
related\attached with database.
That is because we have to take off several databases including objects
related.
Of course we have the respective documentation of the relationships but in
the past several engineers did mistakes and they missed the removement of
some object related...So, we got sql server with dirty objects.
The idea is to make a kind of script that checks all external objects
related to database before to delete it.
Thanks in advance.HI ,
You could try a documentation tool like my SQL Scribe Documentation Builder.
There a 14 day trial . Document the entire system , the documentation
details logins, jobs and other server elements as well as the schema details
http://www.geckoware.com.au/Content.aspx?Doc_id=1001
-
Greg O
SQL Server Documentation the easy way
SQL 2000, SQL 2005 the best is back
http://www.geckoware.com.au/Content.aspx?Doc_id=1001
"Jaisol" <jaisol@.QUITARMEhotmail.com> wrote in message
news:eNYZB%23WrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Hi Folks,
> I suppose should exist a way to find the relationship between all external
> objects (logins, dts, jobs, ...) with one database. I mean, the objects
> related\attached with database.
> That is because we have to take off several databases including objects
> related.
> Of course we have the respective documentation of the relationships but in
> the past several engineers did mistakes and they missed the removement of
> some object related...So, we got sql server with dirty objects.
> The idea is to make a kind of script that checks all external objects
> related to database before to delete it.
> Thanks in advance.
>

Objects related to DB

Hi Folks,
I suppose should exist a way to find the relationship between all external
objects (logins, dts, jobs, ...) with one database. I mean, the objects
related\attached with database.
That is because we have to take off several databases including objects
related.
Of course we have the respective documentation of the relationships but in
the past several engineers did mistakes and they missed the removement of
some object related...So, we got sql server with dirty objects.
The idea is to make a kind of script that checks all external objects
related to database before to delete it.
Thanks in advance.HI ,
You could try a documentation tool like my SQL Scribe Documentation Builder.
There a 14 day trial . Document the entire system , the documentation
details logins, jobs and other server elements as well as the schema details
http://www.geckoware.com.au/Content.aspx?Doc_id=1001
-
Greg O
SQL Server Documentation the easy way
SQL 2000, SQL 2005 the best is back
http://www.geckoware.com.au/Content.aspx?Doc_id=1001
"Jaisol" <jaisol@.QUITARMEhotmail.com> wrote in message
news:eNYZB%23WrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Hi Folks,
> I suppose should exist a way to find the relationship between all external
> objects (logins, dts, jobs, ...) with one database. I mean, the objects
> related\attached with database.
> That is because we have to take off several databases including objects
> related.
> Of course we have the respective documentation of the relationships but in
> the past several engineers did mistakes and they missed the removement of
> some object related...So, we got sql server with dirty objects.
> The idea is to make a kind of script that checks all external objects
> related to database before to delete it.
> Thanks in advance.
>

Objects in Procedure or Data Cache

I am having some performance issues on my server and wish to track down the particular objects responsible.
Is there any way of determining which objects are in memory?
Many thanks.
Dontable "syscacheobjects" in master database, for cache usage.
setting up a trace in SQL Profiler is also useful

des|||Thanks Des,
It provides some of what I am looking for. I don't seem to find objects of type 'user table' being generated. My SQL Server reports to be using something like 1.4 GB memory from Sysmon. I was hoping that syscacheobjects will shed like on the objects in memory. However, I will continue with the profiler.

Many thanks once more.

Don

Originally posted by DesmondX
table "syscacheobjects" in master database, for cache usage.
setting up a trace in SQL Profiler is also useful

des|||an easy way to keep unnecessary stuff out of memory is to run a scheduled "checkpoint" command on each database every few hours, to flush the memory to disk...

good luck sorting it out...
des

Objects in precedence constraints?

Is it possible to use objects in precedence constraints? For example, I have a collection object that may or may not be populated in a script at some point and want to be able to execute different paths depending upon whether the Count property equals zero or not.

I realize I could very easily just create another int variable and populate it accordingly, but I am just curious about this. I couldn't get it to work, but then I am really just starting out here and just not sure if I am doing it wrong.

Thanks!

Brandon

Brandon,

No, its not possible to do that. The expression language doesn't have that capability. You will have to use a script task I'm afraid.

-Jamie

Objects dissappearing from SQL Server database

I wasn't sure if I was going mad or if something strange was happening with
my SQL Server 2005. I was convinced that changes I had made to
tables/views/procs etc were 'dissappearing' from time to time.
I have just written (couple of days ago) a ddl trigger that audits ddl
changes to an audit table.
This morning I looked at my database and noticed that a couple of objects
that I create yesterday were no longer in my db. I checked in my ddl audit
table to find that I had created the objects and I wasn't going mad.
Could someone advise me on how to find out what is going on.
Terry,
in Management Studio you can view a report "Schema Changes History"
which shows you which objects have been created, altered or dropped
and by which user. You need to select your server name and then either
go to the reports tab of the Summary page (pre SP2) or right click the
server and go to standard reports (SP2).
Markus

Objects dissappearing from SQL Server database

I wasn't sure if I was going mad or if something strange was happening with
my SQL Server 2005. I was convinced that changes I had made to
tables/views/procs etc were 'dissappearing' from time to time.
I have just written (couple of days ago) a ddl trigger that audits ddl
changes to an audit table.
This morning I looked at my database and noticed that a couple of objects
that I create yesterday were no longer in my db. I checked in my ddl audit
table to find that I had created the objects and I wasn't going mad.
Could someone advise me on how to find out what is going on.Terry,
in Management Studio you can view a report "Schema Changes History"
which shows you which objects have been created, altered or dropped
and by which user. You need to select your server name and then either
go to the reports tab of the Summary page (pre SP2) or right click the
server and go to standard reports (SP2).
Markus

Objects dissappearing from SQL Server database

I wasn't sure if I was going mad or if something strange was happening with
my SQL Server 2005. I was convinced that changes I had made to
tables/views/procs etc were 'dissappearing' from time to time.
I have just written (couple of days ago) a ddl trigger that audits ddl
changes to an audit table.
This morning I looked at my database and noticed that a couple of objects
that I create yesterday were no longer in my db. I checked in my ddl audit
table to find that I had created the objects and I wasn't going mad.
Could someone advise me on how to find out what is going on.Terry,
in Management Studio you can view a report "Schema Changes History"
which shows you which objects have been created, altered or dropped
and by which user. You need to select your server name and then either
go to the reports tab of the Summary page (pre SP2) or right click the
server and go to standard reports (SP2).
Markus

Objects cannot be created by using Transact-SQL statement

I can create a database or table or schema by using the (right click) context menu. This method is successful.

When I use the Transact-SQL statement (query window) to create an object, after being executed the reply is: command(s) completed successfully. However, the object does not get created.

Has anyone else had this issue? Any ideas?

We are using the MSSQL 2005 Enterprise server eval edition.

Thanks for your help.

Did you try to create an object in the schema and check again ? :-)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Please check the Database name (Available Database - Combobox), it may creates your object on some other (or on default) database..|||Where did you create the object? What was your selected database when you ran the script?

Also, Enterprise Mgr doesn't refresh the screen. You need to refresh the screen to get new objects to show up.|||

Tom Phillips wrote:

Where did you create the object? What was your selected database when you ran the script?

Also, Enterprise Mgr doesn't refresh the screen. You need to refresh the screen to get new objects to show up.

Just to add, simply highlight the database in a management suite, right click it and choose refresh... if you have created a stored procedure or a view, highlighting the appropriate node and right clicking, choosing refresh does a good job also.

hth
Barry|||

Thank you for all responses. My problem was resolved. I realized that I had to add a local user to my server before creating the new user in MS SQL Server. After that I could grant the permission to the database. Now everything is working smoothly.

Thanks again,

Yui

Objects by date

Is there a way to pull a list of objects by modified date?
like views, procedures and so on...
Thanks,
VictorBy modified, do you mean when data in tables were modified, or structure of tables? SQL Server
doesn't keep track of when data were modified. For 2005, you can see when structure were modified in
sys.objects.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
news:e%23OmtzibGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Is there a way to pull a list of objects by modified date?
> like views, procedures and so on...
> Thanks,
> Victor
>|||I mean changes on the structure of objects.
thanks,
Victor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23K7Cx3ibGHA.3352@.TK2MSFTNGP03.phx.gbl...
> By modified, do you mean when data in tables were modified, or structure
> of tables? SQL Server doesn't keep track of when data were modified. For
> 2005, you can see when structure were modified in sys.objects.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
> news:e%23OmtzibGHA.3908@.TK2MSFTNGP02.phx.gbl...
>> Is there a way to pull a list of objects by modified date?
>> like views, procedures and so on...
>> Thanks,
>> Victor
>>
>|||Hi Victor,
Thanks for your post.
As Tibor mentioned, you could find the latest modified datetime of each sql
server object in the sys.objects catalog view.
The column modify_date includes the date when the object was last modified
by using an ALTER statement. If the object is a table or a view,
modify_date also changes when a clustered index on the table or view is
created or altered.
The column type_desc includes the object type. Example:
SQL_STORED_PROCEDURE
USER_TABLE
VIEW
Here is the article about sys.objects catelog view:
sys.objects (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms190324.aspx
Thank you for your patience and hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Objects by date

Is there a way to pull a list of objects by modified date?
like views, procedures and so on...
Thanks,
VictorBy modified, do you mean when data in tables were modified, or structure of
tables? SQL Server
doesn't keep track of when data were modified. For 2005, you can see when st
ructure were modified in
sys.objects.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
news:e%23OmtzibGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Is there a way to pull a list of objects by modified date?
> like views, procedures and so on...
> Thanks,
> Victor
>|||I mean changes on the structure of objects.
thanks,
Victor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23K7Cx3ibGHA.3352@.TK2MSFTNGP03.phx.gbl...
> By modified, do you mean when data in tables were modified, or structure
> of tables? SQL Server doesn't keep track of when data were modified. For
> 2005, you can see when structure were modified in sys.objects.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
> news:e%23OmtzibGHA.3908@.TK2MSFTNGP02.phx.gbl...
>|||Hi Victor,
Thanks for your post.
As Tibor mentioned, you could find the latest modified datetime of each sql
server object in the sys.objects catalog view.
The column modify_date includes the date when the object was last modified
by using an ALTER statement. If the object is a table or a view,
modify_date also changes when a clustered index on the table or view is
created or altered.
The column type_desc includes the object type. Example:
SQL_STORED_PROCEDURE
USER_TABLE
VIEW
Here is the article about sys.objects catelog view:
sys.objects (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms190324.aspx
Thank you for your patience and hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

objects

How to find the invalid and corrupted objects in
sql server 2000?
I tried sysindexes and sysobjects tables. Checked the
STATUS column, but didn't understand the values assigned
by Microsoft. Which number means IVALID or Corruption?> How to find the invalid and corrupted objects in
> sql server 2000?
See DBCC CHECKDB and DBCC CHECKTABLE in BOL for starters
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

objects

How can I get a list of all view, procedures, tables that has been
change/edit in the last month?
Thanks,
VictorSELECT *
FROM sys.objects
WHERE modify_date > DATEADD(month, -1, GETDATE())
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
news:OHcK$I3eGHA.1456@.TK2MSFTNGP04.phx.gbl...
> How can I get a list of all view, procedures, tables that has been
> change/edit in the last month?
> Thanks,
> Victor
>|||Thank you very much!!!
Victor
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OSJz9K3eGHA.4912@.TK2MSFTNGP05.phx.gbl...
> SELECT *
> FROM sys.objects
> WHERE modify_date > DATEADD(month, -1, GETDATE())
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
> news:OHcK$I3eGHA.1456@.TK2MSFTNGP04.phx.gbl...
>

objects

How to find the invalid and corrupted objects in
sql server 2000?
I tried sysindexes and sysobjects tables. Checked the
STATUS column, but didn't understand the values assigned
by Microsoft. Which number means IVALID or Corruption?> How to find the invalid and corrupted objects in
> sql server 2000?
See DBCC CHECKDB and DBCC CHECKTABLE in BOL for starters
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

objects

How can I get a list of all view, procedures, tables that has been
change/edit in the last month?
Thanks,
VictorSELECT *
FROM sys.objects
WHERE modify_date > DATEADD(month, -1, GETDATE())
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
news:OHcK$I3eGHA.1456@.TK2MSFTNGP04.phx.gbl...
> How can I get a list of all view, procedures, tables that has been
> change/edit in the last month?
> Thanks,
> Victor
>|||Thank you very much!!!
Victor
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OSJz9K3eGHA.4912@.TK2MSFTNGP05.phx.gbl...
> SELECT *
> FROM sys.objects
> WHERE modify_date > DATEADD(month, -1, GETDATE())
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Victor Rodriguez" <lasalsatx@.newsgroups.nospam> wrote in message
> news:OHcK$I3eGHA.1456@.TK2MSFTNGP04.phx.gbl...
>> How can I get a list of all view, procedures, tables that has been
>> change/edit in the last month?
>> Thanks,
>> Victor
>>
>