Credentials

When running reports inside the Visual Studio IDE the datasources lose the
credentials that were previously set up.
I use the 'Use a specific user name and password'.
Any ideas?Try setting the credentials in the advanced datasource options, and
then using the "Test Connection" button on the lower left. If the
credentials aren't correct, the connection will fail, and the report
manager will not keep them in the connection string. Of course, I may
have misinterpreted your problem, in which case sorry! Hope this helps.
Lance M.
Brian Shannon wrote:
> When running reports inside the Visual Studio IDE the datasources lose the
> credentials that were previously set up.
> I use the 'Use a specific user name and password'.
> Any ideas?|||Brian,
Are you saying that the credentials aren't in Report Manager when you
deploy, or are you saying that the credentials disappear while you are
designing the report in VS?
-Josh
Lance M wrote:
> Try setting the credentials in the advanced datasource options, and
> then using the "Test Connection" button on the lower left. If the
> credentials aren't correct, the connection will fail, and the report
> manager will not keep them in the connection string. Of course, I may
> have misinterpreted your problem, in which case sorry! Hope this helps.
> Lance M.
> Brian Shannon wrote:
> > When running reports inside the Visual Studio IDE the datasources lose the
> > credentials that were previously set up.
> >
> > I use the 'Use a specific user name and password'.
> >
> > Any ideas?|||The credentials disapprear while I am designing the report in VS.
Scenerio:
I create a report. Deploy the report. All works fine. I come back a few
days later to make a change and the credentials are gone. I simply have to
add username/password back. Then all works fine again. It seems like a
random problem.
There are 3 of us using the VS solution. Could that be causing problems?
Thanks.
"Josh" <bell.joshua@.gmail.com> wrote in message
news:1155234202.244242.157330@.h48g2000cwc.googlegroups.com...
> Brian,
> Are you saying that the credentials aren't in Report Manager when you
> deploy, or are you saying that the credentials disappear while you are
> designing the report in VS?
> -Josh
>
> Lance M wrote:
>> Try setting the credentials in the advanced datasource options, and
>> then using the "Test Connection" button on the lower left. If the
>> credentials aren't correct, the connection will fail, and the report
>> manager will not keep them in the connection string. Of course, I may
>> have misinterpreted your problem, in which case sorry! Hope this helps.
>> Lance M.
>> Brian Shannon wrote:
>> > When running reports inside the Visual Studio IDE the datasources lose
>> > the
>> > credentials that were previously set up.
>> >
>> > I use the 'Use a specific user name and password'.
>> >
>> > Any ideas?
>|||Hi Brian,
I am getting the same problem. Did you find a solution?
Regards,
Robbie Berghan
rberghan@.gmail.com
"Brian Shannon" wrote:
> The credentials disapprear while I am designing the report in VS.
> Scenerio:
> I create a report. Deploy the report. All works fine. I come back a few
> days later to make a change and the credentials are gone. I simply have to
> add username/password back. Then all works fine again. It seems like a
> random problem.
> There are 3 of us using the VS solution. Could that be causing problems?
> Thanks.
> "Josh" <bell.joshua@.gmail.com> wrote in message
> news:1155234202.244242.157330@.h48g2000cwc.googlegroups.com...
> >
> > Brian,
> >
> > Are you saying that the credentials aren't in Report Manager when you
> > deploy, or are you saying that the credentials disappear while you are
> > designing the report in VS?
> >
> > -Josh
> >
> >
> > Lance M wrote:
> >> Try setting the credentials in the advanced datasource options, and
> >> then using the "Test Connection" button on the lower left. If the
> >> credentials aren't correct, the connection will fail, and the report
> >> manager will not keep them in the connection string. Of course, I may
> >> have misinterpreted your problem, in which case sorry! Hope this helps.
> >>
> >> Lance M.
> >>
> >> Brian Shannon wrote:
> >> > When running reports inside the Visual Studio IDE the datasources lose
> >> > the
> >> > credentials that were previously set up.
> >> >
> >> > I use the 'Use a specific user name and password'.
> >> >
> >> > Any ideas?
> >
>
>

Creation of a subscription to deliver reports via e-mail to individuals

Hi all,

how can standard subscription or data driven subscription be used to deliver reports to specific email address.

the standard subscription or data driven subscription can be used to delever reports using Windows file share ,but how to make it work for delivering to emails..

can anyone help me out?

In the create subscription page you don't have the e-mail option available ?

If so, it's probably a setup issue. Go to the Reporting Services setup option and check you e-mail settings (you have to configure the Sender Address and SMTP Server)

Creating XML Document from SQL Server

Would anyone please help me , how to create XML document
for a specific record from the SQL Server database.
Is there any builtin tool available. If so, how to access
it.
Would I need to create a program to generate XML
document. If so, could you suggest the development
package/code examples...
Appreciate the info.
Would something along the line of
select * from T for xml auto
or
select * from T for xml raw
help?
Best regards
Michael
"Sat" <anonymous@.discussions.microsoft.com> wrote in message
news:442c01c42b8b$f0669640$a001280a@.phx.gbl...
> Would anyone please help me , how to create XML document
> for a specific record from the SQL Server database.
> Is there any builtin tool available. If so, how to access
> it.
> Would I need to create a program to generate XML
> document. If so, could you suggest the development
> package/code examples...
> Appreciate the info.

creating temp tables during login

Frenz,

I need to create tables that are session specific. i.e When I login to a database i have to create a Table that can be accessed across all the procedures and triggers. When I log out the Tables should be droped. I understand that Local Temporary (#) tables can be created in annonymus block, but I need the tables to be created during login.

Your response is highly appreciated.

-CheekuEnsure the TEMPDB has not restricted to a max. size, if so it may fail.
And ensure the server is not stressed with this approach. In general the temp tables will be dropped once the session is closed at the SQL server.
Check books online for more information on temp. tables topic.|||annonymus block

Holy Oracle Batman

And Why do you need to do this?

Creating specific users

I have a user who is just responsible for monitoring SQL backups. However I
seem to have to grant sys admin rights in order for the user to be able to
see the backups in Enterprise manager. What is the lowest level that I can
grant to the user in this situation ?
SiSimon
Why do the users need to see the backups in EM? The files are located on
filesystem.
Can just users issue BACKUP DATABASE/RESTORE DATABASE... from QA?
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:1A221F70-5B21-4A76-97DF-4BF62A4E4196@.microsoft.com...
>I have a user who is just responsible for monitoring SQL backups. However I
> seem to have to grant sys admin rights in order for the user to be able to
> see the backups in Enterprise manager. What is the lowest level that I can
> grant to the user in this situation ?
> Si|||The whole backup regime was setup before I joined the company. They use SQL
jobs to run the SQL backups onto tape. A user has been designated to check E
M
to ensure the tape contents are correct. This will change in the medium term
as we are looking at how everything is backed up and will probably move to
something like Quest Litespeed. However in the short term I don`t really wan
t
to change the way things are done, only to change in the future.
"Uri Dimant" wrote:

> Simon
> Why do the users need to see the backups in EM? The files are located on
> filesystem.
> Can just users issue BACKUP DATABASE/RESTORE DATABASE... from QA?
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:1A221F70-5B21-4A76-97DF-4BF62A4E4196@.microsoft.com...
>
>|||Simon,
A user does not need to be sysadmin to run backups (such as through Query
Analyze as Uri mentioned), but I believe that Enterprise Manager for SQL
Server 2000 specifically checks for sysadmin in the application.
So, as long as SQL Server 2000 and Enterprise Manager are the tools, I think
you are stuck with this level of access. Perhaps your revised approach will
not be too far away.
RLF
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:26A07029-860D-40FB-B382-B064819B3A04@.microsoft.com...[vbcol=seagreen]
> The whole backup regime was setup before I joined the company. They use
> SQL
> jobs to run the SQL backups onto tape. A user has been designated to check
> EM
> to ensure the tape contents are correct. This will change in the medium
> term
> as we are looking at how everything is backed up and will probably move to
> something like Quest Litespeed. However in the short term I don`t really
> want
> to change the way things are done, only to change in the future.
> "Uri Dimant" wrote:
>|||Hi,
The minimum rights a user account needs is:
Public (can not remove)
db_backupoperator
db_denydatareader (don't need but locks down)
db_denydatawriter (don't need but locks down)
If db_denydatareader is not set the user will be able to see the tables and
the structure (table properties only), but not the data in the tables. See
the stored procedures and views there content but cannot edit them. When
selected it will hide all the tables, views, stored procedures and users
from them and they will only be able to backup the database.
The only issue you will have is if they are backing up to disk they will not
be able to browse the DIR structure but can enter the location manually.
Alternatively you can set it up as a backup device.
- Mike
----
--
<a href="http://links.10026.com/?link=http://www.cogitar.net"> Cogitar Software. (
http://www.cogitar.net ) </a>
http://www.cogitar.net Cogitar Software. (Software Development and Managment
Systems)
http://www.web-dominion.co.uk Web-Dominion. (Web Design and hosting )
http://shop-dominion.com (senery landscape pictur gallery)
----
--
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:1A221F70-5B21-4A76-97DF-4BF62A4E4196@.microsoft.com...
>I have a user who is just responsible for monitoring SQL backups. However I
> seem to have to grant sys admin rights in order for the user to be able to
> see the backups in Enterprise manager. What is the lowest level that I can
> grant to the user in this situation ?
> Si

Creating process doing specific job periodically

Hi, can I create a background process in SQL Server doing specific jobs periodically? Stored procedure is the only solution?

hi,

remedios_ wrote:

Hi, can I create a background process in SQL Server doing specific jobs periodically? Stored procedure is the only solution?

SQLExpress does not provide the SQL Server Agent, the native SQL Server scheduler where specific jobs can be scheduled to execute specific "task", such as Transact-SQL statements, CmdExec OS commands and the like..
obviously the full blown SQL Server 2005 editions provides this component..
this is not as "creating a background process", as the Agent runs a proprietary process and related threads..

a solution can even be to schedule, via the native OS scheduler (AT, SCHTASKS) the required tasks.. this is usualy the solution for scheduled backups in SQLExpress scenarios...

now the question is about what you have to do and on which "platform", SQLExpress or full blown SQL Server editions..

regards

|||

Hi, Andrea.

I'm using SQL 2005 Express edition.
There is a table which saves data from clients every other minute. This table keeps only one hour's data before from current time. So I must delete the expired data. This is the task that I want to schedule.

I don't exactly know which is efficiant that doing in my application or let SQL Server does.

I'll visit the site you commented and thank you very much.

|||

Hi,

I'd go for an OS scheduled task, running SQLCmd.exe to execute the desired Transact-SQL statement...

something like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.History (

Id int NOT NULL IDENTITY,

data varchar(25) DEFAULT 'will be removed',

Registered datetime NOT NULL DEFAULT DATEADD(Hour, -1, GETDATE())

);

GO

WHILE (SELECT COUNT(*) FROM dbo.History) < 10 BEGIN

INSERT INTO dbo.History DEFAULT VALUES;

END;

INSERT INTO dbo.History VALUES ( 'this will not be removed', GETDATE());

GO

SELECT * FROM dbo.History;

GO

PRINT 'actual statement to be scheduled every hour';

DELETE FROM dbo.History

WHERE DATEDIFF(Hour, Registered, GETDATE()) >= 1;

SELECT * FROM dbo.History;

GO

DROP TABLE dbo.History;

--<--

Id data Registered

-- - --

1 will be removed 2006-12-09 12:03:35.843

2 will be removed 2006-12-09 12:03:35.860

3 will be removed 2006-12-09 12:03:35.860

4 will be removed 2006-12-09 12:03:35.860

5 will be removed 2006-12-09 12:03:35.860

6 will be removed 2006-12-09 12:03:35.860

7 will be removed 2006-12-09 12:03:35.860

8 will be removed 2006-12-09 12:03:35.860

9 will be removed 2006-12-09 12:03:35.860

10 will be removed 2006-12-09 12:03:35.860

11 this will not be removed 2006-12-09 13:03:35.860

actual statement to be scheduled every hour

Id data Registered

-- - --

11 this will not be removed 2006-12-09 13:03:35.860

the actual "cleaning" statement should then be included in a cmd file like

SQLCmd -S(Local) -E -dTheDatabase -Q"DELETE FROM dbo.History WHERE DATEDIFF(Hour, Registered, GETDATE()) >= 1;" >c:\MyLog\ScheduledCleanup.txt

schedule it as desired..

regards

Creating process doing specific job periodically

Hi, can I create a background process in SQL Server doing specific jobs periodically? Stored procedure is the only solution?

hi,

remedios_ wrote:

Hi, can I create a background process in SQL Server doing specific jobs periodically? Stored procedure is the only solution?

SQLExpress does not provide the SQL Server Agent, the native SQL Server scheduler where specific jobs can be scheduled to execute specific "task", such as Transact-SQL statements, CmdExec OS commands and the like..
obviously the full blown SQL Server 2005 editions provides this component..
this is not as "creating a background process", as the Agent runs a proprietary process and related threads..

a solution can even be to schedule, via the native OS scheduler (AT, SCHTASKS) the required tasks.. this is usualy the solution for scheduled backups in SQLExpress scenarios...

now the question is about what you have to do and on which "platform", SQLExpress or full blown SQL Server editions..

regards

|||

Hi, Andrea.

I'm using SQL 2005 Express edition.
There is a table which saves data from clients every other minute. This table keeps only one hour's data before from current time. So I must delete the expired data. This is the task that I want to schedule.

I don't exactly know which is efficiant that doing in my application or let SQL Server does.

I'll visit the site you commented and thank you very much.

|||

Hi,

I'd go for an OS scheduled task, running SQLCmd.exe to execute the desired Transact-SQL statement...

something like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.History (

Id int NOT NULL IDENTITY,

data varchar(25) DEFAULT 'will be removed',

Registered datetime NOT NULL DEFAULT DATEADD(Hour, -1, GETDATE())

);

GO

WHILE (SELECT COUNT(*) FROM dbo.History) < 10 BEGIN

INSERT INTO dbo.History DEFAULT VALUES;

END;

INSERT INTO dbo.History VALUES ( 'this will not be removed', GETDATE());

GO

SELECT * FROM dbo.History;

GO

PRINT 'actual statement to be scheduled every hour';

DELETE FROM dbo.History

WHERE DATEDIFF(Hour, Registered, GETDATE()) >= 1;

SELECT * FROM dbo.History;

GO

DROP TABLE dbo.History;

--<--

Id data Registered

-- - --

1 will be removed 2006-12-09 12:03:35.843

2 will be removed 2006-12-09 12:03:35.860

3 will be removed 2006-12-09 12:03:35.860

4 will be removed 2006-12-09 12:03:35.860

5 will be removed 2006-12-09 12:03:35.860

6 will be removed 2006-12-09 12:03:35.860

7 will be removed 2006-12-09 12:03:35.860

8 will be removed 2006-12-09 12:03:35.860

9 will be removed 2006-12-09 12:03:35.860

10 will be removed 2006-12-09 12:03:35.860

11 this will not be removed 2006-12-09 13:03:35.860

actual statement to be scheduled every hour

Id data Registered

-- - --

11 this will not be removed 2006-12-09 13:03:35.860

the actual "cleaning" statement should then be included in a cmd file like

SQLCmd -S(Local) -E -dTheDatabase -Q"DELETE FROM dbo.History WHERE DATEDIFF(Hour, Registered, GETDATE()) >= 1;" >c:\MyLog\ScheduledCleanup.txt

schedule it as desired..

regards

Creating one specific value as unique

Hello, I have a question about SQL Serer.
I don't know how to make a "constrain" whitch protect one column in table
for specific value.
In example:
I would to have unique value '33' in column a
'id' , 'a'
1, 33
2, 45
3, 45
4, 45
6, 33 - I want to have exception i this momentUNIQUE
Create Table Test
(
ID INT,
Nombres INT UNIQUE
)
Insert into Test Values (1,1)
Insert into Test Values (1,1)
Look in BOL --Section CREATE TABLE
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
Newsbeitrag news:3669E088-9E38-4CEF-94E1-A6A5715B4134@.microsoft.com...
> Hello, I have a question about SQL Serer.
> I don't know how to make a "constrain" whitch protect one column in table
> for specific value.
> In example:
> I would to have unique value '33' in column a
> 'id' , 'a'
> 1, 33
> 2, 45
> 3, 45
> 4, 45
> 6, 33 - I want to have exception i this moment|||Jens
I think it is not exactly what he wanted
>I would to have unique value '33' in column a
'>id' , 'a'
>1, 33
>2, 45
>3, 45
>4, 45
> 6, 33 - I want to have exception i this moment
It will be thrown an error when you try to insert a value=45
Well ,the following solution in terms of performance amy be not ideal , so
you can re-write it as
a trigger to do the same thing.
CREATE TABLE TableA
(
col INT
)
INSERT INTO TableA VALUES (20)--WORKS
GO
CREATE FUNCTION dbo.fn_check_TableA()
RETURNS bit
AS
BEGIN
IF EXISTS(SELECT *
FROM TableA
WHERE col = 20
GROUP BY col
HAVING COUNT(*)>1)
RETURN 0
RETURN 1
END
GO
ALTER TABLE TableA
ADD CONSTRAINT df_col CHECK (dbo.fn_check_TableA()=1)
GO
INSERT INTO TableA VALUES (30)--works
INSERT INTO TableA VALUES (30)--works
INSERT INTO TableA VALUES (20)--failed
DROP TABLE TableA
DROP FUNCTION dbo.fn_check_TableA
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:O6qk2DOQFHA.3076@.TK2MSFTNGP14.phx.gbl...
> UNIQUE
>
> Create Table Test
> (
> ID INT,
> Nombres INT UNIQUE
> )
> Insert into Test Values (1,1)
> Insert into Test Values (1,1)
> Look in BOL --Section CREATE TABLE
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:3669E088-9E38-4CEF-94E1-A6A5715B4134@.microsoft.com...
table
>|||Didnt he want a exception ?
"I want to have exception i this moment"
Jens.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:OZgR0NOQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Jens
> I think it is not exactly what he wanted
> '>id' , 'a'
> It will be thrown an error when you try to insert a value=45
> Well ,the following solution in terms of performance amy be not ideal , so
> you can re-write it as
> a trigger to do the same thing.
> CREATE TABLE TableA
> (
> col INT
> )
> INSERT INTO TableA VALUES (20)--WORKS
> GO
> CREATE FUNCTION dbo.fn_check_TableA()
> RETURNS bit
> AS
> BEGIN
> IF EXISTS(SELECT *
> FROM TableA
> WHERE col = 20
> GROUP BY col
> HAVING COUNT(*)>1)
> RETURN 0
> RETURN 1
> END
> GO
> ALTER TABLE TableA
> ADD CONSTRAINT df_col CHECK (dbo.fn_check_TableA()=1)
> GO
> INSERT INTO TableA VALUES (30)--works
> INSERT INTO TableA VALUES (30)--works
> INSERT INTO TableA VALUES (20)--failed
> DROP TABLE TableA
> DROP FUNCTION dbo.fn_check_TableA
>
>
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:O6qk2DOQFHA.3076@.TK2MSFTNGP14.phx.gbl...
> table
>|||Try this ...
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [IX_test] UNIQUE NONCLUSTERED
(
id
)|||Yep, but it still should be allowed to insert another value,45? Am I right?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:u1VpVQOQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> Didnt he want a exception ?
> "I want to have exception i this moment"
> Jens.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:OZgR0NOQFHA.3544@.TK2MSFTNGP12.phx.gbl...
so
>|||Dead Jans thank you for yor reply, but you did not understand my question .
If i use yours script i will get all values as unique. But I would like to
get ONLY ONE - SPECIFIC VALUE (in my example 33) as unique , other values
can duplicate.
"Jens Sü?meyer" wrote:

> UNIQUE
>
> Create Table Test
> (
> ID INT,
> Nombres INT UNIQUE
> )
> Insert into Test Values (1,1)
> Insert into Test Values (1,1)
> Look in BOL --Section CREATE TABLE
> HTH, Jens Sü?meyer.|||Thank Uri Dimanti, this answer solve part of my problem :)
But i have another question - how to solve this problem on sql server ce (on
sql server ce i can't create functions) ?
Mariusz Wilk|||Yeah, bit i think that ok, because in his example on exception is thown in
the fifth line.
So the exception or the Contraint should be just palin unique.
Jens.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uFQoxSOQFHA.2580@.TK2MSFTNGP10.phx.gbl...
> Yep, but it still should be allowed to insert another value,45? Am I
> right?
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:u1VpVQOQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> so
>|||OK, understand.
Thanks for clearing up.
"Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C233C0DD-E834-4744-BFC6-FB2C5422F1E9@.microsoft.com...
> Dead Jans thank you for yor reply, but you did not understand my question
> .
> If i use yours script i will get all values as unique. But I would like to
> get ONLY ONE - SPECIFIC VALUE (in my example 33) as unique , other
> values
> can duplicate.
>
> "Jens Smeyer" wrote:
>
>
>

creating jobs in SQL Server 2005

Hello

I am using SQL Server 2005
I want to delete the data from my table after specific number of days.

This should happen automatically.

After the days I specified the data from the table should automatically get deleted. Can anyone let me know how to achieve this.

If anyone can give an example for this it will be great.

Thanks in advance.

Hi,
As the title of your post suggests you've already identified that you need a job running on the server to facilitate this.

SQL Server Agent will allow jobs to be scheduled and run - read up about this feature of SQL Server if unsure.

As for how to add a job...

In SQL Server Management Studio connect to the server.