microsoft sql server 2000

i wanted to restore user DB from one sql server to another sql server ,
one database is having so many table and have user called digi
he is created so many SP and some tables., when i restore the db it
restored without any problem.,

after restoring application is not working

how to ensure that restored DB is having same security as the original
for that user .

when i tried to run the script of that db over that restored DB

it was showing user digi does not exist. but i have creted user digi

where can i get the useful information about this

/sreeni

--
srin

Posted via http://dbforums.comUytkownik "sreenivasa" <member32738@.dbforums.com> napisa w wiadomoci
news:3084542.1057641115@.dbforums.com...
> i wanted to restore user DB from one sql server to another sql server ,
<cut>
It's probably your answer - another sql so it means another master database.
You have user account in database wich was restored, but you do not have
user login (in master database - sysusers table) wich is connected to
database account.
You must drop database user and create new login - not recomended, or use
sp_change_users_login with Auto_Fix option.
regards
Marcin D

Microsoft Query?

I am using Microsoft Query to query against an SQL database and place the data into a pivot table but cannot figure out how to get the preceding 4 days worth of data. I currently am trying to get this to work using the following;

>=#3/13/2007# And <=#3/13/2007 11:59:59 PM#...this works, but i would like this so the report will run without hard coding dates.. i am trying to use this but with no luck.

>=getdate() And <=dateadd(dd,-1,getdate())

any ideas?

I think you need to reverse your logic to the following:

WHERE @.MyDate <= getdate() AND @.MyDate >= dateadd(dd,-1,getdate())

or equally :

WHERE @.MyDate BETWEEN dateadd(dd,-1,getdate()) AND getdate()

The criteria that you specified are impossible to meet.

Chris

Microsoft .NET Framework: Unhandled Exception

Hi,
I built a very simple report that uses a shared data source to access
an SQL server. I wanted to select all columns from a table on a linked
server. My query is simply "select * from SERVER.DATABASE.USER.table"
and it works fine in Query Analyzer. However, when I enter this query
into the Report Designer (running SP1) and try to run it, I get the
error below.
Thanks in advance for any help you wonderful MSFT guys can give,
especially if there's a workaround!
--Adam Bliss
--TEXT OF ERROR MESSAGE--
Microsoft .NET Framework
An unhandled exception has occured in a component in your appliaction.
Click continue and application [sic] will ignore this error and attempt
to
continue.
Object reference not set to an instance of an object.
Details:
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance
of an object.
at Microsoft.VisualStudio.OLE.Interop.IOleCommandTarget.Exec(Guid&
pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, Object[] pvaIn,
IntPtr pvaOut)
at
Microsoft.ReportDesigner.Design.VDTToolBar.SQLToolBar_ButtonClick(Object
sender, ToolBarButtonClickEventArgs e)
at
System.Windows.Forms.ToolBar.OnButtonClick(ToolBarButtonClickEventArgs
e)
at System.Windows.Forms.ToolBar.WmReflectCommand(Message& m)
at System.Windows.Forms.ToolBar.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32
msg, IntPtr wparam, IntPtr lparam)
************** Loaded Assemblies **************
mscorlib
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/microsoft.net/framework/v1.1.4322/mscorlib.dll
---
Microsoft.ReportingServices.Designer
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Designer.dll
---
Microsoft.VisualStudio.Shell.Interop
Assembly Version: 7.0.3500.0
Win32 Version: 8.0.30310 (lab21.030309-2105)
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.Shell.Interop.DLL
---
Microsoft.VisualStudio.OLE.Interop
Assembly Version: 7.0.3500.0
Win32 Version: 8.0.30310 (lab21.030309-2105)
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.OLE.Interop.DLL
---
Microsoft.VisualStudio
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VisualStudio.DLL
---
Microsoft.DataWarehouse.Interfaces
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.Interfaces.DLL
---
Microsoft.DataWarehouse.VsIntegration
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.VsIntegration.DLL
---
System
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
System.Xml
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.xml/1.0.5000.0__b77a5c561934e089/system.xml.dll
---
System.Web
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.web/1.0.5000.0__b03f5f7f11d50a3a/system.web.dll
---
System.Drawing
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.drawing/1.0.5000.0__b03f5f7f11d50a3a/system.drawing.dll
---
Microsoft.DataWarehouse
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.DLL
---
EnvDTE
Assembly Version: 7.0.3300.0
Win32 Version: 7.00.9466
CodeBase:
file:///c:/windows/assembly/gac/envdte/7.0.3300.0__b03f5f7f11d50a3a/envdte.dll
---
System.Windows.Forms
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.windows.forms/1.0.5000.0__b77a5c561934e089/system.windows.forms.dll
---
Microsoft.VSDesigner
Assembly Version: 7.0.5000.0
Win32 Version: 7.10.3077
CodeBase:
file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VSDesigner.DLL
---
Microsoft.VisualStudio.Designer.Interfaces
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VisualStudio.Designer.Interfaces.DLL
---
RegexAssembly1_0
Assembly Version: 0.0.0.0
Win32 Version: n/a
CodeBase:
---
Microsoft.VisualStudio.TextManager.Interop
Assembly Version: 7.0.3500.0
Win32 Version: 8.0.30310 (lab21.030309-2105)
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.TextManager.Interop.DLL
---
Microsoft.ReportingServices.ReportPreview
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ReportPreview.DLL
---
Microsoft.ReportingServices.Processing
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Processing.DLL
---
Microsoft.ReportingServices.Interfaces
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Interfaces.DLL
---
Microsoft.ReportingServices.Diagnostics
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Diagnostics.DLL
---
qcafn_1e
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
bz_nfjwh
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
yl9hu7fj
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
ilerz4wa
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
5jocxjac
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
jrfgwn5w
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
fxnv6itt
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
zg4sz4ni
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
ny4juciw
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
xlnic1xx
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
atmntgbj
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
imdmrqdn
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
-t4ubrdd
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
ogl20pjz
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
uzelzqkt
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
7gxa3lt5
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
zafsetv2
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
ttnoe-eu
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
zzdw1h6p
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
r9dhpvbw
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
qexy1od1
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
x5cdllnv
Assembly Version: 0.0.0.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
---
System.Design
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.design/1.0.5000.0__b03f5f7f11d50a3a/system.design.dll
---
Accessibility
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/accessibility/1.0.5000.0__b03f5f7f11d50a3a/accessibility.dll
---
Microsoft.SqlServer.WizardFramework
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.SqlServer.WizardFramework.dll
---
Microsoft.ReportingServices.Designer.Wizards
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Designer.Wizards.DLL
---
Microsoft.SqlServer.CustomControls
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.SqlServer.CustomControls.DLL
---
Microsoft.NetEnterpriseServers.ExceptionMessageBox
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.NetEnterpriseServers.ExceptionMessageBox.DLL
---
Microsoft.ReportingServices.DataExtensions
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.DataExtensions.DLL
---
System.Data
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.data/1.0.5000.0__b77a5c561934e089/system.data.dll
---
System.Data.OracleClient
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.data.oracleclient/1.0.5000.0__b77a5c561934e089/system.data.oracleclient.dll
---
System.EnterpriseServices
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/windows/assembly/gac/system.enterpriseservices/1.0.5000.0__b03f5f7f11d50a3a/system.enterpriseservices.dll
---
Interop.Vdt70
Assembly Version: 8.0.242.0
Win32 Version: 8.0.242.0
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Interop.Vdt70.DLL
---
ReportingServicesLibrary
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/ReportingServicesLibrary.DLL
---
Microsoft.ReportingServices.XmlRendering
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.XmlRendering.DLL
---
Microsoft.ReportingServices.CsvRendering
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.CsvRendering.DLL
---
Microsoft.ReportingServices.ImageRendering
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ImageRendering.DLL
---
Microsoft.ReportingServices.HtmlRendering
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.HtmlRendering.DLL
---
Microsoft.ReportingServices.ExcelRendering
Assembly Version: 8.0.242.0
Win32 Version: 8.00.878.00
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ExcelRendering.DLL
---
ReportingServicesNativeClient
Assembly Version: 0.0.0.0
Win32 Version: 2000.080.0878.000
CodeBase:
file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/ReportingServicesNativeClient.DLL
---That looks like bug.
As workaround I suggest to use Generic Query Designer
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Bliss" <abliss@.gmail.com> wrote in message
news:cdjf9k$vk4@.odak26.prod.google.com...
> Hi,
> I built a very simple report that uses a shared data source to access
> an SQL server. I wanted to select all columns from a table on a linked
> server. My query is simply "select * from SERVER.DATABASE.USER.table"
> and it works fine in Query Analyzer. However, when I enter this query
> into the Report Designer (running SP1) and try to run it, I get the
> error below.
> Thanks in advance for any help you wonderful MSFT guys can give,
> especially if there's a workaround!
> --Adam Bliss
> --TEXT OF ERROR MESSAGE--
> Microsoft .NET Framework
> An unhandled exception has occured in a component in your appliaction.
> Click continue and application [sic] will ignore this error and attempt
> to
> continue.
> Object reference not set to an instance of an object.
> Details:
> See the end of this message for details on invoking
> just-in-time (JIT) debugging instead of this dialog box.
> ************** Exception Text **************
> System.NullReferenceException: Object reference not set to an instance
> of an object.
> at Microsoft.VisualStudio.OLE.Interop.IOleCommandTarget.Exec(Guid&
> pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, Object[] pvaIn,
> IntPtr pvaOut)
> at
> Microsoft.ReportDesigner.Design.VDTToolBar.SQLToolBar_ButtonClick(Object
> sender, ToolBarButtonClickEventArgs e)
> at
> System.Windows.Forms.ToolBar.OnButtonClick(ToolBarButtonClickEventArgs
> e)
> at System.Windows.Forms.ToolBar.WmReflectCommand(Message& m)
> at System.Windows.Forms.ToolBar.WndProc(Message& m)
> at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
> at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
> at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32
> msg, IntPtr wparam, IntPtr lparam)
>
> ************** Loaded Assemblies **************
> mscorlib
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/microsoft.net/framework/v1.1.4322/mscorlib.dll
> ---
> Microsoft.ReportingServices.Designer
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Designer.dll
> ---
> Microsoft.VisualStudio.Shell.Interop
> Assembly Version: 7.0.3500.0
> Win32 Version: 8.0.30310 (lab21.030309-2105)
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.Shell.Interop.DLL
> ---
> Microsoft.VisualStudio.OLE.Interop
> Assembly Version: 7.0.3500.0
> Win32 Version: 8.0.30310 (lab21.030309-2105)
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.OLE.Interop.DLL
> ---
> Microsoft.VisualStudio
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VisualStudio.DLL
> ---
> Microsoft.DataWarehouse.Interfaces
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.Interfaces.DLL
> ---
> Microsoft.DataWarehouse.VsIntegration
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.VsIntegration.DLL
> ---
> System
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> System.Xml
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.xml/1.0.5000.0__b77a5c561934e089/system.xml.dll
> ---
> System.Web
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.web/1.0.5000.0__b03f5f7f11d50a3a/system.web.dll
> ---
> System.Drawing
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.drawing/1.0.5000.0__b03f5f7f11d50a3a/system.drawing.dll
> ---
> Microsoft.DataWarehouse
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.DataWarehouse.DLL
> ---
> EnvDTE
> Assembly Version: 7.0.3300.0
> Win32 Version: 7.00.9466
> CodeBase:
> file:///c:/windows/assembly/gac/envdte/7.0.3300.0__b03f5f7f11d50a3a/envdte.dll
> ---
> System.Windows.Forms
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.windows.forms/1.0.5000.0__b77a5c561934e089/system.windows.forms.dll
> ---
> Microsoft.VSDesigner
> Assembly Version: 7.0.5000.0
> Win32 Version: 7.10.3077
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VSDesigner.DLL
> ---
> Microsoft.VisualStudio.Designer.Interfaces
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20Visual%20Studio%20.NET%202003/Common7/IDE/Microsoft.VisualStudio.Designer.Interfaces.DLL
> ---
> RegexAssembly1_0
> Assembly Version: 0.0.0.0
> Win32 Version: n/a
> CodeBase:
> ---
> Microsoft.VisualStudio.TextManager.Interop
> Assembly Version: 7.0.3500.0
> Win32 Version: 8.0.30310 (lab21.030309-2105)
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.VisualStudio.TextManager.Interop.DLL
> ---
> Microsoft.ReportingServices.ReportPreview
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ReportPreview.DLL
> ---
> Microsoft.ReportingServices.Processing
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Processing.DLL
> ---
> Microsoft.ReportingServices.Interfaces
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Interfaces.DLL
> ---
> Microsoft.ReportingServices.Diagnostics
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Diagnostics.DLL
> ---
> qcafn_1e
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> bz_nfjwh
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> yl9hu7fj
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> ilerz4wa
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> 5jocxjac
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> jrfgwn5w
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> fxnv6itt
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> zg4sz4ni
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> ny4juciw
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> xlnic1xx
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> atmntgbj
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> imdmrqdn
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> -t4ubrdd
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> ogl20pjz
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> uzelzqkt
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> 7gxa3lt5
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> zafsetv2
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> ttnoe-eu
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> zzdw1h6p
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> r9dhpvbw
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> qexy1od1
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> x5cdllnv
> Assembly Version: 0.0.0.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
> ---
> System.Design
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.design/1.0.5000.0__b03f5f7f11d50a3a/system.design.dll
> ---
> Accessibility
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/accessibility/1.0.5000.0__b03f5f7f11d50a3a/accessibility.dll
> ---
> Microsoft.SqlServer.WizardFramework
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.SqlServer.WizardFramework.dll
> ---
> Microsoft.ReportingServices.Designer.Wizards
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.Designer.Wizards.DLL
> ---
> Microsoft.SqlServer.CustomControls
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.SqlServer.CustomControls.DLL
> ---
> Microsoft.NetEnterpriseServers.ExceptionMessageBox
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.NetEnterpriseServers.ExceptionMessageBox.DLL
> ---
> Microsoft.ReportingServices.DataExtensions
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.DataExtensions.DLL
> ---
> System.Data
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.data/1.0.5000.0__b77a5c561934e089/system.data.dll
> ---
> System.Data.OracleClient
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.data.oracleclient/1.0.5000.0__b77a5c561934e089/system.data.oracleclient.dll
> ---
> System.EnterpriseServices
> Assembly Version: 1.0.5000.0
> Win32 Version: 1.1.4322.573
> CodeBase:
> file:///c:/windows/assembly/gac/system.enterpriseservices/1.0.5000.0__b03f5f7f11d50a3a/system.enterpriseservices.dll
> ---
> Interop.Vdt70
> Assembly Version: 8.0.242.0
> Win32 Version: 8.0.242.0
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Interop.Vdt70.DLL
> ---
> ReportingServicesLibrary
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/ReportingServicesLibrary.DLL
> ---
> Microsoft.ReportingServices.XmlRendering
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.XmlRendering.DLL
> ---
> Microsoft.ReportingServices.CsvRendering
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.CsvRendering.DLL
> ---
> Microsoft.ReportingServices.ImageRendering
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ImageRendering.DLL
> ---
> Microsoft.ReportingServices.HtmlRendering
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.HtmlRendering.DLL
> ---
> Microsoft.ReportingServices.ExcelRendering
> Assembly Version: 8.0.242.0
> Win32 Version: 8.00.878.00
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/Microsoft.ReportingServices.ExcelRendering.DLL
> ---
> ReportingServicesNativeClient
> Assembly Version: 0.0.0.0
> Win32 Version: 2000.080.0878.000
> CodeBase:
> file:///C:/Program%20Files/Microsoft%20SQL%20Server/80/Tools/Report%20Designer/ReportingServicesNativeClient.DLL
> ---
>

Mgmt Studio: Script Table as...

Hi,

I'm using SQL Server 2005 Express SP1 & Mgmt Studio express.

I'm trying to use the script output to track my db changes in source code mgmt, but when I output a table to a script the first two bytes of the "text" file are 0xFF and 0xFE which sends my SCM into storingthe file in binary defeating the purpose of storing text files I can "diff" to see the changes.

Does anyone know why the mgmt studio is doing this and is there a way to stop it?

thanks!

Management Studio creates UNICODE files so characters from all alphabets can be represented in the same file. Your SCM system is probably interpretting the UNICODE signature at the top of the file as being "binary."

If you are scripting to a Query Editor Window, you can save the script as an ASCII file by clicking File | Save As... and clicking the little pull-down arrow on the right side of the save button to select the file type. US-ASCII is toward the bottom of the list.

Hope this helps,
Steve

|||

Thanks! This was helpful and it explains the situation.

I'm working around it by the copy & paste method you mention while I deal with the SCM issue.

It's interesting that SQL Server Mgmt Studio uses unicode BOM and Visual Studio uses utf-8 for it's configuration files, so the SCM has to deal with the many "standards" of unicode...

life was not mean't to be easy...

methods of storing the Connection String in package configuration

hi!

I want to store the value of the Connection String in an Common table exisiting for the whole project and retrieve it for the whole package to Run. I want to know if there is a way of doing this.

I do not want to have a config file which is created trhough the package configuration.

In case of Parent package variable the Value of the variable needs to exist which again needs to execute the SQL Task which requires a connection string.

In case of Environment variabIe.I have very less knowledge about the environment variable . Even if we store it? how can we access it and change it in client place.

In case of SQL server . Can we store in an existing table. what is the configuration filter? what is the vlaue for it.

I do not want to execute any command line.............................

Thanks,

jazz

Well, I have not done this but I think you could simply have a parent package variable that uses a SQL Task to load the connection string for the child packages (obviously you need an initial connection string to load the config in the first place so I am assuming you are looking for a connection string to a second database.).

Once you do that you can use a script task in each of the child packages to explicitly set the Dts.Connections("ConnectionName").ConnectionString property manually based on the parent variable. You can use parent package configurations to map a parent variable to the child package so the designer can see the variable used.

Hope this helps...

|||

Can i execute a stored preocedure to get the connection string from the database while installing the pacakge which can set the value of the parent package variables.

Where i can use the Parent Package Varaible in Package Configuration to be accessed by the Child Packages.

is this ok......

Thanks

Methods of #temp table creation.

Hello,
What is the difference between the two?
(a) Explicitly create a temp table using "create table #tableName...".
After it has been created, populate it using an explicit "insert into
#tableName".
Table creation and population of records take place in 2 t-sql statements.
(b) Let the #tableName get created on the fly when using a "select top 10
cid into #tableName from storesLink".
Table creation and population of records take place in a single t-sql
statement.
Both of them achieve the same result.
But, what is the difference in performance?
Are there any other points that I should keep in mind when adopting any of
the above two approaches ?
Cheers!
SQLCatzThe second one causes locking on some of the system tables and should
be avoided. Use the first one or better yet, use the table variable.
Aramid
On Wed, 6 Apr 2005 23:29:03 -0700, "SQLCatz"
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello,
>What is the difference between the two?
>(a) Explicitly create a temp table using "create table #tableName...".
>After it has been created, populate it using an explicit "insert into
>#tableName".
>Table creation and population of records take place in 2 t-sql statements.
>(b) Let the #tableName get created on the fly when using a "select top 10
>cid into #tableName from storesLink".
>Table creation and population of records take place in a single t-sql
>statement.
>Both of them achieve the same result.
>But, what is the difference in performance?
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
>Cheers!
>SQLCatz
>|||they are about the same in performance if you don't have recompile. For
recompile info, see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q243586
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:9FBD6D28-361B-460A-B47B-7CBD7DE86430@.microsoft.com...
> Hello,
> What is the difference between the two?
> (a) Explicitly create a temp table using "create table #tableName...".
> After it has been created, populate it using an explicit "insert into
> #tableName".
> Table creation and population of records take place in 2 t-sql statements.
> (b) Let the #tableName get created on the fly when using a "select top 10
> cid into #tableName from storesLink".
> Table creation and population of records take place in a single t-sql
> statement.
> Both of them achieve the same result.
> But, what is the difference in performance?
> Are there any other points that I should keep in mind when adopting any of
> the above two approaches ?
> Cheers!
> SQLCatz
>|||On Wed, 6 Apr 2005 23:29:03 -0700, SQLCatz wrote:
(snip)
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
Hi SQLCatz,
If you use CREATE TABLE, you can add constraints and indexes right away,
or you can chooose to add them after the INSERT. If you use SELECT INTO,
you can only add the constraints and indexes later.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Methods of #temp table creation.

Hello,
What is the difference between the two?
(a) Explicitly create a temp table using "create table #tableName...".
After it has been created, populate it using an explicit "insert into
#tableName".
Table creation and population of records take place in 2 t-sql statements.
(b) Let the #tableName get created on the fly when using a "select top 10
cid into #tableName from storesLink".
Table creation and population of records take place in a single t-sql
statement.
Both of them achieve the same result.
But, what is the difference in performance?
Are there any other points that I should keep in mind when adopting any of
the above two approaches ?
Cheers!
SQLCatz
The second one causes locking on some of the system tables and should
be avoided. Use the first one or better yet, use the table variable.
Aramid
On Wed, 6 Apr 2005 23:29:03 -0700, "SQLCatz"
<SQLCatz@.discussions.microsoft.com> wrote:

>Hello,
>What is the difference between the two?
>(a) Explicitly create a temp table using "create table #tableName...".
>After it has been created, populate it using an explicit "insert into
>#tableName".
>Table creation and population of records take place in 2 t-sql statements.
>(b) Let the #tableName get created on the fly when using a "select top 10
>cid into #tableName from storesLink".
>Table creation and population of records take place in a single t-sql
>statement.
>Both of them achieve the same result.
>But, what is the difference in performance?
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
>Cheers!
>SQLCatz
>
|||they are about the same in performance if you don't have recompile. For
recompile info, see:
http://support.microsoft.com/default...;en-us;q243586
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:9FBD6D28-361B-460A-B47B-7CBD7DE86430@.microsoft.com...
> Hello,
> What is the difference between the two?
> (a) Explicitly create a temp table using "create table #tableName...".
> After it has been created, populate it using an explicit "insert into
> #tableName".
> Table creation and population of records take place in 2 t-sql statements.
> (b) Let the #tableName get created on the fly when using a "select top 10
> cid into #tableName from storesLink".
> Table creation and population of records take place in a single t-sql
> statement.
> Both of them achieve the same result.
> But, what is the difference in performance?
> Are there any other points that I should keep in mind when adopting any of
> the above two approaches ?
> Cheers!
> SQLCatz
>
|||On Wed, 6 Apr 2005 23:29:03 -0700, SQLCatz wrote:
(snip)
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
Hi SQLCatz,
If you use CREATE TABLE, you can add constraints and indexes right away,
or you can chooose to add them after the INSERT. If you use SELECT INTO,
you can only add the constraints and indexes later.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Methods of #temp table creation.

Hello,
What is the difference between the two?
(a) Explicitly create a temp table using "create table #tableName...".
After it has been created, populate it using an explicit "insert into
#tableName".
Table creation and population of records take place in 2 t-sql statements.
(b) Let the #tableName get created on the fly when using a "select top 10
cid into #tableName from storesLink".
Table creation and population of records take place in a single t-sql
statement.
Both of them achieve the same result.
But, what is the difference in performance?
Are there any other points that I should keep in mind when adopting any of
the above two approaches ?
Cheers!
SQLCatzThe second one causes locking on some of the system tables and should
be avoided. Use the first one or better yet, use the table variable.
Aramid
On Wed, 6 Apr 2005 23:29:03 -0700, "SQLCatz"
<SQLCatz@.discussions.microsoft.com> wrote:

>Hello,
>What is the difference between the two?
>(a) Explicitly create a temp table using "create table #tableName...".
>After it has been created, populate it using an explicit "insert into
>#tableName".
>Table creation and population of records take place in 2 t-sql statements.
>(b) Let the #tableName get created on the fly when using a "select top 10
>cid into #tableName from storesLink".
>Table creation and population of records take place in a single t-sql
>statement.
>Both of them achieve the same result.
>But, what is the difference in performance?
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
>Cheers!
>SQLCatz
>|||they are about the same in performance if you don't have recompile. For
recompile info, see:
http://support.microsoft.com/defaul...b;en-us;q243586
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:9FBD6D28-361B-460A-B47B-7CBD7DE86430@.microsoft.com...
> Hello,
> What is the difference between the two?
> (a) Explicitly create a temp table using "create table #tableName...".
> After it has been created, populate it using an explicit "insert into
> #tableName".
> Table creation and population of records take place in 2 t-sql statements.
> (b) Let the #tableName get created on the fly when using a "select top 10
> cid into #tableName from storesLink".
> Table creation and population of records take place in a single t-sql
> statement.
> Both of them achieve the same result.
> But, what is the difference in performance?
> Are there any other points that I should keep in mind when adopting any of
> the above two approaches ?
> Cheers!
> SQLCatz
>|||On Wed, 6 Apr 2005 23:29:03 -0700, SQLCatz wrote:
(snip)
>Are there any other points that I should keep in mind when adopting any of
>the above two approaches ?
Hi SQLCatz,
If you use CREATE TABLE, you can add constraints and indexes right away,
or you can chooose to add them after the INSERT. If you use SELECT INTO,
you can only add the constraints and indexes later.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Method to insert all record from Access table to SQL server one

Anyone know if there is method that can insert all record from a table
in an MS Access 2000 database to a table in MS SQL Server 2000
database by a SQL statement? (Therefore, I can execute the statement
in my program)

--
Posted via http://dbforums.comTry OPENROWSET. For example:

INSERT INTO MyTable
FROM OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0',
'c:\MyDatabases\\MyDatabase.mdb';
'admin';
'',
'SELECT * FROM MyTable'
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"king" <member29622@.dbforums.com> wrote in message
news:3341972.1063004486@.dbforums.com...
> Anyone know if there is method that can insert all record from a table
> in an MS Access 2000 database to a table in MS SQL Server 2000
> database by a SQL statement? (Therefore, I can execute the statement
> in my program)
>
> --
> Posted via http://dbforums.com|||Thanks for Dan Guzman!

I have tried your method, but I get returning error like:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
denied. You must access this provider through a linked server."

What can I do now?

--
Posted via http://dbforums.com|||Check out MSKB 327489:

http://support.microsoft.com/defaul...kb;en-us;327489

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"king" <member29622@.dbforums.com> wrote in message
news:3345907.1063072590@.dbforums.com...
> Thanks for Dan Guzman!
>
> I have tried your method, but I get returning error like:
> "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
> denied. You must access this provider through a linked server."
>
> What can I do now?
>
> --
> Posted via http://dbforums.com

Metadata on data flow path not updating

I have a data flow task with a single source and destination task. I'm having the source task creating a table from a variable expression and the destination table also created from a variable expression. I'm running this under 3 scenarios in which each scenario has a different source and destination table. They are different in name but close in table structure with the exception of one column being different. The Metadata for the source flow path seems to be "sticky" in that it is not modifying the source table structure in the flow to account for this different column. I'm not sure how to adjust this. Any ideas? I've modified several properties in the task and data flow but nothing seems to make this adjustment in run-time.Metadata cannot and does not change dynamically nor at runtime.

This is by design and there's very little you can do to get around that, short of coding your own application using the SSIS API.

Metadata in sql server 2005

Hi
I add extended properties to table in sql server 2005, for example i add Caption property to every column.
In case I generate a datasource from table in sql server in visual studio 2005, Is there a way that "Caption" property in datatable to be filled from that metadata, or metadata is useless for dataset.
Every time i had to fill manually the caption property of datatable generated .

hi,

you can access those extended properties calling fn_listextendedproperty function, similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.Invoice ( ID INT NOT NULL PRIMARY KEY , BillNo INT NOT NULL -- , others ) GO EXEC sp_addextendedproperty '1st property' , '1St property value' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL; EXEC sp_addextendedproperty '2nd property' , '2nd property value' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL; GO PRINT 'All available properties'; SELECT CONVERT(VARCHAR(20), [name]) AS [Property Name] , CONVERT(VARCHAR(20), [value]) AS [Property vCharValue] FROM ::fn_listextendedproperty( NULL , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL ); GO PRINT 'just the required [1st property] property'; SELECT CONVERT(VARCHAR(20), [name]) AS [Property Name] , convert(VARCHAR(20),[value]) AS [Property vCharValue] FROM ::fn_listextendedproperty( '1st property' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL ); GO DROP TABLE dbo.Invoice; --< All available properties Property Name Property vCharValue -- -- 1st property 1St property value 2nd property 2nd property value just the required [1st property] property Property Name Property vCharValue -- -- 1st property 1St property value

so, assuming you are interested in the Invoice management, you have to query for all the properties of the dbo.Invoice table columns...

this can obviously fill a dataset or a datareader you can then consume to map as desired the returned values so you could assume the "1st property" is the caption of the corresponding UI control and you can set it accordingly..

BTW, to manage those properties outside the Microsoft tools you have to resort on http://msdn2.microsoft.com/en-us/library/aa174648(SQL.80).aspx and http://msdn2.microsoft.com/en-us/library/ms190243.aspx

regards|||Thank you for your reply.
I mean , what is the extended properties that may be used automatically by visual studio 2005.
For example , Can the "Caption " property of datatable created in vs 2005 , be set automatically using extended properties?
This property is useful, because form wizard creates label with that "caption" value(when i set it at design time), and textbox bound to column.

|||

hi,

no, you have to deal with that "by hand"...

regards

metadata driven SSIS package--table variable

Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!

Oliver

Hmm,

I suppose you could do it that way. I am currently generating complete SSIS packages from metadata. No variables. Explicit references. You would still need to handle the objects of the package that change, like columns datatypes, etc...|||You cannot have 1 data-flow that is used for different tables (unless the structure of each table is exactly the same - which I doubt).

You will need multiple data-flows, one for each table.

Once you have your multiple data-flows, decide which one of them to execute using workflow expressions. Allan and Darren have a good example of this here: http://www.sqlis.com/default.aspx?306

-Jamie|||Patrick,

Do you think you could post a simple example of what you are doing? I think there are a lot of people in the SSIS community who are realizing they need to head toward dynamic package creation in order to achieve the maintainability that they were able to with DTS and ActiveX scripts.

Thanks,

Jason.|||Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver|||

Oliver77 wrote:

Basicall, we have 150 tables for ETL process. The transformation of each table is more or less the same. We would like to creat a SSIS package for one table,and all the other tables to be constructed based on the table name retrieved from a metadata table that contains all the table names. Thanks!

Oliver

"More or less the same" isn't enough. They have to be exactly the same for them to be able to use the same data-flow.

-Jamie|||

Can we create a SSIS package using VB .NET? If we can, we can call metadata driven stored procedure to build the transformation task specifically for each table.

|||

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

|||

Oliver77 wrote:

Could you give more detail as how you generate the metadata driven SSIS packages? THanks

Oliver

One of the samples provided with the product shows how to build a package progamatically (although I've just looked at my Junne CTP installation and can't find it - perhaps its been removed?). Once you have mastered how to do that you could work on building them based on metadata. I haven't seen any examples of doing that - perhaps you'd be the first person to do it?

-Jamie|||Patrick,

DO you have sample code? Thanks!|||Patrick,

Can you give some rough ideas as what you are using in terms of programming languages, scripts or SSIS tasks to create metadata driven SSIS packages.

Many thanks!

Oliver

MetaData and FKCOLUMN_NAME

I've got a SQL Server 2000 database with a table that has two Foreign Keys.
They are declared as such in the database.
When I use the latest JDBC driver (sp3) to get the MetaData info about these
Foreign Keys, I'm runing into what appears to be a bug. Here's a snippet
code:
ResultSet rsForeignKeys = dbmd.getImportedKeys( null, null, "Recipes" );
while( rsForeignKeys.next() ) {
log.debug( "fktablename: " + rsForeignKeys.getString("FKTABLE_NAME") );
log.debug( "fkcolumname: " + rsForeignKeys.getString("FKCOLUMN_NAME") );
log.debug( "pktablename: " + rsForeignKeys.getString("PKTABLE_NAME") );
log.debug( "pkcolumnename: " + rsForeignKeys.getString("PKCOLUMN_NAME") );
}
This yields the following (with Log4J prefix info ommitted):
fktablename: Recipes
fkcolumname: MenuSectionID
pktablename: MenuSections
pkcolumnename: MenuSectionID
fktablename: Recipes
fkcolumname: MenuSectionID***
pktablename: RecipeType
pkcolumnename: RecipeTypeID
Notice the asterisked item above (asterisks are my own, not code generated).
The MenuSectionID is the fkcolumn name from the first FK entry but it is
show for both fk entries returned by the DBMD object. This is not correct,
the name of the second FK column for this table is "RecipeID". I have
double-checked my relationships in the database and all seems well there.
Any idea what might be going on here?
My thanks,
- Gary
Oh duh.
I just triple-checked by db-definition and found the problem. I had a typo
in the fk definition. The DBMD object was giving me exactly the fk's exactly
as I had (incorrectly) defined them.
Sorry about that.
- Gary
"gaffonso" wrote:

> I've got a SQL Server 2000 database with a table that has two Foreign Keys.
> They are declared as such in the database.
> When I use the latest JDBC driver (sp3) to get the MetaData info about these
> Foreign Keys, I'm runing into what appears to be a bug. Here's a snippet
> code:
> ResultSet rsForeignKeys = dbmd.getImportedKeys( null, null, "Recipes" );
> while( rsForeignKeys.next() ) {
> log.debug( "fktablename: " + rsForeignKeys.getString("FKTABLE_NAME") );
> log.debug( "fkcolumname: " + rsForeignKeys.getString("FKCOLUMN_NAME") );
> log.debug( "pktablename: " + rsForeignKeys.getString("PKTABLE_NAME") );
> log.debug( "pkcolumnename: " + rsForeignKeys.getString("PKCOLUMN_NAME") );
> }
> This yields the following (with Log4J prefix info ommitted):
> fktablename: Recipes
> fkcolumname: MenuSectionID
> pktablename: MenuSections
> pkcolumnename: MenuSectionID
> fktablename: Recipes
> fkcolumname: MenuSectionID***
> pktablename: RecipeType
> pkcolumnename: RecipeTypeID
> Notice the asterisked item above (asterisks are my own, not code generated).
> The MenuSectionID is the fkcolumn name from the first FK entry but it is
> show for both fk entries returned by the DBMD object. This is not correct,
> the name of the second FK column for this table is "RecipeID". I have
> double-checked my relationships in the database and all seems well there.
> Any idea what might be going on here?
> My thanks,
> - Gary

Metadata

Hi!

I need to pull out some metada from database. From every table I want to pull out relations so PK and FK. But I want to have it in form that I will exactly now that FooId is PK in Foo table nad FooBarId is foreign key from FooBar table and so on. And I need it for specified tables.

It can be done by one or more queries for every table so it doesn't have to be one big select. I just need to know how to get this data ;)

TIA

Jarod

you can use SSMS to generate script for all your objects in your database.

Steps:

(1) go to object explorer

(2) select the databse

(3) right click and choose tasks and then choose the subtask 'Generate Scripts....

(4) This wizard will walk you thru the script generation

Meta Database Design Select Question.

Hi,

I have a Datbase with teh following two tables.

Table 1: default_en_listingsdb
it contains the following fields
ID user_ID Title

Table2:
default_en_listingsdbelements
ID field_name field_value listing_id

listing_ID in table 2 is equal to the ID in table 1.

I am building a search for these two tables where i want to return the ID, user_ID and Title. The table data looks like this.

Table 1:
ID user_ID Title
1 1 Test
2 2 Test2

Table2:
ID field_name field_value listing_id
1 beds 2 2
2 beds 1 1
3 city kingsley 1
4 city kingsley 2

I want to return only the ID's where they contain both beds = 2 and city = kingsley.. I want to do this with on e select statement if possible.

Thanks,I hate this, what Tom Kyte calls the "funky data model". Flexible as you like, but so hard to query (and this is a very simple query!)

Also, data integrity is non-existent, because there can be no database constraint that stops you putting 2 for city or kingsley for beds...

Anyway... </RANT>

select t1.*
from table1 t1, table2 beds, table2 city
where t1.id = beds.listing_id
and beds.field_name = 'beds'
and beds.field_value = '2'
and city.field_name = 'city'
and city.field_value = 'kingsley';

Yuk!|||Thanks, I knew it was going to be ugly, i agree that this data model in some ways really sucks... ohh well off to make this work.. Thanks again|||Originally posted by greengaint
Thanks, I knew it was going to be ugly, i agree that this data model in some ways really sucks... ohh well off to make this work.. Thanks again
Everyone "invents" this data model at some point early in their database careers; well, I'm sure I did once anyway. "Hey, look! With this model we don't have to alter tables or application code ever again! The users can just define their own 'fields' whenever they like!" Then later you find that:
(a) performance sucks very badly, and
(b) you need to write SQL 17 pages long to produce the simplest report, and
(c) the data is full of nonsense like beds='4.2' and city = ' kignsley' and startdate = '31 Feb 2004'... and
(d) nobody likes this system any more

Merging, and loading dynamic flat file.

I have number of csv files in a folder, all of them with same columns, need to be merged into one table and imported to sql server.

-The first row of the csv file is a header.
-The csv files are updated everyday
-The destination table is replace by new table with new info in the csv.
-The new csv files can be created and old csv files may no longer exist, but we are only interested in information contain in current csv files in the folder.
-I need SSIS to combine all the csv files in the folder and merge into one table.
-Other issue is that the field names may change in csv, so can the SSIS package recognize the change in field name and made necessary change in destination table as well.

Any insight on this issues will be greatly appreciated?

Put simply this is not a scenario that SSIS supports. The key problem is the dynamic nature of the file structure, which SSIS has no run-time support for. The target scenarios for SSIS revolve around unattended/automated data transfer, and any kind of dynamic format is just never going to work well with SSIS.

What use would a new table be that the system has never seen before? For you, you obviously then interpret this, but unless you tell SSIS to do that up front, it would never make sense for it.

Merging two tables

SQL 7
How do I merge two table's records? In other words, merge
T1 into T2. There are records in both tables that are the
same, but where T1 has a record that T2 doesn't have,
T1's record needs to be inserted into T2.
I've been reading Join syntax til my head is spinning.
Thanks,
DonInsert into T2
select * from T1 where T1.PrimaryKey
where T1.PrimaryKey NOT IN (Select PrimaryKey from T2)
Replace PrimaryKey with the unique priamry key from each table.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:7ff101c402df$8f68d030$a101280a@.phx.gbl...
> SQL 7
> How do I merge two table's records? In other words, merge
> T1 into T2. There are records in both tables that are the
> same, but where T1 has a record that T2 doesn't have,
> T1's record needs to be inserted into T2.
> I've been reading Join syntax til my head is spinning.
> Thanks,
> Don
>|||Don,

> How do I merge two table's records? In other words, merge
> T1 into T2. There are records in both tables that are the
> same, but where T1 has a record that T2 doesn't have,
> T1's record needs to be inserted into T2.
insert T2
select * from T1
where not exists (select * from T2 where T1.keycol = T2.keycol)
Linda

Merging two rows

Hi All,
I have question about how to do select on below table.
This table has 3column,
Col1->Account Id[Values 1, 2, 3, 4]
Col2>Code [Values 1,2,3]
Col3>Amount[Values 100,200,300,400]
Each code maps to debit, credit, balance...
I want to do a select which can return me debit, credit and balance for
all the Account in the table.
Something like
select debit, credit, balance from Tab1.
But the diffculty is each debit, credit and balance are in a different
row. How can I merge many rows into a single select statement.
Please advise me how I can do this
Thank YouTry,
select
accountid,
sum(case when code = 1 then amount else 0 end) as debit,
sum(case when code = 2 then amount else 0 end) as credit,
sum(case when code = 3 then amount else 0 end) as balance
from
group by accountid
AMB
"dhani" wrote:

> Hi All,
> I have question about how to do select on below table.
> This table has 3column,
> Col1->Account Id[Values 1, 2, 3, 4]
> Col2>Code [Values 1,2,3]
> Col3>Amount[Values 100,200,300,400]
> Each code maps to debit, credit, balance...
> I want to do a select which can return me debit, credit and balance for
> all the Account in the table.
> Something like
> select debit, credit, balance from Tab1.
> But the diffculty is each debit, credit and balance are in a different
> row. How can I merge many rows into a single select statement.
> Please advise me how I can do this
> Thank You
>|||dhani wrote:
> Hi All,
> I have question about how to do select on below table.
> This table has 3column,
> Col1->Account Id[Values 1, 2, 3, 4]
> Col2>Code [Values 1,2,3]
> Col3>Amount[Values 100,200,300,400]
> Each code maps to debit, credit, balance...
> I want to do a select which can return me debit, credit and balance
> for all the Account in the table.
> Something like
> select debit, credit, balance from Tab1.
> But the diffculty is each debit, credit and balance are in a different
> row. How can I merge many rows into a single select statement.
> Please advise me how I can do this
> Thank You
Your requirements are not clear.
Please read and comply with www.aspfaq.com/5006
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||The post by the user provides some information that should be able to help
him out.
Allthough I encourage the use of some 'standard' posting and
recommendations, just replying that the users requirements are not clear
and he should comply with etiquette that is not even requied by the
newsgroup doesn't make sense.
What the user wants is to pivot the table in this case.
Personally I would go for a different table design.
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:%234faQbWuFHA.3224@.TK2MSFTNGP10.phx.gbl...
> dhani wrote:
> Your requirements are not clear.
> Please read and comply with www.aspfaq.com/5006
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||> Allthough I encourage the use of some 'standard' posting and
> recommendations, just replying that the users requirements are not clear
> and he should comply with etiquette that is not even requied by the
> newsgroup doesn't make sense.
Sure it does (and "make sense" is very subjective, isn't it? Just because
SELECT * and a 9-page stored procedure makes sense to you, doesn't mean we
all share that opinion). The requirements were not clear, so Bob asked for
further clarification, and showed the user a quite helpful link in providing
requirements in a generally accepted format here.
Nobody said it was required by the newsgroup (what is required by the
newsgroup anyway, I have no idea what you're talking about). But you'll see
this follow-up a lot, and I can only assume from you lack of visibility here
that it's just a fact of life in here that you haven't yet been exposed to
enough to understand.
21 letters. Wow.|||So you are judging my knowlegde?
I don't think we ever met and I would definetely not judge you, knowing that
you are a respected MVP.
Did you use a user defined function to count the letters in my
certification?
So why do you conclude I have a lack of visibility and what fact of life
have I not been exposed to?
Don't you think it would be better to just share knowledge in order to
provide solutions and results?
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewD9UjXuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Sure it does (and "make sense" is very subjective, isn't it? Just because
> SELECT * and a 9-page stored procedure makes sense to you, doesn't mean we
> all share that opinion). The requirements were not clear, so Bob asked
> for further clarification, and showed the user a quite helpful link in
> providing requirements in a generally accepted format here.
> Nobody said it was required by the newsgroup (what is required by the
> newsgroup anyway, I have no idea what you're talking about). But you'll
> see this follow-up a lot, and I can only assume from you lack of
> visibility here that it's just a fact of life in here that you haven't yet
> been exposed to enough to understand.
> 21 letters. Wow.
>|||> Don't you think it would be better to just share knowledge in order to
> provide solutions and results?
Yes, and part of that knowledge is learning how to post requirements that
work well, are easily re-create-able and test-able, and are requested.
You judged Bob's post and said it didn't make sense. I disagree. Period.|||Dandy Weyn [Dandyman] wrote:
> The post by the user provides some information that should be able to
> help him out.
I'm not trying to be combative or insulting, but, if the post contained
sufficient information, then why didn't you provide a solution?
Look, Alejandro made an attempt based on his guess as to what the user
needed. It may even be the correct solution. The question is: Who knows? I
could have made a similar attempt, only to have the OP come back and say:
"no that's not what I wanted at all", in which case both my time and the
OP's were wasted.

> Allthough I encourage the use of some 'standard' posting and
> recommendations, just replying that the users requirements are not
> clear and he should comply
Well, maybe "comply" was a poor choice of words: I meant "follow the
recommendations made" but I was a little strapped for time and was trying to
be concise.

> with etiquette that is not even requied by
I think if you use google, you will fiind that a large percentage of posts
to this group, as well as the comp.databases.ms-sqlserver group, receive a
similar initial request to provide DDL and sample data. This is usenet,
there is no way to "require" etiquette. That does not mean we should not
make an effort to help posters get help as efficiently as possible by
showing them the best way to provide the information needed to solve their
problems. Aaron's article does a good job of that. He put it in his
"ettiquette" section, but that does not mean this has anything to do with
"ettiquette".

> the newsgroup doesn't make sense.
Why not? What else should I do when the post contains insufficient
information to provide a solution? Waste my time and the OP's by making a
guess? Ignore the message (how does that help the OP)?
> What the user wants is to pivot the table in this case.
Maybe. In fact, I will even go so far as to say "probably". But, does he
need a dynamic pivot? Or does he have static values on which to base a
non-dynamic pivot?

> Personally I would go for a different table design.
Really? Based on what? You know very little about the OP's requirements ...
And why don't you think a vague statement about a "different table design"
is as bad as a request for clarification?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.