Users and Roles script

SQL Server Tools General

Index ‹ SQL Server

- Previous
  - 1
    - Transaction Question SQL Server only physically supports a single transaction. Named transactions are only useful for marking the transaction log. If you are trying to just roll back the stuff in the dynamic SQL, then you need to change the code to: save tran T2 raiserror( ' inside proc', 16, 11) PRINT STR(@@ERROR) rollback tran T2 PRINT STR(@@ERROR) return Save tran lets you roll back part of a transaction Also: Consider getting rid of the double quotes as that is not standard and not a good practice. Just double up quotes in the dynamic SQL: 'save tran T2 raiserror( '' inside proc'', 16, 11) PRINT STR(@@ERROR) rollback tran T2 PRINT STR(@@ERROR) return' Tag: Users and Roles script
  - 2
    - Unable to install SQL Server CE Server Tool on SQL Server 2000 SP 4 Are you 100% sure SP4 is installed (SELECT @@VERSION) Have you downloaded the lastest version of this file: http://www.microsoft.com/downloads/details.aspx familyid=79071E22-52F5-4189-998E-FD03648FD3F4&displaylang=en Tag: Users and Roles script
  - 3
    - Integrating SSRS with report viewer You can use reporting web service to get all reports link and pass the link to report viewer. Tag: Users and Roles script
  - 4
    - successful implementation of dataware house and data mining Moved to Analysis forum Tag: Users and Roles script
  - 5
    - Aggregation wizard It’s not posible... there is something wrong! did you refreshed your Data Source View Regards! Tag: Users and Roles script
  - 6
    - USING SSIS for scheduled import dear friend, Do you have integration services installed in your machine ! go to the services and check if there is a integration services running! regards! Tag: Users and Roles script
  - 7
    - Monitor network resources used by single database The tools are Profiler, accessable from the Tools menu in SSMS or Start/Programs/SQL Server/Performance Tools or perfmon accessable from Start/Programs/Administrative Tools/Performance. The tool to use depends on the meaning of "network utilization". Profiler will allow you to run a trace you can use to aggregate reads, writes and duration by database. Perfmon has many counters that can be used by database. See SQL Server 2005 Books Online topics: SQL Server, Databases Object http://msdn2.microsoft.com/en-US/library/ms189883.aspx Monitoring Resource Usage (System Monitor) http://msdn2.microsoft.com/en-us/library/ms191246.aspx Tag: Users and Roles script
  - 8
    - Drop database on a different SQL Server. If you have the remote server credential connect your SQL Mang. Studio with the target server & execute the same script. Tag: Users and Roles script
  - 9
    - replicating UDT's to subscribers Are you trying to exclude the UDT column, so it doesn't replicate to subscriber How did you specify, so it does not replicate UDT Did you add a vertical filter Or Gary Tag: Users and Roles script
  - 10
    - XQUERY / XPATH versus nodes containing ":"-character MyData is a prefix which you need to declare in your query as follows: Code Snippet DECLARE @x xml ; SET @x = '<MyData:Output xmlns:MyData="www.myprovider.com"> <MyData:Provider> <MyData:Property MyData:Name="FirstName">Joe</MyData:Property> </MyData:Provider> </MyData:Output>' ; SELECT @x . query ( ' declare namespace MyData="www.myprovider.com"; /MyData:Output/MyData:Provider ' ); Tag: Users and Roles script
  - 11
    - SQL Server vs. XML Configurations Are you sure your SQL Server config-based package is actually set to the SQL Server configurations Also that you have one and only one package configuration defined in each package Did you copy one package to a new name Be sure to generate a new GUID for the copied package, or else it will have the same GUID as the original, which can cause problems. Tag: Users and Roles script
  - 12
    - converting to numeric (6,3) A numeric(6,3) would only accept a number as large as 999.999 -six digits total, three digits to the right of the decimal. Your value, 1680.xxxx exceeds the maximum size and creates an overflow condition -resulting in an error. Perhaps you need a larger number, like maybe, just guessing here, numeric(8,3) Tag: Users and Roles script
  - 13
    - Datasource Views and Teradata I'm trying to to use SSAS 05 w/ our Teradata warehouse (VERSION 05.01.02.70 RELEASE V2R.05.01.02.40) and I'm having a problem creating datasource views. My issue is that the wizard returns every view and table in the warehouse (tens of thousands) which takes over 10 minutes . I can't seem to limit it to only the database I'm intersted in using. I've tried using the restrict schema option but then no objects are returned. Any ideas on how to do this Can you create the datasource views without the wizard Thanks. Tag: Users and Roles script
  - 14
    - Understanding code created by SSIS import/export Wizard You'll need to open the package in Business Intelligence Dev Studio. -Jamie Tag: Users and Roles script
  - 15
    - SQL Server 2005 Service Pack 2 - RTM - WHEN??? You never know ;-) RTM is planned for Q1 07. HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: Users and Roles script
  - 16
    - Parameterized Queries Sadly, SQL Server mobile does not support batch queries. Queries must be a single SQL statement. Stored prcedures are therefore not supported either. ExecuteNonQuery returns the number of rows affected for selects. For more information see: http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand.aspx Tag: Users and Roles script
- Next
  - 1
    - how to extract this text? Here is a first pass at what you might be looking for: declare @sizeLimit integer set @sizeLimit = 80 declare @mockup table ( rid integer identity primary key, theData varchar (80) ) insert into @mockup values ('DT·NB·NM·ANI=8008808039139662' ) insert into @mockup values ('ANI=8008808036637576·DNIS=80088080·DT·NB·NM·UNIT' ) insert into @mockup values ('ANI=8008808039188390·ANI=8008808017403125·DNIS=80088080·DT·NB·NM·UNIT' ) insert into @mockup values ('ANI=8008808039653135·ALIAS·DNIS=80088080·DT·NB·NM·UNIT' ) insert into @mockup values ('ALIAS·ANI=8008808039169467·ANI=8008808017326875·UNIT·NM·NB·DT·DNIS=80088080' ) insert into @mockup values ('DT·NB·NM·DNIS=80088080·ANI=8008808036331200·ANI=8008808039318646' ) declare @aniList table ( rid integer, startLoc integer, iter integer, targetString varchar(10), primary key (rid, startLoc, iter) ) insert into @aniList select rid, startLoc, n.iter, substring (targetString, 8*n.iter - 7, 8) as targetString from ( select rid, i.iter as startLoc, min (j.iter) - i.iter - 4 as aniLength, substring (theData, i.iter + 4, @sizeLimit) as targetString from @mockup inner join small_iterator (nolock) i on i.iter <= @sizeLimit and substring (theData, i.iter, 4) = 'ANI=' inner join small_iterator (nolock) j on j.iter >= i.iter + 4 and j.iter <= @sizeLimit and charindex (substring (theData, j.iter, 1), '0123456789') = 0 group by rid, i.iter, substring (theData, i.iter + 4, @sizeLimit) ) a inner join small_iterator (nolock) n on n.iter <= (aniLength + 7) / 8 and substring (targetString, 8*n.iter - 7, 2) in ('39', '36') order by rid, startLoc, n.iter select distinct rid, replace ( ( select targetString as [data()] from @aniList s where s.rid = a.rid order by s.rid, s.startLoc, s.iter for xml path ('') ), ' ', '-') as targetString from @aniList a group by rid order by rid -- rid targetString -- ----------- ----------------- -- 1 39139662 -- 2 36637576 -- 3 39188390 -- 4 39653135 -- 5 39169467 -- 6 36331200-39318646 Tag: Users and Roles script
  - 2
    - Differences in varchar size usage, declaring the maximum available of 8000 characters The storage of varchar is always the same. We store the length of the actual data, and the data itself. The length in the table definition is used to validate that the varchar is not longer than the declared length, but does not take up any storage. The same argument is valid when you use variables as well. My suggestion would be that you define the size of the variable or column to what makes sense to your application. If you never need more than 500 chars, I don't see the need to define a table as varchar(8000). Thanks, Marcel van der Holst [MSFT] Tag: Users and Roles script
  - 3
    - Daylight Saving Time DST 2007 Problems SQL Server gets tim (using all three of these functions) from the Windows OS. This is a quote from Books Online for getutcdate ()- Returns the datetime value that represents the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running. Tag: Users and Roles script
  - 4
    - end conversation : security context could not be retrieved Each Broker: Message undeliverable --- This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation . That info should explain why the 'security context could not be retrieved'. Tag: Users and Roles script
  - 5
    - Unicode causes Dimension build failure? Chris, I tried similar dimensions with Chinese unicode characters and I was able to build the dimension with no problem, with the same collation and settings. Can you try to execute the last SELECT DISTINCT statement on its own and see what happens. Yan Tag: Users and Roles script
  - 6
    - Change member properties order in excel 2007 pivot table Anyone Tag: Users and Roles script
  - 7
    - SQL Server 2005 Limitation: Having many attributes for Neural Network The reason that Neural Networks doesn't support table prediction is precisely to avoid situations where you could create models to predict 100,000 attributes. Essentially what you are asking the model to do is to create 100,000 simultaneous neural networks. Each of these networks would have 100,000 input nodes, somewhere on the order of 100,000 squared hidden nodes and one output node. A few calculations would determine that the memory required to solve such a problem would be enormous, and you likely wouldn't want to wait until it completed even if you had the memory. For example, take your 100,000 attribute problem as input and predict a case-level attribute. Time that and multiply by 100,000 (if you have a dual proc box, divide by 2!). Yes, that's how long it would theoretically take assuming you had enough memory. Let me know if you disagree that the problem you are trying to solve isn't likely suitable for NN. I would try Naive Bayes or Decision Trees for this problem. Logistic Regression may also work, but I'm unsure of it's nested table requirements. Tag: Users and Roles script
  - 8
    - readonly custom property No, currently we do not have a way to do that. What would you use the read-only properties for Thanks. Tag: Users and Roles script
  - 9
    - Label in parameter Is your parameter query driven Do something like this: SELECT Name + ', ' + Age as ParameterLabel from Table Use the field ParameterLabel for the Label of your parameter. Tag: Users and Roles script
  - 10
    - SSIS performance SP1 SP2 Are you passing variables to the child packages from the parent to perform configuration, or anything I just wonder if most of the time is spent in the pre-validate stage. When you run it in BIDS, where's the bulk of the time spent when looking at the Progress tab Tag: Users and Roles script
  - 11
    - - You need a mapi email client such as Outlook 2000. I have around 17 SQL/Server 2000's using Domino 6.5 email accounts. Here is the scoop: Log on to the server as the account that is used for the SQL Service account and install Outlook 2000 (with no service pack). Launch Outlook 2000 and create a mail profile that points to your Domino server. Call this email account SQLMail. Create another Outlook 2000 profile with the name SQLAgentMail and test it (this can be the same Domino account). Then log off the server and log back on using the login account that you normally run the server with. Restart SQL Server (and SQL Server Agent) and then the mapi email profiles created earlier will be available. Set SQLMail to use SQLMail profile and then set SQL Server Agent mail to use SQLAgentMail profile. That's it! DeWayne Tag: Users and Roles script
  - 12
    - Problem in Configuring ASPState DB for .NET 2.0 First of all, this is not the right place for posting a question about the ASP.NET Session DB, this is a CLR in SQLServer related forum only. But from the logs posted the db should have been installed. Make sure that you are trying to connect to the right session database from your web.config file. Also try to monitor if the passwort / username provided are right. Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: Users and Roles script
  - 13
    - Restoring Differentials from 2000 to 2005 If both the servers are sql 2005 you have the option of Log shipping in sql 2005 but from sql 2000 to sql 2005 you can restore the full backup but about differential backup I DON'T think you can perform it ! If at all you try it you need to restore it in standby mode then only you can use it for reporting purposes ! ofcourse you have the option of database snapshot but better to avoid this rather have it between sql 2005 to sql2005 or sql 2k to sql 2k Regards Deepak Tag: Users and Roles script
  - 14
    - Drive allocation With just two virtual drives, it is a tough call. I'd probably put the TempDb on the RAID 5 set -leaving only the Transaction Log on the Mirror set. Tag: Users and Roles script
  - 15
    - summing semi-additive attribute Use Distinct Count. --Imran Tag: Users and Roles script
  - 16
    - formating a text field using regular expression I believe that SSRS does not see the members of external references, and treats them as an error in editor, but the report should run. BobP Tag: Users and Roles script

StephanieR

In 2005, is there a way to automate this I'd like to be able to do this automatically periodically:

1) Script out the development users and their roles in my development database

2) Backup the production database and restore the it to the development database

3) Re-synch the existing users (sp_change_users_login), drop any obsolete users (sp_dropuser), and add any development users with roles back on

Does anyone have a script that can help me achieve this

Top

Re: Users and Roles script

Satya SKJ

Refer to this http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx for more information, as you can use SMO in SQL 2005.

Top