Archive for December, 2013

enable users to download files from server

December 20, 2013

Open up IIS

expand the server node
expand sites
right click default web site
add virtual directory
give it a name and file path

turn on directory browsing
turn on basic authentication

Advertisements

Access to the remote server is denied because no login-mapping exists

December 18, 2013

Access to the remote server is denied because no login-mapping exists

Script the linked server and check the @provstr parameter. It must specify a user ID. Even if the userid and password is specified elsewhere, the user id must also be in @provstr.

Example

Change this: @provstr=N’PROVIDER=SQLOLEDB;SERVER=xxxxxx’

into

This: @provstr=N’PROVIDER=SQLOLEDB;SERVER=xxxxxx;User ID=yyyyyyyy’

no need to specify the password here, that comes in the @rmtpassword parameter which you will have elsewhere in your script

running stored procedures/returning data to excel

December 5, 2013

CREATE STORED PROC WITH PARAMETER
Use test
GO

CREATE PROC dbo.excel

@input varchar(100)
AS

select GETDATE() AS field1

GO
SET UP CONNECTION IN EXCEL

1. open excel
2. data ribbon
3. from other sources
4. from microsoft query
5. new data source
6. name your data source
7. sql server driver
8. connect
9. server name
10. untick ‘use trusted connection’ or leave it and skip next step
11. enter sql server username and password
12. pick any table from the list provided
13. tick save user id and password
14. ok
15. ok
16. select any 1 column for your query
17. next, next, next, finish
18. You should now be on the import data diaglogue box
19. properties
20. definition tab
21. change your command text to exec test.dbo.excel ?
22. ok, ok, enter password,options, select database, ok