Wednesday, December 24, 2014

sqldatasource select with argument then bind to dataview


 <asp:SqlDataSource ID="srcPaperTypes" runat="server" ConnectionString="<%$ ConnectionStrings:ODAC_ConnString %>" ProviderName="<%$ ConnectionStrings:ODAC_ConnString.ProviderName %>"
                     SelectCommand="SELECT        DOC_SER, DOC_NAME
FROM            SHR.DOCS_SCAN
WHERE        (SYSNO = 7)
AND DOC_SER=:DOC_SER">
                    <SelectParameters>
                        <asp:Parameter Name="DOC_SER" />
                    </SelectParameters>
                </asp:SqlDataSource>






--------------------------------------------------------------------
 srcPaperTypes.SelectParameters.Clear();
        srcPaperTypes.SelectParameters.Add(new Parameter("DOC_SER",TypeCode.String, papertype.ToString()));
        DataSourceSelectArguments args = new DataSourceSelectArguments();
        DataView DV = new DataView();
        DV = (DataView)srcPaperTypes.Select(DataSourceSelectArguments.Empty);
        if(DV != null)
        result = DV.Table.Rows[0]["DOC_NAME"].ToString();

Friday, December 19, 2014

sql server 2008 add shared network mapped drive to backup select screen UNC SQL SERVER

if you have shared folder or network drive and you need to access it from sql server management studio or add this path to backup plan
use this command

 EXEC xp_cmdshell 'net use y: \\sharedLoc /user:username psw'

ex
 EXEC xp_cmdshell 'net use Z: \\10.5.39.100\Backups /user:sqluser P@$$w0rd'



which Y is the name of network drive will appear in SSMS screen
sharedLoc  is location for shared folder such as \\10.10.10.5\x

username  : permitted user to access shared folder
psw    password needed for user to access the folder



-----------------------------------------
You need to enable it. Check out the Permission section of the xp_cmdshell MSDN docs:
-- To Add Backup Network Shared Location Run the Following Script
-- 1- Change show advanced Options to one
EXEC sp_configure 'show advanced options', 1;
GO
-- 2-Reconfigure
RECONFIGURE;
GO
-- 3-Enable command Shell 
EXEC sp_configure 'xp_cmdshell',1
GO
--4-Reconfigure Again
RECONFIGURE
GO
--**************************************************** if the following commands not working let it run using command prompt net use 
-- To Delete connection To Drive
--EXEC XP_CMDSHELL 'net use */delete   /persistent:yes'


-- set Drive Name and Network Location.
EXEC XP_CMDSHELL 'net use R: \\172.19.8.65\FileServer\Eservices\ServersBackups\SQLServerBackps\ProdDBsBackups /USER:MEDMWA\SQLESER $@l@dmin'