@@ERROR & CREATE TABLE

Hi

I've been looking at scripting some create tables, but want to know if the table created successfully. I've been doing the following:

DECLARE @.ERRCODE INT

CREATE TABLE x
SET @.ERRCODE = @.@.ERROR

This works ok, if there is no erroor. However if I run this again, then obviously I get the error "This object already exists" and the script stops executing.

Is there a way that I can capture the error using @.@.ERROR and still let the script run ?

Thanks in advance

MicksterWell, you shouldn't. "On Error Resume Next" is not present in T-SQL. What you should do is perform a validation for presence of object before attempting to create it.if objectproperty(object_id('dbo.x'), 'isusertable')=1 drop table dbo.x

create table dbo.x (f1 int, ...)|||Thanks, but I already understand that I should check for the existance of the object - as stated in my last mail. I want to check the table is being created correctly incase of other events, like permissions, file full, etc.

I created a store procedure in a database that is located
on my machine. i want to execute that store procedure on
another machine in which i have a linked server. Can
someone tell me how to execute it
ex: insert into #tmp openquery (link_server_name, 'exec
xpto')
help
You have a good subject for this post
Try
EXEC sp_serveroption 'Server', 'Data Access', TRUE
GO
SELECT *
INTO tbl
FROM OPENQUERY('Server', 'EXEC usp') ;
If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF while calling the procedure like:
SELECT * INTO tbl
FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
> I created a store procedure in a database that is located
> on my machine. i want to execute that store procedure on
> another machine in which i have a linked server. Can
> someone tell me how to execute it
> ex: insert into #tmp openquery (link_server_name, 'exec
> xpto')
|||Uri,
Doesn't work because he is still trying to execute the
procedure as if he is located on the other server and is
not. I want a query that will able me to execute a store
procedure on my machine on a remote server that doesnt
have that store procedure
>--Original Message--
>help
>You have a good subject for this post
>Try
>EXEC sp_serveroption 'Server', 'Data Access', TRUE
>GO
>SELECT *
> INTO tbl
> FROM OPENQUERY('Server', 'EXEC usp') ;
>If you have #temp tables used in your stored procedure,
you will have to use
>SET FMTONLY OFF while calling the procedure like:
>SELECT * INTO tbl
> FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
>
>"help" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
located[vbcol=seagreen]
on
>
>.
>
|||Hi
Here is goes...
CREATE PROC spMyproc
AS
SELECT * FROM SERVER.DATABASENAME.DBO.TABLENAME
GO
EXEC spMyproc
GO
DROP PROC spMyproc
<anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c452f0$e53f3510$a001280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> Doesn't work because he is still trying to execute the
> procedure as if he is located on the other server and is
> not. I want a query that will able me to execute a store
> procedure on my machine on a remote server that doesnt
> have that store procedure
> you will have to use
> message
> located
> on

I created a store procedure in a database that is located
on my machine. i want to execute that store procedure on
another machine in which i have a linked server. Can
someone tell me how to execute it
ex: insert into #tmp openquery (link_server_name, 'exec
xpto')help
You have a good subject for this post
Try
EXEC sp_serveroption 'Server', 'Data Access', TRUE
GO
SELECT *
INTO tbl
FROM OPENQUERY('Server', 'EXEC usp') ;
If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF while calling the procedure like:
SELECT * INTO tbl
FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca6d01c452e8$ee0594d0$a501280a@.phx
.gbl...
> I created a store procedure in a database that is located
> on my machine. i want to execute that store procedure on
> another machine in which i have a linked server. Can
> someone tell me how to execute it
> ex: insert into #tmp openquery (link_server_name, 'exec
> xpto')|||Uri,
Doesn't work because he is still trying to execute the
procedure as if he is located on the other server and is
not. I want a query that will able me to execute a store
procedure on my machine on a remote server that doesnt
have that store procedure
>--Original Message--
>help
>You have a good subject for this post
>Try
>EXEC sp_serveroption 'Server', 'Data Access', TRUE
>GO
>SELECT *
> INTO tbl
> FROM OPENQUERY('Server', 'EXEC usp') ;
>If you have #temp tables used in your stored procedure,
you will have to use
>SET FMTONLY OFF while calling the procedure like:
>SELECT * INTO tbl
> FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
>
>"help" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1ca6d01c452e8$ee0594d0$a501280a@.phx
.gbl...
located[vbcol=seagreen]
on[vbcol=seagreen]
>
>.
>|||Hi
Here is goes...
CREATE PROC spMyproc
AS
SELECT * FROM SERVER.DATABASENAME.DBO.TABLENAME
GO
EXEC spMyproc
GO
DROP PROC spMyproc
<anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c452f0$e53f3510$a001280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> Doesn't work because he is still trying to execute the
> procedure as if he is located on the other server and is
> not. I want a query that will able me to execute a store
> procedure on my machine on a remote server that doesnt
> have that store procedure
> you will have to use
> message
> located
> on

I created a store procedure in a database that is located
on my machine. i want to execute that store procedure on
another machine in which i have a linked server. Can
someone tell me how to execute it
ex: insert into #tmp openquery (link_server_name, 'exec
xpto')help
You have a good subject for this post
Try
EXEC sp_serveroption 'Server', 'Data Access', TRUE
GO
SELECT *
INTO tbl
FROM OPENQUERY('Server', 'EXEC usp') ;
If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF while calling the procedure like:
SELECT * INTO tbl
FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
> I created a store procedure in a database that is located
> on my machine. i want to execute that store procedure on
> another machine in which i have a linked server. Can
> someone tell me how to execute it
> ex: insert into #tmp openquery (link_server_name, 'exec
> xpto')|||Uri,
Doesn't work because he is still trying to execute the
procedure as if he is located on the other server and is
not. I want a query that will able me to execute a store
procedure on my machine on a remote server that doesnt
have that store procedure
>--Original Message--
>help
>You have a good subject for this post
>Try
>EXEC sp_serveroption 'Server', 'Data Access', TRUE
>GO
>SELECT *
> INTO tbl
> FROM OPENQUERY('Server', 'EXEC usp') ;
>If you have #temp tables used in your stored procedure,
you will have to use
>SET FMTONLY OFF while calling the procedure like:
>SELECT * INTO tbl
> FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
>
>"help" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
>> I created a store procedure in a database that is
located
>> on my machine. i want to execute that store procedure
on
>> another machine in which i have a linked server. Can
>> someone tell me how to execute it
>> ex: insert into #tmp openquery (link_server_name, 'exec
>> xpto')
>
>.
>|||Hi
Here is goes...
CREATE PROC spMyproc
AS
SELECT * FROM SERVER.DATABASENAME.DBO.TABLENAME
GO
EXEC spMyproc
GO
DROP PROC spMyproc
<anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c452f0$e53f3510$a001280a@.phx.gbl...
> Uri,
> Doesn't work because he is still trying to execute the
> procedure as if he is located on the other server and is
> not. I want a query that will able me to execute a store
> procedure on my machine on a remote server that doesnt
> have that store procedure
> >--Original Message--
> >help
> >You have a good subject for this post
> >Try
> >EXEC sp_serveroption 'Server', 'Data Access', TRUE
> >GO
> >SELECT *
> > INTO tbl
> > FROM OPENQUERY('Server', 'EXEC usp') ;
> >
> >If you have #temp tables used in your stored procedure,
> you will have to use
> >SET FMTONLY OFF while calling the procedure like:
> >
> >SELECT * INTO tbl
> > FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
> >
> >
> >
> >"help" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
> >> I created a store procedure in a database that is
> located
> >> on my machine. i want to execute that store procedure
> on
> >> another machine in which i have a linked server. Can
> >> someone tell me how to execute it
> >>
> >> ex: insert into #tmp openquery (link_server_name, 'exec
> >> xpto')
> >
> >
> >.
> >

:. DataSet to Reporting Service

Hi to ev'dy:
I need to pass a DataSet created in a WebForm to a Reporting Service, Cos'
my DataSet is Modeled like I want to see in the report.
My question is: How i can to pass this DataSet to Reporting Service and
this reports interact whith it?
Thanks in advance.
José AbadYou have two options. You can write a data processing extension
(non-trivial) or you can use the new VS 2005 webform control in local mode.
In local mode you give it the dataset and the report and the rendering is
done with the control. No server needed or allowed if in local mode. For
anything other than a basic report this is also non-trivial.
Third option is to not do this. Create a stored procedure and call that from
RS. RS is not architected to easily do what you want. Or, if this is a hard
and fast requirement then consider looking at other reporting tools.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"José Abad" <JosAbad@.discussions.microsoft.com> wrote in message
news:246AADF0-9D54-4648-8A00-68E81698BE61@.microsoft.com...
> Hi to ev'dy:
> I need to pass a DataSet created in a WebForm to a Reporting Service, Cos'
> my DataSet is Modeled like I want to see in the report.
> My question is: How i can to pass this DataSet to Reporting Service and
> this reports interact whith it?
>
> Thanks in advance.
> José Abad
>

.Net framework 1.0, SQL ce 2.0 and Windows mobile 5.0 work together?

Hi,

Does anyone know if I have created a replication using SQL Server 2000 (sp3a), .Netframework 1.0 with Pocket pc Sql Server CE 2.0 will work or not in pocket pc windows mobile 5.0 environment (with the .netframework for pocket pc(netcf.core.ppc3.arm.cab) and sql ce file (sqlce.ppc3.arm.CAB) installed in pocket pc)?

I have it working in pocket pc Windows mobile 2003 version. But, after I tried to install the exact same cab files and system into Windows mobile 5.0, the replication fail.

Anyone has any idea?

Hi AngelaC,

Same CABs do not work for both PPC 2003 and PPC 2005.

SQL CE 2.0 Cabs for PPC 2005 are shipped with VS 2005.

Also, SQL Mobile 3.0 is already relased and I am still wondering why you are not upgrading to this version when making a move to PPC 2005.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Ev, Microsoft Corporation

|||

Thanks Laxmi,

I will try to give it a try with SQL CE 2.0 Cabs from VS 2005 and let everyone knows if it works.

The reason I am not using SQL Mobile 3.0 is that the program has already been used in production for a year, I guess we won't modify it unless is necessary.

|||

Hi Laxmi,

I have installed VS5.0 and don't know which CAB to install. Following is my current environment that is working in production:

SQL Server 2000 with sp3a

Program in VS 2003 with .Net framework 1.0

For mobile: in Windows mobile 2003, installed "sqlce.ppc3.arm.cab" and "netcf.core.ppc3.arm.cab".

My question is which cab files (for both framework and sql ce) in VS2005 should I install in Windows mobile 5.0 to replace the 2 cab files I installed in Windows mobile 2003?

Thanks

.Net Data Provider for Teradata

Does the .net data provider for Teradata work the same way as the ole db provider for Teradata? I have created a data source using the .net provider and verified the connection to Teradata but when I go through the data source view wizard it will not bring back a list of tables/views and appears to just sit idle.

Thanks.

I experienced the same problem with a self-developed .NET provider for

another

database while creating a data source view in BI Development

Studio:

A connection to the database is established without any problems. Then the

application just queries the provider's data source information table and that's all.

It does not even try to query the tables in the database.

Is this a known bug in SSAS 2005?|||

I also found this where the tables/views were not shown.

If you click Next to accept an empty Data Source View screen I found that you can then add tables/view by right-click on the DSV screen and click on 'New Named Query'. Name the table and add you SQL to extract from the Teradata table (you can click the small arrow icon to run the query or click OK to return to the DSV and then use 'Explore Data'.

I have used this as a workaround until someone comes up with a better answer.

.Net Data Provider for Teradata

Does the .net data provider for Teradata work the same way as the ole db provider for Teradata? I have created a data source using the .net provider and verified the connection to Teradata but when I go through the data source view wizard it will not bring back a list of tables/views and appears to just sit idle.

Thanks.

I experienced the same problem with a self-developed .NET provider for another
database while creating a data source view in BI Development Studio:
A connection to the database is established without any problems. Then the
application just queries the provider's data source information table and that's all.
It does not even try to query the tables in the database.
Is this a known bug in SSAS 2005?|||

I also found this where the tables/views were not shown.

If you click Next to accept an empty Data Source View screen I found that you can then add tables/view by right-click on the DSV screen and click on 'New Named Query'. Name the table and add you SQL to extract from the Teradata table (you can click the small arrow icon to run the query or click OK to return to the DSV and then use 'Explore Data'.

I have used this as a workaround until someone comes up with a better answer.

.Net Data Provider for Teradata

Does the .net data provider for Teradata work the same way as the ole db provider for Teradata? I have created a data source using the .net provider and verified the connection to Teradata but when I go through the data source view wizard it will not bring back a list of tables/views and appears to just sit idle.

Thanks.

I experienced the same problem with a self-developed .NET provider for

another

database while creating a data source view in BI Development

Studio:

A connection to the database is established without any problems. Then the

application just queries the provider's data source information table and that's all.

It does not even try to query the tables in the database.

Is this a known bug in SSAS 2005?|||

I also found this where the tables/views were not shown.

If you click Next to accept an empty Data Source View screen I found that you can then add tables/view by right-click on the DSV screen and click on 'New Named Query'. Name the table and add you SQL to extract from the Teradata table (you can click the small arrow icon to run the query or click OK to return to the DSV and then use 'Explore Data'.

I have used this as a workaround until someone comes up with a better answer.

.MDF DataBase

I was using: Microsoft Access Database File as Data Source of my Data Connection, because my Database was created in Microsoft Access (so it has an .mdb extenstion).

But I read that SQL Server has feature-rich than Access Server, as you know SQL Server use .mdf files extension.

So,How can I create a .mdf database ??Can I convert a .mdb database to .mdf ??

Thanks

Hi,

SQL Server is more advanced than Access and you can download SQL Server 2005 Express edition for free. In order to change the exisitng MDB database to SQL Server, you need to import it in SQL Server. Merely renaming file extensions will not work as both data engines are inherently different.

Hope this helps,

Vivek

|||

Hellovivek_iit ,

Thank you for replay .. I was downloading SQL Server Express Edition on my computer ..

Ok , but can you tell me step by step how could I import the .mdb database to SQL Server.

I am waiting for your next replay .

Have a nice day.

|||

Hi,

The process is somewhat reverse in SQL Server Express Edition (as it does not have any import functionality like SQL Server 2005). First create an ODBC connection to your SQL Server express edition database. Next, open you Access database,select your Tables, right click and chooseExportfrom the menu. In the resulting window, select the Sql Server Express Edition database ODBC connection you created.

Hope this helps,

Vivek

|||

You can also use the upsizing wizard in Access... Tools> Database Utilites> Upsizing Wizard. I have used this on occasion at it seems to work ok...

|||

Hello again ,

I did it, Looooolz

Thank youvivek_iit for your help

Thank yousayitfast ,it is very easy step to flow it and fast.

Have a nice day.

* An error has occurred during report processing.

Created Report using SQL Server Business Intelligence 2005 using Visual studio 2005
MSSQL Server 2005
datasource is a filteredAppointment dbo from MS CRM 3
Shared Datasource
Report runs in visual studio 2005

deployed to ReportServer no errors
Report showing on home page in Report Server
Data source folder also on report server page

go to run report on the report server via internet explorer

getting following message

An error has occurred during report processing.

Cannot create a connection to data source 'FilteredAppointments'.

For more information about this error navigate to the report server on the local server machine, or enable remote errorsPlease can any one help!!!!!!

I am a novice to reporting services, can any one recommend a good basic to intermediate book.
have been on the Microsoft 2030A MSSQL server 2000 reporting services 4 months ago and not had a chance to use it until know

Thank you for all your time
ralph

Have you passed credentials to login in to datasource(database)..

May be it was using windows authentication previously.

Now to access it from IE you need to pass credentials to login to database.

You can store them on report manager also. even in vs2005 go to data view select (...) for data set than select (...) for dat source then go to credentials tab and set user name and password for that and re deploy that report.

|||

I have found the following book a good introduction/reference on SQL 2005 Reporting Services:

SQL Server 2005 Reporting Services Step-by-Step.

http://www.amazon.com/Microsoft-Server-2005-Reporting-Services/dp/0735622507/ref=pd_bbs_1/105-2708315-8436407?ie=UTF8&s=books&qid=1189784803&sr=8-1

Gary.

(SSAS 2K5) MDX and Translations : how does it work ?

Hi,

When you run a MDX query against an SSAS 2K5 cube that has been created with translations, which of the languages can be used for dimension names, measures, attributes etc... ?

Is it possible to use a language which is not the default language ? and how ?

thx&rgds,
Francois

Translations do NOT affect the names of objects (dimensions, measures, etc.), just their captions. MDX queries use object names so they are not affected by translations.

(Single) Subscription and security filtering

Hi,
I have a report (created in Report Builder) which has a security
filter in it (based on userid). I would like to be able to have users
subscribe to it and get only the data they ought to see. However,
since I have to save the credentials in order to render the report
automatically (in subscription) - security filtering doesn't work
right, because the users' credentials aren't use.
Is there any way I can make this work?
Thanks in advance,
R. GreenAnyone please?
On Jul 3, 7:36 pm, Ronald Green wrote:
> Hi,
> I have a report (created in Report Builder) which has a security
> filter in it (based on userid). I would like to be able to have users
> subscribe to it and get only the data they ought to see. However,
> since I have to save the credentials in order to render the report
> automatically (in subscription) - security filtering doesn't work
> right, because the users' credentials aren't use.
> Is there any way I can make this work?
> Thanks in advance,
> R. Green|||Ronald,
Did you ever get an solution for this? i am trying to do something similar.
Ryan
"Ronald Green" wrote:
> Anyone please?
> On Jul 3, 7:36 pm, Ronald Green wrote:
> > Hi,
> >
> > I have a report (created in Report Builder) which has a security
> > filter in it (based on userid). I would like to be able to have users
> > subscribe to it and get only the data they ought to see. However,
> > since I have to save the credentials in order to render the report
> > automatically (in subscription) - security filtering doesn't work
> > right, because the users' credentials aren't use.
> >
> > Is there any way I can make this work?
> >
> > Thanks in advance,
> > R. Green
>
>

(REPLICATION) - Starting synchronization from stored procedure

Hi,
How can I activate a replication from my own stored procedure and
synchronize subscribers with my merge publication?
I have created and configured everything and just need to start
synchronization from an stored procedure(on demand) instead of scheduling
the merge agent.
Thanks in advance.
AminAmin,
if you want manual synchronization, then the windows synchronization manger
may be used.
If you want it to be set off programmatically from TSQL, then sp_start_job
can be used with the merge agent's job.
If you want programmatic initialization from a program then the Merge
ActiveX control or SQLDMO can be used.
Regards,
Paul Ibison

(REPLICATION) - Starting synchronization from stored procedure

Hi,
How can I activate a replication from my own stored procedure and
synchronize subscribers with my merge publication?
I have created and configured everything and just need to start
synchronization from an stored procedure(on demand) instead of scheduling
the merge agent.
Thanks in advance.
Amin
Amin,
if you want manual synchronization, then the windows synchronization manger
may be used.
If you want it to be set off programmatically from TSQL, then sp_start_job
can be used with the merge agent's job.
If you want programmatic initialization from a program then the Merge
ActiveX control or SQLDMO can be used.
Regards,
Paul Ibison

(REPLICATION) - Starting synchronization from stored procedure

Hi,
How can I activate a replication from my own stored procedure and
synchronize subscribers with my merge publication?
I have created and configured everything and just need to start
synchronization from an stored procedure(on demand) instead of scheduling
the merge agent.
Thanks in advance.
AminAmin,
if you want manual synchronization, then the windows synchronization manger
may be used.
If you want it to be set off programmatically from TSQL, then sp_start_job
can be used with the merge agent's job.
If you want programmatic initialization from a program then the Merge
ActiveX control or SQLDMO can be used.
Regards,
Paul Ibison