landscape

fun with Kites

Focus on light !

How to access network file in SQL

How can I access network XML file from SQL server store procedure?

A procedure which is accessing local file "C:\xyzConfig\xyz.xml" as one of the string parameter.

Now my SQL instance is changed to different server but XML location is same.

I tried "\\SERVER100\c$\xyzConfig\xyz.xml" but It won't work.

Error throw as



"....... could not be opened. Operating system error code 5(Access is denied.)."

----------
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d64c98c7-4fdf-41ba-8413-e606ec668efa


here how I resolve with your help,

1) I have a web application on Server100 with IIS & etc..
2) SQL instance is a cluster on Server400 which doesn’t have IIS & etc..
3) make share folder on Server400, Drive X, then I can access file from SQL as local file. Share with read & write permission for domain users
4) but no IIS on Server400 so, it is not allow me to set Web Sharing folder.
5) So, From web app. i have to set up





6) after all I can access file from both process.

MS SQL Server 2008 new features

Data Compression

SQL Server 2008 gives us the ability to compress data and save on disk space. Without getting into many specifics, data in SQL Server can be compressed at the page level. This means that when you compress a table, it actually does it a page at a time. I'd advise you to tread lightly with data compression. You should make sure you do your research and testing before implementing compression in your environment. That said, when disk space is at a premium, you can use this feature to pack more data onto the platters.

Backup Benefits
SQL Server 2008 provides backup compression. With backup compression, you have the savings of file size built right into the native backup.

To use backup compression, you just need to add the WITH COMPRESSION option to a BACKUP DATABASE statement as shown below:

BACKUP DATABASE Adventureworks2008

TO DISK = 'D:\Backup\AdventureWorks.bak'

WITH COMPRESSION

Here's an example of the compression ratio: a backed up 965MB database without compression resulted in a 636MB backup file. Using compression, the same database produced a 147MB backup file.

Learn to Merge
The new MERGE statement in SQL Server 2008 obviates the need for "IF-THEN" logic to decide whether a row needs to be inserted, updated or deleted. MERGE allows you to take care of the logic and the modification all in one shot. What's more, you can compare an entire record set all at once instead of going row by row. Here's a quick example of using MERGE:

MERGE tbl_address AS current_addresses

USING (

SELECT customer_objid = address_label,

addressline1, addressline2, city, region, country, zip

code, is_deleted

FROM @addresses)

AS

source_addresses(address_label, addressline1,

addressline2, city, region, country, zipcode,

is_deleted)

ON

(

current_addresses.address_label = source_address

es.address_label

)

WHEN NOT MATCHED THEN

INSERT (address_label, addressline1, addressline2,

city, region, country, zipcode)

VALUES (source_addresses.address_label,

source_addresses.addressline1,

source_addresses.addressline2,

source_addresses.city, source_addresses.region,

source_addresses.country, source_addresses.zipcode)

WHEN MATCHED AND source_addresses.is_deleted

= 1

THEN DELETE

WHEN MATCHED THEN

UPDATE

SET address_label=source_addresses.address_label,

addressline1=source_addresses.addressline1,

addressline2=source_addresses.addressline2,

city=source_addresses.city, region=source_address

es.region, country=source_addresses.country,zip

code=source_addresses.zipcode

The USING section defines the "new" data, in this case a table variable. The ON section defines the join between the new and existing data. Finally, you have a series of MATCHED statements that do things like insert WHEN NOT MATCHED, update WHEN MATCHED or delete WHEN MATCHED and some other value indicates delete. This is a real time saver, and the syntax is much cleaner that the older alternative of using homegrown logic

How to export database diagram to other database?

Find system table Dbo.sysdiagrams.
From use insert statement to you database Dbo.sysdiagrams using Identity ON / OFF to column Diagram_id.

Or creating test diagram in database & use update statement and update record from source database.

It will display same diagram as source.

SQL Server Reporting Services 2005 "Error "

ERROR Message
====================================================================
An error has occurred because a control with id 'grdComplaints$ctl04$ctl00' could not be located or a different control is assigned to the same ID after postback. If the ID is not assigned, explicitly set the ID property of controls that raise postback events to avoid this error.

Fiest time report will display good.. but refresh/any post back event.. it throw error...

Try :
1) "EnableViewState" property of GridView was False. When I set it to True. still not works

2) Disable all State porpery to false - Still not work
3) Finally I tried from other browsers I works fine ... I still fining what different setting in my browser than others.

----or---

Change the security setting ...
Unchecked Anonymous user from IIS. Then you won't get any issue... but when you are using Anonymous user. you will get this error.. then follow as before.