Bulk Load and SQL function defaults

Is it possible to include a field default in the schema file that represent
a database function?
For example, all of our SQL tables includes the following fields...
CreateDate datetime NOT NULL DEFAULT (getdate())
CreateUser char (255) NOT NULL DEFAULT (suser_sname())
When creating our schema we tried the following:
<xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
/>
<xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
/>
The "CreateDate" element fails with an "Invalid character value for cast
specification." error, while the second element will insert the string value
'suser_sname()' into the "CreateUser" field.
Thanks in advance
No, this is not possible. The default is an XML schema default clause and
cannot contain an T-SQL expression.
Instead, define a default on the relational table column to which you map
the element and make sure that there is no value added.
Best regards
Michael
"Cipher" <c@.c.com> wrote in message
news:OU8QUY0TEHA.3988@.tk2msftngp13.phx.gbl...
> Is it possible to include a field default in the schema file that
> represent
> a database function?
> For example, all of our SQL tables includes the following fields...
> CreateDate datetime NOT NULL DEFAULT (getdate())
> CreateUser char (255) NOT NULL DEFAULT (suser_sname())
> When creating our schema we tried the following:
> <xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
> />
> <xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
> />
> The "CreateDate" element fails with an "Invalid character value for cast
> specification." error, while the second element will insert the string
> value
> 'suser_sname()' into the "CreateUser" field.
>
> Thanks in advance
>
sql

Bulk Insert and Default Values

I am experiencing an issue with bulk insert and default values.
Say that i have a table with thirteen. My format file species all columns
except 14. Column 14 is an int and has a default value of '9'. When i insert
a record manually it picks up the default value fine, but when i bulk insert
all of the records have a default value of 0. Any thoughts?
string|string|s|59|aa|aaaaa|N|N|N|N|N|Y|Y
8.0
13
1 SQLCHAR 0 4 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 25 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 2 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 4 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 6 "|" 6 col6 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 1 "|" 7 col7 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "|" 8 col8 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 1 "|" 9 col9 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "|" 10 col10 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 1 "|" 11 col11 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 1 "|" 12 col12 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 1 "\r\n" 13 col13 SQL_Latin1_General_CP1_CI_AS
BULK INSERT db..table FROM 'file' WITH
(
FORMATFILE='fmt.fmt',
CODEPAGE='RAW',
ROWS_PER_BATCH=141,
MAXERRORS=10,
TABLOCK
)
GO
Thoughts?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
> When i insert
> a record manually it picks up the default value fine, but when i bulk
> insert
> all of the records have a default value of 0. Any thoughts?
I tried your format file, data file and BULK INSERT with the table below and
the default value was assigned properly under both SQL 2000 and SQL 2005. I
find it strange that a value of zero is assigned in your environment...
CREATE TABLE dbo.table1
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
col6 varchar(10),
col7 varchar(10),
col8 varchar(10),
col9 varchar(10),
col10 varchar(10),
col11 varchar(10),
col12 varchar(10),
col13 varchar(10),
col14 varchar(10) NULL CONSTRAINT DF_table_col14 DEFAULT '9'
)
Hope this helps.
Dan Guzman
SQL Server MVP
"lotek via droptable.com" <u16539@.uwe> wrote in message
news:7f4a9d35a33d0@.uwe...
>I am experiencing an issue with bulk insert and default values.
> Say that i have a table with thirteen. My format file species all columns
> except 14. Column 14 is an int and has a default value of '9'. When i
> insert
> a record manually it picks up the default value fine, but when i bulk
> insert
> all of the records have a default value of 0. Any thoughts?
> string|string|s|59|aa|aaaaa|N|N|N|N|N|Y|Y
> 8.0
> 13
> 1 SQLCHAR 0 4 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 25 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 1 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 0 2 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 0 4 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 6 "|" 6 col6 SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 1 "|" 7 col7 SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 1 "|" 8 col8 SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 1 "|" 9 col9 SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 1 "|" 10 col10 SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 1 "|" 11 col11 SQL_Latin1_General_CP1_CI_AS
> 12 SQLCHAR 0 1 "|" 12 col12 SQL_Latin1_General_CP1_CI_AS
> 13 SQLCHAR 0 1 "\r\n" 13 col13 SQL_Latin1_General_CP1_CI_AS
> BULK INSERT db..table FROM 'file' WITH
> (
> FORMATFILE='fmt.fmt',
> CODEPAGE='RAW',
> ROWS_PER_BATCH=141,
> MAXERRORS=10,
> TABLOCK
> )
> GO
> Thoughts?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
>
|||Col14 is an int where you have a varchar. That might make the difference...
Thanks for your time.
-Matt
Dan Guzman wrote:[vbcol=seagreen]
>I tried your format file, data file and BULK INSERT with the table below and
>the default value was assigned properly under both SQL 2000 and SQL 2005. I
>find it strange that a value of zero is assigned in your environment...
>CREATE TABLE dbo.table1
>(
>col1 varchar(10),
>col2 varchar(10),
>col3 varchar(10),
>col4 varchar(10),
>col5 varchar(10),
>col6 varchar(10),
>col7 varchar(10),
>col8 varchar(10),
>col9 varchar(10),
>col10 varchar(10),
>col11 varchar(10),
>col12 varchar(10),
>col13 varchar(10),
>col14 varchar(10) NULL CONSTRAINT DF_table_col14 DEFAULT '9'
>)
>[quoted text clipped - 34 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
|||> Col14 is an int where you have a varchar. That might make the
> difference...
I ran another test with the int and it works in my environment. Below is
the full repro. The only change I made from the info your original post
(other than file paths) was to shorten the test data to avoid truncation.
You might try the CHECK_CONSTRAINTS option of BULK INSERT but it wasn't
necessary in my environment.
CREATE TABLE dbo.table1
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
col6 varchar(10),
col7 varchar(10),
col8 varchar(10),
col9 varchar(10),
col10 varchar(10),
col11 varchar(10),
col12 varchar(10),
col13 varchar(10),
col14 int NULL CONSTRAINT DF_table_col14 DEFAULT 9
)
GO
--c:\temp\fmt.fmt
8.0
13
1 SQLCHAR 0 4 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 25 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 2 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 4 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 6 "|" 6 col6 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 1 "|" 7 col7 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "|" 8 col8 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 1 "|" 9 col9 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "|" 10 col10 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 1 "|" 11 col11 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 1 "|" 12 col12 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 1 "\r\n" 13 col13 SQL_Latin1_General_CP1_CI_AS
--c:\temp\file.txt
a|b|s|59|aa|aaaaa|N|N|N|N|N|Y|Y
BULK INSERT dbo.table1 FROM 'c:\temp\file.txt' WITH
(
FORMATFILE='c:\temp\fmt.fmt',
CODEPAGE='RAW',
ROWS_PER_BATCH=141,
MAXERRORS=10,
TABLOCK
)
GO
SELECT * FROM dbo.table1
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"lotek via droptable.com" <u16539@.uwe> wrote in message
news:7f4ee474dd997@.uwe...
> Col14 is an int where you have a varchar. That might make the
> difference...
> Thanks for your time.
> -Matt
> Dan Guzman wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
>

Bulk Insert and Default Values

I am experiencing an issue with bulk insert and default values.
Say that i have a table with thirteen. My format file species all columns
except 14. Column 14 is an int and has a default value of '9'. When i insert
a record manually it picks up the default value fine, but when i bulk insert
all of the records have a default value of 0. Any thoughts?
string|string|s|59|aa|aaaaa|N|N|N|N|N|Y|Y
8.0
13
1 SQLCHAR 0 4 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 25 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 2 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 4 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 6 "|" 6 col6 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 1 "|" 7 col7 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "|" 8 col8 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 1 "|" 9 col9 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "|" 10 col10 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 1 "|" 11 col11 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 1 "|" 12 col12 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 1 "\r\n" 13 col13 SQL_Latin1_General_CP1_CI_AS
BULK INSERT db..table FROM 'file' WITH
(
FORMATFILE='fmt.fmt',
CODEPAGE='RAW',
ROWS_PER_BATCH=141,
MAXERRORS=10,
TABLOCK
)
GO
Thoughts?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1> When i insert
> a record manually it picks up the default value fine, but when i bulk
> insert
> all of the records have a default value of 0. Any thoughts?
I tried your format file, data file and BULK INSERT with the table below and
the default value was assigned properly under both SQL 2000 and SQL 2005. I
find it strange that a value of zero is assigned in your environment...
CREATE TABLE dbo.table1
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
col6 varchar(10),
col7 varchar(10),
col8 varchar(10),
col9 varchar(10),
col10 varchar(10),
col11 varchar(10),
col12 varchar(10),
col13 varchar(10),
col14 varchar(10) NULL CONSTRAINT DF_table_col14 DEFAULT '9'
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"lotek via SQLMonster.com" <u16539@.uwe> wrote in message
news:7f4a9d35a33d0@.uwe...
>I am experiencing an issue with bulk insert and default values.
> Say that i have a table with thirteen. My format file species all columns
> except 14. Column 14 is an int and has a default value of '9'. When i
> insert
> a record manually it picks up the default value fine, but when i bulk
> insert
> all of the records have a default value of 0. Any thoughts?
> string|string|s|59|aa|aaaaa|N|N|N|N|N|Y|Y
> 8.0
> 13
> 1 SQLCHAR 0 4 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 25 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 1 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 0 2 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 0 4 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 6 "|" 6 col6 SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 1 "|" 7 col7 SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 1 "|" 8 col8 SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 1 "|" 9 col9 SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 1 "|" 10 col10 SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 1 "|" 11 col11 SQL_Latin1_General_CP1_CI_AS
> 12 SQLCHAR 0 1 "|" 12 col12 SQL_Latin1_General_CP1_CI_AS
> 13 SQLCHAR 0 1 "\r\n" 13 col13 SQL_Latin1_General_CP1_CI_AS
> BULK INSERT db..table FROM 'file' WITH
> (
> FORMATFILE='fmt.fmt',
> CODEPAGE='RAW',
> ROWS_PER_BATCH=141,
> MAXERRORS=10,
> TABLOCK
> )
> GO
> Thoughts?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1
>|||Col14 is an int where you have a varchar. That might make the difference...
Thanks for your time.
-Matt
Dan Guzman wrote:
>> When i insert
>> a record manually it picks up the default value fine, but when i bulk
>> insert
>> all of the records have a default value of 0. Any thoughts?
>I tried your format file, data file and BULK INSERT with the table below and
>the default value was assigned properly under both SQL 2000 and SQL 2005. I
>find it strange that a value of zero is assigned in your environment...
>CREATE TABLE dbo.table1
>(
>col1 varchar(10),
>col2 varchar(10),
>col3 varchar(10),
>col4 varchar(10),
>col5 varchar(10),
>col6 varchar(10),
>col7 varchar(10),
>col8 varchar(10),
>col9 varchar(10),
>col10 varchar(10),
>col11 varchar(10),
>col12 varchar(10),
>col13 varchar(10),
>col14 varchar(10) NULL CONSTRAINT DF_table_col14 DEFAULT '9'
>)
>>I am experiencing an issue with bulk insert and default values.
>[quoted text clipped - 34 lines]
>> Thoughts?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1|||> Col14 is an int where you have a varchar. That might make the
> difference...
I ran another test with the int and it works in my environment. Below is
the full repro. The only change I made from the info your original post
(other than file paths) was to shorten the test data to avoid truncation.
You might try the CHECK_CONSTRAINTS option of BULK INSERT but it wasn't
necessary in my environment.
CREATE TABLE dbo.table1
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
col6 varchar(10),
col7 varchar(10),
col8 varchar(10),
col9 varchar(10),
col10 varchar(10),
col11 varchar(10),
col12 varchar(10),
col13 varchar(10),
col14 int NULL CONSTRAINT DF_table_col14 DEFAULT 9
)
GO
--c:\temp\fmt.fmt
8.0
13
1 SQLCHAR 0 4 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 25 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 2 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 4 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 6 "|" 6 col6 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 1 "|" 7 col7 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "|" 8 col8 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 1 "|" 9 col9 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "|" 10 col10 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 1 "|" 11 col11 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 1 "|" 12 col12 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 1 "\r\n" 13 col13 SQL_Latin1_General_CP1_CI_AS
--c:\temp\file.txt
a|b|s|59|aa|aaaaa|N|N|N|N|N|Y|Y
BULK INSERT dbo.table1 FROM 'c:\temp\file.txt' WITH
(
FORMATFILE='c:\temp\fmt.fmt',
CODEPAGE='RAW',
ROWS_PER_BATCH=141,
MAXERRORS=10,
TABLOCK
)
GO
SELECT * FROM dbo.table1
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"lotek via SQLMonster.com" <u16539@.uwe> wrote in message
news:7f4ee474dd997@.uwe...
> Col14 is an int where you have a varchar. That might make the
> difference...
> Thanks for your time.
> -Matt
> Dan Guzman wrote:
>> When i insert
>> a record manually it picks up the default value fine, but when i bulk
>> insert
>> all of the records have a default value of 0. Any thoughts?
>>I tried your format file, data file and BULK INSERT with the table below
>>and
>>the default value was assigned properly under both SQL 2000 and SQL 2005.
>>I
>>find it strange that a value of zero is assigned in your environment...
>>CREATE TABLE dbo.table1
>>(
>>col1 varchar(10),
>>col2 varchar(10),
>>col3 varchar(10),
>>col4 varchar(10),
>>col5 varchar(10),
>>col6 varchar(10),
>>col7 varchar(10),
>>col8 varchar(10),
>>col9 varchar(10),
>>col10 varchar(10),
>>col11 varchar(10),
>>col12 varchar(10),
>>col13 varchar(10),
>>col14 varchar(10) NULL CONSTRAINT DF_table_col14 DEFAULT '9'
>>)
>>I am experiencing an issue with bulk insert and default values.
>>[quoted text clipped - 34 lines]
>> Thoughts?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1
>

builtin\adminstrators

what is best practice with this group account?
it is installed by default, if i understand correctl, it gives anyone who is
in the server admin group, admin rights to sql server. isn't that a bad id
ea?See:
SQL Server 2000 SP3 Security Features and Best Practices
http://www.microsoft.com/technet/pr...n/sp3sec00.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
i believe have looked at this article before...however, i just reviewed it
again.
where does this article address my question specifically..am i missing it.
thanks|||Hi Kevin,
Thanks for your update. As the SQL Server 2000 security model is based on
the Windows security model and as the system administrator of windows
operating system, it will have permission on all the activities on the SQL
Server since they are combined together. So, 'buildin\admin' security will
depends on the windows security model for it. SQL Server will benifit from
the Windows security architecture. Please refer to the following article:
Authentication Modes
http://msdn.microsoft.com/library/e...curity_47u6.asp
Security Architecture
http://msdn.microsoft.com/library/e...curity_4fol.asp
Hope this helps. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||let me put it this way, if you have os adminstrators who are in the local ad
ministrators group and you don't want them to have admin rights to the sql s
erver...then you should i remove the builtin\administrators group from the
sql server logins.....is t
hat correct?
thanks
-jason|||Yes.
The following article has a section with links to some
issues that could come up if you remove the group:
INF: How to impede Windows NT administrators from
administering a clustered instance of SQL Server
http://support.microsoft.com/?id=263712
-Sue
On Wed, 14 Apr 2004 14:01:06 -0700, "jason" <jason_fin>
wrote:

>let me put it this way, if you have os adminstrators who are in the local administr
ators group and you don't want them to have admin rights to the sql server...then y
ou should i remove the builtin\administrators group from the sql server logins.....
is
that correct?
>thanks
>-jason|||Hi Kevin,
I just want to add some more information to your question. Please look at
the
"Step 11. SQL Server Logins, Users, and Roles" in "Securing Your Database
Server" at
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/THCMCh18.asp
That is, if you differentiate the role of domain administrator and database
administrator. You'd better remove the BUILTIN\Administrators SQL Server
login and then create a specific Windows group containing specific database
administrations added to SQL server as a server login.
Hope this helps. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi,
I am reviewing you post. Since we have not heard from you for some time, I
wonder if you still have quesitons of the information I provided. For any
question, please feel free to post new message here and we are glad to
help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks Sue and Biasong,
the reason it takes me a while to tget back is because that nospam alias acc
ount doesn't work as far as sending me notifications for replys.
thanks
-jason

Building Custom Component - marking all inputs

I have been building a custom component with the default GUI. I want to have all the input fields selected to be passed through to outputs without having to explicitly check each one in the GUI. Is there some method or property to set on the input to do this?

You have to map ExternalMetadataColumnID from source to destination

|||I'm not sure this is specific to SSIS. That is, you might want to look at the appropriate .Net forum for specifics on how to code the GUI for your project.|||

I would normally do this in the UI itself, but that would be a custom UI, not the standard UI you get for free, as you cannot extend that one at all.

You could do this in ReinitializeMetaData, such that if no columns are found, and you have forced RMD to build the input/output columns for the first time, when adding you also select the columns as you create them.

How you select the column, the equivalent of selecting a column in the UI, will depend on what type of component you are writing, but normally revolves around setting the usage type, or mapping input/output columns to external metadata columns.

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
Key Ext Key
1 50K
2 73J
3 75K
4 60A
Table 2
Key Value
50K ABC
60A DEF
75K GHI
Current Join on SMC
Key ExtKey Value
1 50K ABC
2 73J (null)
3 75K GHI
4 60A DEF
Desired Join result
Key ExtKey Value
1 50K ABC
2 73J Other
3 75K GHI
4 60A DEF
I've also tried updating the view but that didn't work for me either.
TIA
BillBill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA.464@.TK2MSFTNGP15.phx.gbl>...
> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
Key Ext Key
1 50K
2 73J
3 75K
4 60A
Table 2
Key Value
50K ABC
60A DEF
75K GHI
Current Join on SMC
Key ExtKey Value
1 50K ABC
2 73J (null)
3 75K GHI
4 60A DEF
Desired Join result
Key ExtKey Value
1 50K ABC
2 73J Other
3 75K GHI
4 60A DEF
I've also tried updating the view but that didn't work for me either.
TIA
BillBill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA
.464@.TK2MSFTNGP15.phx.gbl>...

> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Building A View with a default value for left join between two tables

Good Day;
I'm certain there must be a simple solution for this but I've yet to
find it. I wish to build a view between two tables that have a one to
many relationship using a left join to ensure all the records from the
many table are selected. However when the one table doesn't have an
join I'd like the view to exhibit a default value i.e., "Other" The
following are some sample tables and both the simple view and the
currently unattainable but desired view. If anyone could provide some
assistance it would be appreciated.
Table 1
KeyExt Key
150K
273J
375K
460A
Table 2
KeyValue
50KABC
60ADEF
75KGHI
Current Join on SMC
Key ExtKey Value
150KABC
273J(null)
375KGHI
460ADEF
Desired Join result
Key ExtKey Value
150KABC
273JOther
375KGHI
460ADEF
I've also tried updating the view but that didn't work for me either.
TIA
Bill
Bill wrote:
> Good Day;
> I'm certain there must be a simple solution for this but I've yet to
> find it. I wish to build a view between two tables that have a one to
> many relationship using a left join to ensure all the records from the
> many table are selected. However when the one table doesn't have an
> join I'd like the view to exhibit a default value i.e., "Other" The
> following are some sample tables and both the simple view and the
> currently unattainable but desired view. If anyone could provide some
> assistance it would be appreciated.
> Table 1
> Key Ext Key
> 1 50K
> 2 73J
> 3 75K
> 4 60A
> Table 2
> Key Value
> 50K ABC
> 60A DEF
> 75K GHI
>
> Current Join on SMC
> Key ExtKey Value
> 1 50K ABC
> 2 73J (null)
> 3 75K GHI
> 4 60A DEF
> Desired Join result
> Key ExtKey Value
> 1 50K ABC
> 2 73J Other
> 3 75K GHI
> 4 60A DEF
> I've also tried updating the view but that didn't work for me either.
> TIA
> Bill
create table #a (col1 int)
create table #b (col1 int)
insert into #a values (1)
insert into #a values (2)
insert into #a values (3)
insert into #b values (1)
insert into #b values (3)
Select a.col1, ISNULL(b.col1, 99) as col1
From #a a
left outer join #b b
on a.col1 = b.col1
col1 col1
-- --
1 1
2 99
3 3
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<Or1hHe9DFHA.464@.TK2MSFTNGP15.phx.gbl>...

> Select a.col1, ISNULL(b.col1, 99) as col1
David;
Thank you. That worked. Isn't it easy when you know the magic words.
Cheers;
Bill

Building a conditional WHERE clause

Hello experts, I have a sproc that gets several params, default is null. I'
d
like to build a WHERE clause on the fly for just the params with values.
Something like
SELECT * from foo
WHERE if( @.a IS NOT NULL foo.a = @.a) and if( @.b IS NOT NULL foo.b = @.b)IF is a control flow statement... You cannot use it in a query.
How about :
WHERE a = COALESCE(@.a, a)
AND b = COALESCE(@.b, b)
You can also use CASE but it's a bit more drawn out:
WHERE a = CASE WHEN @.a IS NOT NULL THEN @.a ELSE a END
AND b = CASE WHEN @.b IS NOT NULL THEN @.b ELSE b END
(And the latter will almost certainly lead to scans rather than ss, if a
and/or be is indexed.)
A
On 3/5/05 12:45 PM, in article
3AD07FB0-A316-4ED0-A29D-EB3E5F06499A@.microsoft.com, "Coffee guy"
<Coffeeguy@.discussions.microsoft.com> wrote:

> Hello experts, I have a sproc that gets several params, default is null.
I'd
> like to build a WHERE clause on the fly for just the params with values.
> Something like
> SELECT * from foo
> WHERE if( @.a IS NOT NULL foo.a = @.a) and if( @.b IS NOT NULL foo.b = @.b)
>
>|||Aaaah, thanks!
"Aaron [SQL Server MVP]" wrote:

> IF is a control flow statement... You cannot use it in a query.
> How about :
> WHERE a = COALESCE(@.a, a)
> AND b = COALESCE(@.b, b)
> You can also use CASE but it's a bit more drawn out:
> WHERE a = CASE WHEN @.a IS NOT NULL THEN @.a ELSE a END
> AND b = CASE WHEN @.b IS NOT NULL THEN @.b ELSE b END
> (And the latter will almost certainly lead to scans rather than ss, if
a
> and/or be is indexed.)
> A
>
> On 3/5/05 12:45 PM, in article
> 3AD07FB0-A316-4ED0-A29D-EB3E5F06499A@.microsoft.com, "Coffee guy"
> <Coffeeguy@.discussions.microsoft.com> wrote:
>
>

Build conditional WHERE clause

Hi All,

I'm building a simple stored proc to be the basis of an SQL Report.

I've set up my parameters to default to NULL, as the users will have the option to filter the parameters to build the report. I have 2 datetime params which are causing me a problem.

I have a condition which checks if these 2 params are NULL, if they are, it just continues and executes the SQL query, and all is well. However, if they contain dates, I am trying to build a where condition in the variable named @.where_clause, and then I append this to the end of my SQL statement.

- If my @.where_clause is empty, the stored proc returns the desired results
- If the @.where_clause is NOT empty, my stored proc executes but returns NO results
- And last, if I modify the stored proc and remove the WHERE clause completely, and just add the "AND tblProducts.start_time >= @.start_time AND tblProducts.start_time <= @.end_time". to the end of my statement in plain SQL, then it works.

** It only seems to not work when I put build my WHERE clause in a variable **

I hope this is not too confusing.

Here is the stored proc as it is now....not working.

ALTER PROCEDURE [dbo].[spReport_ProductSerialAssemblyResults]
-- Add the parameters for the stored procedure here
@.pec varchar(30) = NULL,
@.rel varchar(30) = NULL,
@.ver varchar(10) = NULL,
@.sn varchar(50) = NULL,
@.start_time datetime = NULL,
@.end_time datetime = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @.where_clause varchar(MAX)

set @.where_clause = '';

if ((@.start_time is not null) and (@.end_time is not null))
BEGIN
set @.where_clause = ' AND tblProducts.start_time >= @.start_time AND tblProducts.start_time <= @.end_time';
END



-- Insert statements for procedure here
SELECT * FROM tblProducts
WHERE (tblProducts.in_production = 1) AND tblProducts.pec = COALESCE(@.pec, tblProducts.pec)
AND tblProducts.release = COALESCE(@.rel, tblProducts.release)
AND tblProducts.version = COALESCE(@.ver, tblProducts.version)
AND tblProducts.serial = COALESCE(@.sn, tblProducts.serial) + @.where_clause;


END

To do what you are trying to do where you take a character variable and execute it as SQL code you need to use dynamic SQL. See the EXECUTE statement, and the sp_executesql stored procedure for info on dynamic SQL.

However, in your case dynamic SQL is not necessary (and it should be avoided unless necessary). What you can do in your SELECT statement is simply this

SELECT * FROM tblProducts
WHERE (tblProducts.in_production = 1)
AND tblProducts.pec = COALESCE(@.pec, tblProducts.pec)
AND tblProducts.release = COALESCE(@.rel, tblProducts.release)
AND tblProducts.version = COALESCE(@.ver, tblProducts.version)
AND tblProducts.serial = COALESCE(@.sn, tblProducts.serial)
AND (tblProducts.start_time >= @.start_time
AND tblProducts.start_time <= @.end_time
OR (@.start_time IS NULL OR @.end_time IS NULL))

|||

Please take a look at the link below for various solutions that doesn't require dynamic SQL code.

http://www.sommarskog.se/dyn-search.html

|||Thank you both for the great feedback...