SQL SERVER - 2005 - Export Data From SQL Server 2005 to Microsoft Excel Datasheet
January 8, 2008 by pinaldave
Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio - Query Editor.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName. Alternatively you can download sample Spreadsheet from here.
Run following code in SQL Server Management Studio - Query Editor.
USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO
Open contact.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.
Make sure your spreadsheet is closed during this operation. If it is open it may thrown an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.
Reference : Pinal Dave (http://www.SQLAuthority
Friday, August 29, 2008
Thursday, July 3, 2008
SharePoint 2007 - Add New User Bug
SharePoint 2007 - Add New User Bug
I am starting to wonder if my expectations are too high. I have been working with this product since Beta 1. Of course there were lots of bugs then. Then Beta 2 came along, fewer bugs, but of course we have to remember, over the course of the betas, I didn't test everything. Then they release Beta 2TR. This build looked pretty darn good. So good in fact that we started a project and were using the Beta 2TR as a development platform, with a release date of mid December (now), knowing (ummm, hoping) that we would be able to use the production build for deployment. We knew it was Beta and we knew there would still be bugs. So fine. Well, now we have the "Gold" code and I am wondering, as stated earlier, if my expectations are to high. If you have a virtual machine available, follow along in the steps below and tell me if you have a solution to my problem. Essentially, we have automated the process of group management in SharePoint using MIIS and the SharePoint web services. Something is terribly wrong with how the product is behaving as this problem manifests itself if the procedure is done thru the user interface as demonstrated below.
This is happening in production; we found this out as we deployed and did some final testing, although the issue will not manifest itself right away, I am confident it will eventually and need a solution and/or fix.
The specs for my virtual environment are as follows:
Windows Server 2003 SP1 with IIS, Active Directory
SQL Server 2005 SP1
Windows SharePoint Service v3 - production is Office SharePoint Server 2007, this issue is WSS related, so WSS will suffice.
Follow these steps:
Open up Active Directory Users and Computers and add a user. This user does not need to be in any group or have any special permission, just create a plain old vanilla user.
Browse to your WSS site, go to Site Actions...Site Settings...People and groups.
Under New, select Add Users.
Use the picker to find your user, although this is not necessary. Make sure that you are adding him/her to the correct group and click OK.
Click the Home tab to get back to the home page of your site.
Click Sign in as Different User from the links available when you click on your login name at the top right hand section of the page.
Sign in as the new user. You will log on successfully. So far so good!
Return to Active Directory Users and Computers and delete the user you created in the previous set of steps.
Return to SharePoint and try to login as the user you just deleted. As expected, you are denied access. Great!
Scenario #1 - Use Existing
Browse to your WSS site, go to Site Actions...Site Settings...People and groups. Notice that your user is still there. That's expected, since we did not remove him from the Site Collection.
Return to Active Directory Users and Computers and add the exact same user; same First name, same Last name, same User logon name, same Password.
Open a new browser and attempt to login as the new user.
You will get the following Access Denied screen:
I expected this behavior because the user I originally added to SharePoint is not the user I just created again in Active Directory. Although they have the same User logon name, they most certainly have different SID's.
Scenario #2 - Replace
Logon to WSS as an administrator, browse to your WSS site, go to Site Actions...Site Settings...People and groups.
Under New, select Add Users.
Use the picker to find your new (second) user, although this is not necessary. Make sure that you are adding him/her to the correct group and click OK. In theory, I would expect this process to "replace" the existing user in SharePoint with the new one.
Open a new browser and attempt to login as the new user.
As in step 13, you get the same Access Denied screen. This is not expected, since I just "replaced" a user.
This is the point where we are going to branch and I am going to demonstrate an unusual phenomenon that I cannot explain.
Scenario #3 - Use New
Logon to WSS as an administrator, browse to your WSS site, go to Site Actions...Site Settings...People and groups.
Click All People in the Quick Launch navigation.
Check the checkbox next to the user you had first added to SharePoint back in step 4. Remember, this is the original user.
Click Actions...Delete Users from Site Collection. Click OK in the warning popup. We do in fact want to completely remove this user from the Site Collection. One would assume that this action does in fact completely remove the user. Keep that in mind.
Click on the group to which you originally had added the user to. You will notice that the user is in fact gone.
Under New, select Add Users.
Use the picker to find your second user, although this is not necessary. Make sure that you are adding him/her to the correct group and click OK. Keep in mind that we have just deleted the user from the Site Collection in step 4, so I fully expect this step to add the new user correctly.
Click the Home tab to get back to the home page of your site.
Click Sign in as Different User from the links available when you click on your login name at the top right hand section of the page.
As before, you get the same Access Denied screen. This is not expected, since I just completely removed a user, then added a completely new one.
Remove the user from the Site Collection by following the steps above.
I fully expected that to work as well. So we can see that "replacing" a user with a user with the same user logon name (imagine John Smith quitting and Jane Smith being hired in the same day; a likely scenario, especially if your User logon name standard is first initial, last name), and completely removing a user and adding a "new" user with the same User logon name, does NOT play nice with SharePoint. Now for the only "Solution" I could come up with.
Lame duck Solution
At this point, SharePoint should not have either of the 2 users you created and added, and Active Directory should have only the 2nd user you created. Restart your virtual environment web server (in my case everything is on one machine).
Verify that the 2nd user you created is still in Active Directory and that the user is not a member of the Site Collection users.
Logon to WSS as an administrator, browse to your WSS site, go to Site Actions...Site Settings...People and groups.
Under New, select Add Users.
Use the picker to find your new (second) user, although this is not necessary. Make sure that you are adding him/her to the correct group and click OK.
Open a new browser and attempt to login as the new user. This will be successful.
Conclusion
All I can conclude is that something is being cached somewhere on the web server, or the user is not completely deleted from the Site Collection until some sort of timer job runs when the server gets rebooted. I have tried IIS resets and they do not work. Regardless, I think the "replace" that is happening in my first scenario should work as I expect it to, but it is not.
Please chime in with ANY ideas you may have.
Published 15 December 2006 06:10 AM by Dan Attis
Filed under: SharePoint
I am starting to wonder if my expectations are too high. I have been working with this product since Beta 1. Of course there were lots of bugs then. Then Beta 2 came along, fewer bugs, but of course we have to remember, over the course of the betas, I didn't test everything. Then they release Beta 2TR. This build looked pretty darn good. So good in fact that we started a project and were using the Beta 2TR as a development platform, with a release date of mid December (now), knowing (ummm, hoping) that we would be able to use the production build for deployment. We knew it was Beta and we knew there would still be bugs. So fine. Well, now we have the "Gold" code and I am wondering, as stated earlier, if my expectations are to high. If you have a virtual machine available, follow along in the steps below and tell me if you have a solution to my problem. Essentially, we have automated the process of group management in SharePoint using MIIS and the SharePoint web services. Something is terribly wrong with how the product is behaving as this problem manifests itself if the procedure is done thru the user interface as demonstrated below.
This is happening in production; we found this out as we deployed and did some final testing, although the issue will not manifest itself right away, I am confident it will eventually and need a solution and/or fix.
The specs for my virtual environment are as follows:
Windows Server 2003 SP1 with IIS, Active Directory
SQL Server 2005 SP1
Windows SharePoint Service v3 - production is Office SharePoint Server 2007, this issue is WSS related, so WSS will suffice.
Follow these steps:
Open up Active Directory Users and Computers and add a user. This user does not need to be in any group or have any special permission, just create a plain old vanilla user.
Browse to your WSS site, go to Site Actions...Site Settings...People and groups.
Under New, select Add Users.
Use the picker to find your user, although this is not necessary. Make sure that you are adding him/her to the correct group and click OK.
Click the Home tab to get back to the home page of your site.
Click Sign in as Different User from the links available when you click on your login name at the top right hand section of the page.
Sign in as the new user. You will log on successfully. So far so good!
Return to Active Directory Users and Computers and delete the user you created in the previous set of steps.
Return to SharePoint and try to login as the user you just deleted. As expected, you are denied access. Great!
Scenario #1 - Use Existing
Browse to your WSS site, go to Site Actions...Site Settings...People and groups. Notice that your user is still there. That's expected, since we did not remove him from the Site Collection.
Return to Active Directory Users and Computers and add the exact same user; same First name, same Last name, same User logon name, same Password.
Open a new browser and attempt to login as the new user.
You will get the following Access Denied screen:
I expected this behavior because the user I originally added to SharePoint is not the user I just created again in Active Directory. Although they have the same User logon name, they most certainly have different SID's.
Scenario #2 - Replace
Logon to WSS as an administrator, browse to your WSS site, go to Site Actions...Site Settings...People and groups.
Under New, select Add Users.
Use the picker to find your new (second) user, although this is not necessary. Make sure that you are adding him/her to the correct group and click OK. In theory, I would expect this process to "replace" the existing user in SharePoint with the new one.
Open a new browser and attempt to login as the new user.
As in step 13, you get the same Access Denied screen. This is not expected, since I just "replaced" a user.
This is the point where we are going to branch and I am going to demonstrate an unusual phenomenon that I cannot explain.
Scenario #3 - Use New
Logon to WSS as an administrator, browse to your WSS site, go to Site Actions...Site Settings...People and groups.
Click All People in the Quick Launch navigation.
Check the checkbox next to the user you had first added to SharePoint back in step 4. Remember, this is the original user.
Click Actions...Delete Users from Site Collection. Click OK in the warning popup. We do in fact want to completely remove this user from the Site Collection. One would assume that this action does in fact completely remove the user. Keep that in mind.
Click on the group to which you originally had added the user to. You will notice that the user is in fact gone.
Under New, select Add Users.
Use the picker to find your second user, although this is not necessary. Make sure that you are adding him/her to the correct group and click OK. Keep in mind that we have just deleted the user from the Site Collection in step 4, so I fully expect this step to add the new user correctly.
Click the Home tab to get back to the home page of your site.
Click Sign in as Different User from the links available when you click on your login name at the top right hand section of the page.
As before, you get the same Access Denied screen. This is not expected, since I just completely removed a user, then added a completely new one.
Remove the user from the Site Collection by following the steps above.
I fully expected that to work as well. So we can see that "replacing" a user with a user with the same user logon name (imagine John Smith quitting and Jane Smith being hired in the same day; a likely scenario, especially if your User logon name standard is first initial, last name), and completely removing a user and adding a "new" user with the same User logon name, does NOT play nice with SharePoint. Now for the only "Solution" I could come up with.
Lame duck Solution
At this point, SharePoint should not have either of the 2 users you created and added, and Active Directory should have only the 2nd user you created. Restart your virtual environment web server (in my case everything is on one machine).
Verify that the 2nd user you created is still in Active Directory and that the user is not a member of the Site Collection users.
Logon to WSS as an administrator, browse to your WSS site, go to Site Actions...Site Settings...People and groups.
Under New, select Add Users.
Use the picker to find your new (second) user, although this is not necessary. Make sure that you are adding him/her to the correct group and click OK.
Open a new browser and attempt to login as the new user. This will be successful.
Conclusion
All I can conclude is that something is being cached somewhere on the web server, or the user is not completely deleted from the Site Collection until some sort of timer job runs when the server gets rebooted. I have tried IIS resets and they do not work. Regardless, I think the "replace" that is happening in my first scenario should work as I expect it to, but it is not.
Please chime in with ANY ideas you may have.
Published 15 December 2006 06:10 AM by Dan Attis
Filed under: SharePoint
Friday, June 13, 2008
Customized Theme in MOSS 2007
Customized Theme in MOSS 2007
Windows Sharepoint Server (WSS) and Sharepoint Portal Server (SPS) both ship with several built-in themes, giving you the ability to change the look and feel of a site quite dramatically. Choosing one of these themes is simply a matter of clicking on the “Site Settings” link in the upper navigation and then “Site Theme” under the “look and Feel” header. On the “Site Theme” page you should see a list of available themes on the right. Click on one of these themes and you’ll see a preview on the left of the page. All of the themes available for use on a SharePoint server are stored beneath a single folder named “THEMES“. This folder contains a unique folder for each theme: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\THEMES.
To create a theme, start by creating a new folder with the images and CSS files that make up the theme. Here are the steps to create a new theme called “MOSSTHEME″:
Here is a short procedure of creating a custom site theme named "MossTheme":
1. Copy any theme folder in "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\THEMES" folder and paste with its name replaced with "MossTheme". In this example, copy GRANITE folder.
2. In MossTheme folder, rename GRANITE.INF file to MOSSTHEME.INF in upper case.
3. Open MOSSTHEME.INF file with notepad.
4. Change the value of title under [Info] to MossTheme.
5. Replace every word, Granite, under [titles] with MossTheme.
6. Open "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\1033\SPTHEMES.XML" file with notepad.
7. Add the following lines under tag:
'<' templates '>'
'<'templateid'>'MossTheme '<'/templateid'>'
'<'displayname'>'MossTheme '<'/displayname'>'
'<'description'>'ossTheme '<'/description'>'
'<'thumbnail'>'images/ MossTheme.gif'<'/thumbnail'>'
'<'preview'>'images/ MossTheme.gif'<'/preview'>'
''
Notice that preview and thumbnail paths are images/MossTheme.gif. By default, MOSS 2007 and WSS 3.0 will not have such image files.
8. In order to display thumbnail and preview correctly, you will need to capture the screen and save the file in "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\IMAGES" folder with MossTheme.gif name. You can change the .gif file name if you change the thumbnail and preview file names in tag.
9. Do an iisrest for the server to recognize the new theme.
Pretty simple procedure. Now you are ready to test your new theme. In Site Settings, you can now choose MOSSTHEME; however, the theme will not differ from Granite theme. Now, it is time for you to play with theme.css file!
Posted by Karun at Wednesday, May 07, 2008
Windows Sharepoint Server (WSS) and Sharepoint Portal Server (SPS) both ship with several built-in themes, giving you the ability to change the look and feel of a site quite dramatically. Choosing one of these themes is simply a matter of clicking on the “Site Settings” link in the upper navigation and then “Site Theme” under the “look and Feel” header. On the “Site Theme” page you should see a list of available themes on the right. Click on one of these themes and you’ll see a preview on the left of the page. All of the themes available for use on a SharePoint server are stored beneath a single folder named “THEMES“. This folder contains a unique folder for each theme: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\THEMES.
To create a theme, start by creating a new folder with the images and CSS files that make up the theme. Here are the steps to create a new theme called “MOSSTHEME″:
Here is a short procedure of creating a custom site theme named "MossTheme":
1. Copy any theme folder in "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\THEMES" folder and paste with its name replaced with "MossTheme". In this example, copy GRANITE folder.
2. In MossTheme folder, rename GRANITE.INF file to MOSSTHEME.INF in upper case.
3. Open MOSSTHEME.INF file with notepad.
4. Change the value of title under [Info] to MossTheme.
5. Replace every word, Granite, under [titles] with MossTheme.
6. Open "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\1033\SPTHEMES.XML" file with notepad.
7. Add the following lines under tag:
'<' templates '>'
'<'templateid'>'MossTheme '<'/templateid'>'
'<'displayname'>'MossTheme '<'/displayname'>'
'<'description'>'ossTheme '<'/description'>'
'<'thumbnail'>'images/ MossTheme.gif'<'/thumbnail'>'
'<'preview'>'images/ MossTheme.gif'<'/preview'>'
''
Notice that preview and thumbnail paths are images/MossTheme.gif. By default, MOSS 2007 and WSS 3.0 will not have such image files.
8. In order to display thumbnail and preview correctly, you will need to capture the screen and save the file in "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\IMAGES" folder with MossTheme.gif name. You can change the .gif file name if you change the thumbnail and preview file names in tag.
9. Do an iisrest for the server to recognize the new theme.
Pretty simple procedure. Now you are ready to test your new theme. In Site Settings, you can now choose MOSSTHEME; however, the theme will not differ from Granite theme. Now, it is time for you to play with theme.css file!
Posted by Karun at Wednesday, May 07, 2008
Subscribe to:
Posts (Atom)