Creating a user for replication

Anyone have the steps (permissions and etc...) to create a user for use only in replications instead use "sa" login ?we have created a dedicated login for the Replication with sa rights|||but the user need to have sa role ?!|||Yes it needs to have the sa Rights..sql

Creating a SSIS package

HI All,

can any body give steps to import data from one sqldb to another through ssis package, i was comfotable with dts but ssis is a lil bit confusing.....

thnx

regards

Start with the Wizard. Use it to build your first package, then save it and open it up in the designer, and you have your first sample package to learn from.

You actually want the Data Flow task for this, but why not let the Wizard show you for now.

You may find it useful to give these a try to help get you familiar with SSIS

Integration Services Tutorials
(http://msdn2.microsoft.com/en-us/library/0fc6e3a7-1c12-444a-b1ef-ead622f805d2.aspx)

Creating a Mobile Application with SQL Server Mobile - FIX

This is a great tutorial and it's a shame one of the more important steps was missed.

In the “Create the snapshot user” section you you find the steps to create the snapshot_agent account. Then in the “Create the snapshot folder” section you find the share and folder permissions. However, at no point do the instructions advise you about adding the snapshot_agent to the SQL Server Logins. The result is that agent cannot perform the initial snapshot but you won't find this out until 50 steps later after Step 10 in the section “Create a new subscription".

To get back on track, openthe Object Explorer's Security section and add the snapshot_agent to your logins. Then using the "User Mappings", set an appropriate level for the SQLMobile database role. Once completed you then need to run the agent.

Right-click the SQLMobile publication you created and select "View Snapshot Agent status". From that dialog you can select "Start" to run the agent. When it completes, you can return to the tutorial section "Create a new subscription" and continue with the tutorial.

that's one way to do it and thanks for pointing out the omission. a more common approach is to make sure the account that the snapshot agent runs as is granted permissions on the publication, the database engine, and the database.

Darren

|||

Darren,

In order to grant permissions on the database engine you need to create the login. And just granting permission on the database engine, database and the publication doesn't seem do it. You still need to asign a "Server Role" to the login. My first guess was "processadmin"; however, I tried a number of different combinations without success. Only when I set the snaphot-agent to the role of "sysadmin" was I able to get the agent to complete the process of creating the initial snapshot.

|||

that's true if the account you chose to run SQL Agent as isn't already recognized in the sysadmin role as a SQL Server login. for the average developer trying to get merge repl working the first time, SQL Server and IIS are both running on the machine that the device is connected to via ActiveSync. what I was trying to say in my last post is, whatever account you log on to you machine with, as long as it is an Administrator account, this is a good account to run the SQL Agent under. That makes the permissions issues easier to configure on SQL Server as you only have to grant that login appropriate permissions on the pub and the db. Of course you also grant permissions to the IUSR_{your machine name} account if using anonymous auth.

in a production environment, some other account should be used and as you correctly noted, this account needs to either be sysadmin on SQL Server or be granted db_reader and db_writer on the pub and published database. typically, IIS and SQL Server are on separate boxes in this scenario and a domain account is used that both machines can recognize.

Darren

|||

It is less than practical to use the "sysadmin" role in this instance. Review of the documentation shows that the minimum permissions for a "pull subscription" require the login to be associated with a user in the distribution database. At minimum be a member of the db_owner fixed database role in the distribution database.

So I have now removed the sysadmin role from the computername\snapshot_agent and added it as a user in the distribution database with the db_owner role. These permissions allow the agent to run the replication snapshot job.

Now, I have no indication that others are able to run this tutorial without specifically setting the above permission. If they are, then perhaps some other settings related to the wizards or replication itself are necessary.

The real point here is that given a tutorial which identifies each step in setting up replication (including specific names and permisions) should work according to the names/permissions identified. So lets fix the omission and move on.

Note: specific infornation is available at http://msdn2.microsoft.com/en-us/library/ms151868(d=ide).aspx

Creating a local SQL Server Compact Edition DB

Hi All ...

I'm setting up replication for the 1st time following the steps in the Books Online to get it up and running:

ms-help://MS.SSCE.v31.EN/ssmmain3/html/5a82aa7a-41a3-4246-a01a-2b1e4b2fdfe9.htm.

Anyhow, I get down to the section labeled - Create a new SQL Server Compact Edition database and am having a problem. I open SQL Server Management Studio as instructed. I click Connect and then am supposed to select SQL Server Compact Edition - yet can't find that option.

And, yes, I've installed Compact Edition on my development laptop. So, how do I get this option so that I can create the local DB for Compact Edition and continue development?

UPDATE:

Ok, duh moment, I have SQL Server Management Studio Express loaded on my laptop, so did not have the option. I've attempted to uninstall SQL Server Management Studio Express and install the full version from the SQL Server CDs. However, when I go to install the full version it tells me that the management tools are already installed and won't let me install anything. So, how do I get around this so that I can install full version of SQL Server Management Studio and then get the option to connect to the SQL Server Compact Edition?

Any help will be appreciated.

UPDATE 2:

Ok, figured it out. You can't just uninstall SQL Server Management Studio express, you must go in to Add/Remove Programs, SQL Server 2005, Change. Then select to uninstall the Workstation components. Then you can go use the SQL Server install discs to install the full blown SQL Server Management Studio along with the other Workstation components.

Thanks ...

David L. Collison

Any day above ground is a good day!

As a script-kiddie wannabe, I will suggest that you can use the following vbscript as well:

<job id="SqlServerCompactEditionDatabaseCreator">
<script language="VBScript">

Option Explicit

Dim strConnectionString
Dim oCatalog

strConnectionString = "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=" & WScript.Arguments(0) & ";"

Set oCatalog = WScript.CreateObject("ADOX.Catalog")
oCatalog.Create strConnectionString

</script>
</job>

Save the script to something like SqlServerCompactEditionDatabaseCreator.wsf, and you can create a SQL Server Compact Edition database with something like cscript SqlServerCompactEditionDatabaseCreator.wsf foo.sdf from the command prompt.

Then again, that is probably just my 2 cents

-Raymond

Creating a job with many steps including DTS

Hi,
(using Windows 2000 Server/SQL2K Ent Ed SP3a)
I am looking to create a job with a number of steps inside it. I need
the job to run on a sunday afternoon when our OLTP system is at our
quietest in terms of user connections and throughput. The job purpose
is to copy data (24,000,000 rows) from one table to another on the same
database, via a DTS package. I then need to rename the old table,
removing the triggers and views on it, then rename the new table, and
add the relevent objects (including indexs and constraints) then add
the permissions on it.
These are the steps:
1) Set restricted user mode on database
2) Run the DTS package
3) Remove triggers and views on old table
4) Rename old table
5) Rename new table
6) Add indexs and constraints to new table
7) Add triggers/views to new table
8) Set permissions to new table
9) Set multi user mode on database
My question would be does the above seem logical, or would there be a
better way of performing this process? Do I need the DTS package, would
a INSERT INTO be better, or could I use a DTS package for the entire
process?
Thanks and rgds,
qhHi
INSERT..SELECT may be a better option.
You may want to try something like the following to avoid the rename steps:
Create Archive Table
Insert Into Archive Table
Delete Archived Data from Live Table
ReIndex Live Table
You may also be able to do this in smaller increments each night instead of
once a w.
John
"Scott" wrote:

> Hi,
> (using Windows 2000 Server/SQL2K Ent Ed SP3a)
> I am looking to create a job with a number of steps inside it. I need
> the job to run on a sunday afternoon when our OLTP system is at our
> quietest in terms of user connections and throughput. The job purpose
> is to copy data (24,000,000 rows) from one table to another on the same
> database, via a DTS package. I then need to rename the old table,
> removing the triggers and views on it, then rename the new table, and
> add the relevent objects (including indexs and constraints) then add
> the permissions on it.
> These are the steps:
> 1) Set restricted user mode on database
> 2) Run the DTS package
> 3) Remove triggers and views on old table
> 4) Rename old table
> 5) Rename new table
> 6) Add indexs and constraints to new table
> 7) Add triggers/views to new table
> 8) Set permissions to new table
> 9) Set multi user mode on database
>
> My question would be does the above seem logical, or would there be a
> better way of performing this process? Do I need the DTS package, would
> a INSERT INTO be better, or could I use a DTS package for the entire
> process?
> Thanks and rgds,
> qh
>