Adding vertical space between rows

I am trying to create a mailing labels report but can't seem to get any vertical space between the rows. I see a cellpadding setting but nothing equivalent to cellspacing. Can anyone help me out?

Thanks.Try adding a text box, turning off can increase and can decrease in properties, under advanced, choose format, and work with the 'amount of space to leave on each side of report item.
Also try adding characters into the box, and set the color to be the same as background so that it is invisible.

Adding logins via SSEUtil compared to SQL Server Management Studio

Hello all,

I am currently in the process of setting up an SQL Server Express installation that comes packaged with an application I have written. My problem is that I want to use SQL Server user management (not just windows users) which work fine if I set them up manually. I started writing a script that I have SSEUtil execute once the application is fully installed (a step in my installation script) which sets up the users and passwords etc. The script is similar to the following:

USE [DBName]
GO

EXEC sp_DropUser 'user1'
EXEC sp_DropUser 'user2'
EXEC sp_DropUser 'user3'
EXEC sp_DropUser 'user4'
GO

USE [master]
GO

EXEC sp_DropLogin 'user1'
EXEC sp_DropLogin 'user2'
EXEC sp_DropLogin 'user3'
EXEC sp_DropLogin 'user4'
GO

CREATE LOGIN user1 WITH Password = 'user1', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user2 WITH Password = 'user2', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user3 WITH Password = 'user3', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user4 WITH Password = 'user4', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [DBName]
GO

EXEC sp_AddUser 'user1'
EXEC sp_AddUser 'user2'
EXEC sp_AddUser 'user3'
EXEC sp_AddUser 'user4'
GO

ALTER USER user1 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user2 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user3 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user4 WITH DEFAULT_SCHEMA = MySchema
GO

REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 FROM MyRole
GO

EXEC sp_DropRole 'MyRole'
EXEC sp_AddRole 'MyRole'
GO

GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 TO MyRole
GO

EXEC sp_AddRoleMember 'MyRole','user1'
EXEC sp_AddRoleMember 'MyRole','user2'
EXEC sp_AddRoleMember 'MyRole','user3'
EXEC sp_AddRoleMember 'MyRole','user4'
GO

Now if I run this script from within SQL Server Management Studio it executes perfectly. The logins add, the role is added, each user is added to the database logins and assigned to the role, the schema is set correctly on each user.

Then when I try to run the exact same script from the SSEUtil application (SSEUTIL -s PCNAME\Instance -run USERS.SQL), it processes everything, except the Logins.

This is frustrating as it means to install for a client I would need to either get them to open the management console and run the script from there, or I have to go to site just to setup users.

Am I on the right track? Or is there another way to automate the adding of Logins?

Thanks in advance,

DSXC

Just an update.

I did a check within my database and found that the sys.syslogins has the users (when I do a select from the view) but they just don't work. The only difference I can see between my scripted login I created and the SA user is the flag for sysadmin, but thats understandable as these users are not to be sysadmins.

Is there another table that actually enables the login?

DSXC

|||

I noticed a lot of people using the SQLCMD.EXE instead of the SSEUtil.EXE I was using so I thought I'd give it a try.

Lo and behold... it works!

Talk about crazy... oh well. Just so everyone knows, use the SQLCMD.EXE over SSEUtil.exe... gah!

EDIT: I didn't mention the command line to run it...

SQLCMD.EXE -i MYSCRIPT.SQL

Hope that helps.

DSXC

|||

Hi DSXC,

Sorry to have missed this thread earlier, I can shed some light on what you're seeing.

SSEUtil.exe is an unsupported tool primarily used for troubleshooting User Instance problems, it is not meant to be used as a general solution for runing scripts, nor is it licensed to be deployed with your application. Additionally, the default mechanism of SSEUtil works against the User Instance, not the parent instance, so my guess is it was not working the way you think it was.

SQLCmd is the general scripting utility that is installed with all copies of SQL 2005 and is exactly the tool you should be using for what you wish to accomplish, but you've already discovered that.

Mike

|||

Hi Mike,

Thanks for your response. It makes a bit more sense now.

I found the SSEUtil app with a search on attaching the database to my SQL Server so I guessed it would have worked running scripts against it also. I just noticed that the SSEUtil doesn't attach my database correctly either so I've now transferred over to using another SQL script.

Again thanks for your response.

DSXC

Adding Foreach container programmatically and setting the enumerator properties

Hi

I have a package which contains a foreach container. Can anyone help me in setting the properties for the enumerators programmatically? I am trying to set the properties for the enumerator "ForEach File Enumerator"

The properties which i need to set are

1. Folder

2. File Type

3. Traverse Subfolder

4. Retrieve File Name

Thanks in Advance

Suganya

Can anyone help me..?Am still searching for a solution

-Suganya

Adding days and setting time

Hi,
Can someone please help me with a SQL Server 2005 issue with date and
time.
I want to take the current date and time and add 2 days to it, but the
time must be set to 5pm.
If the current date/time is past 5pm it will go to the next day at 5pm.
So if the date was:
2006-06-23 15:55:46.337 then the new date should say 2006-06-25
17:00:46.337
If the current time was past 5pm then it should be as follows:
2006-06-23 19:55:46.337 then the new date should say 2006-06-26
17:00:46.337
Notice the day is an extra day because 5pm has already gone by hence it
has to go to the next 5pm, which is the next day.
Thanks.
SimonTry this:
SELECT DATEADD(DAY, (CASE
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 17 THEN 2
ELSE 3
END), DATEADD(HOUR, 17, CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS
DATETIME)));
HTH
Vern Rabe
"simon_s_li@.hotmail.com" wrote:

> Hi,
> Can someone please help me with a SQL Server 2005 issue with date and
> time.
> I want to take the current date and time and add 2 days to it, but the
> time must be set to 5pm.
> If the current date/time is past 5pm it will go to the next day at 5pm.
> So if the date was:
> 2006-06-23 15:55:46.337 then the new date should say 2006-06-25
> 17:00:46.337
> If the current time was past 5pm then it should be as follows:
> 2006-06-23 19:55:46.337 then the new date should say 2006-06-26
> 17:00:46.337
> Notice the day is an extra day because 5pm has already gone by hence it
> has to go to the next 5pm, which is the next day.
> Thanks.
> Simon
>|||Something like:
DECLARE @.Date1 DATETIME;
SELECT @.Date1 = GETDATE();
PRINT @.Date1;
SET @.Date1 = CASE WHEN DATEPART(HH, @.date1) > 17 THEN DATEADD(DAY, 1,
@.Date1) ELSE @.Date1 END;
SET @.Date1 = DATEADD(HH, (17-DATEPART(HH, @.Date1)), @.Date1);
PRINT @.Date1;|||I just realized that you apparently want to retain the current seconds and
milliseconds, to be added to 5:00 PM. Seems strange, but to do that, this
should work:
SELECT DATEADD(DAY, (CASE
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 17 THEN 2
ELSE 3
END), DATEADD(HOUR, 17, CAST(CONVERT(varchar(10), CURRENT_TIMESTAMP, 110) +
' 00:00' + RIGHT(CONVERT(varchar(24), CURRENT_TIMESTAMP, 13), 7) AS
DATETIME)));
HTH
Vern Rabe
"simon_s_li@.hotmail.com" wrote:

> Hi,
> Can someone please help me with a SQL Server 2005 issue with date and
> time.
> I want to take the current date and time and add 2 days to it, but the
> time must be set to 5pm.
> If the current date/time is past 5pm it will go to the next day at 5pm.
> So if the date was:
> 2006-06-23 15:55:46.337 then the new date should say 2006-06-25
> 17:00:46.337
> If the current time was past 5pm then it should be as follows:
> 2006-06-23 19:55:46.337 then the new date should say 2006-06-26
> 17:00:46.337
> Notice the day is an extra day because 5pm has already gone by hence it
> has to go to the next 5pm, which is the next day.
> Thanks.
> Simon
>

Adding data to more than one table

Hi there,

I am currently setting up a registration system where customers can registers their details and the details of the product, using ASP.net and MS SQL.

There is a column called customerID in the Custoemrs table, and a column of the same name in the Products table, so that I can have relationships between the tables.

For obvious reasons (ie. people that quit half-way through), I want to hold all the information until the end. The ID in the Customer table is unique, and auto-increasing, and therefore not assigned until the data enters the database.

However, I wish to submit information to the Products table at the same time, but what shall I put in for the custoemrID (which hasn't yet been assigned)

Thank you in advance for your help,

Nathair

It's a Referential Integrity issue. You need a ForeignKey with Cascading on UPDATE/DELETE to maintain the Referential Integrity on UPDATE/DELETE: take CustomerID column on Customer table as PrimaryKey, and CustomerID on Products table as ForeignKey, you can refer to this link:

http://msdn2.microsoft.com/en-us/library/ms177463.aspx

To maintain the Referential Integrity when INSERT, you can create an INSRET Trigger on the Customer table as following:

create trigger trg_Customer on Customer for insert
as
insert into Products select CustomerId,'myProduct' from inserted
go

You can take a look at this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

|||Thanks for that. UPDATE/DELETE isn't really gonna be an issue.

Normally I hard-core my SQL statement into the actual page - however, this time I'm thinking of using an SP in MS SQL. For both ways, I am unsure on how to incorporate the trigger?

Thanks,
Nathair