problem with srever 2005 and Oracle

I am creating report model witch have connection to Oracle database. I was
created the connection and data source view, but then I want to create
Report model with wizard I am geting the error: ORA-02179: valid options:
ISOLATION LEVEL {SERIAZABLE | READ COMMITTED}.
how to change isolation level to valid oracle connection isolation level.It is my understanding that the model part of RS in 2005 is not yet
compatible with Oracle. I am tied to find where I read that on technet
but I can't. Maybe someone else can elaborate.
Thanks!|||Work-around for Report Builder:
1. Create linked server connection to Oracle database using the
OraOLEDB.Oracle provider(more up-to-date than Microsoft's).
2. Create a Data Source using the native SQL provider to the SQL Server
where you created in step 1.
3. Create a data source view; do not select objects.
4. Right-click in the DSV designer pane and create a New Named Query. Build
your query against the linked server (i.e. use 4-part names: select * from
linkedservername..schema.object). Repeat step 4 for each object you wish to
add to your model.
5. Add logical keys where applicable.
6. Build your model.
7. Deploy & build reports using Report Builder :-)
X

problem with sqlserver 2005 express

I am using VS2005 with sql server 2005 express.
I have database with atached datafile. when I am using Select command from
created table, there is not case sensitive in the result. I mean I have in
the table the row with value "Disp1" in col1, and when I am selecting with
filter Where col1="disp1" , I amd getting this one row where value is
"Disp1".
I saw in server explorer window, that connection have parameter Case
Sensitive, which value is False, but it is desabled and I can't change.
How to solve this problem.
ThanksCollation is an attribute of the column in the table. A database has a default collation which is
applied if you don't specify a collation in the CREATE TABLE statement. Also, if you want a query to
be resolved with a different collation than the data is stored with, you can use COLLATE keyword in
the query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vaidas" <Vaidas@.discussions.microsoft.com> wrote in message
news:8E0BF60C-1A43-49F3-A41B-E1E05E30BE23@.microsoft.com...
>I am using VS2005 with sql server 2005 express.
> I have database with atached datafile. when I am using Select command from
> created table, there is not case sensitive in the result. I mean I have in
> the table the row with value "Disp1" in col1, and when I am selecting with
> filter Where col1="disp1" , I amd getting this one row where value is
> "Disp1".
> I saw in server explorer window, that connection have parameter Case
> Sensitive, which value is False, but it is desabled and I can't change.
> How to solve this problem.
> Thanks
>

problem with sqlserver 2005 express

I am using VS2005 with sql server 2005 express.
I have database with atached datafile. when I am using Select command from
created table, there is not case sensitive in the result. I mean I have in
the table the row with value "Disp1" in col1, and when I am selecting with
filter Where col1="disp1" , I amd getting this one row where value is
"Disp1".
I saw in server explorer window, that connection have parameter Case
Sensitive, which value is False, but it is desabled and I can't change.
How to solve this problem.
ThanksUsing a case-sensitive collation such as Latin1_General_CS_AS for col1 when you create your table, or for the "=" comparison in your query should solve your problem. CS stands for "case sensitive" and AS for "Accent Sensitive." See
Working With Collations (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/61cdbb6b-3ca1-4d73-938b-22e4f06f75ea.htm) in SQL Server 2005 books online.

Eric

Problem with SQL Script with Clarion Date format

I need to run a sql script to update records in a table that were created
that day. I have a huge database and it would take a while to run a script
on all.
Here's what i have so far: (this is a select stmt version not update stmt
i'm using for testing)
SELECT tm5user.matter.c_date as CreateDate,
((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
FROM tm5user.matter
INNER JOIN tm5user.billopt ON tm5user.matter.sysid = tm5user.billopt.owner_id
WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
I got the script to work but it doesn't calculate the date correctly. The
date field "c_date" is based on a clarion base date of 12/28/1800.
I have my select statement return the "c_date" field, the calculated date
field, and full table columns for testing. The CreateDate and CalcDate
should be equal and they are not.
Does anyone have any ideas ... I tried CastDate that also did not work.
Any help or tips would be great ..... thanks.
rob bartley
msce> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate
The above expression (1800-12-28) is doing integer arithmetic. I suggest you use the DATEDIFF or
DATEADD function (depending on what you want to achieve). Also, I suggest you format the date in
language neutral format ('yyyymmdd') so it doesn't break in a nationalized environment.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rob" <temp@.dstek.com> wrote in message news:us1cOsGtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> I need to run a sql script to update records in a table that were created
> that day. I have a huge database and it would take a while to run a script
> on all.
> Here's what i have so far: (this is a select stmt version not update stmt
> i'm using for testing)
> SELECT tm5user.matter.c_date as CreateDate,
> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
> FROM tm5user.matter
> INNER JOIN tm5user.billopt ON tm5user.matter.sysid => tm5user.billopt.owner_id
> WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
>
> I got the script to work but it doesn't calculate the date correctly. The
> date field "c_date" is based on a clarion base date of 12/28/1800.
> I have my select statement return the "c_date" field, the calculated date
> field, and full table columns for testing. The CreateDate and CalcDate
> should be equal and they are not.
> Does anyone have any ideas ... I tried CastDate that also did not work.
> Any help or tips would be great ..... thanks.
> rob bartley
> msce
>

Problem with SnapShot replication

Hi
I'm trying to set up a snapshot replication to replicate data out to a SQL
server in our DMZ. I have created the snapshot etc. but when I then run the
distribution agent to synchronize data out to the server, it stops after a
few seconds and comes up with the error message below:
Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
The name ' ' is not permitted in this context. Only constants, expressions,
or variables allowed here. Column names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
The name 'Statistics have been updated for all tables.' is not permitted in
this context. Only constants, expressions, or variables allowed here. Column
names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
I'm a little bit stucked with this.error message. The Source is the IP
adress of my SQL server in the DMZ (subscriber) so it looks like it's
something out that isn't right. Is there anywhere I can view the slq
statements that are being ran? Since it refers to a Line 17 it must run some
code somewhere. I'm also a bit stumped on why it does an "UPDATE
STATISTICS", but that's maybe a part of the Replication job?
Regards
Steen
Problem solved...(I hope...).
I tried to run a trace on the subscriber, to see which commands it was
actually running when trying to apply the subscription. I then found that
one of the stored procedures it applied from the source, had a syntax error.
When I omit that SP from the synchronization, it works......
Regards
Steen
Steen Persson wrote:
> Hi
> I'm trying to set up a snapshot replication to replicate data out to
> a SQL server in our DMZ. I have created the snapshot etc. but when I
> then run the distribution agent to synchronize data out to the
> server, it stops after a few seconds and comes up with the error
> message below:
> Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
> ----
--
> --
> The name ' ' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not
> permitted. (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> The name 'Statistics have been updated for all tables.' is not
> permitted in this context. Only constants, expressions, or variables
> allowed here. Column names are not permitted.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> I'm a little bit stucked with this.error message. The Source is the IP
> adress of my SQL server in the DMZ (subscriber) so it looks like it's
> something out that isn't right. Is there anywhere I can view the slq
> statements that are being ran? Since it refers to a Line 17 it must
> run some code somewhere. I'm also a bit stumped on why it does an
> "UPDATE STATISTICS", but that's maybe a part of the Replication job?
> Regards
> Steen

Problem with SnapShot replication

Hi
I'm trying to set up a snapshot replication to replicate data out to a SQL
server in our DMZ. I have created the snapshot etc. but when I then run the
distribution agent to synchronize data out to the server, it stops after a
few seconds and comes up with the error message below:
Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
----
--
The name ' ' is not permitted in this context. Only constants, expressions,
or variables allowed here. Column names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
----
--
The name 'Statistics have been updated for all tables.' is not permitted in
this context. Only constants, expressions, or variables allowed here. Column
names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
----
--
I'm a little bit stucked with this.error message. The Source is the IP
adress of my SQL server in the DMZ (subscriber) so it looks like it's
something out that isn't right. Is there anywhere I can view the slq
statements that are being ran? Since it refers to a Line 17 it must run some
code somewhere. I'm also a bit stumped on why it does an "UPDATE
STATISTICS", but that's maybe a part of the Replication job?
Regards
SteenProblem solved...(I hope...).
I tried to run a trace on the subscriber, to see which commands it was
actually running when trying to apply the subscription. I then found that
one of the stored procedures it applied from the source, had a syntax error.
When I omit that SP from the synchronization, it works......
Regards
Steen
Steen Persson wrote:
> Hi
> I'm trying to set up a snapshot replication to replicate data out to
> a SQL server in our DMZ. I have created the snapshot etc. but when I
> then run the distribution agent to synchronize data out to the
> server, it stops after a few seconds and comes up with the error
> message below:
> Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
> ----
--
> --
> The name ' ' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not
> permitted. (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> The name 'Statistics have been updated for all tables.' is not
> permitted in this context. Only constants, expressions, or variables
> allowed here. Column names are not permitted.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> I'm a little bit stucked with this.error message. The Source is the IP
> adress of my SQL server in the DMZ (subscriber) so it looks like it's
> something out that isn't right. Is there anywhere I can view the slq
> statements that are being ran? Since it refers to a Line 17 it must
> run some code somewhere. I'm also a bit stumped on why it does an
> "UPDATE STATISTICS", but that's maybe a part of the Replication job?
> Regards
> Steen

Problem with SnapShot replication

Hi
I'm trying to set up a snapshot replication to replicate data out to a SQL
server in our DMZ. I have created the snapshot etc. but when I then run the
distribution agent to synchronize data out to the server, it stops after a
few seconds and comes up with the error message below:
Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
----
--
The name ' ' is not permitted in this context. Only constants, expressions,
or variables allowed here. Column names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
----
--
The name 'Statistics have been updated for all tables.' is not permitted in
this context. Only constants, expressions, or variables allowed here. Column
names are not permitted.
(Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
----
--
I'm a little bit stucked with this.error message. The Source is the IP
adress of my SQL server in the DMZ (subscriber) so it looks like it's
something out that isn't right. Is there anywhere I can view the slq
statements that are being ran? Since it refers to a Line 17 it must run some
code somewhere. I'm also a bit stumped on why it does an "UPDATE
STATISTICS", but that's maybe a part of the Replication job?
Regards
SteenProblem solved...(I hope...).
I tried to run a trace on the subscriber, to see which commands it was
actually running when trying to apply the subscription. I then found that
one of the stored procedures it applied from the source, had a syntax error.
When I omit that SP from the synchronization, it works......
Regards
Steen
Steen Persson wrote:
> Hi
> I'm trying to set up a snapshot replication to replicate data out to
> a SQL server in our DMZ. I have created the snapshot etc. but when I
> then run the distribution agent to synchronize data out to the
> server, it stops after a few seconds and comes up with the error
> message below:
> Line 17: Incorrect syntax near 'UPDATE STATISTICS '.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 170)
> ----
--
> --
> The name ' ' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not
> permitted. (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> The name 'Statistics have been updated for all tables.' is not
> permitted in this context. Only constants, expressions, or variables
> allowed here. Column names are not permitted.
> (Source: xxx.xxx.xxx.xxx (Data source); Error number: 128)
> ----
--
> --
> I'm a little bit stucked with this.error message. The Source is the IP
> adress of my SQL server in the DMZ (subscriber) so it looks like it's
> something out that isn't right. Is there anywhere I can view the slq
> statements that are being ran? Since it refers to a Line 17 it must
> run some code somewhere. I'm also a bit stumped on why it does an
> "UPDATE STATISTICS", but that's maybe a part of the Replication job?
> Regards
> Steen

Problem with size of databse sql server 7

2 databases (sql server 7) have been created in same conditions : same tools ("bcp...") and same options
On both 2 series of "users tests" have been executed. These tests were exactly the same
At the end we have executed a "backup" procedure on each database
We expected the same size for the two backup database but we obtained two different results :
304,56Mo and 302,81Mo for databases, 10,64Mo and 11,34Mo for the log transaction
Could you help us explaining this difference of almost 2 Mo ?No need to repost after only 25 minutes. See my other reply.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Laurent L. (France)" <llechat@.sodifrance.fr> wrote in message
news:4987F961-0A67-438B-94A0-439687537630@.microsoft.com...
> 2 databases (sql server 7) have been created in same conditions : same tools ("bcp...") and same
options.
> On both 2 series of "users tests" have been executed. These tests were exactly the same !
> At the end we have executed a "backup" procedure on each database.
> We expected the same size for the two backup database but we obtained two different results :
> 304,56Mo and 302,81Mo for databases, 10,64Mo and 11,34Mo for the log transaction.
> Could you help us explaining this difference of almost 2 Mo ?

Problem with size of database sql server 7

2 databases (sql server 7) have been created in same conditions : same tools "bcp..." and same options.
On both 2 series of "users tests" have been executed. These tests were exactly the same !
At the end we have executed a "backup" procedure on each database.
We expected the same size for the two backup database but we obtained two different results :
304,56Mo and 302,81Mo for databases, 10,64Mo and 11,34Mo for the log transaction.
Could you help us explaining this difference of almost 2 Mo ?I wouldn't worry about that small size difference in backup. Are the tsql SQL Server of the same
service pack? Also, do you do *exactly* the same for the two, all the way since the database
creation. If you want to pursuit this (I wouldn't), then I recommend that you have a scrip file
which includes the CREATE DATABASE command to exclude as much "noise" as possible,
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Laurent L. (France)" <llechat@.sodifrance.fr> wrote in message
news:649E6E72-A43F-4084-88BB-48ACDF8AECC1@.microsoft.com...
> 2 databases (sql server 7) have been created in same conditions : same tools "bcp..." and same
options.
> On both 2 series of "users tests" have been executed. These tests were exactly the same !
> At the end we have executed a "backup" procedure on each database.
> We expected the same size for the two backup database but we obtained two different results :
> 304,56Mo and 302,81Mo for databases, 10,64Mo and 11,34Mo for the log transaction.
> Could you help us explaining this difference of almost 2 Mo ?

Problem with SET QUOTED_IDENTIFIER ON

Hi,
I am creating User defined function with
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
But function is created with QUOTED_IDENTIFIER OFF and SET ANSI_NULLS OFF.
What is wrong.
ThanksHi,
look here, Iposted that some time ago:
http://forums.microsoft.com/MSDN/Sh...228076&SiteID=1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||How do you know the settings are OFF? What version of SQL Server? The
following works for me under SQL 2000:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.TestFunction(@.Parameter1 int)
RETURNS int
AS
BEGIN
RETURN @.Parameter1
END
GO
SELECT
OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsAnsiNullsOn'),
OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsQuotedIdentOn')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"AMiha" <amiha@.hotmail.com.false> wrote in message
news:urdlGKmTGHA.5496@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am creating User defined function with
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> But function is created with QUOTED_IDENTIFIER OFF and SET ANSI_NULLS
> OFF.
> What is wrong.
> Thanks
>|||I'm working with sql 2000 and result of
SELECT
OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsAnsiNullsOn'),
OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsQuotedIdentOn')
GO
is null for myUdf.
Result of
select OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'IsTableFunction')
is 1.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e7DFkFnTGHA.5900@.tk2msftngp13.phx.gbl...
> How do you know the settings are OFF? What version of SQL Server? The
> following works for me under SQL 2000:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE FUNCTION dbo.TestFunction(@.Parameter1 int)
> RETURNS int
> AS
> BEGIN
> RETURN @.Parameter1
> END
> GO
> SELECT
> OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsAnsiNullsOn'),
> OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsQuotedIdentOn')
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AMiha" <amiha@.hotmail.com.false> wrote in message
> news:urdlGKmTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>|||The 'sticky' SET options for table valued functions are apparently not
reported correctly in SQL 2000 SP4. The create-time settings are used for
execution though. No problem in SQL 2005.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.myTableFunction(@.Parameter1 int)
RETURNS TABLE
AS
RETURN (SELECT 1 AS test)
GO
CREATE FUNCTION dbo.myInLineFunction(@.Parameter1 int)
RETURNS @.MyTable TABLE (Col1 int)
AS
BEGIN
RETURN
END
GO
CREATE FUNCTION dbo.myScalarFunction(@.Parameter1 int)
RETURNS int
AS
BEGIN
RETURN 1
END
GO
SELECT
OBJECTPROPERTY(id, 'IsInLineFunction'),
OBJECTPROPERTY(id, 'IsScalarFunction'),
OBJECTPROPERTY(id, 'IsTableFunction'),
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn'),
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn')
FROM sysobjects
WHERE id IN
(
OBJECT_ID('dbo.myTableFunction'),
OBJECT_ID('dbo.myInLineFunction'),
OBJECT_ID('dbo.myScalarFunction')
)
Hope this helps.
Dan Guzman
SQL Server MVP
"AMiha" <amiha@.hotmail.com.false> wrote in message
news:umq0ocnTGHA.4452@.TK2MSFTNGP12.phx.gbl...
> I'm working with sql 2000 and result of
> SELECT
> OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsAnsiNullsOn'),
> OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsQuotedIdentOn')
> GO
> is null for myUdf.
> Result of
> select OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'IsTableFunction')
> is 1.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e7DFkFnTGHA.5900@.tk2msftngp13.phx.gbl...
>|||Thank you Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e4CxrBoTGHA.196@.TK2MSFTNGP10.phx.gbl...
> The 'sticky' SET options for table valued functions are apparently not
> reported correctly in SQL 2000 SP4. The create-time settings are used for
> execution though. No problem in SQL 2005.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE FUNCTION dbo.myTableFunction(@.Parameter1 int)
> RETURNS TABLE
> AS
> RETURN (SELECT 1 AS test)
> GO
> CREATE FUNCTION dbo.myInLineFunction(@.Parameter1 int)
> RETURNS @.MyTable TABLE (Col1 int)
> AS
> BEGIN
> RETURN
> END
> GO
> CREATE FUNCTION dbo.myScalarFunction(@.Parameter1 int)
> RETURNS int
> AS
> BEGIN
> RETURN 1
> END
> GO
> SELECT
> OBJECTPROPERTY(id, 'IsInLineFunction'),
> OBJECTPROPERTY(id, 'IsScalarFunction'),
> OBJECTPROPERTY(id, 'IsTableFunction'),
> OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn'),
> OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn')
> FROM sysobjects
> WHERE id IN
> (
> OBJECT_ID('dbo.myTableFunction'),
> OBJECT_ID('dbo.myInLineFunction'),
> OBJECT_ID('dbo.myScalarFunction')
> )
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AMiha" <amiha@.hotmail.com.false> wrote in message
> news:umq0ocnTGHA.4452@.TK2MSFTNGP12.phx.gbl...
>

Problem with 'sa' user and password

Hello.
I have problem with connection to my SQL server 2000 through 'sa' user
without password.
On my windows XP with Access XP I created ODBC Source, user DSN source for
default user 'sa'. On SQL this user has not a password. Now, in Access, when
I try to open linked (from SQL Server) tables the dialog window appears and
system tells me to enter the password (which is empty). Then I must only
press Enter, and table is being opened. What should I do to avoid pressing
Enter, in other words, What should I do in order to get access to tables
without this appearing dialog window to enter the password?
Thank you very much for help.
PS. Without solution for this problem I can not run my batch jobs, and it is
very bad, and I'm getting nervous ;)
I am not sure that you can do this. The password, or in this case, the lack
of password, does not get stored in the DSN. So the dialog box has to open
so you cna supply it.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Problem with 'sa' user and password

Hello.
I have problem with connection to my SQL server 2000 through 'sa' user
without password.
On my Windows XP with Access XP I created ODBC Source, user DSN source for
default user 'sa'. On SQL this user has not a password. Now, in Access, when
I try to open linked (from SQL Server) tables the dialog window appears and
system tells me to enter the password (which is empty). Then I must only
press Enter, and table is being opened. What should I do to avoid pressing
Enter, in other words, What should I do in order to get access to tables
without this appearing dialog window to enter the password?
Thank you very much for help.
PS. Without solution for this problem I can not run my batch jobs, and it is
very bad, and I'm getting nervous ;)I am not sure that you can do this. The password, or in this case, the lack
of password, does not get stored in the DSN. So the dialog box has to open
so you cna supply it.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Problem with Reporting Server Security. I can not have the correct permissions.

Hi,
I'm trying to configure the RS's Security. i 've created two rules, one
rule for administrators and the other one for IUSR_machine. I connect to
Windows 2000 server as administrator.
The problem is that when i access the Report Administrator via URL, it
applies the rules of the IUSR_machine, so i can't make administrator tasks.
¿Why does the Report Administrator think that I am an IUSR_machine user, and
how can I connect as administrator?
Please, anybody can help me,
thanks in advance.
Oliver.I am able to reproduce your issue when I set my report manager site to allow
anonymous access. When I do this, it ignores my windows authentication
forcing the report manager to treat me as a consumer, not an admin. This is
not the default setting. Did someone change it afterwards? If you find this
to be the case, uncheck the "Anonymous Access" box and try it again. Make
sure "Integrated Windows Authentication" is checked at the bottom of the
Authentication Methods dialog.
({IIS Manager}=>{Default Web Site}=>{Reports}=>{Properties}=>{Directory
Security}=>{Edit}).
"oli" wrote:
> Hi,
> I'm trying to configure the RS's Security. i 've created two rules, one
> rule for administrators and the other one for IUSR_machine. I connect to
> Windows 2000 server as administrator.
> The problem is that when i access the Report Administrator via URL, it
> applies the rules of the IUSR_machine, so i can't make administrator tasks.
> ¿Why does the Report Administrator think that I am an IUSR_machine user, and
> how can I connect as administrator?
>
> Please, anybody can help me,
> thanks in advance.
> Oliver.
>
>|||Thanks for your response.
I have verifed the Security in IIS. When I configured the authentication I
activated the Report Server with "anonymous access", but I configured the
Report Manager only with "Integrated Windows Authentication". With this
configuration, the RS works in the way I wrote in the first mail.
Oliver.
>I am able to reproduce your issue when I set my report manager site to
>allow
> anonymous access. When I do this, it ignores my windows authentication
> forcing the report manager to treat me as a consumer, not an admin. This
> is
> not the default setting. Did someone change it afterwards? If you find
> this
> to be the case, uncheck the "Anonymous Access" box and try it again. Make
> sure "Integrated Windows Authentication" is checked at the bottom of the
> Authentication Methods dialog.
> ({IIS Manager}=>{Default Web Site}=>{Reports}=>{Properties}=>{Directory
> Security}=>{Edit}).
>
> "oli" wrote:
>> Hi,
>> I'm trying to configure the RS's Security. i 've created two rules,
>> one
>> rule for administrators and the other one for IUSR_machine. I connect to
>> Windows 2000 server as administrator.
>> The problem is that when i access the Report Administrator via URL, it
>> applies the rules of the IUSR_machine, so i can't make administrator
>> tasks.
>> ¿Why does the Report Administrator think that I am an IUSR_machine user,
>> and
>> how can I connect as administrator?
>>
>> Please, anybody can help me,
>> thanks in advance.
>> Oliver.
>>|||If i put both report server and report administration with integrated
windows authentication it's goes fine, but the problems appears when i
configure report administration with itegrated windows authentication and
report server with anonymous access.
Oliver
"oli" <oli1350@.hotmail.com> escribió en el mensaje
news:eu%23AV%23IpEHA.3900@.TK2MSFTNGP10.phx.gbl...
> Thanks for your response.
> I have verifed the Security in IIS. When I configured the authentication I
> activated the Report Server with "anonymous access", but I configured the
> Report Manager only with "Integrated Windows Authentication". With this
> configuration, the RS works in the way I wrote in the first mail.
>
> Oliver.
>
>>I am able to reproduce your issue when I set my report manager site to
>>allow
>> anonymous access. When I do this, it ignores my windows authentication
>> forcing the report manager to treat me as a consumer, not an admin. This
>> is
>> not the default setting. Did someone change it afterwards? If you find
>> this
>> to be the case, uncheck the "Anonymous Access" box and try it again. Make
>> sure "Integrated Windows Authentication" is checked at the bottom of the
>> Authentication Methods dialog.
>> ({IIS Manager}=>{Default Web Site}=>{Reports}=>{Properties}=>{Directory
>> Security}=>{Edit}).
>>
>> "oli" wrote:
>> Hi,
>> I'm trying to configure the RS's Security. i 've created two rules,
>> one
>> rule for administrators and the other one for IUSR_machine. I connect to
>> Windows 2000 server as administrator.
>> The problem is that when i access the Report Administrator via URL, it
>> applies the rules of the IUSR_machine, so i can't make administrator
>> tasks.
>> ¿Why does the Report Administrator think that I am an IUSR_machine user,
>> and
>> how can I connect as administrator?
>>
>> Please, anybody can help me,
>> thanks in advance.
>> Oliver.
>>
>

Problem with remove files older then in backup

I am using MSDE 2000 sp3 on Windows 2003 Server. I am connecting to this
MSDE from another computer using Enterprise manager. I have created a
maintenance plan that backup the DB once a day. The problem is that in the
option to "remove files older then" - the combo box where the day/week/month
should be is empty so I can not set this option.
How can I fix it ?
Thanks for your time
ra294@.hotmail.comIn the end, a maintenance plan just generates a parameter list for
xp_sqlmaint. You can modify this yourself in the job that is created. For
details on the options have a look in BOL for "sqlmaint utility" e.g. the
switch for deleting backups is
-DelBkUps <time_period> where
<time_period> ::= number[minutes | hours | days | weeks | months]
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ra294" <ra294@.hotmail.com> wrote in message
news:%23CIRutpBEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I am using MSDE 2000 sp3 on Windows 2003 Server. I am connecting to this
> MSDE from another computer using Enterprise manager. I have created a
> maintenance plan that backup the DB once a day. The problem is that in the
> option to "remove files older then" - the combo box where the
day/week/month
> should be is empty so I can not set this option.
> How can I fix it ?
> Thanks for your time
> ra294@.hotmail.com
>
>|||Thanks for the quick response.
Where is this script located ? What's the name of the file ?
ra294@.hotmail.com
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23c9MECrBEHA.1140@.TK2MSFTNGP10.phx.gbl...
> In the end, a maintenance plan just generates a parameter list for
> xp_sqlmaint. You can modify this yourself in the job that is created. For
> details on the options have a look in BOL for "sqlmaint utility" e.g. the
> switch for deleting backups is
> -DelBkUps <time_period> where
> <time_period> ::= number[minutes | hours | days | weeks | months]
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "ra294" <ra294@.hotmail.com> wrote in message
> news:%23CIRutpBEHA.3524@.TK2MSFTNGP10.phx.gbl...
the
> day/week/month
>|||Jobs can be found in the Management>SQL Server Agent>Jobs section in
Enterprise Manager. You should see a job that references your maintenance
plan name. If not you can simply create a job yourself that calls
xp_sqlmaint directly and supply the switches as detailed in BOL
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ra294" <ra294@.hotmail.com> wrote in message
news:%23%23K2E3rBEHA.140@.TK2MSFTNGP09.phx.gbl...
> Thanks for the quick response.
> Where is this script located ? What's the name of the file ?
> ra294@.hotmail.com
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%23c9MECrBEHA.1140@.TK2MSFTNGP10.phx.gbl...
For
the
this
> the
>

Problem with regenerated report model.

Hi,

I regenerated a report model (just added a new field) and now the existing reports created using Report Builder no longer run. I get the following error:

An error has occurred during report processing.
Semantic query compilation failed: e EmptySemanticQuery The SemanticQuery does not contain any Groupings or MeasureGroups. SemanticQuery must contain at least one of these elements. (SemanticQuery '').

When I try to open the report in Repoprt Builder, it does not see any of the entities in the model. Instead, it displays "Unknown Entity".

How can I get the reports to be usable again?

Thanks,

Rocco M.

Hi Rocco,

I am also facing the same issue. Did you find the resolution ?

Thanks

Ashutosh

|||

It sounds like you used the Regenerate Model button in Report Manager, which will delete elements of the model if the underlying schema element is no longer present. The Generate command in a VS Report Model project does not do this -- it is only additive.

It appears model regeneration was unable to discover any tables/columns in the underlying database. I don't know why this might have been the case (messed up connection string?), but unfortunately the model is gone now, and it will be very tedious to resuscitate your reports. If you have a recent backup of your report server database, you are in much better shape, however.

Server-based model generation is for quick-and-dirty scenarios. If you are serious about developing and deploying reports based on a report model, you should create one in Visual Studio (or create an empty project, download the generated one from the server, and add it to the project). You will have much finer control over the evolution of the model over time, and it will be much easier to maintain backups and/or old versions as needed.

|||

Hi Ashutosh,

I did not find a resolution to this problem. All of the reports created with Report Builder needed to be re-created. This was not a good situation.

All of the changes made to the report model were done using Visual Studio.

To prevent this situation from happening again, I have created a duplicate "development" version of the report model. All proposed changes are first done to this development version and some reports created specifically against this development version are used to verify proper operation. If the verification is successful, the changs are then made to the production version of the report model using the exact same steps performed on the development version.

Regards,

Rocco

Problem with regenerated report model.

Hi,

I regenerated a report model (just added a new field) and now the existing reports created using Report Builder no longer run. I get the following error:

An error has occurred during report processing.
Semantic query compilation failed: e EmptySemanticQuery The SemanticQuery does not contain any Groupings or MeasureGroups. SemanticQuery must contain at least one of these elements. (SemanticQuery '').

When I try to open the report in Repoprt Builder, it does not see any of the entities in the model. Instead, it displays "Unknown Entity".

How can I get the reports to be usable again?

Thanks,

Rocco M.

Hi Rocco,

I am also facing the same issue. Did you find the resolution ?

Thanks

Ashutosh

|||

It sounds like you used the Regenerate Model button in Report Manager, which will delete elements of the model if the underlying schema element is no longer present. The Generate command in a VS Report Model project does not do this -- it is only additive.

It appears model regeneration was unable to discover any tables/columns in the underlying database. I don't know why this might have been the case (messed up connection string?), but unfortunately the model is gone now, and it will be very tedious to resuscitate your reports. If you have a recent backup of your report server database, you are in much better shape, however.

Server-based model generation is for quick-and-dirty scenarios. If you are serious about developing and deploying reports based on a report model, you should create one in Visual Studio (or create an empty project, download the generated one from the server, and add it to the project). You will have much finer control over the evolution of the model over time, and it will be much easier to maintain backups and/or old versions as needed.

|||

Hi Ashutosh,

I did not find a resolution to this problem. All of the reports created with Report Builder needed to be re-created. This was not a good situation.

All of the changes made to the report model were done using Visual Studio.

To prevent this situation from happening again, I have created a duplicate "development" version of the report model. All proposed changes are first done to this development version and some reports created specifically against this development version are used to verify proper operation. If the verification is successful, the changs are then made to the production version of the report model using the exact same steps performed on the development version.

Regards,

Rocco

Problem with recurring execution of SSIS Package

Hi All,

I have created a SSIS package which calls child packages internally. In other words there is hierarchy of packages. I am using For Loop Container with certain check conditions to execute whole set of packages repeatedly. I have to execute this set for almost 5000 times. But my problem is this set fails after every 50 and sometimes 55 cycles. Can Anybody let me know how to get solution for such a problem?

Regards,

Prash

Is there any error in the output or in your logging destination of choice ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem with Query

I need to replace the text (III|xII) with (1II|xII)'. For that i created the following query.

SELECT (REPLACE((STUFF(Col1,1,1,'1')),'I','1')) FROM SourceData
WHERE Record = (SELECT Record + 2 FROM SourceData WHERE Col1 = 'MILESTONES' AND Heading = 'Milestones')
AND Heading = 'Milestones'

The problem with this query is that it replaces all '|' with '1'. ie, it gives (111|x11).

Could anyone ple show me what's wrong with the query.

All help appreciated.

Thanks,

The following code will give you the flexibility to change any char in the string, at any position, at any length. This function returns max 15 chars (but that’s easy to change)…

CREATE FUNCTION dbo.FixString
(
@.DataString varchar(15),
@.StartPosition smallint,
@.StartLength smallint,
@.SeacrhString varchar(15),
@.ReplaceString varchar(15)
)
RETURNS varchar(15)
AS
BEGIN

DECLARE @.Part1 varchar(15),
@.Part2 varchar(15),
@.Part3 varchar(15)

SET @.Part1 = SUBSTRING(@.DataString, 1, @.StartPosition - 1)
SET @.Part2 = REPLACE(SUBSTRING(@.DataString, @.StartPosition, @.StartLength), @.SeacrhString, @.ReplaceString)
SET @.Part3 = SUBSTRING( @.DataString, @.StartPosition + @.StartLength, LEN(@.DataString) - (@.StartPosition + @.StartLength) + 1 )

RETURN @.Part1 + @.Part2 + @.Part3
END
GO

SELECT dbo.FixString(Col1,1,1,'B','1') FROM SourceData
WHERE Record = (SELECT Record + 2 FROM SourceData WHERE Col1 = 'MILESTONES' AND Heading = 'Milestones')
AND Heading = 'Milestones'

Happy SQL'n,
Kent Howerter

|||You don't need the REPLACE since it will replace all occurrences of the specified string. STUFF is enough in this case since you are using the length parameter to delete the character at specified position and insert the new one.