putting mdx query in sql job and emailing the results?

Is this possible to email w/ just using the job script window, as long as working with 2D and 3D queries?

I want to know the dates of data in the cube ie:

WITH MEMBER Min1 as

Head(Exists([Date].[Date].[Date].Members, , 'Internet Sales'))(0).MemberValue

MEMBER Max1 as

Tail(Exists([Date].[Date].[Date].Members, , 'Internet Sales'))(0).MemberValue

SELECT {Min1, Max1} on COLUMNS

FROM [Adventure Works]

In the past, I've used xp_sendmail to send out query results. Of course, this requires a SQL query to be performed. One option here would be to perform the MDX query through a SQL OPENROWSET function call. This KB article is a little dated but shows you the basics: http://support.microsoft.com/kb/218592.

So, I have to ask, where are the dates in the OLAP cube coming from? Are these coming from a relational database? If so, could you more easily just query that database?

B.

|||I could query the sql data. but I wanted to know without a doubt that the data is in the cube. I can more easily look at my email than remote in to connect to the cube.

I'll first try openrowset().
Thanks!

Putting DDL statemnets (CREATE SCHEMA), in a transaction

Are T-SQL DDL Statements allowed within a transaction in SQL Server 2005?

Hi,

I'm working with some folks on a VS add-in that maps conceptual data models to physical DBMS implementations. Currently, the only target that doesn't generate a schema creation script inside a transaction is SQL Server 2005. Being a development project, having the CREATE SCHEMA script in a transaction would be useful, to avoid the need to clean out the parts of the DDL script that did run, and just start with a new iteration. When I asked why, they thought that SQL Server didn't allow DDL statements to run inside a transaction. Searching this, I could only find references that suggested you shouldn't (for performance reasons - but these are not an issue in this case).

The rules for this are likely in Books online, etc..., but searches I tried gave gave too many references, or too few.

If you know the answer or the exact reference, please pass that along. Also, if you know of an alternative way of removing a schema from a DB, without having to delete all the individual items within that schema first, please post that as well.

Thanks BRN..

Hi,

Here is some good info concerning your issue: http://www.sybase.com/detail?id=42077.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi and thanks for the quick response - I'll check it out. BRN..

|||

Hi,

Took a look at the SYBASE ref. That talks about MS having determined table create, alter, drop, not being allowed in a transaction due to 1) not wanting the DB structure altered 2) Performance.

As Mentioned, performance not an issue in this case. Also, I was hoping to find a MS document that stated their restrictions, and if they could be over-ridden, even if not recommended.

Still, more info than I had before, so thanks again. BRN..|||

Brian,

Not sure why you are consulting sybase documentation for SQL2005 issue.

The create schema statement can be run in a user transaction. You should be able to find most of the information @. http://msdn2.microsoft.com/en-us/library/ms189462.aspx

On the second issue, I assume you are asking if there is a way to drop a schema along with all objects contained in it in one shot. Unfortunately, this is not possible right now. You need to drop or move objects out of the schema before dropping it. We are considering this feature for a future release.

|||

Sameer,

Thanks for the link and the answer - I'll check out the link, and pass that along to the development team. The SYBASE reference was from an earlier reply, and the content was generic RDBMS, pointing out some preferences by specific vendors.

The reasons requiring schema objects to be dropped before dropping the schema are pretty easy to figure out - when a DB is populated. If there was a way to allow dropping a schema in one shot during the development phase, that would be helpful.

There might be reasons to allow dropping complete schemas in a production DB too; but that would be very dependant on how schemas (in the 2005 namespace seperate from ownership form), were utilized in the DB design. I'm looking at some of this in the project I mentioned.

Thanks again for the replies. BRN..

push subscription error Xprepl.dll

I used a working publication as a template to create a publication.
Everything was good
I then opened the propeties and added a push subscribtion and
selected execute snapshot immediately.
The merge snapshot agent errored out.
A review of the session shows the following:
connection to publisher is good
connection to subscriber is good
connection to database good
execute <article>.sc good
fail: can not find DLL Xprepl.dll
Any ideas?
Is this in your binn directory? It is called xprepl.dll or xpcobrepl.dll
there.
I don't have a sql 2000 system here, but can you try this
sp_helpextendedproc and see if any of the procs reference it?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mj" <mj@.discussions.microsoft.com> wrote in message
news:9F4BBB11-3610-4E9C-B961-554BB322D938@.microsoft.com...
> I used a working publication as a template to create a publication.
> Everything was good
> I then opened the propeties and added a push subscribtion and
> selected execute snapshot immediately.
> The merge snapshot agent errored out.
> A review of the session shows the following:
> connection to publisher is good
> connection to subscriber is good
> connection to database good
> execute <article>.sc good
> fail: can not find DLL Xprepl.dll
>
> Any ideas?
>
|||I will verify this shortly.
2nd question:
does the subscribing server have to be enterprise, standard or does it
matter?
"Hilary Cotter" wrote:

> Is this in your binn directory? It is called xprepl.dll or xpcobrepl.dll
> there.
> I don't have a sql 2000 system here, but can you try this
> sp_helpextendedproc and see if any of the procs reference it?
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "mj" <mj@.discussions.microsoft.com> wrote in message
> news:9F4BBB11-3610-4E9C-B961-554BB322D938@.microsoft.com...
>
>
|||Latest update:
my subscriber is running sql server standard edition, service pack 3a
my publisher is running sql server enterprise edition, service pack 4
the subscriber has xprelp.dll in binn directory
and is going to run sp_helpextendedproc and get back.
I will update this link shortly.
Thanks
"mj" wrote:
[vbcol=seagreen]
> I will verify this shortly.
> 2nd question:
> does the subscribing server have to be enterprise, standard or does it
> matter?
>
>
> "Hilary Cotter" wrote:
|||This topology is supported. MS recommends running consistent service packs
between all servers of the same version.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mj" <mj@.discussions.microsoft.com> wrote in message
news:0917B4D9-C17B-43E6-A481-620DAAB5F1FB@.microsoft.com...[vbcol=seagreen]
> Latest update:
> my subscriber is running sql server standard edition, service pack 3a
> my publisher is running sql server enterprise edition, service pack 4
> the subscriber has xprelp.dll in binn directory
> and is going to run sp_helpextendedproc and get back.
> I will update this link shortly.
> Thanks
>
> "mj" wrote:

Punctuation ?

Hi
I have this simple and working well SQL-row:

MyCommand = New SqlDataAdapter("SELECT * From Tbl_Table where Type = '" & TestType.tostring & "' ", MyConnection)

----
Then I have this one which I am very proud of because I can retrieve any word or part of word from the Item-field:

Test_Text=(Session("session-Text").tostring)

Dim val1, val2, val3 as string
val1 = Test_Text
val2 = "%"
val3= val1 + val2

MyCommand = New SqlDataAdapter("SELECT * From Tbl_Table where Item Like '%" & val1 & " %' OR Item Like '%" & val3 & " %' ", MyConnection)

----
The problem is:
When I try to merge these two in one big "AND" row as shown below, It only cares of picking up the Test_Text string and ignores totaly the TestType one!
I guess it is a matter of punctuation, can any one help me? Thanks.

MyCommand = New SqlDataAdapter("SELECT * From Tbl_Table where Type = '" & TestType.tostring & "' AND Item Like '%" & val1 & " %' OR Item Like '%" & val3 & " %'", MyConnection)I fixed it like this:

MyCommand = New SqlDataAdapter("SELECT * From tbl_Table where Item Like '" & val1 & " %' OR Item Like '%" & val3 & "' AND Type = '" & TestType.tostring & "' ", MyConnection)

Thanks|||when i'm debugging a concatenated sql string like that, i usually dim a var and set the var to the string. then i can see the result of the concatenation before passing it to the command object. it helps me to pick out the syntax errs quicker...

i.e.

dim mySQL as string = "SELECT * From tbl_Table where Item Like '" & val1 & " %' OR Item Like '%" & val3 & "' AND Type = '" & TestType.tostring & "' "

MyCommand = New SqlDataAdapter(mySQL, MyConnection)|||That´s a good Idea, thanks!

pulling all dates within a date range

I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am unsatisfied with
them. Here is what I have so far:

declare @.Sdate as datetime
declare @.Edate as datetime

set @.SDate = '07/01/2006'
set @.EDate = '12/31/2006'

select dateadd(dd, count(*) - 1, @.SDate)
from [atable] v
inner join [same table] v2 on v.id < v2.id
group by v.id
having count(*) < datediff(dd, @.SDate, @.EDate)+ 2
order by count(*)

this works just fine but it is dependent on the size of the table you
pull from, and is really more or less a hack job. Can anyone help me
with this?

thanks in advanceOn 6 Jul 2006 14:14:40 -0700, rugger81 wrote:

Quote:

Originally Posted by

>I am currently working in the sql server 2000 environment and I want to
>write a function to pull all dates within a given date range. I have
>created several diferent ways to do this but I am unsatisfied with
>them. Here is what I have so far:

(snip)

Hi rugger81,

http://www.aspfaq.com/show.asp?id=2519
--
Hugo Kornelis, SQL Server MVP|||rugger81 (jgilchrist@.ots.net) writes:

Quote:

Originally Posted by

I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am unsatisfied with
them. Here is what I have so far:
>
declare @.Sdate as datetime
declare @.Edate as datetime
>
set @.SDate = '07/01/2006'
set @.EDate = '12/31/2006'
>
select dateadd(dd, count(*) - 1, @.SDate)
from [atable] v
inner join [same table] v2 on v.id < v2.id
group by v.id
having count(*) < datediff(dd, @.SDate, @.EDate)+ 2
order by count(*)
>
this works just fine but it is dependent on the size of the table you
pull from, and is really more or less a hack job. Can anyone help me
with this?

If I understand this correctly, given the sample data you want

2006-01-07, 2006-01-08, ... 2006-12-30, 2006-12-31

The best is simply to create a table of dates. Here is a script that
create our dates table:

TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)

-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'

SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) + ' rows into
#numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers

--
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|||Thanks guys, I'll do just that. The idea of creating a date table
crossed my mind before, but I like to do things dynamically. Now that
I think of it however, a date table for the any time frame I would need
would still be relatively small and would save alot of time.

Erland Sommarskog wrote:

Quote:

Originally Posted by

rugger81 (jgilchrist@.ots.net) writes:

Quote:

Originally Posted by

I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am unsatisfied with
them. Here is what I have so far:

declare @.Sdate as datetime
declare @.Edate as datetime

set @.SDate = '07/01/2006'
set @.EDate = '12/31/2006'

select dateadd(dd, count(*) - 1, @.SDate)
from [atable] v
inner join [same table] v2 on v.id < v2.id
group by v.id
having count(*) < datediff(dd, @.SDate, @.EDate)+ 2
order by count(*)

this works just fine but it is dependent on the size of the table you
pull from, and is really more or less a hack job. Can anyone help me
with this?

>
If I understand this correctly, given the sample data you want
>
2006-01-07, 2006-01-08, ... 2006-12-30, 2006-12-31
>
The best is simply to create a table of dates. Here is a script that
create our dates table:
>
>
>
TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)
>
-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'
>
SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) + ' rows into
#numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers
>
>
>
>
>
--
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

Pull Subscriptions not working at all

I cannot get a pull subscription to work at all, push works, anyone have any suggestions as to why?
I currently have 1 subscription pulling from this server, but I can't add any new pull subscriptions, I receive an error message:
From EM, on Choose Publication (Using Wizard) - Click the + next to the server I want to pull from
Could not retrieve information about publications from Publisher
Error 208: Invalid object name 'sysextendedarticlesview'
I do see this view in some of the db's that are currently replicating, but I'm not sure of where it's looking for the view in this particular instance, as I have not gotten to the point of selecting a publication.
Did my Distribution db become corrupt in some way?
Any suggestions will be greatly appreciated.
You should find this view in the subscriber. You can copy it from another
database of the same version to this one. Open up the database in EM, locate
the view, right click on it and select copy. Then paste it into QA on the
subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:O8h6N1wCGHA.344@.TK2MSFTNGP11.phx.gbl...
I cannot get a pull subscription to work at all, push works, anyone have any
suggestions as to why?
I currently have 1 subscription pulling from this server, but I can't add
any new pull subscriptions, I receive an error message:
From EM, on Choose Publication (Using Wizard) - Click the + next to the
server I want to pull from
Could not retrieve information about publications from Publisher
Error 208: Invalid object name 'sysextendedarticlesview'
I do see this view in some of the db's that are currently replicating, but
I'm not sure of where it's looking for the view in this particular instance,
as I have not gotten to the point of selecting a publication.
Did my Distribution db become corrupt in some way?
Any suggestions will be greatly appreciated.
|||I did as you instructed & recreated this view & other missing objects on each subscribing db. It worked, I now see the publications to pull.
Thanx! You're the best!!!!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:%23fBQ6y1CGHA.344@.TK2MSFTNGP11.phx.gbl...
You should find this view in the subscriber. You can copy it from another
database of the same version to this one. Open up the database in EM, locate
the view, right click on it and select copy. Then paste it into QA on the
subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:O8h6N1wCGHA.344@.TK2MSFTNGP11.phx.gbl...
I cannot get a pull subscription to work at all, push works, anyone have any
suggestions as to why?
I currently have 1 subscription pulling from this server, but I can't add
any new pull subscriptions, I receive an error message:
From EM, on Choose Publication (Using Wizard) - Click the + next to the
server I want to pull from
Could not retrieve information about publications from Publisher
Error 208: Invalid object name 'sysextendedarticlesview'
I do see this view in some of the db's that are currently replicating, but
I'm not sure of where it's looking for the view in this particular instance,
as I have not gotten to the point of selecting a publication.
Did my Distribution db become corrupt in some way?
Any suggestions will be greatly appreciated.

pull subscription chaning identity seed/increment

I have an anynonomous pull merge set and working. But, I create a new
table on the client machine with increment and seed as -1000 and -20.
The table is created fine. But when I execture the first synchrnoize
data from the client, the table is changed to be 1, 2, which is what the
server has. I am using SQL-DMO on the client with the following code:
With myMergeObj
.Distributor = inxRS.Globals.gServer
.DistributorLogin = "inxsql"
.DistributorPassword = "inxsql4u"
.DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.DistributorNetwork = NETWORK_TYPE.TCPIP_SOCKETS
.DistributorAddress = inxRS.Globals.gServerIP
.Publisher = inxRS.Globals.gServer
.PublisherDatabase = in_comp
.Publication = Trim(in_comp) & "RS"
.PublisherLogin = "inxsql"
.PublisherPassword = "inxsql4u"
.PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.PublisherNetwork = NETWORK_TYPE.TCPIP_SOCKETS
.PublisherAddress = inxRS.Globals.gServerIP
.Subscriber = Trim(Environment.MachineName)
.SubscriberDatabase = in_comp
.SubscriberSecurityMode = SECURITY_TYPE.NT_AUTHENTICATION
.SubscriptionType = SUBSCRIPTION_TYPE.ANONYMOUS
.SubscriptionName = Trim(in_comp) & "RS"
.HostName = Trim(inxRS.Globals.gLogin)
.ExchangeType = EXCHANGE_TYPE.BIDIRECTIONAL
.ReinitializeSubscription(false)
End With
myMergeObj.Initialize()
myMergeObj.Run()
myMergeObj.Terminate()
I can't tell if it is a problem in the pull or in how the publication
was/is setup.
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
I removed the reinit statement and I still have the same problem.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||FIXED IT.
Darin
*** Sent via Developersdex http://www.codecomments.com ***

Pull Replication with Filter

Hello,
we are working with:
SQL Server 2000 SP 4, publicating a database and trying to filter data
with:
UserGroup = Host_Name()
on the subscriber (agent properties) we set:
-Hostname TW
Starting the subscription with the SyncManager works fine, but no data
with the criteria 'TW' was replicated to the subscriber.
This problem occurs all the time i try to work with the pull
replication. The same procedure on a push replication seems to be OK.
Also two of our customers have the problems and this begins to be a
very big problem for us. Somebody knows an solution. For me it seems to
be an MS SQL Bug?
Thanks for help
Tulio Mossorous
Are you using dynamic snapshots?
You have to do this to make it work or create a separate publication for
each subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tulio" <mossorous@.yahoo.de> wrote in message
news:1123439126.086812.143350@.z14g2000cwz.googlegr oups.com...
> Hello,
> we are working with:
> SQL Server 2000 SP 4, publicating a database and trying to filter data
> with:
> UserGroup = Host_Name()
> on the subscriber (agent properties) we set:
> -Hostname TW
> Starting the subscription with the SyncManager works fine, but no data
> with the criteria 'TW' was replicated to the subscriber.
> This problem occurs all the time i try to work with the pull
> replication. The same procedure on a push replication seems to be OK.
> Also two of our customers have the problems and this begins to be a
> very big problem for us. Somebody knows an solution. For me it seems to
> be an MS SQL Bug?
> Thanks for help
> Tulio Mossorous
>
|||Hello Hilary,
thanks for your help!
Yes, I'am using dynamic snapshot.
Regards
Tulio Mossorous
|||Hello again,
in a lot of posts users have problems with the dynamic filtering of a
pull replication. I tried the dynamic filtering for about 2 days,
without any result!
The dynamic snapshot don't get any data. What happens? An Bug?
Best regards
Tulio Mossorous
|||Hello again,
in a lot of posts users have problems with the dynamic filtering of a
pull replication. I tried the dynamic filtering for about 2 days,
without any result!
The dynamic snapshot don't get any data. What happens? A Bug?
Best regards
Tulio Mossorous

Pull data from Cache 5.0 to SQL 2000 (fast?)

I'm currently working with a 10 million plus row database with the data
residing on a Unix box with Cache 5.0. The problems is that it can take five
days to pull one table from Cache to SQL 2000 using the ODBC connection
provided by Cache in a SQL 2000 DTS package. I think the real problem is
converting the data from the post relational format (Cache) to a relational
format (SQL 2000)?

Does anyone have any ideas / suggestions on how to speed this transfer of
data? I'm very new to Cache and any help would be greatly appreciated.

Thanks,

-p"Pippen" <123@.hotmail.com> wrote in message
news:uadBb.1525$8y1.14237@.attbi_s52...
> I'm currently working with a 10 million plus row database with the data
> residing on a Unix box with Cache 5.0. The problems is that it can take
five
> days to pull one table from Cache to SQL 2000 using the ODBC connection
> provided by Cache in a SQL 2000 DTS package. I think the real problem is
> converting the data from the post relational format (Cache) to a
relational
> format (SQL 2000)?
> Does anyone have any ideas / suggestions on how to speed this transfer of
> data? I'm very new to Cache and any help would be greatly appreciated.

I don't know a thing about Cache, but it could be several things.

Is the table you're pulling the data into have a large number of indices?
If so, it is probably faster to drop them, load the data and then rebuild
them.

Also, you can try dumping the cache data to a text file of some sort and
using BCP or a DTS package to load it.

As reference, I load 3 tables compromising I think about 10 gig of data (I'd
have to look it up) and index them in I think under 6 hours. And that's on
moderately old hardware (3 years old).

I do this 4 times a year for 3 different databases. If I had to do it more
often I could probably speed it up even faster than that.

So, it's quite possible you may be able to speed this up.

> Thanks,
> -p|||"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:5AdBb.198332$1N3.102110@.twister.nyroc.rr.com. ..
> "Pippen" <123@.hotmail.com> wrote in message
> news:uadBb.1525$8y1.14237@.attbi_s52...
> > I'm currently working with a 10 million plus row database with the data
> > residing on a Unix box with Cache 5.0. The problems is that it can take
> five
> > days to pull one table from Cache to SQL 2000 using the ODBC connection
> > provided by Cache in a SQL 2000 DTS package. I think the real problem is
> > converting the data from the post relational format (Cache) to a
> relational
> > format (SQL 2000)?
> > Does anyone have any ideas / suggestions on how to speed this transfer
of
> > data? I'm very new to Cache and any help would be greatly appreciated.
> I don't know a thing about Cache, but it could be several things.
> Is the table you're pulling the data into have a large number of indices?
> If so, it is probably faster to drop them, load the data and then rebuild
> them.
> Also, you can try dumping the cache data to a text file of some sort and
> using BCP or a DTS package to load it.
> As reference, I load 3 tables compromising I think about 10 gig of data
(I'd
> have to look it up) and index them in I think under 6 hours. And that's
on
> moderately old hardware (3 years old).
> I do this 4 times a year for 3 different databases. If I had to do it
more
> often I could probably speed it up even faster than that.
> So, it's quite possible you may be able to speed this up.
>
> > Thanks,
> > -p
>
Thanks for the advice. I probably should have mentioned that the data is
going in to empty non indexed tables...

-js|||"Pippen" <123@.hotmail.com> wrote in message
news:9koBb.484514$Tr4.1330184@.attbi_s03...
> Thanks for the advice. I probably should have mentioned that the data is
> going in to empty non indexed tables...

Hmm, interesting. I'd then try to dump the data out of Cache and use BCP
and see if that's any faster.

At the very least it may break it into two parts and give you an idea of
which one is the slow part.

> -js

Publishing Wizard - Bad Magic?

I've been working with the Database Publishing Wizard and have found what I initially thought was a bug but have decided is a rather nasty "feature". Or am I missing something?

I have membership implemented in a live database. I took a backup and loaded it into a local database. I created a new table locally that is foreign-keyed off aspnet_roles, and added some data.

Using the Publishing Wizard for a partial deployment, I followed the steps which seem perfectly clear to script out ONLY the new table and its data. The screens provide a nice GUI for selecting specific database objects, and I carefully chose the one new table.

From good habits I looked at the script before deploying it. Itdrops and recreates aspnet_roles and aspnet_applications as well (and inserts the data from the development tables).

Presumably this is because of the foreign key relationships. But there's no need to do so. What if I or another developer had added in some roles to the live table in the meantime? This script would blow them away.

Am I missing an option with the Wizard that would script out only what is specified? I've tidied up the script (i.e. removed the undesirable sections) - but this seems to be a naughty wizard. Any thoughts?

What publishing wizard are you using for your database? It is a bit expensive but I believe Red Gate has the best db tools available. It contains separate data comparison and sql structure (table changes) tools.

|||

We use Red Gate when we have a choice. But one of our clients uses the Database Publishing Wizard, which is a free download from Microsoft.

I was curious so was checking it out. It seems to me that it should pop up a list of tables affected by the user choice, particularly when the user has selected only one table. Anyway, I'll continue pursuing this elsewhere, thanks for the response.

|||

If that's the case is it possible to just modify the sql by removing the data insert statements?

|||

At the risk of stating the obvious, did you try changing the "Drop existing objects in script" option to false? This will prevent the script from adding the if exists-drop statements when publsihing and when writing scripts.

This option is on the "Select Publishing Options" page.

|||

Yes, but take this scenario - I deploy a single new table fk-ed to aspnet_roles and uncheck "drop existing objects". Everything is hunky-dory first time round. But then I want to add a single new column to this recent table? It makes sense to check "drop existing objects" and then select the changed table - behind the scenes, aspnet_roles is dropped and recreated alongside the new table.

And regardless of whether the "drop existing objects" is unchecked - the Wizard will still try to insert the data from the development database into aspnet_roles. This will fail, because it turns identity insert on - so will bomb out on the primary key.

I seem to remember looking at the Wizard when it first was released and it didn't have partial deployment options - only full deployment. So partial deployment has been added later - which is a nice feature. But I think if the Wizard "decides" that it knows best and will also work with foreign-keyed tables for a chosen table in the GUI - it should warn the user of exactly which tables are affected.

Yes - the user can edit the script - but this Wizard isn't aimed at DBAs who will tend to want to roll their own scripts anyway - I think it's aimed at developers who are proficient in coding but don't want to get bogged down in the database side.

Publishing SQL Express June CTP as a "Prerequisite"?

Hello,

I have not conducted extensive testing yet, but I have installed the June CTP of SQL Express and everything is working fine with my Beta 2 copy of VS.NET 2005. I see that "SQL Server 2005 Express Edition Beta" is listed in the "Choose which prerequisites to install" list of the VS.NET 2005 project settings for a basic Windows Forms Application.

My question is: Is this functionality expected to work fine in the Beta 2 for testing? Meaning, if I were to test publishing my app on a new test-copy of Windows XP Pro SP2, can I expect that in most cases it will deploy the June CTP components just fine?

Thanks much,

Sean H.If it does not work then its a bug and should be reported.

-Euan|||

FYI - From what I can tell this feature does (and yet does not) work with "ClickOnce" deployment and the publish feature. It seems that even though you can select SQL Express as a prerequisite and it does seem to install properly on test clients, it installs a previous version of SQL Express and not the June CTP.

I haven't investigated fully, but I get the error:

"The database 'C:\Test.MDF' cannot be opened because it is version 607. This server supports version 603 and earlier. A downgrade path is not supported."

.. when attempting to use the prerequisite publish feature with June CTP created databases. I would imagine this will be fixed in the final build.

For now, it seems ClickOnce publishing does not support the June CTP of SQL Express 2005.

|||I think this is because VS B2 understands the april CTP not the June CTP, this should be fixed for RTM.

-Euan|||

Do you know how to solve this problem without downloading a new version of SQL 2005

Thanks

Regards,Vincent

|||I think you will need a new version of VS and SQL Server Express if it does not work in the VS June CTP.

-Euan

publishing problem

My test sql seems to be working like a train now. Also now discovering the full functionality

of sql and can see that im at the bottom of a large hill, but an interesting one.

My next problem is publishing.

My hosting package has been upgraded to the version 2.0 asp.net runtime. and has sql enabled.

web dev.......copy function drops all the files into the server nicely and i can see them all.

The problem is it will not compile or show, and returns.........you do not have privaleges to view this htm.

All a bit strange to me but?

I have not touched any of the security controls in the config manager or code.

Anybody else had these issues or problems

regards Richard

Hi,

Could not understand about the problem!!! Could you post the error message here to get a good answer.

Hemantgiri S. Goswami

Published Reports -- URGENT

Hi
I am trying to supply input parameters to a report from an aspx page. I tried many methods , but none seem to be working.
Can anyone suggest how I can work with parameterized reports from code?
Moreover , Do I have to publish a report to be able to supply Report Parameters from the code?
Thanks
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.You always have to publish a report to the server before you can use it. Not
sure what you are trying to do.
Are you using URL Access or the SOAP API? Supplying paramters to each is
described in BOL.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:uOaURsuZEHA.3112@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am trying to supply input parameters to a report from an aspx page. I
> tried many methods , but none seem to be working.
> Can anyone suggest how I can work with parameterized reports from code?
> Moreover , Do I have to publish a report to be able to supply Report
> Parameters from the code?
> Thanks
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.|||Hi
This is what i think.
1) in your .aspx page, you would have a hyperlink or something that links to the targeted report Eg:
<a href="http://links.10026.com/?link=http://localhost/Reportserver?/report1¶m1=123" >Go to report1</a>
2) In report1.rdl, you would have created a parameter called param1.
3) You definitely need to publish your report. You do this through the report manager: upload.
Chiara
"SqlJunkies User" wrote:
> Hi
> I am trying to supply input parameters to a report from an aspx page. I tried many methods , but none seem to be working.
> Can anyone suggest how I can work with parameterized reports from code?
> Moreover , Do I have to publish a report to be able to supply Report Parameters from the code?
> Thanks
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>

Public Synonym?

Hi,

I am an Oracle Developer and just started working on a SQL Server database. I created a view and gave ALL rights to PUBLIC and my supervisor. However, when my supervisor logs on as herself she can see the query (in Query Analyzer) but she can't select data from it.

In Oracle I typically have to create a public synonym so users can use an object without specifying the owner of that object. Does the same apply in SQL Server and if so, how do I create a public synonym?

Thanks,
KDoes that login have access to the database - and within that database does public have "select" rights ? When you manage the permissions for the view do you see that login and if so click on "select" rights.|||Did you create your view in your own schema? (as in user.viewname)

Basically, MS goes through a routine for each table on each query. Suppose you issue:

select * from table

SQL Server first looks for username.table where username is your username. If that fails, SQL Server than looks for dbo.table. If that fails, then an error is returned. Because of that first lookup, it is suggested that you supply the owner of the table for queries that are run many many times per hour. The first lookup is not too work intensie, but if you do it a million times an hour...

As for a public synonym, you have to create a view as dbo.view. Then anyone can query it as just select * from view (provided they have no view or table of trhe same name in their own schema).

Public Role

Hi:
I want to remove permissions from the public role but it is not working. I
used "revoke all from public". It had worked for me before. Some of the us
ers I created are inheriting permissions from the public role. I want to r
emove those permissions fro
m the public role but the above command is not working. Any help will be gr
eatly appreciated.
ThanksSohail,
What rights are you running with? The BOL says:
Revokes all applicable permissions. For statement permissions, ALL can be
used only by members of the sysadmin and db_securityadmin roles. For object
permissions, ALL can be used by members of the sysadmin, db_securityadmin,
and db_owner roles, and by database object owners.
Russell Fields
"Sohail" <anonymous@.discussions.microsoft.com> wrote in message
news:B1540684-F053-49A0-97AA-CA4BAA02AF86@.microsoft.com...

quote:

---

> Hi:
> I want to remove permissions from the public role but it is not working.

---

I used "revoke all from public". It had worked for me before. Some of the
users I created are inheriting permissions from the public role. I want to
remove those permissions from the public role but the above command is not
working. Any help will be greatly appreciated.

quote:

---

> Thanks

---

|||Hi Russell:
Thanks for replying. I am using sa but still not working. Very confusing.
Any other thought.
Thanks|||What is the error you receive?
Adam
"Sohail" <anonymous@.discussions.microsoft.com> wrote in message
news:117BF945-B35E-445E-93CF-7AFA0EB8F968@.microsoft.com...
> Hi Russell:
> Thanks for replying. I am using sa but still not working. Very
confusing. Any other thought.
> Thanks