Is it possible that i can use a store proc to modify all the rest of my store procedures that i have in my DB ??
I have so many created allready and it will be to long to go throught each one of them to modify my text inside.
what i wish to do is a store proc that will allow me to loop to all my store proc of the current DB and look inside for specific text that i would like to change with the new value !!
any advise or example..
thanx.My strong suggestion is to script all of the stored procedures (including their permissions) using SQL Enterprise Mangler, and save two copies of the script. Edit one of the scripts to suit your fancy, then play it into your database. Test, test, test, then test some more.
When you discover what you mangled, play in the pristine script and start over!
-PatP|||Are the procedures stored in files or do they solely exist in sqlserver? If the latter, you have perhaps a challenge. I'm not sure if this will work: select the inside from syscomments (column text), then change what you want. You could commit the change to the syscomments table but you'll have to reconfigure sql server to allow this and I am not sure if that's a good idea. Besides, you'll have to think about having to let sql server know the procedure changed so it'll recompile (in order to make sure it'll use the latest version). Instead you could try to have the contents of the procedure executed using sp_executesql, don't forget the drop and create commands though.
I would not recommend either one since you'll have some issues to consider prior actually doing the changes.|||btw: Pat's suggestion is way preferred over mine|||Hey look at that...a cross post :D
SQLTeam (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35267)
Check out the link in the link...let me know what you think...
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Friday, March 30, 2012
Modified Stored Procedure Scripts Wrong ANSI Settings
Query Analyzer for 2000 was smart enough to realize that a stored procedure was created with the ANSI settings like such:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
go
However, when I use SSMS to modify a stored procedure from the context menu in the object explorer, instead get:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Which is not even the default for my connection.
I'm working on a legacy code base with tons of double quoted strings, so I really need the ANSI settings to stay where they were without fighting the SQL editor about it.
Any suggestions? Is this a bug? I'm using SQL2005 RTM.
Matthew Martin
I would do this from your script in the query pane:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE <yourprocedure>
...
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO
Should be something like this at the end.
HTH, Jens Suessmeyer.
|||True, I can revert to OSQL and generate the script correctly with my keyboard, the problem is that EM used to be able to script out a stored procedure with the correct settings (namely the settings that were in effect when the stored procedure was last altered), now with SSMS, right clicking on a stored procedure and selecting modify will script out a ALTER PROCEDURE script with the wrong settings.
Either I've hit a SSMS bug or I haven't found the 'Make-it-work-the-way-it-used-to' check box. I'm hoping it is the later.
Matthew Martin
|||This is a known issue in SSMS. It will be fixed in SP1.
Modified image filegroup never get saved
I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peter
Hi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John
|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> "wodoy.peter" wrote:
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonly
> in SSMS (on my version (SP1))
>
>
>
>
|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John
|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> because once the table has data it would be a very expensive thing to change
> which could cause severe problems. But then if there is no data in the table
> I don't see why it can't be changed. You may want to log this athttps://connect.microsoft.com/SQLServer/Feedback
> John
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peter
Hi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John
|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> "wodoy.peter" wrote:
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonly
> in SSMS (on my version (SP1))
>
>
>
>
|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John
|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> because once the table has data it would be a very expensive thing to change
> which could cause severe problems. But then if there is no data in the table
> I don't see why it can't be changed. You may want to log this athttps://connect.microsoft.com/SQLServer/Feedback
> John
Modified image filegroup never get saved
I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peterHi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> > I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.Which version of SQL 2005 are you using?
> > Problem:
> > I created a table and gave the properties "Text/Image Filegroup" the
> > value of the secondary filegroup, gave the properties "Filegroup or
> > Partition Scheme Name" the value of primary file group. After I save
> > it. Then I open it for edit again, the value of the properties
> > "Text/Image Filegroup" was not showing secondary, instead, it show
> > primary.I assume this was through SSMS? As far as I can see there is not a way of
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonly
> in SSMS (on my version (SP1))
>
> > I'd try a few times to change it, from primary to secondary, then save.
> > After that open for modify again, it still revert back to primary
> > filegroup. Seems like the changes I made to that particular properties
> > can't be saved.Creating the table with T-SQL seems to be ok!
>
> > Anyone can help?
> > Thanks in advance.
> > wodoy.peterJohn|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> > I'm using SQL Server 2005 SP1 developer edition.
> > The complete version name and number: Microsoft SQL Server 2005 -
> > 9.00.2047.00
> > Is there any reason behind MS make the properties editable in user
> > interface only, but infact, it can't be saved/changed through the
> > SSMS?This would be one for the program group! I would guess one reason would be
> because once the table has data it would be a very expensive thing to change
> which could cause severe problems. But then if there is no data in the table
> I don't see why it can't be changed. You may want to log this athttps://connect.microsoft.com/SQLServer/Feedback
> John
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peterHi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> > I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.Which version of SQL 2005 are you using?
> > Problem:
> > I created a table and gave the properties "Text/Image Filegroup" the
> > value of the secondary filegroup, gave the properties "Filegroup or
> > Partition Scheme Name" the value of primary file group. After I save
> > it. Then I open it for edit again, the value of the properties
> > "Text/Image Filegroup" was not showing secondary, instead, it show
> > primary.I assume this was through SSMS? As far as I can see there is not a way of
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonly
> in SSMS (on my version (SP1))
>
> > I'd try a few times to change it, from primary to secondary, then save.
> > After that open for modify again, it still revert back to primary
> > filegroup. Seems like the changes I made to that particular properties
> > can't be saved.Creating the table with T-SQL seems to be ok!
>
> > Anyone can help?
> > Thanks in advance.
> > wodoy.peterJohn|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> > I'm using SQL Server 2005 SP1 developer edition.
> > The complete version name and number: Microsoft SQL Server 2005 -
> > 9.00.2047.00
> > Is there any reason behind MS make the properties editable in user
> > interface only, but infact, it can't be saved/changed through the
> > SSMS?This would be one for the program group! I would guess one reason would be
> because once the table has data it would be a very expensive thing to change
> which could cause severe problems. But then if there is no data in the table
> I don't see why it can't be changed. You may want to log this athttps://connect.microsoft.com/SQLServer/Feedback
> John
Modified image filegroup never get saved
I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peterHi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> "wodoy.peter" wrote:
>
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonl
y
> in SSMS (on my version (SP1))
>
>
>
>
>
>|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
>
> because once the table has data it would be a very expensive thing to chan
ge
> which could cause severe problems. But then if there is no data in the tab
le
> I don't see why it can't be changed. You may want to log this athttps://co
nnect.microsoft.com/SQLServer/Feedback
> John
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peterHi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> "wodoy.peter" wrote:
>
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonl
y
> in SSMS (on my version (SP1))
>
>
>
>
>
>|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
>
> because once the table has data it would be a very expensive thing to chan
ge
> which could cause severe problems. But then if there is no data in the tab
le
> I don't see why it can't be changed. You may want to log this athttps://co
nnect.microsoft.com/SQLServer/Feedback
> John
Wednesday, March 28, 2012
Model Question
Does anyone know if a change is made to a model, will reports created based
on it be affected? Or, once created, are these reports independent?
ThanksYes,
--
Regards,
Raja
â'Smile is a curve that sets everything straight"
"TomT" wrote:
> Does anyone know if a change is made to a model, will reports created based
> on it be affected? Or, once created, are these reports independent?
> Thanks|||Raja, thanks for your reply. Do you mean yes, they will be affected, or yes
they are independent?
"Raja" wrote:
> Yes,
> --
> Regards,
> Raja
> â'Smile is a curve that sets everything straight"
>
> "TomT" wrote:
> > Does anyone know if a change is made to a model, will reports created based
> > on it be affected? Or, once created, are these reports independent?
> >
> > Thanks|||Hello Tom,
I think Raja means that the report will be affected.
You could refer the article:
Creating a Report Model Project
http://msdn2.microsoft.com/en-us/ms156285.aspx
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Ok, thanks very much.
"Wei Lu [MSFT]" wrote:
> Hello Tom,
> I think Raja means that the report will be affected.
> You could refer the article:
> Creating a Report Model Project
> http://msdn2.microsoft.com/en-us/ms156285.aspx
> Thanks!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
on it be affected? Or, once created, are these reports independent?
ThanksYes,
--
Regards,
Raja
â'Smile is a curve that sets everything straight"
"TomT" wrote:
> Does anyone know if a change is made to a model, will reports created based
> on it be affected? Or, once created, are these reports independent?
> Thanks|||Raja, thanks for your reply. Do you mean yes, they will be affected, or yes
they are independent?
"Raja" wrote:
> Yes,
> --
> Regards,
> Raja
> â'Smile is a curve that sets everything straight"
>
> "TomT" wrote:
> > Does anyone know if a change is made to a model, will reports created based
> > on it be affected? Or, once created, are these reports independent?
> >
> > Thanks|||Hello Tom,
I think Raja means that the report will be affected.
You could refer the article:
Creating a Report Model Project
http://msdn2.microsoft.com/en-us/ms156285.aspx
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Ok, thanks very much.
"Wei Lu [MSFT]" wrote:
> Hello Tom,
> I think Raja means that the report will be affected.
> You could refer the article:
> Creating a Report Model Project
> http://msdn2.microsoft.com/en-us/ms156285.aspx
> Thanks!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
Monday, March 26, 2012
Mobile sdf File created on Server
Hi there,
Background: I've created an application to run on windows mobile 5.0 devices. It replicates to a publication on a server. To do this it goes through ASP.net. This creates the database on the Mobile Devices.
Issue: Problem is the initial download of the database is taking 2hours plus to replicate (create) for the first time. So I'm trying to create the .sdf file on the server, zip it, and send it across to the handheld. All subsequent replications take about 5 minutes.
Question: Does anyone know how, or have examples for creating a Mobile CE database on the server. I need to create it using ASP.net and go through a pre-existing publication where a hostname used for filtering.
http://msdn2.microsoft.com/en-us/library/ms173009.aspx explains how to create this in SQL Server Management Stuidio. How do you do the same thing over ASP?
Can this be done using SMO? How do you create a SQLServerCompactEdition database using normal .NET Framework? Thanks for your help!!
Is it possible to access System.Data.SqlServerCe namespace without using the .NET Compact Framework?
So if I wanted to access the assembly in a VB.NET Windows App inside the .NET Framework, is this possible?
|||Yes, SQL CE 3.1 allows you to do that. There’s a special version of SQL CE provider for desktop framework.
|||You can run code like in this sample (http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcereplication.synchronize.aspxon your web server. create the file to the local file system and zip it. Then give the device the URL to the .zip file and your .NET Compact Framework app can the download the .zip file using HttpWebRequest class and unzip.
|||So the problem i was having was in using an assembly that came with the compact framework. The System.Data.SqlServerCE.dll file is located in at least two places on my machine. One in the CE and one in the Common folder...both have the same name and version number.
I changed the reference from pointing at the CE version to the normal version and it cleared up the problem. Thanks for the responses, I'm still not sure what the differences in the assemblies are.
Mobile sdf File created on Server
Hi there,
Background: I've created an application to run on windows mobile 5.0 devices. It replicates to a publication on a server. To do this it goes through ASP.net. This creates the database on the Mobile Devices.
Issue: Problem is the initial download of the database is taking 2hours plus to replicate (create) for the first time. So I'm trying to create the .sdf file on the server, zip it, and send it across to the handheld. All subsequent replications take about 5 minutes.
Question: Does anyone know how, or have examples for creating a Mobile CE database on the server. I need to create it using ASP.net and go through a pre-existing publication where a hostname used for filtering.
http://msdn2.microsoft.com/en-us/library/ms173009.aspx explains how to create this in SQL Server Management Stuidio. How do you do the same thing over ASP?
Can this be done using SMO? How do you create a SQLServerCompactEdition database using normal .NET Framework? Thanks for your help!!
Is it possible to access System.Data.SqlServerCe namespace without using the .NET Compact Framework?
So if I wanted to access the assembly in a VB.NET Windows App inside the .NET Framework, is this possible?
|||Yes, SQL CE 3.1 allows you to do that. There’s a special version of SQL CE provider for desktop framework.
|||You can run code like in this sample (http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcereplication.synchronize.aspxon your web server. create the file to the local file system and zip it. Then give the device the URL to the .zip file and your .NET Compact Framework app can the download the .zip file using HttpWebRequest class and unzip.
|||So the problem i was having was in using an assembly that came with the compact framework. The System.Data.SqlServerCE.dll file is located in at least two places on my machine. One in the CE and one in the Common folder...both have the same name and version number.
I changed the reference from pointing at the CE version to the normal version and it cleared up the problem. Thanks for the responses, I'm still not sure what the differences in the assemblies are.
sqlMonday, March 19, 2012
Mistakenly removed dtproperties, now I can find my diagram
I am currently testing SQL Server 2000 Evaluation version.
I created a diagram with the diagraming tool, but in the process I mistakenly deleted the system table 'dtproperties'. I have a backup of the database, but the last time I restored it, I lost my diagram (and had to do it again)
What I've done to try to get it back:
I have another database with the old table schema, and I tried to enter all the values it that old database's dtproperties into the recent db's dtproperties, but that didnt' work especially since I didn't know how to copy the lvalue column.
How can I restore my diagram?I believe no chance to retrieve the diagram as you're using EEE.
I created a diagram with the diagraming tool, but in the process I mistakenly deleted the system table 'dtproperties'. I have a backup of the database, but the last time I restored it, I lost my diagram (and had to do it again)
What I've done to try to get it back:
I have another database with the old table schema, and I tried to enter all the values it that old database's dtproperties into the recent db's dtproperties, but that didnt' work especially since I didn't know how to copy the lvalue column.
How can I restore my diagram?I believe no chance to retrieve the diagram as you're using EEE.
Mission Impossible ? Pivot Table at front end Excel
I have a table with this field headers Product/Status/USD:-
A/Actual/100
A/Budget/90
A/Variance/10
I have created a Pivot Table in Excel with
Product as row field (A or B)
Status as Column field (Actual, Budget, Variance)
USD as Value
It looks fine like this but I need to create a column called Variance % which is (Variance/Budget x 100%).
Please urgently advise how to create this new % column within Pivot Table.
Thanks. Sorry it seems a bit more about Excel but can't get answers in Excel forum...
I am not using SQL Server.
help...
Sorry, but I think your chances better off with trying Excel forum.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Missing zeros
I'm trying to report on the number of tickets created by day for 21 days, but
when there are no tickets logged on a day, i need a 0. I have been trying to
get my query to return 0's for every day of the 21 days, but that hasn't
worked. I have tried to get the Matrix to show 21 days, but that hasn't
worked either. To add complexity, i need to graph this matrix, so i cannot
simply use a sub-report. (otherwise it would be done.)
A subset of the results from the query are as follows:
1/5/2006 4
1/6/2006 5
1/8/2006 3
1/20/2006 1
What i need is:
1/4/2006 0
1/5/2006 4
1/6/2006 5
1/7/2006 0
1/8/2006 3
1/9/2006 0
1/20/2006 1
Back in my access days, i just ran the maketable query, then used VB to
'pad' in as many zero values as needed, and then ran the report off the table.
Any suggestions?Where do you get the data from? Where does the numbers come from - are you
summing in your query or is that done automagically somewhere in the
database? And where are the dates and the numbers connected?
My guess is that you have to make your SQL query return all 21 lines. If you
use an outer join for dates and numbers, and do a Case test for the numbers,
you should get all your rows.
Kaisa M. Lindahl
"Brent Maloney" <BrentMaloney@.discussions.microsoft.com> wrote in message
news:7C5F489A-68D9-4681-88B9-324B77104AC7@.microsoft.com...
> I'm trying to report on the number of tickets created by day for 21 days,
> but
> when there are no tickets logged on a day, i need a 0. I have been trying
> to
> get my query to return 0's for every day of the 21 days, but that hasn't
> worked. I have tried to get the Matrix to show 21 days, but that hasn't
> worked either. To add complexity, i need to graph this matrix, so i
> cannot
> simply use a sub-report. (otherwise it would be done.)
> A subset of the results from the query are as follows:
> 1/5/2006 4
> 1/6/2006 5
> 1/8/2006 3
> 1/20/2006 1
> What i need is:
> 1/4/2006 0
> 1/5/2006 4
> 1/6/2006 5
> 1/7/2006 0
> 1/8/2006 3
> 1/9/2006 0
> 1/20/2006 1
> Back in my access days, i just ran the maketable query, then used VB to
> 'pad' in as many zero values as needed, and then ran the report off the
> table.
> Any suggestions?|||If there are no rows for days with no tickets and you are using a query like..
select date, sum(ticketsales) from table group by date
you will get no rows for dates with no tickets... One way to do this would
be to create another table with one for each date /... Then your query could
be
select dt.date, sum(isnull(ticketsales,0)) from table inner join dttable dt
on dt.date = table.dt group by dt.date
You will get a row for each date...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Kaisa M. Lindahl" wrote:
> Where do you get the data from? Where does the numbers come from - are you
> summing in your query or is that done automagically somewhere in the
> database? And where are the dates and the numbers connected?
> My guess is that you have to make your SQL query return all 21 lines. If you
> use an outer join for dates and numbers, and do a Case test for the numbers,
> you should get all your rows.
> Kaisa M. Lindahl
> "Brent Maloney" <BrentMaloney@.discussions.microsoft.com> wrote in message
> news:7C5F489A-68D9-4681-88B9-324B77104AC7@.microsoft.com...
> > I'm trying to report on the number of tickets created by day for 21 days,
> > but
> > when there are no tickets logged on a day, i need a 0. I have been trying
> > to
> > get my query to return 0's for every day of the 21 days, but that hasn't
> > worked. I have tried to get the Matrix to show 21 days, but that hasn't
> > worked either. To add complexity, i need to graph this matrix, so i
> > cannot
> > simply use a sub-report. (otherwise it would be done.)
> >
> > A subset of the results from the query are as follows:
> > 1/5/2006 4
> > 1/6/2006 5
> > 1/8/2006 3
> > 1/20/2006 1
> >
> > What i need is:
> > 1/4/2006 0
> > 1/5/2006 4
> > 1/6/2006 5
> > 1/7/2006 0
> > 1/8/2006 3
> > 1/9/2006 0
> > 1/20/2006 1
> >
> > Back in my access days, i just ran the maketable query, then used VB to
> > 'pad' in as many zero values as needed, and then ran the report off the
> > table.
> >
> > Any suggestions?
>
>
when there are no tickets logged on a day, i need a 0. I have been trying to
get my query to return 0's for every day of the 21 days, but that hasn't
worked. I have tried to get the Matrix to show 21 days, but that hasn't
worked either. To add complexity, i need to graph this matrix, so i cannot
simply use a sub-report. (otherwise it would be done.)
A subset of the results from the query are as follows:
1/5/2006 4
1/6/2006 5
1/8/2006 3
1/20/2006 1
What i need is:
1/4/2006 0
1/5/2006 4
1/6/2006 5
1/7/2006 0
1/8/2006 3
1/9/2006 0
1/20/2006 1
Back in my access days, i just ran the maketable query, then used VB to
'pad' in as many zero values as needed, and then ran the report off the table.
Any suggestions?Where do you get the data from? Where does the numbers come from - are you
summing in your query or is that done automagically somewhere in the
database? And where are the dates and the numbers connected?
My guess is that you have to make your SQL query return all 21 lines. If you
use an outer join for dates and numbers, and do a Case test for the numbers,
you should get all your rows.
Kaisa M. Lindahl
"Brent Maloney" <BrentMaloney@.discussions.microsoft.com> wrote in message
news:7C5F489A-68D9-4681-88B9-324B77104AC7@.microsoft.com...
> I'm trying to report on the number of tickets created by day for 21 days,
> but
> when there are no tickets logged on a day, i need a 0. I have been trying
> to
> get my query to return 0's for every day of the 21 days, but that hasn't
> worked. I have tried to get the Matrix to show 21 days, but that hasn't
> worked either. To add complexity, i need to graph this matrix, so i
> cannot
> simply use a sub-report. (otherwise it would be done.)
> A subset of the results from the query are as follows:
> 1/5/2006 4
> 1/6/2006 5
> 1/8/2006 3
> 1/20/2006 1
> What i need is:
> 1/4/2006 0
> 1/5/2006 4
> 1/6/2006 5
> 1/7/2006 0
> 1/8/2006 3
> 1/9/2006 0
> 1/20/2006 1
> Back in my access days, i just ran the maketable query, then used VB to
> 'pad' in as many zero values as needed, and then ran the report off the
> table.
> Any suggestions?|||If there are no rows for days with no tickets and you are using a query like..
select date, sum(ticketsales) from table group by date
you will get no rows for dates with no tickets... One way to do this would
be to create another table with one for each date /... Then your query could
be
select dt.date, sum(isnull(ticketsales,0)) from table inner join dttable dt
on dt.date = table.dt group by dt.date
You will get a row for each date...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Kaisa M. Lindahl" wrote:
> Where do you get the data from? Where does the numbers come from - are you
> summing in your query or is that done automagically somewhere in the
> database? And where are the dates and the numbers connected?
> My guess is that you have to make your SQL query return all 21 lines. If you
> use an outer join for dates and numbers, and do a Case test for the numbers,
> you should get all your rows.
> Kaisa M. Lindahl
> "Brent Maloney" <BrentMaloney@.discussions.microsoft.com> wrote in message
> news:7C5F489A-68D9-4681-88B9-324B77104AC7@.microsoft.com...
> > I'm trying to report on the number of tickets created by day for 21 days,
> > but
> > when there are no tickets logged on a day, i need a 0. I have been trying
> > to
> > get my query to return 0's for every day of the 21 days, but that hasn't
> > worked. I have tried to get the Matrix to show 21 days, but that hasn't
> > worked either. To add complexity, i need to graph this matrix, so i
> > cannot
> > simply use a sub-report. (otherwise it would be done.)
> >
> > A subset of the results from the query are as follows:
> > 1/5/2006 4
> > 1/6/2006 5
> > 1/8/2006 3
> > 1/20/2006 1
> >
> > What i need is:
> > 1/4/2006 0
> > 1/5/2006 4
> > 1/6/2006 5
> > 1/7/2006 0
> > 1/8/2006 3
> > 1/9/2006 0
> > 1/20/2006 1
> >
> > Back in my access days, i just ran the maketable query, then used VB to
> > 'pad' in as many zero values as needed, and then ran the report off the
> > table.
> >
> > Any suggestions?
>
>
Friday, March 9, 2012
Missing SSAS Diagrams
I've created multiple SSAS diagrams and saved them back to the server. The next day, I connected to the server and the diagrams are gone. Rather than recreate all of them, I recreated one and tried saving it back to the server. I get the message stating "updating server", but when I close out and get back in the diagram is missing again. We have so many cubes we really need these so everyone can have a visualization of the structure. I even tried saving the project and reprocessing the database to no avail. Please help.
Many Thanks.
Scott,
When you say diagrams, are you referring to diagrams within the DSV for an SSAS project? Or something else?
Dave Fackler
|||Hi Dave,
Thanks for your reply. I've figured it out. The diagrams were just on the server. I had to create a new project and download a more recent copy of the SSAS database. Feeling really smart:)
Thanks again,
Scott
Missing SqlContext.GetCommand() in new namespace
I'm just starting with using the SQLCLR features in SSX, and I'm following the most recent Beta 2 MSDN docs. I created an SQL Server project and made a simple stored procedure method, and the problem now is the docs refer to SqlContext.GetCommand( ) inside the stored procedure method to return an SqlCommand object, but this method does not exist.
I did some searches and noticed that the MSDN docs still refer to the old System.Data.SqlServer namespace where it has been replaced by Microsoft.SqlServer.Server in Beta 2.In the latest CTP (April), the server side provider has been merged with the client side, so you no longer reference sqlaccess.dll.
In addition you no longer do SqlContext.Connection/Command etc., but you get your connection through:
SqlConnection conn = new SqlConnection("Context Connection=true");
subsequently you get create your command as you'd have done in a client app:
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
or:
SqlCommand cmd = conn.CreateCommand():
You use the SqlContext to get your Pipe object and for the TransactionContext etc.
Hope this helps!!
Niels|||Thanks. I've been looking for that. Is there a page/blog where I can see all those updates to the API?|||Pablo Castro (PM at MS) wrote a MSDN article about it here.
A couple of blogs that cover this stuff is mine and Bob Beauchemins. Pablo and his team at MS also has a blog which is worth following.
I did some searches and noticed that the MSDN docs still refer to the old System.Data.SqlServer namespace where it has been replaced by Microsoft.SqlServer.Server in Beta 2.In the latest CTP (April), the server side provider has been merged with the client side, so you no longer reference sqlaccess.dll.
In addition you no longer do SqlContext.Connection/Command etc., but you get your connection through:
SqlConnection conn = new SqlConnection("Context Connection=true");
subsequently you get create your command as you'd have done in a client app:
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
or:
SqlCommand cmd = conn.CreateCommand():
You use the SqlContext to get your Pipe object and for the TransactionContext etc.
Hope this helps!!
Niels|||Thanks. I've been looking for that. Is there a page/blog where I can see all those updates to the API?|||Pablo Castro (PM at MS) wrote a MSDN article about it here.
A couple of blogs that cover this stuff is mine and Bob Beauchemins. Pablo and his team at MS also has a blog which is worth following.
Missing SELECT statement, but where?
Hi
I have created a question in Access and then copied the SQL question from it.
When I try to run it in eg SQL plus I get an error. I can't see what's wrong.
SQL> SELECT MC_POSUM.ID, MC_POLINE.ID, MC_INVTRANS.TQTY_AMT, MC_PCITEM.ITID, MC_INVTRANS.PRSD_DTTM,
MC_EMPLOYEE.AENM
2 FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_P
ODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI = MC_INVTR
ANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI = MC_EMPLOYEE.EMPOI) INNER JOIN MC_
PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC
_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE AS MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI
3 WHERE (((MC_EMPLOYEE_1.AENM) Not Like [MC_EMPLOYEE.AENM]) AND ((MC_STOREROOM.STOROI) Not Like 3
2050) AND ((MC_INVTRANS.TRNTYP) Like 5));
FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_PODEL
*
ERROR at line 2:
ORA-00928: missing SELECT keyword
hi christina,
First off this is a SQL Server newsgroup. So try putting your question in some of the oracle
newgroup/forum. However, with some oracle knowledge i've here are little annotations on my
part.
As far as i know ansi sql syntaxes are only available in oracle from oracle9i so if you are
using oralce8i this is not supported.
Also, enclosing identifiers in square brackets is syntactically correct in SQL Server and not
in Oracle. See following corrected query(should work in oracle9i).
SELECT MC_POSUM.POSUMOI
FROM
--one bracket missing below
(((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN
MC_PODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI
= MC_INVTRANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI =
MC_EMPLOYEE.EMPOI) INNER JOIN MC_PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN
MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE
MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI) --remove as
WHERE (((MC_EMPLOYEE_1.AENM) Not Like MC_EMPLOYEE.AENM) AND ((MC_STOREROOM.STOROI) Not Like
32050) AND ((MC_INVTRANS.TRNTYP) Like 5)) --no square bracket required.
--End of query
MC_PODEL
Also once where clause is over it is syntactically incorrect to use FROM clause again.
Vishal Parkar
vgparkar@.yahoo.co.in
I have created a question in Access and then copied the SQL question from it.
When I try to run it in eg SQL plus I get an error. I can't see what's wrong.
SQL> SELECT MC_POSUM.ID, MC_POLINE.ID, MC_INVTRANS.TQTY_AMT, MC_PCITEM.ITID, MC_INVTRANS.PRSD_DTTM,
MC_EMPLOYEE.AENM
2 FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_P
ODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI = MC_INVTR
ANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI = MC_EMPLOYEE.EMPOI) INNER JOIN MC_
PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC
_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE AS MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI
3 WHERE (((MC_EMPLOYEE_1.AENM) Not Like [MC_EMPLOYEE.AENM]) AND ((MC_STOREROOM.STOROI) Not Like 3
2050) AND ((MC_INVTRANS.TRNTYP) Like 5));
FROM ((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN MC_PODEL
*
ERROR at line 2:
ORA-00928: missing SELECT keyword
hi christina,
First off this is a SQL Server newsgroup. So try putting your question in some of the oracle
newgroup/forum. However, with some oracle knowledge i've here are little annotations on my
part.
As far as i know ansi sql syntaxes are only available in oracle from oracle9i so if you are
using oralce8i this is not supported.
Also, enclosing identifiers in square brackets is syntactically correct in SQL Server and not
in Oracle. See following corrected query(should work in oracle9i).
SELECT MC_POSUM.POSUMOI
FROM
--one bracket missing below
(((((((MC_POSUM INNER JOIN MC_POLINE ON MC_POSUM.POSUMOI = MC_POLINE.PO_OI) INNER JOIN
MC_PODEL ON MC_POLINE.POLNOI = MC_PODEL.POLINE_OI) INNER JOIN MC_INVTRANS ON MC_PODEL.PODELOI
= MC_INVTRANS.PODEL_OI) INNER JOIN MC_EMPLOYEE ON MC_INVTRANS.AUDT_USER_OI =
MC_EMPLOYEE.EMPOI) INNER JOIN MC_PCITEM ON MC_POLINE.PCITEM_OI = MC_PCITEM.PCITOI) LEFT JOIN
MC_STOREROOM ON MC_INVTRANS.STOR_OI = MC_STOREROOM.STOROI) INNER JOIN MC_EMPLOYEE
MC_EMPLOYEE_1 ON MC_POSUM.BUY_OI = MC_EMPLOYEE_1.EMPOI) --remove as
WHERE (((MC_EMPLOYEE_1.AENM) Not Like MC_EMPLOYEE.AENM) AND ((MC_STOREROOM.STOROI) Not Like
32050) AND ((MC_INVTRANS.TRNTYP) Like 5)) --no square bracket required.
--End of query
MC_PODEL
Also once where clause is over it is syntactically incorrect to use FROM clause again.
Vishal Parkar
vgparkar@.yahoo.co.in
Missing rows when viewing data in Report Builder. What gives?
I am very confused. To simplify this, I have a report model with two tables named Unit and Unit_Type. I have created and published this model to our Report Server.
When I build an ad-hoc report against this model, I end up with 2 different scenarios.
The first scenario is as follows:
I select a single attribute from the entity "Unit_Number" and add it to my report. I run the report and the footer of the report displays 10576 rows. I then export the report to Excel and when I check the actual number of records, there are only 7652 rows. I have confirmed the correct number of records by writing a simple SQL query which also generates a result with 10576 rows.
The same results are achieved if I add the attribute "Type_Code" from the "Unit_Type" table IF the Type_Code is part of the "Unit" group. To be clear, if I first add an attribute from the Unit table and then add an attribute from the Unit_Type table, my report has a single group and both entities belong to that group. This format will never give me correct number of records.
One more important piece of info... I have validated that there are absolutely NO duplicate records in my result set. All 10575 rows are unique.
The second scenario is as follows:
Rather than first selecting from the Unit table, I select "Unit_Type" from the "Unit_Type" table and then I add the attribute "Unit_Number" from the Unit table. Report Builder creates 2 groups when I set up the report this way.
The result when I run this report is 10576 rows when I import my report using this scenario into Excel! This is correct!
Question
So, why does Report Builder generate an incorrect result set unless attributes from both entities are included in the report and the attributes are in seperate groups? This makes no sense to me and will be extremely difficult to explain to our users.
I would really appreciate any feedback on this.
Scott
Are you running Service Pack 1? We fixed a bug with the wrong number of rows in Report Builder.|||I have the same problem, the solution was not to use Report Builder because it give you wrong results , its not a question of service pack or version, if you have a tool that don't give correct result's can you tell your client, "sorry but you have order $100.000 of product that you have in stock call microsoft they will give your money back"
Saturday, February 25, 2012
Missing Information from Database
I have a serious problem with either SQL Server or SQL Reporting Services. I have 7 tables in a database that are relational. I have created serveral reports against this database. Last week when I pulled one of the reports, I was missing information on one of the cells. The reason why I know this is because my boss has us verify the report against the database. That took about an hour to complete. When we found that the report was not correct I repulled the report and the cell that was empty, was now populated. What gives? This is the same report I have pulled for the last month and had no problems until now. Is there a time issue involved between the last update to the database to the time I pull the report? Or maybe there is something wrong with the query?
SELECT Lease.[Lease #], MineralContacts.[Mineral Owner First Name], MineralContacts.[Mineral Owner Last Name], Tract.County, Tract.[Tract Number],
Tract.[Tract Description], Tract.Section, Tract.Block, Tract.Survey, Tract.Abstract, Lease.[Lease Date], Tract.[Title Check Though], Tract.[2ndTitleReview],
Tract.[2nd R/S Complete], Tract.[Title Agent], Tract.[Tract Gross Acres], SubTract.[Tract Net Acres], Draft.[Draft Status], Draft.[Draft Due Date],
Draft.[Draft Amount], Draft.[Draft #], Draft.[ANB Invoice #], Draft.[Lse File Sent to CP], Draft.[Money Wired from CP to EA],
Draft.[STA Approved Draft for pmt], Draft.[STA Recommend to Return Daft], Draft.[KE Advised ANB to Pay Draft], Draft.[KE Advised ANB to Return Draft],
Draft.[Paid Draft Recd], SubTract.Comments, SubTract.Hide
FROM Tract INNER JOIN
SubTract ON Tract.TractID = SubTract.TractID INNER JOIN
Mineral ON SubTract.SubTractID = Mineral.SubTractID INNER JOIN
Lease ON Mineral.MineralID = Lease.MineralID INNER JOIN
Draft ON Lease.LeaseID = Draft.LeaseID INNER JOIN
MineralContacts ON Mineral.MineralID = MineralContacts.MineralID
WHERE (Draft.[Draft Status] IS NOT NULL) AND (Draft.[Draft Due Date] IS NOT NULL)
Anyone have an answer?
Missing Information from Database
I have a serious problem with either SQL Server or SQL Reporting Services. I have 7 tables in a database that are relational. I have created serveral reports against this database. Last week when I pulled one of the reports, I was missing information on one of the cells. The reason why I know this is because my boss has us verify the report against the database. That took about an hour to complete. When we found that the report was not correct I repulled the report and the cell that was empty, was now populated. What gives? This is the same report I have pulled for the last month and had no problems until now. Is there a time issue involved between the last update to the database to the time I pull the report? Or maybe there is something wrong with the query?
SELECT Lease.[Lease #], MineralContacts.[Mineral Owner First Name], MineralContacts.[Mineral Owner Last Name], Tract.County, Tract.[Tract Number],
Tract.[Tract Description], Tract.Section, Tract.Block, Tract.Survey, Tract.Abstract, Lease.[Lease Date], Tract.[Title Check Though], Tract.[2ndTitleReview],
Tract.[2nd R/S Complete], Tract.[Title Agent], Tract.[Tract Gross Acres], SubTract.[Tract Net Acres], Draft.[Draft Status], Draft.[Draft Due Date],
Draft.[Draft Amount], Draft.[Draft #], Draft.[ANB Invoice #], Draft.[Lse File Sent to CP], Draft.[Money Wired from CP to EA],
Draft.[STA Approved Draft for pmt], Draft.[STA Recommend to Return Daft], Draft.[KE Advised ANB to Pay Draft], Draft.[KE Advised ANB to Return Draft],
Draft.[Paid Draft Recd], SubTract.Comments, SubTract.Hide
FROM Tract INNER JOIN
SubTract ON Tract.TractID = SubTract.TractID INNER JOIN
Mineral ON SubTract.SubTractID = Mineral.SubTractID INNER JOIN
Lease ON Mineral.MineralID = Lease.MineralID INNER JOIN
Draft ON Lease.LeaseID = Draft.LeaseID INNER JOIN
MineralContacts ON Mineral.MineralID = MineralContacts.MineralID
WHERE (Draft.[Draft Status] IS NOT NULL) AND (Draft.[Draft Due Date] IS NOT NULL)
Anyone have an answer?
Monday, February 20, 2012
Missing expansion images on Matrix Report
I've created some Matrix reports which work just fine, I'm able to drill down and the expansion images(+/-) appear correctly.
However, after setting Role security on the folder containing the reports(this is an asp.net application), the reports still function but the images for the (+/-) are missing, just getting the missing image icon.
Any ideas?
Thanks, Burl
Ok, seems the problem is with the <authorization> element in the web.config file. Changed it form <deny users="*"> to <deny users="?"> and my toggle images started working. Seems to be a security problem for the builtin resource file on the matrix report.
Subscribe to:
Posts (Atom)