Problems getting all the data back with a XML query

I am having a nightmare trying to get to the bottom of this problem.
I have a stored procedure that has a user defined function to create a
select table from a csv string of IDs.
The function and the Stored procedure works fine and returns the XML,
Elements as required except that when i process this sp on the client most
of the data is missing.
If i run the query in the query analyser with the csv parameter and no FOR
XML output i get 72 records (10 unique resources, the rest are due to the
joins in the query)
If i run it with the FOR XML i get 6 lines of XML although it is oddly
truncated. If i cut and past it into xmlspy there are missing elements so i
can't verify exactly what i'm getting.
But when i run this in my web app and use am XMLTextReader to turn the
results into a string I get 2 records, when i was expecting 10. I can verify
this by pasting the xml into xmlspy.
I have tried SQLXML managed classes but can't get them to accept parameters,
i keep getting the message that the sp is expecting parameter despite using
createParameter against the command as per all the documentation, so i gave
up with this approach. I thought that this way i could populate a new
dataset and then bind a table to a datagrid control to see what was
returned. I can get it all to work bar the parameters.
Can anyone shed any light on why the results are significantly less than the
sp should return
and can someone give me an example of working with a parameterised sp and
SQLXML
Many thanks
John
VS2003, SQLXML sp2, XPpro sp2
I am confused about what you are trying to get.
FOR XML results a single XML stream if you use the supported APIs through
ADO.Net, ADO, or OLEDB's stream interfaces. Query Analyser is using ODBC and
thus shows the XML result junked into 2033 characters per row. You should
not use QA if you plan on further process the result.
So if you can provide us with some more information about what exactly you
do on the API level, we may be able to help...
Best regards
Michael
"John Mas" <mase@.btopenworld.org> wrote in message
news:G2x8d.316$Xy3.217@.newsfe6-gui.ntli.net...
>I am having a nightmare trying to get to the bottom of this problem.
> I have a stored procedure that has a user defined function to create a
> select table from a csv string of IDs.
> The function and the Stored procedure works fine and returns the XML,
> Elements as required except that when i process this sp on the client most
> of the data is missing.
> If i run the query in the query analyser with the csv parameter and no FOR
> XML output i get 72 records (10 unique resources, the rest are due to the
> joins in the query)
> If i run it with the FOR XML i get 6 lines of XML although it is oddly
> truncated. If i cut and past it into xmlspy there are missing elements so
> i can't verify exactly what i'm getting.
> But when i run this in my web app and use am XMLTextReader to turn the
> results into a string I get 2 records, when i was expecting 10. I can
> verify this by pasting the xml into xmlspy.
> I have tried SQLXML managed classes but can't get them to accept
> parameters, i keep getting the message that the sp is expecting parameter
> despite using createParameter against the command as per all the
> documentation, so i gave up with this approach. I thought that this way i
> could populate a new dataset and then bind a table to a datagrid control
> to see what was returned. I can get it all to work bar the parameters.
> Can anyone shed any light on why the results are significantly less than
> the sp should return
> and can someone give me an example of working with a parameterised sp and
> SQLXML
>
> Many thanks
> John
> VS2003, SQLXML sp2, XPpro sp2
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.xml:25072
Michael,
thanks I have sorted the problem eventually. Brain fade and not enough time
thinking it throgh. The problem was in the sql statement which took ages to
track down but there we go.
One question that is unanswered is how do i pass parameters to SQLXML with
stored procedures, not raw sql text?
i keep getting the message expecting parameter as per my post
thanks
john
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Ok7uv9DrEHA.2796@.TK2MSFTNGP10.phx.gbl...
>I am confused about what you are trying to get.
> FOR XML results a single XML stream if you use the supported APIs through
> ADO.Net, ADO, or OLEDB's stream interfaces. Query Analyser is using ODBC
> and thus shows the XML result junked into 2033 characters per row. You
> should not use QA if you plan on further process the result.
> So if you can provide us with some more information about what exactly you
> do on the API level, we may be able to help...
> Best regards
> Michael
> "John Mas" <mase@.btopenworld.org> wrote in message
> news:G2x8d.316$Xy3.217@.newsfe6-gui.ntli.net...
>
|||To what exactly do you want to pass parameters? SQLXML is a general term and
the name of the mid-tier component.
Do you mean how to pass parameters into the SQL statement that uses FOR XML
via stored procs?
Thanks
Michael
"John Mas" <mase@.btopenworld.org> wrote in message
news:Fmz9d.270$Vd.96@.newsfe5-win.ntli.net...
> Michael,
> thanks I have sorted the problem eventually. Brain fade and not enough
> time thinking it throgh. The problem was in the sql statement which took
> ages to track down but there we go.
> One question that is unanswered is how do i pass parameters to SQLXML with
> stored procedures, not raw sql text?
> i keep getting the message expecting parameter as per my post
> thanks
> john
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:Ok7uv9DrEHA.2796@.TK2MSFTNGP10.phx.gbl...
>
|||Michael
here is the code that i am using
Dim strConn = "provider=SQLOLEDB;data source='....';initial
catalog=......;user id=sa;password=......"
Dim sxcCmd As New SqlXmlCommand(strConn)
Dim sdaDA As New SqlXmlAdapter(sxcCmd)
Dim sxpParam As SqlXmlParameter
Dim xr As Xml.XmlReader
Dim ds As New DataSet
With sxcCmd
..RootTag = "root"
..CommandType = SqlXmlCommandType.Sql
..CommandText = "Test2"
sxpParam = .CreateParameter
End With
With sxpParam
..Name = "@.IDs"
..Value = 5
End With
sdaDA.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
and here is the sp
ALTER PROCEDURE dbo.test2
(
@.IDs int
)
AS
/* SET NOCOUNT ON */
SELECT '<root>'
SELECT * FROM tblResource WHERE ResourceID=@.Ids
FOR XML AUTO, Elements
SELECT '</root>'
as you can see the sp has this parameter @.IDs but when i run the code the
error meesage says ' expecting parameter @.IDs'' yet i am passing it. If i
change the command text to a sql statement with a ? for the parameter then
it works.
Obviously i am missing something here, I presume the command type might be
wrongly set.
thanks
john
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:O45cbdZrEHA.1964@.TK2MSFTNGP12.phx.gbl...
> To what exactly do you want to pass parameters? SQLXML is a general term
> and the name of the mid-tier component.
> Do you mean how to pass parameters into the SQL statement that uses FOR
> XML via stored procs?
> Thanks
> Michael
> "John Mas" <mase@.btopenworld.org> wrote in message
> news:Fmz9d.270$Vd.96@.newsfe5-win.ntli.net...
>

Problems exporting Reports programatically

I am simply trying to use ActiveX to export a Report to another format, programatically, i.e. without the user having to select a format using the Export dialog.

I can set up all the properties in the ExportOptions object that is part of the Report Object itself, but these do not appear to have any effect when I call the the Export() method on the Report object; which simply brings up the Export dialog with default values.

In fact, even after entering different values through the Export dialog, these do not appear to change the properties that are found in the ExportOptions object. These properties are as what they were coded previously, i.e. unchanged

It's as though the IReport::Export() method and the ExportOptions object are totally separate... Can anyone help to see what I am missing?Try passing "False" to Export so that it does not prompt for the values - from memory, that's what I had to do to get it working.

problems creating a datatable within a UDF in C#

Hi!

I'm having a lot of difficulty in creating a user defined function in vs2005.

why I'm doing it in c# instead of vb or tsql is that I know C# better than the others (that and tsql won't let you make temp tables in functions

But as far as this goes, I can't seem to get this working.

I' trying to execute a simple select statement and get a datatable variable before I run it through the creative algorithm I have planned.

But I can't seem to get the data into a table.

I get this exception when I execute the function on the server:

System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

This is the code I use to get the data:

using(SqlConnection sqlcnct = new SqlConnection("Context Connection=true"))
{ SqlCommand sqlcmd = new SqlCommand(sqlstrng, sqlcnct);

sqlcnct.Open();
SqlDataReader sqldr = sqlcmd.ExecuteReader();

while (sqldr.Read())
{
//inserts into datatable here (I don't yet have code here because i've been trying to figure out how to get it to read first. )
}
}

But unfortunately SQL server 2005 doesn't like that.

Can anyone point me in the right direction or shoot down my hopes right here?

I'm trying to use a function to avoid having to run a sproc on every employee on the employee table and storing it in an actual field /table every time I need to run this function.Two things:

1. In order to access database data from a CLR UDF you need to set the DataAccess and SystemDataAccess properties of the SqlFunction attribute to DataAccessKind.Read, and SystemDataAccessKind.Read:
[SqlFunction(DataAccess=DataAccessKind.Read, SystemDataAccess.SystenDataAccessKind.Read)]
your method name

2. You can not update/insert/delete data from an UDF

HTH

Niels
|||Thanks! It works perfectly now. I'm new to writing functions for sqlserver in visual studio so I had no idea i needed to do that. Fortunately I didn't need to update/insert/delete any data. =)

The reason I needed to do this was because the data I need to work with needs to be picked out of a table and sorted and then calculated from but sqlserver wasn't letting me make a temporary table in a function.

Problems connecting to SQL-Server

Hi,
we have an customer trying to run our app. after installation. They get the
error messeage: Login failed for user skdomain/skuser.
Our app uses ADO.NET and the database is MS SQL Server. The program and sql
server is installed on a separate server. To run the app. they have 8
application servers with metaframe. Our app is set up to connect to the
database using WIN NT autentication.
Our app. runs fine if the user log in as domain administrator.
Is skdomain\skuser been granted access with a login in SQL Server? Do
they have the required users added to the server as well as setup as
users in the database?
-Sue
On Mon, 25 Oct 2004 08:23:03 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:

>Hi,
>we have an customer trying to run our app. after installation. They get the
>error messeage: Login failed for user skdomain/skuser.
>Our app uses ADO.NET and the database is MS SQL Server. The program and sql
>server is installed on a separate server. To run the app. they have 8
>application servers with metaframe. Our app is set up to connect to the
>database using WIN NT autentication.
>Our app. runs fine if the user log in as domain administrator.
|||Hi Sue,
i dont follow you here. My be because i use a standalone XP Pro PC?
Where do i "click" to grant access with a login in SQL Servers? Can i see it
on my PC or must i use my customers?
Olav
"Sue Hoegemeier" wrote:

> Is skdomain\skuser been granted access with a login in SQL Server? Do
> they have the required users added to the server as well as setup as
> users in the database?
> -Sue
> On Mon, 25 Oct 2004 08:23:03 -0700, "Olav"
> <Olav@.discussions.microsoft.com> wrote:
>
>
|||You won't see skdomain\skuser on your PC. A windows login is
specific to the domain or workgroup. When using windows
authentication, any login you want to have access to SQL
Server needs to be added as a login for SQL Server. In
Enterprise Manager go to Security and then to Logins. Right
click on select New Login. Click the button to the right of
the Name text box and you will pull up the domain or
workgroup logins that you can add as logins for SQL Server.
-Sue
On Wed, 27 Oct 2004 03:37:04 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue,
>i dont follow you here. My be because i use a standalone XP Pro PC?
>Where do i "click" to grant access with a login in SQL Servers? Can i see it
>on my PC or must i use my customers?
>Olav
>"Sue Hoegemeier" wrote:
|||Thanks Sue,
this can take som time when there is 200 users to add?
May be a faster approach is to use SQL authentication.
Then it would work with only one user registered the same place?
Is there a difference in connection speed with NT and SQL authentication?
Olav
"Sue Hoegemeier" wrote:

> You won't see skdomain\skuser on your PC. A windows login is
> specific to the domain or workgroup. When using windows
> authentication, any login you want to have access to SQL
> Server needs to be added as a login for SQL Server. In
> Enterprise Manager go to Security and then to Logins. Right
> click on select New Login. Click the button to the right of
> the Name text box and you will pull up the domain or
> workgroup logins that you can add as logins for SQL Server.
> -Sue
> On Wed, 27 Oct 2004 03:37:04 -0700, "Olav"
> <Olav@.discussions.microsoft.com> wrote:
>
>
|||When you have a lot of users, it's generally more efficient to use
Windows groups rather than individual Windows logins. Another
consideration is that you don't necessarily have to use just
Enterprise Manager to add the logins - you can execute sp_grantlogin
to grant a windows account login access to SQL Server. You can use
sp_grantdbaccess to allow the login access to a database. This can
give you more flexibility in terms of building a script the users
access.
There is no real performance difference between the two authentication
modes. Windows Authentication is more secure than using SQL logins.
You may also want to check SQL Server books online and read up on
application roles as that may be something you would want to look
into.
-Sue
On Wed, 27 Oct 2004 06:47:06 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Sue,
>this can take som time when there is 200 users to add?
>May be a faster approach is to use SQL authentication.
>Then it would work with only one user registered the same place?
>Is there a difference in connection speed with NT and SQL authentication?
>Olav
>
>"Sue Hoegemeier" wrote:
|||Using a single SQL Server auth login for 200 users is not very secure.
Although I'm sure the DBA will probably appreciate getting out and talking
to all 200 employees every time he decides to change that password.
Tracking down any security issues should be easier, since you don't actually
have to track 200 users; just track the one login and blame all 200 users!
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:A35FA747-EAF6-44CE-AAAB-659A6C80AB14@.microsoft.com...[vbcol=seagreen]
> Thanks Sue,
> this can take som time when there is 200 users to add?
> May be a faster approach is to use SQL authentication.
> Then it would work with only one user registered the same place?
> Is there a difference in connection speed with NT and SQL authentication?
> Olav
>
> "Sue Hoegemeier" wrote:
see it[vbcol=seagreen]
get the[vbcol=seagreen]
and sql[vbcol=seagreen]
8[vbcol=seagreen]
the[vbcol=seagreen]

Problems connecting to SQL-Server

Hi,
we have an customer trying to run our app. after installation. They get the
error messeage: Login failed for user skdomain/skuser.
Our app uses ADO.NET and the database is MS SQL Server. The program and sql
server is installed on a separate server. To run the app. they have 8
application servers with metaframe. Our app is set up to connect to the
database using WIN NT autentication.
Our app. runs fine if the user log in as domain administrator.Is skdomain\skuser been granted access with a login in SQL Server? Do
they have the required users added to the server as well as setup as
users in the database?
-Sue
On Mon, 25 Oct 2004 08:23:03 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:

>Hi,
>we have an customer trying to run our app. after installation. They get the
>error messeage: Login failed for user skdomain/skuser.
>Our app uses ADO.NET and the database is MS SQL Server. The program and sql
>server is installed on a separate server. To run the app. they have 8
>application servers with metaframe. Our app is set up to connect to the
>database using WIN NT autentication.
>Our app. runs fine if the user log in as domain administrator.|||Hi Sue,
i dont follow you here. My be because i use a standalone XP Pro PC?
Where do i "click" to grant access with a login in SQL Servers? Can i see it
on my PC or must i use my customers?
Olav
"Sue Hoegemeier" wrote:

> Is skdomain\skuser been granted access with a login in SQL Server? Do
> they have the required users added to the server as well as setup as
> users in the database?
> -Sue
> On Mon, 25 Oct 2004 08:23:03 -0700, "Olav"
> <Olav@.discussions.microsoft.com> wrote:
>
>|||You won't see skdomain\skuser on your PC. A windows login is
specific to the domain or workgroup. When using windows
authentication, any login you want to have access to SQL
Server needs to be added as a login for SQL Server. In
Enterprise Manager go to Security and then to Logins. Right
click on select New Login. Click the button to the right of
the Name text box and you will pull up the domain or
workgroup logins that you can add as logins for SQL Server.
-Sue
On Wed, 27 Oct 2004 03:37:04 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue,
>i dont follow you here. My be because i use a standalone XP Pro PC?
>Where do i "click" to grant access with a login in SQL Servers? Can i see i
t
>on my PC or must i use my customers?
>Olav
>"Sue Hoegemeier" wrote:
>|||Thanks Sue,
this can take som time when there is 200 users to add?
May be a faster approach is to use SQL authentication.
Then it would work with only one user registered the same place?
Is there a difference in connection speed with NT and SQL authentication?
Olav
"Sue Hoegemeier" wrote:

> You won't see skdomain\skuser on your PC. A windows login is
> specific to the domain or workgroup. When using windows
> authentication, any login you want to have access to SQL
> Server needs to be added as a login for SQL Server. In
> Enterprise Manager go to Security and then to Logins. Right
> click on select New Login. Click the button to the right of
> the Name text box and you will pull up the domain or
> workgroup logins that you can add as logins for SQL Server.
> -Sue
> On Wed, 27 Oct 2004 03:37:04 -0700, "Olav"
> <Olav@.discussions.microsoft.com> wrote:
>
>|||When you have a lot of users, it's generally more efficient to use
Windows groups rather than individual Windows logins. Another
consideration is that you don't necessarily have to use just
Enterprise Manager to add the logins - you can execute sp_grantlogin
to grant a windows account login access to SQL Server. You can use
sp_grantdbaccess to allow the login access to a database. This can
give you more flexibility in terms of building a script the users
access.
There is no real performance difference between the two authentication
modes. Windows Authentication is more secure than using SQL logins.
You may also want to check SQL Server books online and read up on
application roles as that may be something you would want to look
into.
-Sue
On Wed, 27 Oct 2004 06:47:06 -0700, "Olav"
<Olav@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Sue,
>this can take som time when there is 200 users to add?
>May be a faster approach is to use SQL authentication.
>Then it would work with only one user registered the same place?
>Is there a difference in connection speed with NT and SQL authentication?
>Olav
>
>"Sue Hoegemeier" wrote:
>|||Using a single SQL Server auth login for 200 users is not very secure.
Although I'm sure the DBA will probably appreciate getting out and talking
to all 200 employees every time he decides to change that password.
Tracking down any security issues should be easier, since you don't actually
have to track 200 users; just track the one login and blame all 200 users!
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:A35FA747-EAF6-44CE-AAAB-659A6C80AB14@.microsoft.com...[vbcol=seagreen]
> Thanks Sue,
> this can take som time when there is 200 users to add?
> May be a faster approach is to use SQL authentication.
> Then it would work with only one user registered the same place?
> Is there a difference in connection speed with NT and SQL authentication?
> Olav
>
> "Sue Hoegemeier" wrote:
>
see it[vbcol=seagreen]
get the[vbcol=seagreen]
and sql[vbcol=seagreen]
8[vbcol=seagreen]
the[vbcol=seagreen]

Problems configuring proxy account

Hi, at this moment I have a user that is member of sysadmin in a msde. I
need to remove this user from this group but the user use the
xp_cmdshell.
I configured the proxy account on this intance with a user that is
domain admin and also grant the exec permissions on the xp_cmdshell
extended store procedure, and when I execute a simple query:
exec master.dbo.xp_cmdshell 'dir c:'
returns this error message:
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1813 from CreateProcessAsUser on line 636
I already restart de sqlserveragent service, but it doesnt work.
Do someone know what could be the reason.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
It seems like the service account for the SQL Server service lacks some of the windows Privileges
needed. Search for below in Books Online and you will find what those are:
"level token"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maria Isabel Guzman" <mariaisabelguzman@.icasa.com.gt> wrote in message
news:%237yaKpegHHA.4064@.TK2MSFTNGP02.phx.gbl...
> Hi, at this moment I have a user that is member of sysadmin in a msde. I
> need to remove this user from this group but the user use the
> xp_cmdshell.
> I configured the proxy account on this intance with a user that is
> domain admin and also grant the exec permissions on the xp_cmdshell
> extended store procedure, and when I execute a simple query:
> exec master.dbo.xp_cmdshell 'dir c:'
> returns this error message:
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 1813 from CreateProcessAsUser on line 636
> I already restart de sqlserveragent service, but it doesnt work.
> Do someone know what could be the reason.
> Thanks a lot for your help.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks a lot for your help. I already check and the service account i
use is a domainadmin and domainadmins are administrator of the server.
do you have any other clue?
*** Sent via Developersdex http://www.codecomments.com ***
|||Domain admin isn't enough. You need to make sure it has privileges like "Replace a Process Level
Token" and the other stuff mentioned in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MariaGuzman" <marisa@.devdex.com> wrote in message news:%236iAzLhgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> Thanks a lot for your help. I already check and the service account i
> use is a domainadmin and domainadmins are administrator of the server.
> do you have any other clue?
>
> *** Sent via Developersdex http://www.codecomments.com ***

problem: user account access (always uses the same account)

I've configured the IIS with only windows authentication, when i connect to
Reporting Server or Reporting Administrator uses the windows account, but
the security rules work different:
I have configured rules for two accounts: my windows account and
IUSR_machine.
When i try to connect to servers i put my windows account and goes ok, but
the security rules works with the rules assigned to the iusr_machine. Any
rule assigned to my user doesn't have effect, and all the changes makes in
the IUSR_machine's rules have effect.
I've configured restrict access to the iusr_machine and grant permission for
my windows account. When i connect with my windows account and i try to do
any task that iusr_machine haven't permission, the server response me that
the iusr_machine haven't permmission to do it.
Any idea?
thank you.
Oliver
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> escribió en el
mensaje news:OT0ifxqZEHA.212@.TK2MSFTNGP12.phx.gbl...
> Usually this is because anonymous is set on the IIS web site settings.
>
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "oli" <oli1350@.hotmail.com> wrote in message
> news:eHPoJtZZEHA.3132@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > I'm having problem with the authentication in reports. When a user
sign
> > in to Report Administrator or Report Server validates with the IIS'
> > configuration. Onces inside always use the same user with the rules
> > security: IWAM_machine.
> > I need the account used inside of Reporting Server/Reporting
> > Administration
> > should be the same that the account used to validates with IIS.
> >
> > It's possible'
> >
> > Thank you in advanced.
> >
> > Oliver
> >
> >
>
>You have to disable anonymous access for both Reports and Reportserver
virtual directories.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"oli" <oli1350@.hotmail.com> wrote in message
news:#YUfIwBaEHA.212@.TK2MSFTNGP12.phx.gbl...
> I've configured the IIS with only windows authentication, when i connect
to
> Reporting Server or Reporting Administrator uses the windows account, but
> the security rules work different:
> I have configured rules for two accounts: my windows account and
> IUSR_machine.
> When i try to connect to servers i put my windows account and goes ok, but
> the security rules works with the rules assigned to the iusr_machine. Any
> rule assigned to my user doesn't have effect, and all the changes makes in
> the IUSR_machine's rules have effect.
> I've configured restrict access to the iusr_machine and grant permission
for
> my windows account. When i connect with my windows account and i try to do
> any task that iusr_machine haven't permission, the server response me
that
> the iusr_machine haven't permmission to do it.
> Any idea?
> thank you.
> Oliver
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> escribió en el
> mensaje news:OT0ifxqZEHA.212@.TK2MSFTNGP12.phx.gbl...
> > Usually this is because anonymous is set on the IIS web site settings.
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "oli" <oli1350@.hotmail.com> wrote in message
> > news:eHPoJtZZEHA.3132@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > > I'm having problem with the authentication in reports. When a user
> sign
> > > in to Report Administrator or Report Server validates with the IIS'
> > > configuration. Onces inside always use the same user with the rules
> > > security: IWAM_machine.
> > > I need the account used inside of Reporting Server/Reporting
> > > Administration
> > > should be the same that the account used to validates with IIS.
> > >
> > > It's possible'
> > >
> > > Thank you in advanced.
> > >
> > > Oliver
> > >
> > >
> >
> >
>|||I uses the windows integrated authentication in both, but with the same
result: the rules works like if i authenticates with iwam_machine.
"Tudor Trufinescu (MSFT)" <tudortr@.ms.com> escribió en el mensaje
news:utWbctEaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> You have to disable anonymous access for both Reports and Reportserver
> virtual directories.
> --
> Tudor Trufinescu
> Dev Lead
> Sql Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "oli" <oli1350@.hotmail.com> wrote in message
> news:#YUfIwBaEHA.212@.TK2MSFTNGP12.phx.gbl...
> > I've configured the IIS with only windows authentication, when i connect
> to
> > Reporting Server or Reporting Administrator uses the windows account,
but
> > the security rules work different:
> > I have configured rules for two accounts: my windows account and
> > IUSR_machine.
> >
> > When i try to connect to servers i put my windows account and goes ok,
but
> > the security rules works with the rules assigned to the iusr_machine.
Any
> > rule assigned to my user doesn't have effect, and all the changes makes
in
> > the IUSR_machine's rules have effect.
> >
> > I've configured restrict access to the iusr_machine and grant permission
> for
> > my windows account. When i connect with my windows account and i try to
do
> > any task that iusr_machine haven't permission, the server response me
> that
> > the iusr_machine haven't permmission to do it.
> >
> > Any idea?
> >
> > thank you.
> >
> > Oliver
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> escribió en el
> > mensaje news:OT0ifxqZEHA.212@.TK2MSFTNGP12.phx.gbl...
> > > Usually this is because anonymous is set on the IIS web site settings.
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "oli" <oli1350@.hotmail.com> wrote in message
> > > news:eHPoJtZZEHA.3132@.TK2MSFTNGP10.phx.gbl...
> > > > Hi,
> > > > I'm having problem with the authentication in reports. When a
user
> > sign
> > > > in to Report Administrator or Report Server validates with the IIS'
> > > > configuration. Onces inside always use the same user with the rules
> > > > security: IWAM_machine.
> > > > I need the account used inside of Reporting Server/Reporting
> > > > Administration
> > > > should be the same that the account used to validates with IIS.
> > > >
> > > > It's possible'
> > > >
> > > > Thank you in advanced.
> > > >
> > > > Oliver
> > > >
> > > >
> > >
> > >
> >
> >
>|||sorry,
i wanted to say i uses only windows integrated authentication in both(not
uses anonymous access)
"oli" <oli1350@.hotmail.com> escribió en el mensaje
news:ub%23HEVLaEHA.3420@.TK2MSFTNGP12.phx.gbl...
> I uses the windows integrated authentication in both, but with the same
> result: the rules works like if i authenticates with iwam_machine.
>
> "Tudor Trufinescu (MSFT)" <tudortr@.ms.com> escribió en el mensaje
> news:utWbctEaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> > You have to disable anonymous access for both Reports and Reportserver
> > virtual directories.
> >
> > --
> > Tudor Trufinescu
> > Dev Lead
> > Sql Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "oli" <oli1350@.hotmail.com> wrote in message
> > news:#YUfIwBaEHA.212@.TK2MSFTNGP12.phx.gbl...
> > > I've configured the IIS with only windows authentication, when i
connect
> > to
> > > Reporting Server or Reporting Administrator uses the windows account,
> but
> > > the security rules work different:
> > > I have configured rules for two accounts: my windows account and
> > > IUSR_machine.
> > >
> > > When i try to connect to servers i put my windows account and goes ok,
> but
> > > the security rules works with the rules assigned to the iusr_machine.
> Any
> > > rule assigned to my user doesn't have effect, and all the changes
makes
> in
> > > the IUSR_machine's rules have effect.
> > >
> > > I've configured restrict access to the iusr_machine and grant
permission
> > for
> > > my windows account. When i connect with my windows account and i try
to
> do
> > > any task that iusr_machine haven't permission, the server response me
> > that
> > > the iusr_machine haven't permmission to do it.
> > >
> > > Any idea?
> > >
> > > thank you.
> > >
> > > Oliver
> > >
> > > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> escribió en el
> > > mensaje news:OT0ifxqZEHA.212@.TK2MSFTNGP12.phx.gbl...
> > > > Usually this is because anonymous is set on the IIS web site
settings.
> > > >
> > > > --
> > > > Brian Welcker
> > > > Group Program Manager
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > >
> > > > "oli" <oli1350@.hotmail.com> wrote in message
> > > > news:eHPoJtZZEHA.3132@.TK2MSFTNGP10.phx.gbl...
> > > > > Hi,
> > > > > I'm having problem with the authentication in reports. When a
> user
> > > sign
> > > > > in to Report Administrator or Report Server validates with the
IIS'
> > > > > configuration. Onces inside always use the same user with the
rules
> > > > > security: IWAM_machine.
> > > > > I need the account used inside of Reporting Server/Reporting
> > > > > Administration
> > > > > should be the same that the account used to validates with IIS.
> > > > >
> > > > > It's possible'
> > > > >
> > > > > Thank you in advanced.
> > > > >
> > > > > Oliver
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

PROBLEM: Report parameters can not be set. Default values always take priority.

I am trying to create a report with parameters which are prompted by the user and are initially populated with default values. However, whenever I edit the parameters in Report Viewer and click 'View Report', the parameter values revert to their default value. The user-specified parameters never "stick".

Please help!

The xml looks like this:

<ReportParameters>

<ReportParameter Name="ConnectString">

<DataType>String</DataType>

<Prompt>ConnectString</Prompt>

<Hidden>true</Hidden>

</ReportParameter>

<ReportParameter Name="FromDate">

<DataType>DateTime</DataType>

<DefaultValue>

<Values>

<Value>2006-1-1</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>From:</Prompt>

</ReportParameter>

<ReportParameter Name="ToDate">

<DataType>DateTime</DataType>

<DefaultValue>

<Values>

<Value>2006-12-31</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>To:</Prompt>

</ReportParameter>

</ReportParameters>

One more observation: It works within Report Manager. The problem is when the report is hosted in my application. It seems there might be something in my code that is causing the user-specified parameters to be ignored.

I tried eliminating SetParameters call for the one hidden parameter, and I made sure I was setting ShowParameterPrompts attribute once (in case changing value would reset parameters). The problem still exists.

|||One possible cause is that you are setting the report path or report server url after the LoadViewState event on the postback. If you do this, the report viewer control will see this as a change in the report definition and reload the report from the server, ignoring any input from the postback because it applies to the "old report". Try setting the report path/server url during OnInit, or only when Page.IsPostBack is false.|||

Thank you Brian! That worked!

I verified that either setting ServerReport properties in OnInit(), or after checking Page.IsPostBack is false in Page_Load(), will fix this problem. I also had to call SetParameters within these same constraints.

On to new problems...

PROBLEM: Login name is 'dbo' ?? in Database

Here is a good one:
This is from sp_helplogins:
LOGINNAME DATABASE
DOMAIN\LOGIN1 Database1 db_owner MemberOf
DOMAIN\LOGIN1 Database1 dbo User
DOMAIN\LOGIN1 Database2 db_owner MemberOf
DOMAIN\LOGIN1 Database2 DOMAIN\LOGIN1 User
ALTER USER [dbo] WITH NAME=[DOMAIN\LOGIN1]
GO
results in:
Msg 15150, Level 16, State 1, Line 1
Cannot alter the user 'dbo'.
Someone must have tried to make the login db_owner by setting the name
to 'dbo'. What I need is to have the 2nd line above have DOMAIN\LOGIN1
as a user in Database1. Any ideas on how to fix this mess?Supposing that DOMAIN\LOGIN1 is represented by the dbo user, what problem do
you have?
Please, rate this post. Thanks!
--
May the bytes be with you!!!
Pedro López-Belmonte Eraso
MCAD, MCT
"Erik G" wrote:
> Here is a good one:
> This is from sp_helplogins:
> LOGINNAME DATABASE
> DOMAIN\LOGIN1 Database1 db_owner MemberOf
> DOMAIN\LOGIN1 Database1 dbo User
> DOMAIN\LOGIN1 Database2 db_owner MemberOf
> DOMAIN\LOGIN1 Database2 DOMAIN\LOGIN1 User
> ALTER USER [dbo] WITH NAME=[DOMAIN\LOGIN1]
> GO
> results in:
> Msg 15150, Level 16, State 1, Line 1
> Cannot alter the user 'dbo'.
> Someone must have tried to make the login db_owner by setting the name
> to 'dbo'. What I need is to have the 2nd line above have DOMAIN\LOGIN1
> as a user in Database1. Any ideas on how to fix this mess?
>|||On Jan 18, 3:59=A0pm, Pedro L=F3pez-Belmonte
<plber...@.mizarsoluciones.com.quitaestoparaenviar> wrote:
> Supposing that DOMAIN\LOGIN1 is represented by the dbo user, what problem =do
> you have?
I can not add a DOMAIN\LOGIN1 as a user. I need to set a database
role (db_role1 for this example) for user DOMAIN\LOGIN1 and the user
does not exist. I don't want DOMAIN\LOGIN1 to be db_owner, I want it
to be a user with db_role1.
Thanks
Erik|||> ALTER USER [dbo] WITH NAME=[DOMAIN\LOGIN1]
> GO
> results in:
> Msg 15150, Level 16, State 1, Line 1
> Cannot alter the user 'dbo'.
You need to change the database owner to a different login before you can
add the user to the database as a normal user. For example:
USE Database1;
ALTER AUTHORIZATION ON DATABASE::Database1 TO [sa];
CREATE USER [DOMAIN\LOGIN1];
For more info in database ownership issues, see
http://weblogs.sqlteam.com/dang/archive/2008/01/13/Database-Owner-Troubles.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Erik G" <info@.fdaregulatory.com> wrote in message
news:ffe839b8-93fd-4281-b494-f5b439d7d351@.s12g2000prg.googlegroups.com...
> Here is a good one:
> This is from sp_helplogins:
> LOGINNAME DATABASE
> DOMAIN\LOGIN1 Database1 db_owner MemberOf
> DOMAIN\LOGIN1 Database1 dbo User
> DOMAIN\LOGIN1 Database2 db_owner MemberOf
> DOMAIN\LOGIN1 Database2 DOMAIN\LOGIN1 User
> ALTER USER [dbo] WITH NAME=[DOMAIN\LOGIN1]
> GO
> results in:
> Msg 15150, Level 16, State 1, Line 1
> Cannot alter the user 'dbo'.
> Someone must have tried to make the login db_owner by setting the name
> to 'dbo'. What I need is to have the 2nd line above have DOMAIN\LOGIN1
> as a user in Database1. Any ideas on how to fix this mess?

PROBLEM: Database Mainence plan "All user Databases" missing database

If I select "All user Databases" and click T-SQL, 3 our of 4
databases are listed- and the missing database has not been backed up
via the plan. All databases are listed, however, when I select
"Selected Databases"- they are shown in the drop downbox.
This is for SQL 2005 Service Pack 2 with Hotfix 3175.
My guess: The missing database was restored and not restored to
overwrite an existing database. Since the CREATE DATABASE command was
not executed, maybe there is system info in master that doesn't get
set'
Help please. Thanks
Erik> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
No, that should not be the reason. The same meta-data would exist regardless of how the database was
created (CREATE DATABASE, attach or by RESTORE). Perhaps the database is in 70 compatibility mode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:25261d77-880b-4b83-9e9a-94866e14af6e@.e6g2000prf.googlegroups.com...
> If I select "All user Databases" and click T-SQL, 3 our of 4
> databases are listed- and the missing database has not been backed up
> via the plan. All databases are listed, however, when I select
> "Selected Databases"- they are shown in the drop downbox.
> This is for SQL 2005 Service Pack 2 with Hotfix 3175.
> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
> Help please. Thanks
> Erik|||Thanks. No, i've seent that 7.0 Compat mode issue before, it is
actually set to 9.0.|||Do the plan do both db and log backup? Is this missing backup a db or a log backup? Perhaps there's
a difference in the recovery model setting.
If not, then I'd check other database options, just doing a quick visual compare:
SELECT * FROM sys.databases
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:42c70b00-ac98-4d82-b150-69d1dbc3bf29@.i29g2000prf.googlegroups.com...
> Thanks. No, i've seent that 7.0 Compat mode issue before, it is
> actually set to 9.0.|||Thanks for suggstions. I found that Auto Update Statistics was
different and I turned it on and also set to FULL RECOVER MODE and it
got listed in View t-SQL preview !!
THAN I turned everything back to the previous settings for my missing
databses and the All Databases support still was fixed and in tact! t-
SQL Preview showed my previously missing database.
Updating sys.databases seems to fix the problem. maybe there is some
trigger in the background or something.
Thank you for all of your help.
Erik

Problem with user prompt

I want to prompt the user for a specific ID number or use a * for all
records at the time of running the report
So in my criteria for the employee ID field I put "= @.empid"
In the report the input box is there. If I put in an ID number it
works great. However if I put in an asterisk "*" nothing shows up.
Any ideas?Your where clause should be written like this:
where
(isnull(@.EmpID, '*') = '*')
or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
this lets your users enter a value for EmpID (then the second part of the
where clause will deal with it)
or leave the parameter field empty - then all their records will be selected
You could also replace the '*' with a '' (empty string) - this way, if the
users leave the parameter field blank, it will behave like a '*'.
Andrei.
"Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
>I want to prompt the user for a specific ID number or use a * for all
> records at the time of running the report
> So in my criteria for the employee ID field I put "= @.empid"
> In the report the input box is there. If I put in an ID number it
> works great. However if I put in an asterisk "*" nothing shows up.
> Any ideas?
>|||Ok you lost me a little bit.
Here is my current where clause.
WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX')) AND (pshstj.empid =@.ClockNo)
The "@.clockno" section is where I prompt them for a clock number.
If I put a * it doesn't work. If I put a good number in it works fine.
Where does your 'where' clause fit into this?
Andrei wrote:
> Your where clause should be written like this:
> where
> (isnull(@.EmpID, '*') = '*')
> or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
> this lets your users enter a value for EmpID (then the second part of the
> where clause will deal with it)
> or leave the parameter field empty - then all their records will be selected
> You could also replace the '*' with a '' (empty string) - this way, if the
> users leave the parameter field blank, it will behave like a '*'.
> Andrei.
>
> "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
> news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
> >I want to prompt the user for a specific ID number or use a * for all
> > records at the time of running the report
> >
> > So in my criteria for the employee ID field I put "= @.empid"
> >
> > In the report the input box is there. If I put in an ID number it
> > works great. However if I put in an asterisk "*" nothing shows up.
> >
> > Any ideas?
> >|||I'd write an IIF statement (either in a stored procedure, or in the
Report Query):
IF @.clockno = '*'
BEGIN
select ...
from ...
where (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX'))
END
ELSE
BEGIN
select ..
from ..
where (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX')) AND (pshstj.empid =@.ClockNo)
END
Bruce Lawrence wrote:
> Ok you lost me a little bit.
> Here is my current where clause.
> WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
> (pshstj.trn = 'RO1' OR
> pshstj.trn = 'WO1' OR
> pshstj.trn = 'XXX')) AND (pshstj.empid => @.ClockNo)
> The "@.clockno" section is where I prompt them for a clock number.
> If I put a * it doesn't work. If I put a good number in it works fine.
> Where does your 'where' clause fit into this?
>
> Andrei wrote:
> > Your where clause should be written like this:
> >
> > where
> > (isnull(@.EmpID, '*') = '*')
> > or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
> >
> > this lets your users enter a value for EmpID (then the second part of the
> > where clause will deal with it)
> > or leave the parameter field empty - then all their records will be selected
> >
> > You could also replace the '*' with a '' (empty string) - this way, if the
> > users leave the parameter field blank, it will behave like a '*'.
> >
> > Andrei.
> >
> >
> > "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
> > news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
> > >I want to prompt the user for a specific ID number or use a * for all
> > > records at the time of running the report
> > >
> > > So in my criteria for the employee ID field I put "= @.empid"
> > >
> > > In the report the input box is there. If I put in an ID number it
> > > works great. However if I put in an asterisk "*" nothing shows up.
> > >
> > > Any ideas?
> > >|||WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate)
AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX'))
AND
(
(isnull(@.ClockNo, '*') = '*')
or
( (isnull(@.ClockNo, '*') <> '*') and (pshstj.empid =lockNo) )
)
"Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1164828482.158109.84050@.j44g2000cwa.googlegroups.com...
> Ok you lost me a little bit.
> Here is my current where clause.
> WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
> (pshstj.trn = 'RO1' OR
> pshstj.trn = 'WO1' OR
> pshstj.trn = 'XXX')) AND (pshstj.empid => @.ClockNo)
> The "@.clockno" section is where I prompt them for a clock number.
> If I put a * it doesn't work. If I put a good number in it works fine.
> Where does your 'where' clause fit into this?
>
> Andrei wrote:
>> Your where clause should be written like this:
>> where
>> (isnull(@.EmpID, '*') = '*')
>> or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
>> this lets your users enter a value for EmpID (then the second part of the
>> where clause will deal with it)
>> or leave the parameter field empty - then all their records will be
>> selected
>> You could also replace the '*' with a '' (empty string) - this way, if
>> the
>> users leave the parameter field blank, it will behave like a '*'.
>> Andrei.
>>
>> "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
>> news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
>> >I want to prompt the user for a specific ID number or use a * for all
>> > records at the time of running the report
>> >
>> > So in my criteria for the employee ID field I put "= @.empid"
>> >
>> > In the report the input box is there. If I put in an ID number it
>> > works great. However if I put in an asterisk "*" nothing shows up.
>> >
>> > Any ideas?
>> >
>|||Andrei,
I'm not sure how... but it works.
I love you

Problem with user defined funtions

Hi,
I defined a publisher and a pushsubscriber with some articles. The articles
were Tables, Views and user defined functions. The pushsubscriber was created
with the sync_type=automatic. When I synchronisize the publisher with the
subscriber, the merge-agent fails on the follwing view creation command:
CREATE VIEW [dbo].[TGS_CATALOG]
AS
SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
+
dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
Latin1_General_CS_AS AS PFAD,
dbo.TGS_CATALOG_MASTER.ENTITYNUM,
dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
dbo.TGS_CATALOG_MASTER.KEYS,
dbo.TGS_CATALOG_MASTER.REVIERID
FROM dbo.TGS_BAUMTHEMEN INNER JOIN
dbo.TGS_CATALOG_MASTER ON dbo.TGS_BAUMTHEMEN.REVIERID
= dbo.TGS_CATALOG_MASTER.REVIERID AND
dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
dbo.TGS_REVIERE.REVIERID
I looked in the logfile and saw that there was a drop command of the user
defined function 'NullStringFilter', but no recreationof it before the
create command
was proccessed. Therfore the creation of the view fails.
What can I do to change the order of the schema creation process ?
I want to recreate the user defined functions before the recreation of the
views
starts !!!
Best regards
Axel Lanser
replication uses sysdepends to figure out the ordering of articles.
Sometimes this table can be out of sync (read erroneous).
I tend to deploy my problematic schema using a pre-snapshot command where
all constraints are disabled. Then I set all articles up so that they use
the delete data in existing table in the name conflicts section. I then use
a post-snapshot command to enable the constraints.
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
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> Hi,
> I defined a publisher and a pushsubscriber with some articles. The
articles
> were Tables, Views and user defined functions. The pushsubscriber was
created
> with the sync_type=automatic. When I synchronisize the publisher with the
> subscriber, the merge-agent fails on the follwing view creation command:
> CREATE VIEW [dbo].[TGS_CATALOG]
> AS
> SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
> dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
> dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
> dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
> +
> dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
> Latin1_General_CS_AS AS PFAD,
> dbo.TGS_CATALOG_MASTER.ENTITYNUM,
> dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
> dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
> dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
> dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
> dbo.TGS_CATALOG_MASTER.KEYS,
> dbo.TGS_CATALOG_MASTER.REVIERID
> FROM dbo.TGS_BAUMTHEMEN INNER JOIN
> dbo.TGS_CATALOG_MASTER ON
dbo.TGS_BAUMTHEMEN.REVIERID
> = dbo.TGS_CATALOG_MASTER.REVIERID AND
> dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
> dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
> dbo.TGS_REVIERE.REVIERID
> I looked in the logfile and saw that there was a drop command of the user
> defined function 'NullStringFilter', but no recreationof it before the
> create command
> was proccessed. Therfore the creation of the view fails.
> What can I do to change the order of the schema creation process ?
> I want to recreate the user defined functions before the recreation of the
> views
> starts !!!
> Best regards
> Axel Lanser
|||Hi,
how can I create pre- and post-snapshots ?
Best regards
Axel Lanser
"Hilary Cotter" wrote:

> replication uses sysdepends to figure out the ordering of articles.
> Sometimes this table can be out of sync (read erroneous).
> I tend to deploy my problematic schema using a pre-snapshot command where
> all constraints are disabled. Then I set all articles up so that they use
> the delete data in existing table in the name conflicts section. I then use
> a post-snapshot command to enable the constraints.
> --
> 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
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> articles
> created
> dbo.TGS_BAUMTHEMEN.REVIERID
>
>
|||ALN:
You need to write you own SQL File where you set your custom ALTER TABLE
commands for disabling constraints. While setting up publication you can
specify which SQL file needs to be run before snapshot is applied. Check BOL
for @.pre_snapshot_script parameter in sp_addpublication procedure.
"ALN" wrote:
[vbcol=seagreen]
> Hi,
> how can I create pre- and post-snapshots ?
> Best regards
> Axel Lanser
> "Hilary Cotter" wrote:
|||Hi,
thank you very much for your fast response. It helps me.
Best regards
Axel Lanser
"Mark" wrote:
[vbcol=seagreen]
> ALN:
> You need to write you own SQL File where you set your custom ALTER TABLE
> commands for disabling constraints. While setting up publication you can
> specify which SQL file needs to be run before snapshot is applied. Check BOL
> for @.pre_snapshot_script parameter in sp_addpublication procedure.
>
> "ALN" wrote:

Problem with User Defined Function

I wrote a UDF to get a rolling average based upon a date passed to the UDF. The error I get is:

Server: Msg 102, Level 15, State 1, Procedure fn_RollAverage, Line 26
Incorrect syntax near ')'.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION fn_RollAverage(@.CURVE_DATE AS SMALLDATETIME)
RETURNS @.TBLRESULT TABLE
(US0001M_Index AS FLOAT,
US0003M_INDEX AS FLOAT,
US0006M_INDEX AS FLOAT,
US0012M_INDEX AS FLOAT,
usswap2_index AS FLOAT,
usswap3_index AS FLOAT,
usswap4_index AS FLOAT,
usswap5_index AS FLOAT,
usswap6_index AS FLOAT,
usswap7_index AS FLOAT,
usswap8_index AS FLOAT,
usswap9_index AS FLOAT,
usswap10_index AS FLOAT,
usswap11_index AS FLOAT,
usswap12_index AS FLOAT,
usswap13_index AS FLOAT,
usswap14_index AS FLOAT,
usswap15_index AS FLOAT,
usswap20_index AS FLOAT,
usswap25_index AS FLOAT,
usswap30_index AS FLOAT)

AS BEGIN

DECLARE @.BEG_DATE AS SMALLDATETIME

SET @.BEG_DATE = (@.CURVE_DATE - 30)

INSERT @.TBLRESULT(
US0001M_Index,
US0003M_INDEX,
US0006M_INDEX,
US0012M_INDEX,
usswap2_index,
usswap3_index,
usswap4_index,
usswap5_index,
usswap6_index,
usswap7_index,
usswap8_index,
usswap9_index,
usswap10_index,
usswap11_index,
usswap12_index,
usswap13_index,
usswap14_index,
usswap15_index,
usswap20_index,
usswap25_index,
usswap30_index)

SELECT
AVG(US0001M_Index) AS US0001M_Index,
AVG(US0003M_INDEX) AS US0003M_INDEX,
AVG(US0006M_INDEX) AS US0006M_INDEX,
AVG(US0012M_INDEX) AS US0012M_INDEX,
AVG(usswap2_index) AS usswap2_index,
AVG(usswap3_index) AS usswap3_index,
AVG(usswap4_index) AS usswap4_index,
AVG(usswap5_index) AS usswap5_index,
AVG(usswap6_index) AS usswap6_index,
AVG(usswap7_index) AS usswap7_index,
AVG(usswap8_index) AS usswap8_index,
AVG(usswap9_index) AS usswap9_index,
AVG(usswap10_index) AS usswap10_index,
AVG(usswap11_index) AS usswap11_index,
AVG(usswap12_index) AS usswap12_index,
AVG(usswap13_index) AS usswap13_index,
AVG(usswap14_index) AS usswap14_index,
AVG(usswap15_index) AS usswap15_index,
AVG(usswap20_index) AS usswap20_index,
AVG(usswap25_index) AS usswap25_index,
AVG(usswap30_index) AS usswap30_index

FROM dbo.LiborSwap
WHERE CURVE_DATE BETWEEN @.BEG_DATE AND @.CURVE_DATE

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

DECLARE @.TBLRESULT TABLE
(US0001M_Index FLOAT,
US0003M_INDEX FLOAT,
US0006M_INDEX FLOAT

...)

|||

The " AS " keyword is not valid in the @.TBLRESULT table declaration.

|||Thanks thanks a thousand thanks

Problem with user created

Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and pa
s
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database object
s
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test databas
e
and the few extended sp in the master database.
ThanksAll users can access master, msdb and tempdb via the guest user in those
databases. Access to master and tempdb via the guest user is essential for
the proper functioning of any user login. Msdb access is not strictly
essential, but users by default have permissions to create jobs and DTS
Packages, among others, and jobs and DTS Packages are stored in msdb
Jacco Schalkwijk
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
> Hi,
> I created a user (from EM) and give him only access to a test database and
> 3
> extended sp in the master db, however, when I login with the username and
> pas
> in Query Analyzer I can access app the sp, extended sp, tables in the
> master
> database, tempdb and the msdb. I even see them listed in the database
> objects
> section. When I go to users under tempdb and msdb I don't see the user
> there
> whent going on here. I only want the user to access and see the test
> database
> and the few extended sp in the master database.
> Thanks|||It is probably due to the fact that the guest user is enabled in those
databases.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and
pas
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database
objects
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test
database
and the few extended sp in the master database.
Thanks|||Thanks Guys!
"Chris" wrote:

> Hi,
> I created a user (from EM) and give him only access to a test database and
3
> extended sp in the master db, however, when I login with the username and
pas
> in Query Analyzer I can access app the sp, extended sp, tables in the mast
er
> database, tempdb and the msdb. I even see them listed in the database obje
cts
> section. When I go to users under tempdb and msdb I don't see the user the
re
> whent going on here. I only want the user to access and see the test datab
ase
> and the few extended sp in the master database.
> Thanks

Problem with user created

Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and pas
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database objects
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test database
and the few extended sp in the master database.
ThanksAll users can access master, msdb and tempdb via the guest user in those
databases. Access to master and tempdb via the guest user is essential for
the proper functioning of any user login. Msdb access is not strictly
essential, but users by default have permissions to create jobs and DTS
Packages, among others, and jobs and DTS Packages are stored in msdb
--
Jacco Schalkwijk
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
> Hi,
> I created a user (from EM) and give him only access to a test database and
> 3
> extended sp in the master db, however, when I login with the username and
> pas
> in Query Analyzer I can access app the sp, extended sp, tables in the
> master
> database, tempdb and the msdb. I even see them listed in the database
> objects
> section. When I go to users under tempdb and msdb I don't see the user
> there
> whent going on here. I only want the user to access and see the test
> database
> and the few extended sp in the master database.
> Thanks|||It is probably due to the fact that the guest user is enabled in those
databases.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and
pas
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database
objects
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test
database
and the few extended sp in the master database.
Thanks|||Thanks Guys!
"Chris" wrote:
> Hi,
> I created a user (from EM) and give him only access to a test database and 3
> extended sp in the master db, however, when I login with the username and pas
> in Query Analyzer I can access app the sp, extended sp, tables in the master
> database, tempdb and the msdb. I even see them listed in the database objects
> section. When I go to users under tempdb and msdb I don't see the user there
> whent going on here. I only want the user to access and see the test database
> and the few extended sp in the master database.
> Thanks

Problem with user created

Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and pas
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database objects
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test database
and the few extended sp in the master database.
Thanks
All users can access master, msdb and tempdb via the guest user in those
databases. Access to master and tempdb via the guest user is essential for
the proper functioning of any user login. Msdb access is not strictly
essential, but users by default have permissions to create jobs and DTS
Packages, among others, and jobs and DTS Packages are stored in msdb
Jacco Schalkwijk
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
> Hi,
> I created a user (from EM) and give him only access to a test database and
> 3
> extended sp in the master db, however, when I login with the username and
> pas
> in Query Analyzer I can access app the sp, extended sp, tables in the
> master
> database, tempdb and the msdb. I even see them listed in the database
> objects
> section. When I go to users under tempdb and msdb I don't see the user
> there
> whent going on here. I only want the user to access and see the test
> database
> and the few extended sp in the master database.
> Thanks
|||It is probably due to the fact that the guest user is enabled in those
databases.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and
pas
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database
objects
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test
database
and the few extended sp in the master database.
Thanks
|||Thanks Guys!
"Chris" wrote:

> Hi,
> I created a user (from EM) and give him only access to a test database and 3
> extended sp in the master db, however, when I login with the username and pas
> in Query Analyzer I can access app the sp, extended sp, tables in the master
> database, tempdb and the msdb. I even see them listed in the database objects
> section. When I go to users under tempdb and msdb I don't see the user there
> whent going on here. I only want the user to access and see the test database
> and the few extended sp in the master database.
> Thanks