A helpful tip for resetting your Identity Seed Column Property. . .

Open Table > Design Mode > Select Column with Indentity Specification of ‘Yes’ and review the following settings:

  • (Is Identity) = Yes
  • Identity Increment = [user-defined value] (e.g. ‘1’)
  • Identity Seed = [user-defined] (e.g ‘1’)

Now for the actually Management Studio Statements. . . Open New Query Window :

  1. DBCC CHECKIDENT ([table name], NORESEED) — Will check identity information : current identity value ‘x’, current column value ‘1’.
  2. DBCC CHECKIDENT ([table name], RESEED, [numeric value]) — Will set the Identity Seed to [numeric value]
  3. Run Step # 1 to validate Step # 2 Change
Advertisements

If you’re scripting and looking to retrieve data from a relational or non-relational database this post should help. Whether you’re retrieving data from Microsoft or another database provider you will need the following API to interface with the database(s) : (A)ctiveX (D)ata (O)bjects

I put (2) examples together. . . 1st w/ Microsoft SQL Connection. . . 2nd w/ LDAP which will build on what you learn from the SQL example.

IMPORTANT – You are required to know connection credentials and be familiar with the data schema. If you don’t, consult with your database admin to gather these requirements.

Here is a VB Script example for connecting to a Microsoft SQL Server v2005 via ADODB API, and then retrieving the row output with a record set (rs) array. You can extend this in the user-defined sections (Highlighted in Blue)

Details of the following code :

  1. You will need access to the server’s file system via FSO (FileSystem Object). With FSO you can create new or access existing files. Do this by setting the FSO variable to the following.
  2. Declare the connection (con), command (com) and record set (rs) variables via DIM statement
  3. Set the connection variable (var)
  4. Set the command var
  5. Set the con properties : Provider. Connection String.
  6. Open the con
  7. Set com property to con : ActiveConnection
  8. Set com property with SQL query : CommandText
  9. Execute com and set results to rs array; cycle through records to rs array until end-of-file (EOF) is met; then end loop via Wend
  10. Close rs array

 VB Script for Microsoft SQL ADO Connection

Set fso = CreateObject(“Scripting.FileSystemObject”)
Dim con, com, rs

Set con = CreateObject(“ADODB.Connection”)
Set com = CreateObject(“ADODB.Command”)
con.Provider = “SQLOLEDB”
con.ConnectionString = “Driver={SQL Server};Server=[server name];Database=[database name];UID=[username];PWD=[password]
con.Open

Set com.ActiveConnection = con
   com.CommandText = “Select [field 1], . . . , [field n] from [table]”

Set rs = com.Execute

rs.MoveFirst

counter = 1

While NOT rs.EOF

[user defined logic]

rs.MoveNext

counter = counter + 1

Wend

rs.close

Once you have the data stored within the record set you can begin building your vbscript logic to determine what you want to do with the data stored within the record set (rs). This should get you rolling. . . If you have any questions about the above script or need help moving to the next step, let me know.

VB Script for Lightweight Directory Access Protocol Connection

Const LDAPSource = “LDAP://

Dim con, com, rs
    Set con = CreateObject(“ADODB.Connection”)
    Set com = CreateObject(“ADODB.Command”)
    con.Provider = “ADsDSOObject”
  con.Properties(“User ID”) = “[username]”
  con.Properties(“Password”) = “[password]
    con.Open

Set com.ActiveConnection = con
        com.CommandText = “Select [field 1], . . . , [field n] from ‘” & LDAPSource & “‘ where [condition(s)]

[ . .  record set logic . . ]

Don’t forget to close the record set logic . . .

I’m working with a situation that a production database was populating a notification table for a downstream process which would delete the data post-process. Here’s what would happen. . .Once a set of application rules inserted the formatted variables into respective fields a downstream process will retrieve the data, deliver notifications, and then re-query the database for that row and delete it.

So if you’re trying to retrieve the row(s) prior to the down stream process deleting it – you can use the following logic to continue looking until it finds data in the table. I did this to copy a production data row and then insert the results into a test/dev environment. This approach helped avoid impact of production processes to force data output.

NOTE : I initially built this without a timing mechanism so it would continually run until one or more rows returned – then a select statement follows to a specific set of fields from the table. To help matters, I introduced a delay in the loop, to avoid maxing out a CPU on the SQL Server, at a (5) second interval. . .

declare @count int
SET @count = 0

WHILE @count = 0
BEGIN
 set @count = (Select count(*) from [table name])
 waitfor delay ‘0:0:5’
END

Select * from [table name]

Thanx – J

If you’re an administrator of BMC Service Desk Express 9.x , you might find these SQL Statements helpful for retrieving the following SDE ticket types.

–INCIDENT Type

SELECT COUNT(*) FROM _SMDBA_._TELMASTE_

–PROBLEM Type

SELECT COUNT(*) FROM _SMDBA_._PROBMGMT_

–CHANGE Type

SELECT COUNT(*) FROM _SMDBA_._CHANGE_

–WORK ORDER Type

SELECT COUNT(*) FROM _SMDBA_._WORKORD_

Expand upon the statements as you need to introduce JOIN or WHERE Clauses. . . The above will get you started anyway. If you need help – just ask.

Thanx – J

 

If you want to maintain a healthy sharepoint environment, it takes requirements determined by the business and the system administrators. If you haven’t taken time to evaluate your user’s environment, start asking questions. Sharepoint will take planning.

Evaluate the business. Understand the users actions; how the environment works.  Physical and logical contraints need to be known. When you think about configuring the network, hardware, application, database, and disk storage layers of the environment. Define your sharepoint deployment strategy at the physical, process, logical, and site-level perspectives.

On a spectrum of Isolation to Scalability, consider the following:

  1. Physical Isolation : separation of networks for each site collection — not practical for most environments
  2. Physical Isolation : seperation of servers for each site collection — not practical for most environments
  3. Process Isolation : seperation of application pools for each site collection — Internet/Extranet, or Sensitive Data
  4. Logical Isolation : seperation of web applications for each site collection — Internet/Extranet, or Sensitive Data
  5. Logical Isolation : seperate host names for each site collection — Internet/Extranet, or Sensitive Data OR Internet (non-interactive/readonly) or Intranet
  6. Logical Isolation : seperate site collections for each site — Internet (non-interactive/readonly) or Intranet
  7. Sharepoint Site Isolation : multiple subsites, and workspaces in a site collection — Intranet Only
  8. Sharepoint Site Isolation : specific security for a list or library within a site collection or subsite — Intranet Only

Though measuring the isolation and scalability variables, think about how they determines your ability to administer this.

  • Will it be easy for you to limit the exposure of failures?
  • Will security be easy to manage?
  • If you’re introducing a highly-scalable environment you’re also inviting complexity — increasing risks to break.
  • If a user’s team loses their site, what will your degree of restoration be? Item level? Site Collection Level? Database Level?
  • If the architecture approach is #7 the site collections can grow (if you place no quota contraints) with many groups using sub-sites within a single site collection (a top-level site / logical partition within a contentdb). To restore a previous version of a particular sub-site may impact the all teams within this site collection, creating noise, possible data loss, etc. This is assuming you’re not using a technology like Microsoft DRM that will manage all this for you — however this solution requires large amounts of storage equating to more costs.
  • What about security management? Do you want to commit to managing item-level security? Do you want to manage multiple subsite permissions that are unique within a parent site collection? Wouldn’t it be easier to set the permissions at the site collection level and then require all subsite’s to inherit the parent-level perms?

So isolate the architecture as best you can, but keep it flexible to scale it to the degree your company intends to take the technology. Every business will have a different.

Easy. Right?

Tune in next for information on factors to consider when you’re putting data in Sharepoint.

  • How storage quotas will help. . .
  • Versioning is a convenience to user’s, right? What about additional storage factors?
  • What about disk inflation? Why is 1GB of RAW Fileshare Storage inflate to 1.2 GB of storage in Sharepoint?

I’ll have more on this topic, and it will certainly help plan for the needs of your business.