creative ideas on problem needed

Hello! I am looking for creative ideas to the following situation.
Currently I receive multiple .dbf files coming in from multiple locations at
the end of the day. I then use DTS to bring those files into their
appropriate tables in a sql database.
However, now we would like to get the information real time from these
multiple locations that are on our WAN. So there are multiple remote.db
databases sitting on different workstations on the WAN that we would like to
get data from and pull back to our central MS Sql 2000 database. It can pol
l
every 15 minutes, every time an update hits remote.db, or every
hour...Doesn't matter, we just don't want to wait until the end of the day.
We can easily install an application on the remote workstations that have
remote.db on.
Any high level ideas are appreciated! Using any technologies...
Thanks, AshleyTAshley,
Have you looked in to the Replication capabilities of SQL Server?
Barry|||Why don't you use replication?|||Can I get the data from a Sybase Adaptive SqlAnywhere databases acrosse the
WAN?
Thank you,
"Alexander Kuznetsov" wrote:

> Why don't you use replication?
>|||>> we would like to get the information real time from these
multiple locations that are on our WAN. <<
A 15 minute cycle is not real time. Have you looked at products that
are designed for streaming data? Stonebraker's Streambase, Kx Systems,
etc. are built to handle things like stock market feeds in real time.

creation of table that type of format........

hello all
i want to create a phone table and it contains two fields empid ,ph.
the phone table following format:

Phone table
-------------
empid ph
-- ----------
office Mobile home
--- --- ---
100 9380768532 98455555 98822213

--------------

i want above type of format and then how to insert into values that
phone table . please help me.surya (suryaitha@.gmail.com) writes:
> i want to create a phone table and it contains two fields empid ,ph.
> the phone table following format:
> Phone table
> -------------
> empid ph
> -- ----------
> office Mobile home
> --- --- ---
> 100 9380768532 98455555 98822213
> --------------
> i want above type of format and then how to insert into values that
> phone table . please help me.

The INSERT statement would be:

INSERT phonetable (empid, office, mobile, home)
VALUES (100, '9380768532', '98455555', '98822213')

If you are using some application environment, it is not unlikely that
the Client API offers some interface that constructs the INSERT statement
under the covers, but gives you a "nicer" interface.

The table design as such is not unquestionable. You have indicated
that office, modbile and home are infact subfields of ph, but there
is no such thing in a database table.

Depending on your requirements, it may be better to do:

CREATE TABLE phonetypes
(phonetype char(3) NOT NULL,
phonetypename varchar(20) NOT NULL,
CONSTRAINT pk_phonetypes PRIMARY KEY(phonetypeid))

INSERT phonetypes (phonetypes, phonetypename)
VALUES ('OFC', 'Office')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('MOB', 'Mobile')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('HOME', 'Home')

CREATE TABLE phonenumbers
(empid int NOT NULL,
phoneno varchar(200) NOT NULL,
phonetype char(3) NOT NULL,
isdefault bit NOT NULL,
CONSTRAINT pk_phonenumbers PRIMARY KEY(empid, phoneno),
CONSTRAINT fk_phonetype FORIEGN KEY (phonetype)
REFERENCES phonetypes (phonetype),
CONSTRAINT fk_employees (empid)
REFERENCES employees(empid))

There would be a trigger on phonenumbers, that enforces that isdefault may
be 1 for at most one combination of (empid, phoneno).

This design buys you more flexibility. Some people have more than one mobile
phone. It also permits you to add other telephone types such as FAX or
IP telephone like Skype. Since on services like Skype you don't have
traditional telephone numbers, but, as I understand it, something that looks
more like an email address, I've made phoneno varchar(200).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"surya" <suryaitha@.gmail.com> wrote in news:1144477944.400250.34550
@.z34g2000cwc.googlegroups.com:

> hello all
> i want to create a phone table and it contains two fields empid ,ph.
> the phone table following format:

Aren't you going to fail your class if you don't
do your own homework?

Creation of aligned partitioned indexes

Hi All,

In the manual I find the following comment for creating indexes.

"If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table."

We are creating some dimensional models in SQL Server with about 100M rows in the largest fact tables.

What I have created are base tables which were on 'primary' and then created a clustered index over a partitioning scheme. I did this as I figured the data will go into the clustered index in any case.

However, when I then create indexes on these tables they do not look like they are petitioned....at least not as far as I can tell.

Q1. Is there some way to tell if the index was partitioned properly?

Q2. If the index is not partitioned is there any simply way to alter the table to the partitioning scheme? My reading of the manual tells me I have to unload, truncate, alter and then reload the table. Is there a better way?

Thanks in advance for your assistance.

Best Regards

Peter

Hi All,

since I did the work to figure this out I thought I would share.....

If you go into the sql studio and look at the properties of the index you can see if it is partitions and if the partitioning column is there.

There is no way I have found to alter the table to the partitioning scheme. However, if you create the index and make the partitioning column the first element of the index and use the partitioning scheme for the table it will have exactly the same effect as partitioning the table and then providing no on clause in the create index.

So you have a choice of specifiying the on clause in the index create or letting sql server default and do this for you......I guess it is a personal opinion as to whether one wants explicit control or default control over creating indexes.....also, even if the table is partitioned if you use an on clause in the index you can partition the index and place it onto the file groups you want to....

Oh the joy of setting up partitioned indexes etc.. :-)

Peter

|||

And now I have one more question on this topic....

In oracle it is possible to partition indexes and have them be 'local' in that they are only related to the partition to which they point.

The reason for doing this is when loading the data the local bit mapped indexes are dropped and once the days transactions/summaries are loaded the local bit mapped indexes can be put back...so you are only every rebuilding the indexes for the data that changed and it is only one partition that you are pointing to for that bit map rebuild...this is is really handy because of the cost of index rebuilds. So you can easily put a few hundred million rows into a partition with little difficulty when doing it this way...

I wondered if SQL Sever had the abiity yet to remove just the portion of a partitioned index pointing to the current partition to have rows inserted/updated and then allowed that index to be rebuilt or updated after updates table place...What I want to avoid is the dropping and recreation of all the query indexes on the table because that would rebuild the index for all partitions and not just the one...

Can this be done? Can anyone point me to the relevant documentation on how to do this?

Thanks

Peter

|||

Hi All,

as we move forward with testing we had one other question we thought someone here might have seen before we test it out ourselves.

We have created aligned partitioning indexes for each table with each index containing the partitioning column and one dimension table key.

We have placed these indexes onto the same partitioning scheme as the table and therefore the same file groups and the table inside the clustered index.

We suspect that performance will be improved if we place the indexes onto their own file groups....but we wonder 'by how much' and 'is it worth it'.

Has anyone else out there performed testing on partitioned tables and aligned indexes both on the same file groups and on different file groups and have an idea of the difference in performance?

Thanks

Peter

Creating Views

I get the following error when i create views in a batch
using 'sql query analyser' as I want to create all the
views at one go. Is there solution.
Server: Msg 156, Level 15, State 1, Procedure View1, Line
11
Incorrect syntax near the keyword 'CREATE'.
Server: Msg 111, Level 15, State 1, Procedure View2, Line
11
'CREATE VIEW' must be the first statement in a query batch.
Make sure the=20
GO
keyword appears between each statement
CREATE VIEW <name>=20
AS
SELECT...
GO
CREATE VIEW <name>=20
AS
SELECT...
GO
--=20
Keith
"BILLI98" <billi98@.hotmail.com> wrote in message =
news:423401c42b7e$8b4560f0$a401280a@.phx.gbl...
> I get the following error when i create views in a batch=20
> using 'sql query analyser' as I want to create all the=20
> views at one go. Is there solution.
>=20
>=20
> Server: Msg 156, Level 15, State 1, Procedure View1, Line=20
> 11
> Incorrect syntax near the keyword 'CREATE'.
> Server: Msg 111, Level 15, State 1, Procedure View2, Line=20
> 11
> 'CREATE VIEW' must be the first statement in a query batch.
>=20
>=20
>
|||On Mon, 26 Apr 2004 04:06:37 -0700, BILLI98 wrote:

>I get the following error when i create views in a batch
>using 'sql query analyser' as I want to create all the
>views at one go. Is there solution.
>
>Server: Msg 156, Level 15, State 1, Procedure View1, Line
>11
>Incorrect syntax near the keyword 'CREATE'.
>Server: Msg 111, Level 15, State 1, Procedure View2, Line
>11
>'CREATE VIEW' must be the first statement in a query batch.
Just put in a "go" before each CREATE VIEW. Make sure you put GO at
the beginning of the line. Like this:
CREATE VIEW TestView
AS SELECT au_id FROM authors
GO
CREATE VIEW TestView2
AS SELECT au_lname FROM authors
GO
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating Views

I get the following error when i create views in a batch
using 'sql query analyser' as I want to create all the
views at one go. Is there solution.
Server: Msg 156, Level 15, State 1, Procedure View1, Line
11
Incorrect syntax near the keyword 'CREATE'.
Server: Msg 111, Level 15, State 1, Procedure View2, Line
11
'CREATE VIEW' must be the first statement in a query batch.Make sure the GO
keyword appears between each statement
CREATE VIEW <name> AS
SELECT...
GO
CREATE VIEW <name> AS
SELECT...
GO
-- Keith
"BILLI98" <billi98@.hotmail.com> wrote in message =news:423401c42b7e$8b4560f0$a401280a@.phx.gbl...
> I get the following error when i create views in a batch > using 'sql query analyser' as I want to create all the > views at one go. Is there solution.
> > > Server: Msg 156, Level 15, State 1, Procedure View1, Line > 11
> Incorrect syntax near the keyword 'CREATE'.
> Server: Msg 111, Level 15, State 1, Procedure View2, Line > 11
> 'CREATE VIEW' must be the first statement in a query batch.
> > >|||On Mon, 26 Apr 2004 04:06:37 -0700, BILLI98 wrote:
>I get the following error when i create views in a batch
>using 'sql query analyser' as I want to create all the
>views at one go. Is there solution.
>
>Server: Msg 156, Level 15, State 1, Procedure View1, Line
>11
>Incorrect syntax near the keyword 'CREATE'.
>Server: Msg 111, Level 15, State 1, Procedure View2, Line
>11
>'CREATE VIEW' must be the first statement in a query batch.
Just put in a "go" before each CREATE VIEW. Make sure you put GO at
the beginning of the line. Like this:
CREATE VIEW TestView
AS SELECT au_id FROM authors
GO
CREATE VIEW TestView2
AS SELECT au_lname FROM authors
GO
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating Views

I get the following error when i create views in a batch
using 'sql query analyser' as I want to create all the
views at one go. Is there solution.
Server: Msg 156, Level 15, State 1, Procedure View1, Line
11
Incorrect syntax near the keyword 'CREATE'.
Server: Msg 111, Level 15, State 1, Procedure View2, Line
11
'CREATE VIEW' must be the first statement in a query batch.Make sure the=20
GO
keyword appears between each statement
CREATE VIEW <name>=20
AS
SELECT...
GO
CREATE VIEW <name>=20
AS
SELECT...
GO
--=20
Keith
"BILLI98" <billi98@.hotmail.com> wrote in message =
news:423401c42b7e$8b4560f0$a401280a@.phx.gbl...
> I get the following error when i create views in a batch=20
> using 'sql query analyser' as I want to create all the=20
> views at one go. Is there solution.
>=20
>=20
> Server: Msg 156, Level 15, State 1, Procedure View1, Line=20
> 11
> Incorrect syntax near the keyword 'CREATE'.
> Server: Msg 111, Level 15, State 1, Procedure View2, Line=20
> 11
> 'CREATE VIEW' must be the first statement in a query batch.
>=20
>=20
>|||On Mon, 26 Apr 2004 04:06:37 -0700, BILLI98 wrote:

>I get the following error when i create views in a batch
>using 'sql query analyser' as I want to create all the
>views at one go. Is there solution.
>
>Server: Msg 156, Level 15, State 1, Procedure View1, Line
>11
>Incorrect syntax near the keyword 'CREATE'.
>Server: Msg 111, Level 15, State 1, Procedure View2, Line
>11
>'CREATE VIEW' must be the first statement in a query batch.
Just put in a "go" before each CREATE VIEW. Make sure you put GO at
the beginning of the line. Like this:
CREATE VIEW TestView
AS SELECT au_id FROM authors
GO
CREATE VIEW TestView2
AS SELECT au_lname FROM authors
GO
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Creating Triggers

Can someone please help me with the following problem:

SQL> create or replace view lguidry_view as
2 select student_id, registration_date
3 from guidry_l;

Create an INSTEAD OF trigger defined on the view created above. This trigger will update the student w/ student_id of 1000 and set the registration date to August 12, 2003.

My textbook doesn't really explain how to create an INSTEAD OF trigger using the UPDATE.Based on my understanding of the below, I sense that an INSTEAD OF trigger isn't appropriate. Are you always overwriting student_id and registration_date with those values? If so, then why not a BEFORE INSERT trigger (if it is Oracle or DB2) on guidry_l table which sets the fields then completes the insert?

If this is off-track, more information will be necessary.

Originally posted by Byrd24
Can someone please help me with the following problem:

SQL> create or replace view lguidry_view as
2 select student_id, registration_date
3 from guidry_l;

Create an INSTEAD OF trigger defined on the view created above. This trigger will update the student w/ student_id of 1000 and set the registration date to August 12, 2003.

My textbook doesn't really explain how to create an INSTEAD OF trigger using the UPDATE.|||Originally posted by dmmac
Based on my understanding of the below, I sense that an INSTEAD OF trigger isn't appropriate. Are you always overwriting student_id and registration_date with those values? If so, then why not a BEFORE INSERT trigger (if it is Oracle or DB2) on guidry_l table which sets the fields then completes the insert?

If this is off-track, more information will be necessary.

I have no idea if I am always overwriting student_id. All I know is that my professor wants us to create an INSTEAD OF trigger using the view that I had created from table guidry_l :

SQL> create table guidry_l (
2 student_id number(8) not null,
3 f_name varchar2(25),
4 l_name varchar2(25),
5 address varchar2(40),
6 zip number(5),
7 phone varchar2(12),
8 registration_date date not null,
9 constraint guidry_l_pk primary key(student_id)
10 );
And update the student w/ id of 1000.|||Re-read your first post and I see what the update is about.
Ok, then here is a template (it is based on DB2, I see an 'OR REPLACE' in your view example, so if you are using Oracle you will need to figure the correct trigger structure syntax -- should be that different):

CREATE TRIGGER x INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
END

Originally posted by Byrd24
I have no idea if I am always overwriting student_id. All I know is that my professor wants us to create an INSTEAD OF trigger using the view that I had created from table guidry_l :

SQL> create table guidry_l (
2 student_id number(8) not null,
3 f_name varchar2(25),
4 l_name varchar2(25),
5 address varchar2(40),
6 zip number(5),
7 phone varchar2(12),
8 registration_date date not null,
9 constraint guidry_l_pk primary key(student_id)
10 );
And update the student w/ id of 1000.|||Okay I have tried it and I am still having problems. I'm getting the following error:

Warning: Trigger created with compilation errors.

What am I doing wrong?

Create or replace trigger updateguidry
INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
end updateguidry;
/

Originally posted by dmmac
Re-read your first post and I see what the update is about.
Ok, then here is a template (it is based on DB2, I see an 'OR REPLACE' in your view example, so if you are using Oracle you will need to figure the correct trigger structure syntax -- should be that different):

CREATE TRIGGER x INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
END|||Try putting a semi-colon at the end of the UPDATE statement.

Originally posted by Byrd24
Okay I have tried it and I am still having problems. I'm getting the following error:

Warning: Trigger created with compilation errors.

What am I doing wrong?

Create or replace trigger updateguidry
INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
end updateguidry;
/|||Okay I create my trigger a little bit differently:

SQL> CREATE OR REPLACE TRIGGER updateguidry
2 INSTEAD OF UPDATE ON lguidry_view
3 FOR EACH ROW
4 BEGIN
5 IF (:new.student_id = '1000') THEN
6 UPDATE guidry_l
7 SET registration_date = TO_DATE('12-AUG-2003','DD-MON-YYYY')
8 WHERE student_id = :new.student_id;
9 END IF;
10 END updateguidry;
11 /

Trigger created.

I finally created the trigger so now how do I get it to work?? From my understanding, I am suppose to create an update code for it to get it to work. I tried to update the info using the lguidry_view and I got an error message:

ERROR at line 1:
ORA-04098: trigger 'CIS305AD8.LGUIDRY_VIEW' is invalid and failed re-validation

I'm guessing I did the trigger right??|||Change if to IF(:new.student_id = 1000) THEN

Originally posted by Byrd24
Okay I create my trigger a little bit differently:

SQL> CREATE OR REPLACE TRIGGER updateguidry
2 INSTEAD OF UPDATE ON lguidry_view
3 FOR EACH ROW
4 BEGIN
5 IF (:new.student_id = '1000') THEN
6 UPDATE guidry_l
7 SET registration_date = TO_DATE('12-AUG-2003','DD-MON-YYYY')
8 WHERE student_id = :new.student_id;
9 END IF;
10 END updateguidry;
11 /

Trigger created.

I finally created the trigger so now how do I get it to work?? From my understanding, I am suppose to create an update code for it to get it to work. I tried to update the info using the lguidry_view and I got an error message:

ERROR at line 1:
ORA-04098: trigger 'CIS305AD8.LGUIDRY_VIEW' is invalid and failed re-validation

I'm guessing I did the trigger right??

creating the subscription via RMO

Hi

I'm creating an anonymous merge HTTPS subscription using RMO and vb.net 2005.

I get the following error on the mergePullSubscription.create call

+ InnerException {"'@.job_login' must be a valid Windows Login in the form : 'MACHINE\Login' or 'DOMAIN\Login'. Please see the documentation for 'sp_addmergepullsubscription_agent'.
Changed database context to 'brucesTest'."} System.Exception
What do I need to do to get this parameter set ?

I'm using the code from BOL to create it...

Thanks
BruceHi, Bruce:

You can specify the agent's properties by do the following:

subscription.SynchronizationAgentProcessSecurity.Login = winLogin;
subscription.SynchronizationAgentProcessSecurity.Password = winPassword;

For a completed reference and code snippet, please refer to this TOP in BOL.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/97a232ce-7171-4124-
8c30-4a4e7f991183.htm

Thanks
Yunwensql

Creating tables within my application.

I am having the following problem and any help would be GREATLY
appreciated:

In an application I am developing, at some points we create a new
table. When I create this table on another users box, I can not access
it from my box. In sql server I am dbo, but the table created by my
application when run on a different box has an owner of : "FC\xxxx". I
have sent permissions on this thing to public, but I am still getting
an error when I try to query this thing from my application. And I can
not get query analyzer to recognize this thing. I can see it in
Enterprise manager. I would think there is a way to handle this sort
of thing. If anyone out there has done anything like this I would be
much obliged for any ideas. Thanks.
Sincerely,
Ed Hawkes
olaamigoquepasa@.nospamplease-hotmail.comEd Hawkes (olaamigoquepasa@.hotmail.com) writes:
> I am having the following problem and any help would be GREATLY
> appreciated:
> In an application I am developing, at some points we create a new
> table. When I create this table on another users box, I can not access
> it from my box. In sql server I am dbo, but the table created by my
> application when run on a different box has an owner of : "FC\xxxx". I
> have sent permissions on this thing to public, but I am still getting
> an error when I try to query this thing from my application. And I can
> not get query analyzer to recognize this thing. I can see it in
> Enterprise manager. I would think there is a way to handle this sort
> of thing. If anyone out there has done anything like this I would be
> much obliged for any ideas. Thanks.

If you are logged into SQL Server as FC\ABC and this user is not dbo,
the the tables will be owned by FC\ABC. For other users to refer to
this table, they would have to say

SELECT * FROM [FC\ABC].tbl

That is they must specify the owner. If this is not desireable, you would
have to say:

CREATE TABLE dbo.tbl (...)

and the table will be owned by dbo. This may require more privledges
than you have granted today.

Generally, having users creating tables is nothing you would normally
do. Chances are, that there is some basic flaw in your design. But since
I don't know anything about your system, I may be completely wrong on
that point.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating tables from ASP.

Hi list, i wanto to use an ASP page to create tables from the devolopers
intranet.
Inside Code, i have the following conn string:
set objRS = CreateObject("ADODB.Recordset")
objcon.connectionstring = "Provider=SQLOLEDB.1;User
ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=master;Data
Source=" & server
I have one string called sqltxt that uses the Create table statement. this
is the execution code: "3704,Operation is not allowed when the object is
closed"
Whats wrong'
Thanks.
objRS.open sqltxt, objcon
response.write err.number & "," & err.description & "<p>"
response.write sqltxt
ObjRS.CLOSE
When i try to execute this, i recieve the message :As a general suggestion, consider using a ADO command object instead of a
recordset object.
Anith|||Can u tell me how?
"Anith Sen" wrote:

> As a general suggestion, consider using a ADO command object instead of a
> recordset object.
> --
> Anith
>
>|||Assuming you really wanted to do this, you should use the Execute method off
the
Connection object like so:
(ah VBScript...Forgot how joyous it was <insert sarcasm> ;-> )
Dim oConn
Dim sSQL
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = ...
oConn.Open
sSQL = "Create Table...
oConn.Execute sSQL
Set oConn = Nothing
HTH
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:1CBE6886-30B6-4FD2-995E-DFFEC08D7123@.microsoft.com...
> Hi list, i wanto to use an ASP page to create tables from the devolopers
> intranet.
> Inside Code, i have the following conn string:
> set objRS = CreateObject("ADODB.Recordset")
> objcon.connectionstring = "Provider=SQLOLEDB.1;User
> ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=master;Da
ta
> Source=" & server
> I have one string called sqltxt that uses the Create table statement. this
> is the execution code: "3704,Operation is not allowed when the object is
> closed"
> Whats wrong'
> Thanks.
> objRS.open sqltxt, objcon
> response.write err.number & "," & err.description & "<p>"
> response.write sqltxt
> ObjRS.CLOSE
> When i try to execute this, i recieve the message :|||Thanks for help Thomas, it works fine!!!
Now, i have a doubt.
When the transaction occurs, i use the err.number and err.description to see
any errors, but i coul not view anything. what do i need to see the "The
command(s) completed successfully."
Thanks again
"Thomas Coleman" wrote:

> Assuming you really wanted to do this, you should use the Execute method o
ff the
> Connection object like so:
> (ah VBScript...Forgot how joyous it was <insert sarcasm> ;-> )
> Dim oConn
> Dim sSQL
> Set oConn = CreateObject("ADODB.Connection")
> oConn.ConnectionString = ...
> oConn.Open
> sSQL = "Create Table...
> oConn.Execute sSQL
> Set oConn = Nothing
>
> HTH
>
> Thomas
>
>
> "Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
> news:1CBE6886-30B6-4FD2-995E-DFFEC08D7123@.microsoft.com...
>
>|||> When the transaction occurs, i use the err.number and err.description to seed">
> any errors, but i coul not view anything. what do i need to see the "The
> command(s) completed successfully."
From ASP? Of course not. From ADO code, you know that a command executed
successfully if an error was not thrown. If we are talking about VBScript,
you'll have to check for Err.Number right after you execute the statement li
ke
so:
sSQL = "Create Table...
oConn.Execute sSQL
If Err.Number <> 0 Then
..call error handler or do whatever in response to an error...
End If
Set oConn = Nothing
Since we're talking about the creation of the table, if the create table cal
l to
ADO fired successfully, then you should be ok to query against it or put dat
a in
it.
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:A2338E8C-02A9-48DB-936C-325B78F9E811@.microsoft.com...
> Thanks for help Thomas, it works fine!!!
> Now, i have a doubt.
> When the transaction occurs, i use the err.number and err.description to s
ee
> any errors, but i coul not view anything. what do i need to see the "The
> command(s) completed successfully."
> Thanks again
> "Thomas Coleman" wrote:
>
<snip>|||Tinchos,

> Hi list, i wanto to use an ASP page to create tables from the devolopers
> intranet.
> ...
> Whats wrong'
Although Thomas already answered your question, you should really rethink
your approach. Very little good can come from allowing tables to be created
by the application ... why are you doing this in the first place?
-- Alex Papadimoulis|||I want to offer a service to developers (only datareader and datawriters) th
e
possibility to create custom tables or execute some scripts. I think its the
best solution to have less workload for me.
"Alex Papadimoulis" wrote:

> Tinchos,
>
> Although Thomas already answered your question, you should really rethink
> your approach. Very little good can come from allowing tables to be create
d
> by the application ... why are you doing this in the first place?
> -- Alex Papadimoulis|||So, how can i parse errors like "Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'table' in the database" '
Objcon.execute sqltxt
if err.number = 0 then
Response.write "Ok...
else
response.write err.number & "," & err.description & "<p>"
end if
if i try to create the table twice i dont recieve any error messages...
Thanks again!!!!|||Firstly, the system will throw an error if you execute a Create Table statem
ent
and the table exists. You need to be mindful of the *owner* of the table.
Specifically, make sure you prefix the table name with the owner like so:
Create Table dbo.TableName
(
..
)
Otherwise, the table will be created under the user's database name. So you
might get Joe.TableName, Fred.TableName and even dbo.TableName.
Secondly, you need to look at the Errors collection on the Connection object
and
trap the specific error number associated with the a duplicate table name.
Thus..
oConn.Execute sSQL
Select Case Err.Number
Case 0
'do nothing
Case 12344 <-- place the actual error number here
..tell user there was a duplicate
Case Else
..display general error
End Select
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:AB4A1EB6-1374-43DD-9722-48BEA85AF2D5@.microsoft.com...
> So, how can i parse errors like "Server: Msg 2714, Level 16, State 6, Line
1
> There is already an object named 'table' in the database" '
> Objcon.execute sqltxt
> if err.number = 0 then
> Response.write "Ok...
> else
> response.write err.number & "," & err.description & "<p>"
> end if
> if i try to create the table twice i dont recieve any error messages...
> Thanks again!!!!

Creating table using dynamic SQL

I have the following dataset using dynamic SQL which works when i execute it, when i try to create a table using this dataset i cant see any fields. Does anyone know why dynamic SQL doesnt work ?

Declare @.TopRange int
Declare @.BottomRange int
Declare @.SQL Varchar(1000)

IF @.Param_leadage = '91+'
SET @.TopRange = 91
ELSE
Set @.TopRange = RTRIM(LEFT(REPLACE(@.Param_leadage,'-',''),2))

IF @.Param_leadage = '91+'
SET @.BottomRange = 4000
ELSE
Set @.BottomRange = LTRIM(RIGHT(REPLACE(@.Param_leadage,'-',''),2))

SET @.SQL = 'SELECT dbo.tblCustomer.idStatus, dbo.tblCustomer.idCustomer, dbo.tblCustomer.DateSigned' +
' FROM dbo.tblCustomer' +
' WHERE DateDiff(day, dbo.tblCustomer.DateSigned, GetDate()) >= ' + convert(varchar,@.TopRange) + ' AND DateDiff(day,dbo.tblCustomer.DateSigned, GetDate()) <= ' + convert(varchar,@.BottomRange)

IF @.Param_status = 'Online Churn'
SET @.SQL = @.SQL + ' AND dbo.tblCustomer.idStatus = 4 or dbo.tblCustomer.idStatus = 5 or dbo.tblCustomer.idStatus = 11'
ELSE
SET @.SQL = @.SQL + ' AND dbo.tblCustomer.idStatus = ' + @.Param_idstatus

EXEC(@.SQL)

If the Refresh Fields toolbar button enabled on the Data tab is enabled, try clicking it to get the fields. Or, plug in only the SELECT statement and hit Refresh Fields to get the fields.Then, change it to the original SQL. Finally, you can create the dataset fields manually.

|||

Hi Teo,

This isnt working, when i try to select a data field from the table textbox properties value dropdown box, it says 'Dataset1 has no fields'

When I enter it in manually it gives an error which is obviously to do with the fields not being in the scope.

In the Data screen within VSS 2005 the sql executes fine, when i refresh it prompts for parameters off which i enter and the data is returned, it is not recognising any fields when i try to access any fields from any controls..

it has something to do with the exec(sql) command and dynamic data i would suspect

I also tried doing the SQL first off which the table fields are recognised but as soon as i add the other portions such as the single quotes etc it gives the same error

Can someone please help

thanks

|||

Try putting this code in a stored proc and changing the command type to be stored proc.

The other option is to not use dynamic SQL, instead use an expression based query and construct the SQL using expression syntax e.g.

= Iif(Parameters!x.value = "91+", "Select...", "select ...")

Again, in this case I follow the previous advice of just putting in the SQL query first, hitting refresh so RS auto-generates the Filed definitions and then swapping in the expression

|||

@. is interpreted as a query parameter in the Query Designer. You can make the whole darn thing expression-based as Adam suggested or move this query to a stored procedure.

|||

thanks guys, i put it in a stored proc and it is working now

Creating Stored Procedures Through VB.NET

Hello,
I've trying to create some stored procedures through VB.Net by adding an
SQLDataAdapter, and following the wizard.
In the wizard I then choose to "Create Stored Procedures Automatically"
However, for some reason I get the following message :
"There was a problem with the INSERT stored procedure. The stored procedure
was not created."
and subsequently my Insert, Update and Delete queries are not created.
Does anyone know why I am getting this message, and how I can fix it ?
The only thing I can think of that may be causing this is the fact that I
have a computed field in my table (forename and surname concatenated).
This is also backed up by the fact that if I do the same task with just a
few fields, then it goes through fine.
I'd appreciate any help you can offer.
Thanks
Post the code and we'll see what's going on.
Cheers,
Jason Lepack
On Mar 9, 3:11 am, Jonny <J...@.discussions.microsoft.com> wrote:
> Hello,
> I've trying to create some stored procedures through VB.Net by adding an
> SQLDataAdapter, and following the wizard.
> In the wizard I then choose to "Create Stored Procedures Automatically"
> However, for some reason I get the following message :
> "There was a problem with the INSERT stored procedure. The stored procedure
> was not created."
> and subsequently my Insert, Update and Delete queries are not created.
> Does anyone know why I am getting this message, and how I can fix it ?
> The only thing I can think of that may be causing this is the fact that I
> have a computed field in my table (forename and surname concatenated).
> This is also backed up by the fact that if I do the same task with just a
> few fields, then it goes through fine.
> I'd appreciate any help you can offer.
> Thanks

Creating Stored Procedures Through VB.NET

Hello,
I've trying to create some stored procedures through VB.Net by adding an
SQLDataAdapter, and following the wizard.
In the wizard I then choose to "Create Stored Procedures Automatically"
However, for some reason I get the following message :
"There was a problem with the INSERT stored procedure. The stored procedure
was not created."
and subsequently my Insert, Update and Delete queries are not created.
Does anyone know why I am getting this message, and how I can fix it ?
The only thing I can think of that may be causing this is the fact that I
have a computed field in my table (forename and surname concatenated).
This is also backed up by the fact that if I do the same task with just a
few fields, then it goes through fine.
I'd appreciate any help you can offer.
ThanksPost the code and we'll see what's going on.
Cheers,
Jason Lepack
On Mar 9, 3:11 am, Jonny <J...@.discussions.microsoft.com> wrote:
> Hello,
> I've trying to create some stored procedures through VB.Net by adding an
> SQLDataAdapter, and following the wizard.
> In the wizard I then choose to "Create Stored Procedures Automatically"
> However, for some reason I get the following message :
> "There was a problem with the INSERT stored procedure. The stored procedur
e
> was not created."
> and subsequently my Insert, Update and Delete queries are not created.
> Does anyone know why I am getting this message, and how I can fix it ?
> The only thing I can think of that may be causing this is the fact that I
> have a computed field in my table (forename and surname concatenated).
> This is also backed up by the fact that if I do the same task with just a
> few fields, then it goes through fine.
> I'd appreciate any help you can offer.
> Thanks

Creating Stored Procedures Through VB.NET

Hello,
I've trying to create some stored procedures through VB.Net by adding an
SQLDataAdapter, and following the wizard.
In the wizard I then choose to "Create Stored Procedures Automatically"
However, for some reason I get the following message :
"There was a problem with the INSERT stored procedure. The stored procedure
was not created."
and subsequently my Insert, Update and Delete queries are not created.
Does anyone know why I am getting this message, and how I can fix it ?
The only thing I can think of that may be causing this is the fact that I
have a computed field in my table (forename and surname concatenated).
This is also backed up by the fact that if I do the same task with just a
few fields, then it goes through fine.
I'd appreciate any help you can offer.
ThanksPost the code and we'll see what's going on.
Cheers,
Jason Lepack
On Mar 9, 3:11 am, Jonny <J...@.discussions.microsoft.com> wrote:
> Hello,
> I've trying to create some stored procedures through VB.Net by adding an
> SQLDataAdapter, and following the wizard.
> In the wizard I then choose to "Create Stored Procedures Automatically"
> However, for some reason I get the following message :
> "There was a problem with the INSERT stored procedure. The stored procedure
> was not created."
> and subsequently my Insert, Update and Delete queries are not created.
> Does anyone know why I am getting this message, and how I can fix it ?
> The only thing I can think of that may be causing this is the fact that I
> have a computed field in my table (forename and surname concatenated).
> This is also backed up by the fact that if I do the same task with just a
> few fields, then it goes through fine.
> I'd appreciate any help you can offer.
> Thanks

creating reports on the fly...

Hi all
Hoping someone can help me out. I'm new to reporting services and cannot
quite seem to figure out what steps to take with the following.
1. Create reports on the fly... one of the problems I have is that I would
like to alter the rdl file before it is displayed to the user so I can
change things in it like the points below. The way I see it happening is
that I extract the rdl from the reports server... make the changes to the
file and then render this as html to display to the user.
Can anyone tell me if this would be the correct process and how to
accomplish this.
The following 2 issues would be obsolete if I can create reports on the run.
2. Alter the type of graph by passing a parameter to the report in which I
can specify whether it is ie: pie/donut/line etc.
3. I would like for my reports to be able to take an input parameter that is
not viewable on the report and is to be able to be dynamic.
Thanks
Kez.You'd have to get the RDL, change it and re-post it, then call the report as
you have said...
Regarding the bottom parameter item , you can make a parameter invisible by
simply not giving the label attribute a value... THe param can be passed in
via URL etc, but the report does not display it with the other parameters.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
> Hi all
> Hoping someone can help me out. I'm new to reporting services and cannot
> quite seem to figure out what steps to take with the following.
> 1. Create reports on the fly... one of the problems I have is that I would
> like to alter the rdl file before it is displayed to the user so I can
> change things in it like the points below. The way I see it happening is
> that I extract the rdl from the reports server... make the changes to the
> file and then render this as html to display to the user.
> Can anyone tell me if this would be the correct process and how to
> accomplish this.
>
> The following 2 issues would be obsolete if I can create reports on the
run.
> 2. Alter the type of graph by passing a parameter to the report in which
I
> can specify whether it is ie: pie/donut/line etc.
> 3. I would like for my reports to be able to take an input parameter that
is
> not viewable on the report and is to be able to be dynamic.
> Thanks
> Kez.
>|||Thanks for the feedback.
When you refer to re-posting the rdl I imagine you mean publishing it back
to the reports server. This is okay but multiple users will be accessing
the report. I only want the report rdl file to be changed for that
instance, not as a permanant thing.
I was also hoping for some direction in achieving this.
Thanks
Kez.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uYITfcTCFHA.2572@.tk2msftngp13.phx.gbl...
> You'd have to get the RDL, change it and re-post it, then call the report
> as
> you have said...
> Regarding the bottom parameter item , you can make a parameter invisible
> by
> simply not giving the label attribute a value... THe param can be passed
> in
> via URL etc, but the report does not display it with the other parameters.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
> news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
>> Hi all
>> Hoping someone can help me out. I'm new to reporting services and cannot
>> quite seem to figure out what steps to take with the following.
>> 1. Create reports on the fly... one of the problems I have is that I
>> would
>> like to alter the rdl file before it is displayed to the user so I can
>> change things in it like the points below. The way I see it happening is
>> that I extract the rdl from the reports server... make the changes to the
>> file and then render this as html to display to the user.
>> Can anyone tell me if this would be the correct process and how to
>> accomplish this.
>>
>> The following 2 issues would be obsolete if I can create reports on the
> run.
>> 2. Alter the type of graph by passing a parameter to the report in which
> I
>> can specify whether it is ie: pie/donut/line etc.
>> 3. I would like for my reports to be able to take an input parameter that
> is
>> not viewable on the report and is to be able to be dynamic.
>> Thanks
>> Kez.
>>
>|||Regarding the parameters... if I remove the prompt label... I get an error
and cannot figure it out.
The error... The properties for the currently selected item are not valid.
Please correct all errors before continuing.
Thanks again
Kez.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uYITfcTCFHA.2572@.tk2msftngp13.phx.gbl...
> You'd have to get the RDL, change it and re-post it, then call the report
> as
> you have said...
> Regarding the bottom parameter item , you can make a parameter invisible
> by
> simply not giving the label attribute a value... THe param can be passed
> in
> via URL etc, but the report does not display it with the other parameters.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
> news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
>> Hi all
>> Hoping someone can help me out. I'm new to reporting services and cannot
>> quite seem to figure out what steps to take with the following.
>> 1. Create reports on the fly... one of the problems I have is that I
>> would
>> like to alter the rdl file before it is displayed to the user so I can
>> change things in it like the points below. The way I see it happening is
>> that I extract the rdl from the reports server... make the changes to the
>> file and then render this as html to display to the user.
>> Can anyone tell me if this would be the correct process and how to
>> accomplish this.
>>
>> The following 2 issues would be obsolete if I can create reports on the
> run.
>> 2. Alter the type of graph by passing a parameter to the report in which
> I
>> can specify whether it is ie: pie/donut/line etc.
>> 3. I would like for my reports to be able to take an input parameter that
> is
>> not viewable on the report and is to be able to be dynamic.
>> Thanks
>> Kez.
>>
>|||If i understand correctly, i am doing a similar thing with a custom report
application. Basically, I have 'report template' rdl files on the server
which have no report data in them. I also have a class that collects all the
users choices in the client app. I use GetReportDefinition to get the
template rdl as xml, then use Xpath to add the custom data that the user has
chosen.
Once the new xml file is built, it is published to the server as a new
report, which the user can run.
that's a simplified description, but I hope it helps!
"Kez Bates" wrote:
> Thanks for the feedback.
> When you refer to re-posting the rdl I imagine you mean publishing it back
> to the reports server. This is okay but multiple users will be accessing
> the report. I only want the report rdl file to be changed for that
> instance, not as a permanant thing.
> I was also hoping for some direction in achieving this.
> Thanks
> Kez.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:uYITfcTCFHA.2572@.tk2msftngp13.phx.gbl...
> > You'd have to get the RDL, change it and re-post it, then call the report
> > as
> > you have said...
> >
> > Regarding the bottom parameter item , you can make a parameter invisible
> > by
> > simply not giving the label attribute a value... THe param can be passed
> > in
> > via URL etc, but the report does not display it with the other parameters.
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
> > news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
> >> Hi all
> >> Hoping someone can help me out. I'm new to reporting services and cannot
> >> quite seem to figure out what steps to take with the following.
> >>
> >> 1. Create reports on the fly... one of the problems I have is that I
> >> would
> >> like to alter the rdl file before it is displayed to the user so I can
> >> change things in it like the points below. The way I see it happening is
> >> that I extract the rdl from the reports server... make the changes to the
> >> file and then render this as html to display to the user.
> >> Can anyone tell me if this would be the correct process and how to
> >> accomplish this.
> >>
> >>
> >> The following 2 issues would be obsolete if I can create reports on the
> > run.
> >> 2. Alter the type of graph by passing a parameter to the report in which
> > I
> >> can specify whether it is ie: pie/donut/line etc.
> >>
> >> 3. I would like for my reports to be able to take an input parameter that
> > is
> >> not viewable on the report and is to be able to be dynamic.
> >>
> >> Thanks
> >> Kez.
> >>
> >>
> >
> >
>
>|||You can use the RDL generator for a ASP.NET or Winform to generate the RDL on
the fly no XML, no VS, no Xpath
http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
"mark-s" wrote:
> If i understand correctly, i am doing a similar thing with a custom report
> application. Basically, I have 'report template' rdl files on the server
> which have no report data in them. I also have a class that collects all the
> users choices in the client app. I use GetReportDefinition to get the
> template rdl as xml, then use Xpath to add the custom data that the user has
> chosen.
> Once the new xml file is built, it is published to the server as a new
> report, which the user can run.
> that's a simplified description, but I hope it helps!
> "Kez Bates" wrote:
> > Thanks for the feedback.
> > When you refer to re-posting the rdl I imagine you mean publishing it back
> > to the reports server. This is okay but multiple users will be accessing
> > the report. I only want the report rdl file to be changed for that
> > instance, not as a permanant thing.
> >
> > I was also hoping for some direction in achieving this.
> >
> > Thanks
> > Kez.
> >
> >
> >
> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > news:uYITfcTCFHA.2572@.tk2msftngp13.phx.gbl...
> > > You'd have to get the RDL, change it and re-post it, then call the report
> > > as
> > > you have said...
> > >
> > > Regarding the bottom parameter item , you can make a parameter invisible
> > > by
> > > simply not giving the label attribute a value... THe param can be passed
> > > in
> > > via URL etc, but the report does not display it with the other parameters.
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
> > > news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
> > >> Hi all
> > >> Hoping someone can help me out. I'm new to reporting services and cannot
> > >> quite seem to figure out what steps to take with the following.
> > >>
> > >> 1. Create reports on the fly... one of the problems I have is that I
> > >> would
> > >> like to alter the rdl file before it is displayed to the user so I can
> > >> change things in it like the points below. The way I see it happening is
> > >> that I extract the rdl from the reports server... make the changes to the
> > >> file and then render this as html to display to the user.
> > >> Can anyone tell me if this would be the correct process and how to
> > >> accomplish this.
> > >>
> > >>
> > >> The following 2 issues would be obsolete if I can create reports on the
> > > run.
> > >> 2. Alter the type of graph by passing a parameter to the report in which
> > > I
> > >> can specify whether it is ie: pie/donut/line etc.
> > >>
> > >> 3. I would like for my reports to be able to take an input parameter that
> > > is
> > >> not viewable on the report and is to be able to be dynamic.
> > >>
> > >> Thanks
> > >> Kez.
> > >>
> > >>
> > >
> > >
> >
> >
> >|||Hi,
I would also suggest you to use ReportPortal for creating MS Reporting
Services reports on the fly as web intranet solution. Great and
productive !
You can order a free 60 days trial or even visit the demo report
portal site on internet and test it online.
Regards,
Marco Groeneveld
www.gmsbv.nl / www.reportportal.com
"=?Utf-8?B?SmVycnk=?=" <Jerry@.discussions.microsoft.com> wrote in message news:<C76C5CD6-439A-4E6A-883E-04478C9DB0A5@.microsoft.com>...
> You can use the RDL generator for a ASP.NET or Winform to generate the RDL on
> the fly no XML, no VS, no Xpath
> http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> "mark-s" wrote:
> > If i understand correctly, i am doing a similar thing with a custom report
> > application. Basically, I have 'report template' rdl files on the server
> > which have no report data in them. I also have a class that collects all the
> > users choices in the client app. I use GetReportDefinition to get the
> > template rdl as xml, then use Xpath to add the custom data that the user has
> > chosen.
> > Once the new xml file is built, it is published to the server as a new
> > report, which the user can run.
> >
> > that's a simplified description, but I hope it helps!
> >
> > "Kez Bates" wrote:
> >
> > > Thanks for the feedback.
> > > When you refer to re-posting the rdl I imagine you mean publishing it back
> > > to the reports server. This is okay but multiple users will be accessing
> > > the report. I only want the report rdl file to be changed for that
> > > instance, not as a permanant thing.
> > >
> > > I was also hoping for some direction in achieving this.
> > >
> > > Thanks
> > > Kez.
> > >
> > >
> > >
> > > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > > news:uYITfcTCFHA.2572@.tk2msftngp13.phx.gbl...
> > > > You'd have to get the RDL, change it and re-post it, then call the report
> > > > as
> > > > you have said...
> > > >
> > > > Regarding the bottom parameter item , you can make a parameter invisible
> > > > by
> > > > simply not giving the label attribute a value... THe param can be passed
> > > > in
> > > > via URL etc, but the report does not display it with the other parameters.
> > > >
> > > > --
> > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > Mariner, Charlotte, NC
> > > > www.mariner-usa.com
> > > > (Please respond only to the newsgroups.)
> > > >
> > > > I support the Professional Association of SQL Server (PASS) and it's
> > > > community of SQL Server professionals.
> > > > www.sqlpass.org
> > > >
> > > > "Kez Bates" <kez.bates@.elmtree.com.au> wrote in message
> > > > news:%23U0pqrLCFHA.1084@.TK2MSFTNGP11.phx.gbl...
> > > >> Hi all
> > > >> Hoping someone can help me out. I'm new to reporting services and cannot
> > > >> quite seem to figure out what steps to take with the following.
> > > >>
> > > >> 1. Create reports on the fly... one of the problems I have is that I
> > > >> would
> > > >> like to alter the rdl file before it is displayed to the user so I can
> > > >> change things in it like the points below. The way I see it happening is
> > > >> that I extract the rdl from the reports server... make the changes to the
> > > >> file and then render this as html to display to the user.
> > > >> Can anyone tell me if this would be the correct process and how to
> > > >> accomplish this.
> > > >>
> > > >>
> > > >> The following 2 issues would be obsolete if I can create reports on the
> > > > run.
> > > >> 2. Alter the type of graph by passing a parameter to the report in which
> > > > I
> > > >> can specify whether it is ie: pie/donut/line etc.
> > > >>
> > > >> 3. I would like for my reports to be able to take an input parameter that
> > > > is
> > > >> not viewable on the report and is to be able to be dynamic.
> > > >>
> > > >> Thanks
> > > >> Kez.
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >

Creating Primary key with ODBC

Hi all,

I've spent hours trying to find the error in the following SQL 2000
command:

ALTER TABLE [ClientList] ADD CONSTRAINT [PK_ClientList] PRIMARY KEY
CLUSTERED ([PhoneNo]) On [PRIMARY]

Every time I try to "Execute" this from my (VB5) ODBC connection I get:

Runtime error 3289;
Syntax error in CONSTRAINT clause

For the life of me I can see nothing wrong. I used Enterprise manager
to create this statement, and I can create the primary key fine from
there. The PhoneNo field does not allow NULLs.

Everything is service-packed up to date. I have tried using
[databasename].[dbo].[ClientList] and suchlike.

I've tried to find a relevant manual, but my SQL Server developers
guide suggests this should be ok and I can't see anything wrong in the
books online.

Can anybody please help?

TIARS200Phil (philsowden@.dataservicesltd.co.uk) writes:
> I've spent hours trying to find the error in the following SQL 2000
> command:
> ALTER TABLE [ClientList] ADD CONSTRAINT [PK_ClientList] PRIMARY KEY
> CLUSTERED ([PhoneNo]) On [PRIMARY]
> Every time I try to "Execute" this from my (VB5) ODBC connection I get:
> Runtime error 3289;
> Syntax error in CONSTRAINT clause
> For the life of me I can see nothing wrong. I used Enterprise manager
> to create this statement, and I can create the primary key fine from
> there. The PhoneNo field does not allow NULLs.

That appears to be an error from the client layer. There certainly is
no syntax error in that statement as far as SQL Server is concerned, as
I can see. (Run in Query Analyzer to verify.)

Could you post the actual VB code you are using? Please also include
the part where you connect.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the prompt reply Erland.

The database is opened as follows:
..
..
..
lsConnectionString = "ODBC;Description=SQL Server 2k;DRIVER=SQL
Server;SERVER=SERVER;UID=Administrator;" & _

"APP=??;WSID=PII333;DATABASE=Sparc;Network=DBNM PNTW;QueryLog_On=Yes;
Trusted_Connection=Yes"

Set gdbSparc = DBEngine.Workspaces(0).OpenDatabase("", dbDriverNoPrompt,
False, lsConnectionString)
..
..
..

(Sorry for line confusion, hope this makes sense!).

The function that would build the indexes contains the following code.

lsSQL = "ALTER TABLE " & lsTable & " " & _
"ADD CONSTRAINT PK_" & lsTable & " PRIMARY KEY CLUSTERED " & _
"([PhoneNo]) On [PRIMARY];"

gdbSparc.Execute lsSQL

This translates to the code I posted previously when the "lsTable"
parameter is provided (e.g. [ClientTable]).

I pasted the code by printing the value of <lsSQL> in the debug window
and copying it from there.

HTH

Phil

*** Sent via Developersdex http://www.developersdex.com ***|||RS200Phil (RS200Phil@.dataservicesltdnospam.co.uk) writes:
> The database is opened as follows:
> .
> .
> .
> lsConnectionString = "ODBC;Description=SQL Server 2k;DRIVER=SQL
> Server;SERVER=SERVER;UID=Administrator;" & _
> "APP=??;WSID=PII333;DATABASE=Sparc;Network=DBNM PNTW;QueryLog_On=Yes;
> Trusted_Connection=Yes"
> Set gdbSparc = DBEngine.Workspaces(0).OpenDatabase("", dbDriverNoPrompt,
> False, lsConnectionString)
> .
> .
> (Sorry for line confusion, hope this makes sense!).
> The function that would build the indexes contains the following code.
> lsSQL = "ALTER TABLE " & lsTable & " " & _
> "ADD CONSTRAINT PK_" & lsTable & " PRIMARY KEY CLUSTERED " & _
> "([PhoneNo]) On [PRIMARY];"
> gdbSparc.Execute lsSQL

Hm, doesn't look like modern technology to me. :-)

Have you checked which version of the ODBC SQL Server driver you have?
Which operating system is this (incl Service Pack)? Do you know which
version of the MDAC you have?

All I can really recommend is to try to get a newer version of the ODBC
driver, as this appeears to be culprit. (You could try to remove the
"On [PRIMARY]" part.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||RS200Phil wrote:
> gdbSparc.Execute lsSQL

Try:
gdbSparc.Execute lsSQL, dbSQLPassthrough

Otherwise Jet sticks its nose in.|||Thanks for that, Trevor.

The passthrough was originally in place, but it failed with error 3416.
I thought I was getting more info when I went through Jet.

However, you've hit the nail on the thumb! I found the following in the
Jet manual:

"Note The Microsoft Jet database engine doesn't support the use of
CONSTRAINT, or any of the data definition language (DDL) statements,
with non-Microsoft Jet databases. Use the DAO Create methods instead."

So it's back to the drawing board, then!

BTW - I am converting about 100 quite large tables from a legacy Access
97 back end to SQL 2k. DTS doesn't do a brilliant job of the
conversion, but it did create the initial vb5 code for me. I just had
to bodge it for the index creation (no good there, then!) and to change
some of the field types (rather than do it manually for about 1000
fields!).

Thanks for your help, I'm optimistic that I can resolve it now.

Cheers

Phil

*** Sent via Developersdex http://www.developersdex.com ***|||RS200Phil wrote:
> Thanks for that, Trevor.
> The passthrough was originally in place, but it failed with error 3416.
> I thought I was getting more info when I went through Jet.

You mean 3146? You can loop the errors collection of the dbengine
object, e.g.

dim e as Error
...
For each e in DbEngine.Errors
debug.print e.number, e.description
Next

> BTW - I am converting about 100 quite large tables from a legacy Access
> 97 back end to SQL 2k. DTS doesn't do a brilliant job of the
> conversion, but it did create the initial vb5 code for me. I just had
> to bodge it for the index creation (no good there, then!) and to change
> some of the field types (rather than do it manually for about 1000
> fields!).

Have you tried the upsizing wizard? http://support.microsoft.com/kb/q176614/|||Thanks so much for the ideas. Of course - I should've thought of the
dbengine error collection.

Also, I like the idea of the upsizing wizard. I've used it before, some
time ago, but opted for the SQL Server DTS tool. I'll give this a go,
too.

I appreciate all your help, Trevor. I'm sure I'll be ok now.

Cheers

Phil

*** Sent via Developersdex http://www.developersdex.com ***

Creating Parent Child Hierarchies

Hi
I have the following in my cube
Location Dimension containing LocationID and LocationName attributes.
Customer Dimension containing CustomerID,CustomerName....,LocationID
The LocationID in the Location Dimension and Customer Dimension are related to each other.
I want to create a hierarchy with the LocationName in the First Level and the CustomerName in the second level. How to I accomplish this? As i said I already have a relationship defined between the two tables.

Thanks and Regards
Guruprasad Karnik

This is not a recursive hierarchy from what you are describing.

You can find a recursive hierarchy in the employee table in the Adventure works sample data base that is a part of the SQL server 2005 installation. You use parent-child hierarchies with this type of primary key-foreign key relationship in the same table.

If you download the performance guide for SSAS2005 (see the first post in this forum) and have a look at page 82 and further(Referenced relationsships), you will find a lot of helpful information.

HTH

Thomas Ivarsson

Creating new Notificatio services instance

When I try to create new Notification services instance, I am getting the following error message.

"Notification services failed to open a connection to sql server".

I have changed default connections to remote.But still I am getting same error.Please help me I am new to Notification services.

Can you elaborate a bit? Anything in the windows event log? Are you an administrator on the SQL Server? Does your SQL Server instance allow remote connections?

Joe

Creating new Notificatio services instance

When I try to create new Notification services instance, I am getting the following error message.

"Notification services failed to open a connection to sql server".

I have changed default connections to remote.But still I am getting same error.Please help me I am new to Notification services.

Can you elaborate a bit? Anything in the windows event log? Are you an administrator on the SQL Server? Does your SQL Server instance allow remote connections?

Joe

Creating MS Access datasource

I am trying to create a MS Access datasource to use it in a IS dataflow. I've done the following things:

1.I've created an ODBC datasource - System DSN, pointing to the Access database.

2.I've created an ODBC connection manager pointing to the ODBS datasource that I created in the previous step.

3.I've created a DataReader Source that uses as its Connection Manager the connection manager that I created in the previous step.

I get the following error message: "Cannot acquire a managed connection from the run-time connection manager." What can I do to solve this error?

Pedro Martins

The DataReader expects a managed connection. The ODBC connection manager returns a native object. The error message is literally correct.

Assuming that there is no particular requirement to use ODBC, I would use an ADO.NET connection manager with the Jet provider.

-Doug