Need some logic help

Trying to bind some data to a datalist for a report.

User selects an Industry from a dropdown list and then I dump all records for that industry. However in order to parse some of the record field values into names (I.E. from a 1 to the actual company name) for some records I have to read TABLE_ONE and for other records I might have to read TABLE_TWO depending on the value of FIELD_ONE.

If FIELD_ONE = "A" then I get the NAME from TABLE_ONE.
If FIELD_ONE = "B" then I get the NAME from TABLE_TWO.
If FIELD_ONE = "C" then I get the NAME from TABLE_THREE.

I'm lost at how to get started on this. I thought about adding IF statements to my query but these won't work because I'm not passing in the value of FIELD_ONE ahead of time - it's part of the query. So I thought maybe I could do a pre-read and store all FIELD_ONE values in an ArrayList and pass these in as parameters, but the stored proc is only being called once - so that won't work.

Any thoughts on how I can do this?I can think of a couple of ways to accomplish this, but the most elegant involves using right outer joins and the COALESE function. COALESE is a function that returns the first non-null expression from a list of parameters. So the SELECT statement would be something like the following

SELECT COALESE(TABLE_ONE.NAME, TABLE_TWO.NAME, TABLE_THREE.NAME)
FROM TABLE RIGHT OUTER JOIN TABLE_ONE ON (TABLE.FIELD = TABLE_ONE.KEY AND TABLE.FIELD_ONE = 'A')
RIGHT OUTER JOIN TABLE_TWO ON (TABLE.FIELD = TABLE_TWO.KEY AND TABLE.FIELD_ONE = 'B')
RIGHT OUTER JOIN TABLE_THREE ON (TABLE.FIELD = TABLE_THREE.KEY AND TABLE.FIELD_ONE = 'C')

The returned field will contain whichever value is not null, the one that is appropriate depending on the value of FIELD_ONE.

HTH|||Am I understanding that the lookup values are in different tables. If so then you'd be doing conditional joins which I don't think is possible. Assuming you can't change the table structure around, I would create a query that combines all the lookup information from Table_One, Table_two, and Table_three (and others) and then join this table to your original table. Something like:

Select YourMainTable.Field1, B.Name
From YourMainTable INNER JOIN
(
Select Field1,Name
From
(
Select Field1,Name FROM Table1
Union
Select Field1,Name FROM Table2
Union
Select Field1,Name FROM Table3
) A
) B ON(YourMainTable.Field1 = B.Field1)

sql

Need some helps on this task

A user photo table with the following fields:

username
path
caption
status

where the status field indicates where the photo is the main one or not. Each user only can have one main photo.

For the deletion operation, another, if there is one, photo's status needs to be changed to main if the removing photo is the main one. What is the best approache to carry out this task: SQL and function/trigger?

Similar situation occur when a user want to change a non-main photo to become a main one.

Any advice?

Thanks,

v.While a trigger could be made to do this, it would not be trivial - would have to deal with the mutating table problem for one thing.

I would prefer to hide the logic in a packaged procedure, and force the user (i.e. the application) to delete via the procedure rather than an update or delete statement.|||Hi, Tony,

Thanks for your response and suggestion.

My thought on the issue is that it is somehow like a DB table constraint and not a business logic. Therefore, it shoud be resolved in the DB layer.

I can implement your suggestion to pass the information what the application knows about whether the photo is a main one or not. I think this solution is a suitable one.

Thanks again.

v.|||Here is a solution I just come out.

For the deletion operation, if the deleting photo placement is larger than 1, not the first/main one, only execute the deletion statement.

Otherwise, after the deletion statement, run the following query:

UPDATE photo SET main = 'true' WHERE userid = 'xxx' AND path IN (SELECT path FROM photo WHERE userid = 'xxx' )

This solution basically is on the DB side with a little help from the application (logic).

My feeling of the above query is the subquery can be in a better form, but can't think out one at this moment.

v.

Need some help with subtotals in a matrix

Hello all,

This is all being done under SQL2000 and VS2003

I have a matrix report which is showing user information. The Rows are displaying numbers for each user, and the columns show the user info in weekly increments. I have 7 fields of info for each user. My stored procedure already is set up to give me the correct numbers. I dont need to SUM them or anything. Although in the report designer it forced me to SUM them since it was part of an aggregate. This still worked for me anyhow because it was Summing a single value.

However, at the end of the report i want to display totals for all the users combined, per week. So right now the report is showing 21 weeks, so at the end of the report i should have 21 sets of totals.

I right clicked on the users name column and selected subtotal. This gave me some of what i want. But some of the numbers are not correct. Some of the numbers should not just be a simple SUM of the column. Some of the values should be averages etc. I know how to calculate those values myself (its very simple math) but i dont know how to do it using this setup in the report designer. So in the matrix, for each week, how can i calculate the totals for all the users combined and specify the formula used to get the totals for each field?

thanks
I think i might have found a way to fix this. I added a table to the matrix, grouped the records by the weeks, and then displayed what fields i wanted in the table header.

however, i need for all the records returned to be displayed in new columns, not new rows. If i can get it displayed in new colums, it'll look like its just part of the existing matrix.

So instead of new records repeating by adding a new row, i need to know how to get the new records to come up as a new column.

Or is there a better way?

Need script to loop through all non-system databases and drop all user schemas - Almost ther

Does anybody have any tsql code that will loop through all non-system databases on a SQL Server 2005 instance and drop all the user schemas in each database? Thanks.

you could get the DB's by using:

select * from master.dbo.sysdatabases where dbid > 4

You could then loop through the objects with:

SELECt CASE WHEN xtype = 'P' THEN 'DROP PROC ' + name

WHEN xtype = 'U' THEN 'DROP TABLE ' + name

END

from sysobjects

where xtype in( 'P','U')

Order by xtype, name

Depending on the objects you could also add functions..views..etc

|||Perhaps you might want to check the objects for not being system ones:

where xtype in( 'P','U')

AND OBJECTPROPERTY(OBJECT_ID(name),'IsMSShipped') = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I use the below script to clean up the objects in a database. The script will fail if you have schemabound dependencies between objects.

For e.g. if there is a schemabound view that is dependent on a table, dropping the table before dropping the view will fail and will be report by an appropriate error message. You will need to manually drop such objects.

DECLARE @.sqlcommand VARCHAR(max), @.delimiter CHAR, @.ErrorMessage NVARCHAR(4000)
SET @.delimiter = ';'
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP '+
CASE type
WHEN 'AF' THEN N'AGGREGATE'
WHEN 'P' THEN N'PROCEDURE'
WHEN 'PC' THEN N'PROCEDURE'
WHEN 'FN' THEN N'FUNCTION'
WHEN 'FS' THEN N'FUNCTION'
WHEN 'FT' THEN N'FUNCTION'
WHEN 'R' THEN N'RULE'
WHEN 'RF' THEN N'PROCEDURE'
WHEN 'SN' THEN N'SYNONYM'
WHEN 'IF' THEN N'FUNCTION'
WHEN 'TF' THEN N'FUNCTION'
WHEN 'U' THEN N'TABLE'
WHEN 'V' THEN N'VIEW'
ELSE N'INVALID'
END
+' '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.objects
WHERE type not in ('C','D','F','PK','UQ','X','S','IT','TR','TA','R','SQ') --Not dropping constraints,triggers,service queues

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch

set @.sqlcommand=null;

--Now lets drop the rules and defaults
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP '+
CASE type
WHEN 'R' THEN N'RULE'
WHEN 'D' THEN N'DEFAULT'
ELSE N'INVALID'
END
+' '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.objects
WHERE type not in ('C','F','PK','UQ','X','S','IT','TR','TA','SQ')

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;

--Now lets drop the types
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP TYPE '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.types
WHERE system_type_id <> user_type_id and name <> 'sysname'--only drops user defined types

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;

--Now lets drop the schemas
begin try
begin transaction
SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP SCHEMA '+name
FROM sys.schemas where schema_id > 4 and schema_id < 16384

exec (@.sqlcommand);
commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch

set @.sqlcommand=null;

|||

We are on track. Basically I am trying to do something like this:

create table #dbs (dbid int IDENTITY(1,1), dbname nvarchar(128))

insert into #dbs (dbname)

select name from master.dbo.sysdatabases where dbid > 4

select * from #dbs

DECLARE @.sqlcommand VARCHAR(max),

@.useCommand varchar(max),

@.delimiter CHAR,

@.dbname nvarchar(128),

@.idx int

SET @.delimiter = ';'

select @.idx = min(dbid) from #dbs

while @.idx is not null

begin

select @.dbname = dbname from #dbs where dbid = @.idx

select @.useCommand = 'USE ' + @.dbname + @.delimiter

select @.useCommand

SELECT @.sqlcommand = COALESCE(quotename(@.sqlcommand), quotename(@.delimiter), ' ') + 'DROP SCHEMA ' + name

FROM quotename(@.dbname)+'.sys.schemas where schema_id > 4 and schema_id < 16384'+''''

select @.sqlcommand

--exec(@.sqlcommand)

select @.idx = min(dbid) from #dbs where dbid > @.idx

select @.sqlcommand

end

My code is just not working because of syntax errors. I think I am very close though, maybe one you SQL experts out there can show me my mistake and help me out. Thanks for all your help thus far!

|||Maybe this isn't even possible, if its not please let me know so I stop spinning my wheels and trying to figure this one out.|||This can get really complicated because you cannot drop the schemas without first dropping the objects in that schema. Is it too much to run the script manually per database ?|||

The script is for a migration from a SQL Server 2000 databse to SQL Server 2005. All of the objects are owned by dbo. The schemas are automatically created for each user as part of the migration from SQL Server 2000 to 2005. We are just doing a back up and restore of the database to migrate it. I need to drop all the schemas in each database before I can drop and re add all the users. Everything needs to be scripted and automated so it can be tested before running on production.

I have come up with this thus far:

/* Drop schemas */

create table #dbs (dbid int IDENTITY(1,1), dbname nvarchar(128))

insert into #dbs (dbname)

select name from master.dbo.sysdatabases where dbid > 4

create table #schemas (schemaid int identity(1,1), dbname nvarchar(max), schemaname nvarchar(max))

/* Change the object owner for these objects temporarily so we can drop the schema */

select @.idx = min(dbid) from #dbs

while @.idx is not null

begin

select @.dbname = dbname from #dbs where dbid = @.idx

select @.sql = 'INSERT INTO #schemas (dbname,schemaname) '

select @.sql = @.sql + 'Select ' +''''+@.dbname+'''' + ', name From ' + quotename(@.dbname) + '.sys.schemas where schema_id > 4 and schema_id < 16384'

Exec(@.sql)

select @.idx = min(dbid) from #dbs where dbid > @.idx

end

select @.idx = min(schemaid) from #schemas

while @.idx is not null

begin

select @.sql = ' USE ' + quotename(dbname) + ' DROP SCHEMA ' + schemaname + ';' from #schemas where schemaid = @.idx

Exec(@.sql)

select @.idx = min(schemaid) from #schemas where schemaid > @.idx

end

Need script to loop through all non-system databases and drop all user schemas

Does anybody have any tsql code that will loop through all non-system databases on a SQL Server 2005 instance and drop all the user schemas in each database? Thanks.

you could get the DB's by using:

select * from master.dbo.sysdatabases where dbid > 4

You could then loop through the objects with:

SELECt CASE WHEN xtype = 'P' THEN 'DROP PROC ' + name

WHEN xtype = 'U' THEN 'DROP TABLE ' + name

END

from sysobjects

where xtype in( 'P','U')

Order by xtype, name

Depending on the objects you could also add functions..views..etc

|||Perhaps you might want to check the objects for not being system ones:

where xtype in( 'P','U')

AND OBJECTPROPERTY(OBJECT_ID(name),'IsMSShipped') = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I use the below script to clean up the objects in a database. The script will fail if you have schemabound dependencies between objects.

For e.g. if there is a schemabound view that is dependent on a table, dropping the table before dropping the view will fail and will be report by an appropriate error message. You will need to manually drop such objects.

DECLARE @.sqlcommand VARCHAR(max), @.delimiter CHAR, @.ErrorMessage NVARCHAR(4000)
SET @.delimiter = ';'
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP '+
CASE type
WHEN 'AF' THEN N'AGGREGATE'
WHEN 'P' THEN N'PROCEDURE'
WHEN 'PC' THEN N'PROCEDURE'
WHEN 'FN' THEN N'FUNCTION'
WHEN 'FS' THEN N'FUNCTION'
WHEN 'FT' THEN N'FUNCTION'
WHEN 'R' THEN N'RULE'
WHEN 'RF' THEN N'PROCEDURE'
WHEN 'SN' THEN N'SYNONYM'
WHEN 'IF' THEN N'FUNCTION'
WHEN 'TF' THEN N'FUNCTION'
WHEN 'U' THEN N'TABLE'
WHEN 'V' THEN N'VIEW'
ELSE N'INVALID'
END
+' '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.objects
WHERE type not in ('C','D','F','PK','UQ','X','S','IT','TR','TA','R','SQ') --Not dropping constraints,triggers,service queues

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch

set @.sqlcommand=null;

--Now lets drop the rules and defaults
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP '+
CASE type
WHEN 'R' THEN N'RULE'
WHEN 'D' THEN N'DEFAULT'
ELSE N'INVALID'
END
+' '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.objects
WHERE type not in ('C','F','PK','UQ','X','S','IT','TR','TA','SQ')

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;

--Now lets drop the types
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP TYPE '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.types
WHERE system_type_id <> user_type_id and name <> 'sysname'--only drops user defined types

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;

--Now lets drop the schemas
begin try
begin transaction
SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP SCHEMA '+name
FROM sys.schemas where schema_id > 4 and schema_id < 16384

exec (@.sqlcommand);
commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch

set @.sqlcommand=null;

|||

We are on track. Basically I am trying to do something like this:

create table #dbs (dbid int IDENTITY(1,1), dbname nvarchar(128))

insert into #dbs (dbname)

select name from master.dbo.sysdatabases where dbid > 4

select * from #dbs

DECLARE @.sqlcommand VARCHAR(max),

@.useCommand varchar(max),

@.delimiter CHAR,

@.dbname nvarchar(128),

@.idx int

SET @.delimiter = ';'

select @.idx = min(dbid) from #dbs

while @.idx is not null

begin

select @.dbname = dbname from #dbs where dbid = @.idx

select @.useCommand = 'USE ' + @.dbname + @.delimiter

select @.useCommand

SELECT @.sqlcommand = COALESCE(quotename(@.sqlcommand), quotename(@.delimiter), ' ') + 'DROP SCHEMA ' + name

FROM quotename(@.dbname)+'.sys.schemas where schema_id > 4 and schema_id < 16384'+''''

select @.sqlcommand

--exec(@.sqlcommand)

select @.idx = min(dbid) from #dbs where dbid > @.idx

select @.sqlcommand

end

My code is just not working because of syntax errors. I think I am very close though, maybe one you SQL experts out there can show me my mistake and help me out. Thanks for all your help thus far!

|||Maybe this isn't even possible, if its not please let me know so I stop spinning my wheels and trying to figure this one out.|||This can get really complicated because you cannot drop the schemas without first dropping the objects in that schema. Is it too much to run the script manually per database ?|||

The script is for a migration from a SQL Server 2000 databse to SQL Server 2005. All of the objects are owned by dbo. The schemas are automatically created for each user as part of the migration from SQL Server 2000 to 2005. We are just doing a back up and restore of the database to migrate it. I need to drop all the schemas in each database before I can drop and re add all the users. Everything needs to be scripted and automated so it can be tested before running on production.

I have come up with this thus far:

/* Drop schemas */

create table #dbs (dbid int IDENTITY(1,1), dbname nvarchar(128))

insert into #dbs (dbname)

select name from master.dbo.sysdatabases where dbid > 4

create table #schemas (schemaid int identity(1,1), dbname nvarchar(max), schemaname nvarchar(max))

/* Change the object owner for these objects temporarily so we can drop the schema */

select @.idx = min(dbid) from #dbs

while @.idx is not null

begin

select @.dbname = dbname from #dbs where dbid = @.idx

select @.sql = 'INSERT INTO #schemas (dbname,schemaname) '

select @.sql = @.sql + 'Select ' +''''+@.dbname+'''' + ', name From ' + quotename(@.dbname) + '.sys.schemas where schema_id > 4 and schema_id < 16384'

Exec(@.sql)

select @.idx = min(dbid) from #dbs where dbid > @.idx

end

select @.idx = min(schemaid) from #schemas

while @.idx is not null

begin

select @.sql = ' USE ' + quotename(dbname) + ' DROP SCHEMA ' + schemaname + ';' from #schemas where schemaid = @.idx

Exec(@.sql)

select @.idx = min(schemaid) from #schemas where schemaid > @.idx

end

Need random/unique "IDs" for key verification

I need to be able to create completely random and unique keys for a key verification system, which would require a user to enter a pre-defined key in order to activate their account, but I need to be able to create those keys on the fly.

This is going to be a key that will be mailed to them on paper, and unfortunately means it needs to be relatively short in order to prevent too much confusion while they are typing it in.

I like the newID() function in SQL, but the ID that it creates is a bit excessive to say the least for someone to have to type when registerring.

I use C#, so I wouldn't have much of a problem creating a small app to create x number of keys, which will sit in the DB until I need them, but I would rather not have to fill the DB with a million or so ID's which might never be used, and don't want to create too little that I have to track when I might need to add more, in case I start to run low on ID's.

Re-using ID's may be an option, but I would prefer to keep them intact for the life of the accounts.

If there is something that I can do to simulate the newID() function, but generate unique/random ID's which look more like this: A97-2C5-77D than this: A972C577-DFB0-064E-1189-0154C99310DAAC12 I would be very grateful to know about it.

Thanks!

You could use Membership.GeneratePassword Method

It allows control of length and complexity both of the generated password

http://msdn2.microsoft.com/en-us/library/system.web.security.membership.generatepassword.aspx

Need Query Help for Search

I am writing a small search feature to return a list of companies whose name "Begins with" a certain string (up to 5 chars) provided by the user via a textbox. I want the results to only return results that begin with the letter/letters specified. Below I will put the code that I came up with that isn't working quite how I expected. I am new to this so any assistance and short explanation would be very much apperciated.

sql="SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 "

sql=sql & "FROM cmmst cm "

sql=sql & "WHERE cm_cname1 + ' ' + cm_cname2 LIKE '%" companyNameBegins,"'","''") & "%' " sql=sql & "AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') " sql=sql & "ORDER BY cm_s16 DESC, cm_cname1 ASC"

companyNameBegins is the string passed in by the user

Thanks,

Zoop

(1) Use parameterized Queries. Your code will look simpler and neater and you can avoid SQL Injection Attacks (Google for more info on this topic)

(2) Append the "%" in the value rather than in the SQL. Also if you want to retrieve records starting with a value the % should be at the end like "SELECT...WHERE column like 'startwith%'"

Here's some sample code:

Dim myCommand As SqlCommand
Dim myParam As SqlParameter

myCommand = New SqlCommand()
myCommand.Connection = objcon
myCommand.CommandText = "SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 FROM cmmst cm WHERE cm_cname1 + ' ' + cm_cname2 LIKE @.companyNameBegins AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') ORDER BY cm_s16 DESC, cm_cname1 ASC"

myCommand.Parameters.Add(New SqlParameter("@.companyNameBegins ",SqlDbType.varchar,100))
myCommand.Parameters("@.companyNameBegins").Value = companyNameBegins & "%"

Try
If objCon.State = 0 Then objCon.Open()
'ExecuteReader and fill some dataContainer.

Catch exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End Try

|||

Thanks for the assistance, much smoother this way.

zoop

Need Parameter Optionally Omitted

Is it possible to have a parameter ignored? I have a report that I am
web-deploying with a large set of parameters, but a user may not wish to
include some for a given execution of the report. For example, I have a
Boolean checkbox that I cannot get the report to ignore. I have tried
toggling the following settings: allow null, allow blank, setting defaults,
not setting defaults. I then recast as char and used a true/false drop-down
but still could not get the report to optionally use it.
Thanks for any helpMike,
It sounds like you're using the Report Parameter to make a Query
Parameter to use in your SQL query. If that's the case, the problem may
be that when the parameter is null, your SQL query fails because it's
expecting a query parameter that doesn't exist.
If this is the case (and I can't be sure since you didn't give the
exact error) what you can do is write a function that checks all of
your parameters, performs whatever logic you need, and returns a sql
statement as a string. In your dataset you will have something like
=Code.GetSQL()
instead of the sql statement you have now.
Just make sure you're using the Generic Query Designer instead of the
Query Builder, or it'll have a fit.|||I am not getting an error, just incorrect results from the query when I
don't want the param used. If I delete the param, I get the results I would
expect if the param were ignored. In the grid colum 'Criteria' I add
'@.param'. Then from the menu Report\Report Parameters I add the addtional
attributes for the param as I mentioned earlier.
I am not a SQL power-user so I am tring for a modest report, accepting some
of the known limitations of the tool esp regarding use of params. I was
hoping to at least get basic function though.
Also, Is there a distinction between a report vs query paramter?
Many thanks!

Need information on syspermissions table

I have a MS Sqlserver 2000 database installed on my Windows NT4 PC.

After I granted permissions to a user for a specific table my Java code
(using JDBC - ODBC) was successfully able to retrieve the contents of the
table. However, when I examined the contents of the "syspermissions" table
using the following command :

osql /U sa /P /d %1 /w 256 -Q "select grantee,grantor,actadd,actmod,
(select substring(name,1,40)
from sysobjects where sysobjects.id = syspermissions.id) 'objname'
from syspermissions,sysobjects where
(sysobjects.xtype = 'U' and sysobjects.id = syspermissions.id) order by objname"

I did not see any difference in the entry for the table to which I granted
the access.

According to the documentation I have read the "syspermissions" table is the
place where access rights information is stored.

Am I missing something ? Where is the info on granted access rights stored
if not in the "syspermissions" table ?

Thanks for any help you can give.Q1 Thanks for any help you can give.

A1 GRANT and DENY statements populate sysprotects (which you have not queried). You may wish to consider using exec sp_helprotect instead?

Example:

Use Pubs
Go

exec sp_helprotect
@.name = 'Authors'
Go

exec sp_helprotect
@.name = 'Authors',
@.username = 'SomeOtherUser' ,
@.grantorname = NULL,
@.permissionarea = 's'
Go

Need Help.

I m using SQL SERVER EXPRESS edition for storing user login information. I have use built in login controls in my application. Login is working properly, user are also created but i also want to assign roles to users and modify their details programmatically.

How can i do this ? Plzzzzzzz Help. Its urgent.

These resouces should help point you in the 'right' direction:

Security -Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html

SQL Server 2005 Security
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/

Need help with xp_cmdshell and proxy accout

I am tracking down a problem using xp_cmdshell on SQLServer 2000 (MSDE). Th
e
DB user is not a sysadmin, but does have exec rights for xp_cmdshell. I als
o
have an agent proxy account (Win2K account) set which is a member of “User
s”
group. This scheme has been working fine until I made some changes recently.
The changes I made are: Applied Win2K SP4, applied a host of hotfixes, and
made several changes in user rights and other security-related settings.
I’ve been testing this from OSQL logged in as the same user that my
application uses when it attaches to SQLServer. I get this error:
“xpsql.cpp: Error 1385 from LogonUserW on line 488”. Another issue whic
h I
think is related is that if I run xp_sqlagent_proxy_account to set the proxy
account, I get this error: “Specified user can not login”. Interestingl
y, if
I make the proxy account a member of the Administrators group, then I can se
t
the proxy account and I can execute xp_cmdshell.
Does anyone know what rights (Win2K rights) are required for SQLServer to
run xp_cmdshell, and what rights are required for the proxy account? By
rights I mean things like: Impersonate another user, logon as a batch job,
logon locally, logon as a service, and so on. This almost certainly has
something to do with rights, but I haven’t been able to isolate it yet. C
an
anyone suggest anything else to try that might help identify the problem?
Thanks,
CraigFor anyone interested, I have found the answer. The proxy account must have
the logon right "Log on as a batch job".
"Craig Daniel" wrote:

> I am tracking down a problem using xp_cmdshell on SQLServer 2000 (MSDE).
The
> DB user is not a sysadmin, but does have exec rights for xp_cmdshell. I a
lso
> have an agent proxy account (Win2K account) set which is a member of “Us
ers”
> group. This scheme has been working fine until I made some changes recentl
y.
> The changes I made are: Applied Win2K SP4, applied a host of hotfixes, and
> made several changes in user rights and other security-related settings.
> I’ve been testing this from OSQL logged in as the same user that my
> application uses when it attaches to SQLServer. I get this error:
> “xpsql.cpp: Error 1385 from LogonUserW on line 488”. Another issue wh
ich I
> think is related is that if I run xp_sqlagent_proxy_account to set the pro
xy
> account, I get this error: “Specified user can not login”. Interestin
gly, if
> I make the proxy account a member of the Administrators group, then I can
set
> the proxy account and I can execute xp_cmdshell.
> Does anyone know what rights (Win2K rights) are required for SQLServer to
> run xp_cmdshell, and what rights are required for the proxy account? By
> rights I mean things like: Impersonate another user, logon as a batch job,
> logon locally, logon as a service, and so on. This almost certainly has
> something to do with rights, but I haven’t been able to isolate it yet.
Can
> anyone suggest anything else to try that might help identify the problem?
> Thanks,
> Craig
>

Need help with VS_NEEDSNEWMETADATA error

Hello:

I need some help figuring out the true source of the following error:

"

Executed as user: EPSILON\SYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:53:09 PM Error: 2007-09-14 12:53:09.59 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-09-14 12:53:10.50 Code: 0xC020837F Source: Data Flow Task Source - icsp [1] Description: The data type of "output column "user2" (138)" does not match the data type "System.String" of the source column "user2". End Error Error: 2007-09-14 12:53:10.50 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "Source - icsp" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". The package execution fa... The step failed."

Reading the error it appears that the issue is with a datatype mismatch with field "user2". It is coming in as a unicode string and being sent into a varchar field. But so are a whole bunch of other userX fields as well. So why is my package having an issue with this specific field. Moreoever the package was running fine until a few days ago and runs successfully in BIDS!

I first figured that the source has changed, as there was some work being performed on the source ERP system. The package had failed a month ago and when I updated the metadata I thought it fixed the problem.

I appreciate your assistance in helping me resolve this issue!

Don't ignore the password issue. Maybe because it can't use the password, it can't determine that the metadata has changed, or something like that. In BIDS, maybe it doesn't have the password problem?

|||Did you move the package from one computer to another by any chance? What is the ProtectionLevel of the package set to? I recommend setting the ProtectionLevel to "DontSaveSensitive" and seeing if it resolves the problem.

I have also had problems in the past where somehow the metadata gets messed up and the easiest thing to do is recreate the package.|||

Thank you for your response gentlemen!

I have checked the connection string for teh SQL Agent and it has the correct ID and password. I have not changed anything in the job. In BIDS, when I enter the password and run the job it runs fine.

|||As Danny asked, what's the ProtectionLevel of the package?|||EncryptSensitiveWithUserKey|||

Using EncryptSensitiveWithUserKey is the cause of the error. This is a link to an article that explains the problem and more importantly, the solution:

http://support.microsoft.com/kb/918760

|||

You mention SQL Agent, so does this error happen when you schedule the package, but works for you on your desktop?

If so the problem is the ProtectionLevel. The EncryptSensitiveWithUserKey value means just that, it uses the user key, your key as you built the package. If your SQL Server Agent service was set to run under you account, then the error would go away. that would also be a stupid thing to do, so change to using DontSaveSensitive is my advice. If you have passwords, then supply them through Configurations.

Some links with more information -

http://support.microsoft.com/kb/904800

http://support.microsoft.com/kb/918760

http://technet.microsoft.com/en-us/library/ms141682.aspx

|||

I have to admit, I have really looked at the Protection Level closely. The issue is that the package for running fine in an Agent, as well are other packages with the same type of Protection Level. Why did it work all this time and is not working now - is the question that puzzles me?

I will review the links that you have provided and figure out how to build Configurations.

PS: The package does not run outside of an agent in Mgmt. Studio but runs in BIDS after I supply the password in the DataReaderSrc.

Thanks again!

|||

Many of us have had to struggle with the ProtectionLevel setting during deployment. I do not think Microsoft documented it very well. It finally clicks after beating your head against the wall for awhile and visiting forums.

Need help with VS_NEEDSNEWMETADATA error

Hello:

I need some help figuring out the true source of the following error:

"

Executed as user: EPSILON\SYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:53:09 PM Error: 2007-09-14 12:53:09.59 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-09-14 12:53:10.50 Code: 0xC020837F Source: Data Flow Task Source - icsp [1] Description: The data type of "output column "user2" (138)" does not match the data type "System.String" of the source column "user2". End Error Error: 2007-09-14 12:53:10.50 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "Source - icsp" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". The package execution fa... The step failed."

Reading the error it appears that the issue is with a datatype mismatch with field "user2". It is coming in as a unicode string and being sent into a varchar field. But so are a whole bunch of other userX fields as well. So why is my package having an issue with this specific field. Moreoever the package was running fine until a few days ago and runs successfully in BIDS!

I first figured that the source has changed, as there was some work being performed on the source ERP system. The package had failed a month ago and when I updated the metadata I thought it fixed the problem.

I appreciate your assistance in helping me resolve this issue!

Don't ignore the password issue. Maybe because it can't use the password, it can't determine that the metadata has changed, or something like that. In BIDS, maybe it doesn't have the password problem?

|||Did you move the package from one computer to another by any chance? What is the ProtectionLevel of the package set to? I recommend setting the ProtectionLevel to "DontSaveSensitive" and seeing if it resolves the problem.

I have also had problems in the past where somehow the metadata gets messed up and the easiest thing to do is recreate the package.|||

Thank you for your response gentlemen!

I have checked the connection string for teh SQL Agent and it has the correct ID and password. I have not changed anything in the job. In BIDS, when I enter the password and run the job it runs fine.

|||As Danny asked, what's the ProtectionLevel of the package?|||EncryptSensitiveWithUserKey|||

Using EncryptSensitiveWithUserKey is the cause of the error. This is a link to an article that explains the problem and more importantly, the solution:

http://support.microsoft.com/kb/918760

|||

You mention SQL Agent, so does this error happen when you schedule the package, but works for you on your desktop?

If so the problem is the ProtectionLevel. The EncryptSensitiveWithUserKey value means just that, it uses the user key, your key as you built the package. If your SQL Server Agent service was set to run under you account, then the error would go away. that would also be a stupid thing to do, so change to using DontSaveSensitive is my advice. If you have passwords, then supply them through Configurations.

Some links with more information -

http://support.microsoft.com/kb/904800

http://support.microsoft.com/kb/918760

http://technet.microsoft.com/en-us/library/ms141682.aspx

|||

I have to admit, I have really looked at the Protection Level closely. The issue is that the package for running fine in an Agent, as well are other packages with the same type of Protection Level. Why did it work all this time and is not working now - is the question that puzzles me?

I will review the links that you have provided and figure out how to build Configurations.

PS: The package does not run outside of an agent in Mgmt. Studio but runs in BIDS after I supply the password in the DataReaderSrc.

Thanks again!

|||

Many of us have had to struggle with the ProtectionLevel setting during deployment. I do not think Microsoft documented it very well. It finally clicks after beating your head against the wall for awhile and visiting forums.

Need help with VS_NEEDSNEWMETADATA error

Hello:

I need some help figuring out the true source of the following error:

"

Executed as user: EPSILON\SYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:53:09 PM Error: 2007-09-14 12:53:09.59 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-09-14 12:53:10.50 Code: 0xC020837F Source: Data Flow Task Source - icsp [1] Description: The data type of "output column "user2" (138)" does not match the data type "System.String" of the source column "user2". End Error Error: 2007-09-14 12:53:10.50 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "Source - icsp" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". The package execution fa... The step failed."

Reading the error it appears that the issue is with a datatype mismatch with field "user2". It is coming in as a unicode string and being sent into a varchar field. But so are a whole bunch of other userX fields as well. So why is my package having an issue with this specific field. Moreoever the package was running fine until a few days ago and runs successfully in BIDS!

I first figured that the source has changed, as there was some work being performed on the source ERP system. The package had failed a month ago and when I updated the metadata I thought it fixed the problem.

I appreciate your assistance in helping me resolve this issue!

Don't ignore the password issue. Maybe because it can't use the password, it can't determine that the metadata has changed, or something like that. In BIDS, maybe it doesn't have the password problem?

|||Did you move the package from one computer to another by any chance? What is the ProtectionLevel of the package set to? I recommend setting the ProtectionLevel to "DontSaveSensitive" and seeing if it resolves the problem.

I have also had problems in the past where somehow the metadata gets messed up and the easiest thing to do is recreate the package.|||

Thank you for your response gentlemen!

I have checked the connection string for teh SQL Agent and it has the correct ID and password. I have not changed anything in the job. In BIDS, when I enter the password and run the job it runs fine.

|||As Danny asked, what's the ProtectionLevel of the package?|||EncryptSensitiveWithUserKey|||

Using EncryptSensitiveWithUserKey is the cause of the error. This is a link to an article that explains the problem and more importantly, the solution:

http://support.microsoft.com/kb/918760

|||

You mention SQL Agent, so does this error happen when you schedule the package, but works for you on your desktop?

If so the problem is the ProtectionLevel. The EncryptSensitiveWithUserKey value means just that, it uses the user key, your key as you built the package. If your SQL Server Agent service was set to run under you account, then the error would go away. that would also be a stupid thing to do, so change to using DontSaveSensitive is my advice. If you have passwords, then supply them through Configurations.

Some links with more information -

http://support.microsoft.com/kb/904800

http://support.microsoft.com/kb/918760

http://technet.microsoft.com/en-us/library/ms141682.aspx

|||

I have to admit, I have really looked at the Protection Level closely. The issue is that the package for running fine in an Agent, as well are other packages with the same type of Protection Level. Why did it work all this time and is not working now - is the question that puzzles me?

I will review the links that you have provided and figure out how to build Configurations.

PS: The package does not run outside of an agent in Mgmt. Studio but runs in BIDS after I supply the password in the DataReaderSrc.

Thanks again!

|||

Many of us have had to struggle with the ProtectionLevel setting during deployment. I do not think Microsoft documented it very well. It finally clicks after beating your head against the wall for awhile and visiting forums.

need help with using maintenance plan..

background sql2kt, nt5
wondering if someone can help me with a backup issue.
if i were to create a maintenance plan to back up all user databases on a
server (like 60 of them)
1. how can i backup just one database on demand outside the maintenance
plan? and have it recognized by the maintenance when the next scheduled
backup occures? (such as purge file and so on)
2. what would be the difference of putting all 60 databases in one
maintenance vs. seperating them into 3 plans with staggering schedule?
any advice would be greatly appreciated.
> if i were to create a maintenance plan to back up all user databases on a
> server (like 60 of them)
> 1. how can i backup just one database on demand outside the maintenance
> plan? and have it recognized by the maintenance when the next scheduled
> backup occures? (such as purge file and so on)
> 2. what would be the difference of putting all 60 databases in one
> maintenance vs. seperating them into 3 plans with staggering schedule?
> any advice would be greatly appreciated.
1. you can always right click on a db and backup from there. i believe the
dbmp determines which backups to delete by reading the
msdb.sysdbmaintplan_history table. you would probably have to "forge" an
entry in that table to get your dbmp to delete your manual backups. i
wouldn't recommend this although i've done something similar with no ill
effects.
i usually keep a couple of weeks of backups on disk. every once in a while i
go through those backup directories looking for old manual backups that don't
need to be there any more and manually delete them.
2. one dbmp for 60 db's will backup them up one at a time in alphabetical
order. 3 seperate plans is much more of a headache to manage. the biggest
problem is that when you add a new db, if you forget to add it to one of
those 3 dbmp's, it won't get backed up. if you delete a db without changing
the dbmp, you'll get errors when the jobs run trying to work on that db
that's been deleted. if you have a dbmp for all user databases, then you
don't have to modify a dbmp every time you add a new db or delete a db.
|||thanks for such a good advice.
regarding #1, your suggestion works.
but if i want call that backup routine from the other scheduled task using
sql script, how would I know the backup dump file name currently available?
since the maintenance plan generate a new file name each day such as
MyDb_2004050401800.bak
"ch" <ch@.dontemailme.com> wrote in message
news:4097D623.1F2E0C9B@.dontemailme.com...[vbcol=seagreen]
a
> 1. you can always right click on a db and backup from there. i believe
the
> dbmp determines which backups to delete by reading the
> msdb.sysdbmaintplan_history table. you would probably have to "forge" an
> entry in that table to get your dbmp to delete your manual backups. i
> wouldn't recommend this although i've done something similar with no ill
> effects.
> i usually keep a couple of weeks of backups on disk. every once in a
while i
> go through those backup directories looking for old manual backups that
don't
> need to be there any more and manually delete them.
> 2. one dbmp for 60 db's will backup them up one at a time in alphabetical
> order. 3 seperate plans is much more of a headache to manage. the
biggest
> problem is that when you add a new db, if you forget to add it to one of
> those 3 dbmp's, it won't get backed up. if you delete a db without
changing
> the dbmp, you'll get errors when the jobs run trying to work on that db
> that's been deleted. if you have a dbmp for all user databases, then you
> don't have to modify a dbmp every time you add a new db or delete a db.
>

need help with using maintenance plan..

background sql2kt, nt5
wondering if someone can help me with a backup issue.
if i were to create a maintenance plan to back up all user databases on a
server (like 60 of them)
1. how can i backup just one database on demand outside the maintenance
plan? and have it recognized by the maintenance when the next scheduled
backup occures? (such as purge file and so on)
2. what would be the difference of putting all 60 databases in one
maintenance vs. seperating them into 3 plans with staggering schedule?
any advice would be greatly appreciated.> if i were to create a maintenance plan to back up all user databases on a
> server (like 60 of them)
> 1. how can i backup just one database on demand outside the maintenance
> plan? and have it recognized by the maintenance when the next scheduled
> backup occures? (such as purge file and so on)
> 2. what would be the difference of putting all 60 databases in one
> maintenance vs. seperating them into 3 plans with staggering schedule?
> any advice would be greatly appreciated.
1. you can always right click on a db and backup from there. i believe the
dbmp determines which backups to delete by reading the
msdb.sysdbmaintplan_history table. you would probably have to "forge" an
entry in that table to get your dbmp to delete your manual backups. i
wouldn't recommend this although i've done something similar with no ill
effects.
i usually keep a couple of weeks of backups on disk. every once in a while
i
go through those backup directories looking for old manual backups that don'
t
need to be there any more and manually delete them.
2. one dbmp for 60 db's will backup them up one at a time in alphabetical
order. 3 seperate plans is much more of a headache to manage. the biggest
problem is that when you add a new db, if you forget to add it to one of
those 3 dbmp's, it won't get backed up. if you delete a db without changing
the dbmp, you'll get errors when the jobs run trying to work on that db
that's been deleted. if you have a dbmp for all user databases, then you
don't have to modify a dbmp every time you add a new db or delete a db.|||thanks for such a good advice.
regarding #1, your suggestion works.
but if i want call that backup routine from the other scheduled task using
sql script, how would I know the backup dump file name currently available?
since the maintenance plan generate a new file name each day such as
MyDb_2004050401800.bak
"ch" <ch@.dontemailme.com> wrote in message
news:4097D623.1F2E0C9B@.dontemailme.com...
a[vbcol=seagreen]
> 1. you can always right click on a db and backup from there. i believe
the
> dbmp determines which backups to delete by reading the
> msdb.sysdbmaintplan_history table. you would probably have to "forge" an
> entry in that table to get your dbmp to delete your manual backups. i
> wouldn't recommend this although i've done something similar with no ill
> effects.
> i usually keep a couple of weeks of backups on disk. every once in a
while i
> go through those backup directories looking for old manual backups that
don't
> need to be there any more and manually delete them.
> 2. one dbmp for 60 db's will backup them up one at a time in alphabetical
> order. 3 seperate plans is much more of a headache to manage. the
biggest
> problem is that when you add a new db, if you forget to add it to one of
> those 3 dbmp's, it won't get backed up. if you delete a db without
changing
> the dbmp, you'll get errors when the jobs run trying to work on that db
> that's been deleted. if you have a dbmp for all user databases, then you
> don't have to modify a dbmp every time you add a new db or delete a db.
>

need help with using maintenance plan..

background sql2kt, nt5
wondering if someone can help me with a backup issue.
if i were to create a maintenance plan to back up all user databases on a
server (like 60 of them)
1. how can i backup just one database on demand outside the maintenance
plan? and have it recognized by the maintenance when the next scheduled
backup occures? (such as purge file and so on)
2. what would be the difference of putting all 60 databases in one
maintenance vs. seperating them into 3 plans with staggering schedule?
any advice would be greatly appreciated.> if i were to create a maintenance plan to back up all user databases on a
> server (like 60 of them)
> 1. how can i backup just one database on demand outside the maintenance
> plan? and have it recognized by the maintenance when the next scheduled
> backup occures? (such as purge file and so on)
> 2. what would be the difference of putting all 60 databases in one
> maintenance vs. seperating them into 3 plans with staggering schedule?
> any advice would be greatly appreciated.
1. you can always right click on a db and backup from there. i believe the
dbmp determines which backups to delete by reading the
msdb.sysdbmaintplan_history table. you would probably have to "forge" an
entry in that table to get your dbmp to delete your manual backups. i
wouldn't recommend this although i've done something similar with no ill
effects.
i usually keep a couple of weeks of backups on disk. every once in a while i
go through those backup directories looking for old manual backups that don't
need to be there any more and manually delete them.
2. one dbmp for 60 db's will backup them up one at a time in alphabetical
order. 3 seperate plans is much more of a headache to manage. the biggest
problem is that when you add a new db, if you forget to add it to one of
those 3 dbmp's, it won't get backed up. if you delete a db without changing
the dbmp, you'll get errors when the jobs run trying to work on that db
that's been deleted. if you have a dbmp for all user databases, then you
don't have to modify a dbmp every time you add a new db or delete a db.|||thanks for such a good advice.
regarding #1, your suggestion works.
but if i want call that backup routine from the other scheduled task using
sql script, how would I know the backup dump file name currently available?
since the maintenance plan generate a new file name each day such as
MyDb_2004050401800.bak
"ch" <ch@.dontemailme.com> wrote in message
news:4097D623.1F2E0C9B@.dontemailme.com...
> > if i were to create a maintenance plan to back up all user databases on
a
> > server (like 60 of them)
> >
> > 1. how can i backup just one database on demand outside the maintenance
> > plan? and have it recognized by the maintenance when the next scheduled
> > backup occures? (such as purge file and so on)
> >
> > 2. what would be the difference of putting all 60 databases in one
> > maintenance vs. seperating them into 3 plans with staggering schedule?
> >
> > any advice would be greatly appreciated.
> 1. you can always right click on a db and backup from there. i believe
the
> dbmp determines which backups to delete by reading the
> msdb.sysdbmaintplan_history table. you would probably have to "forge" an
> entry in that table to get your dbmp to delete your manual backups. i
> wouldn't recommend this although i've done something similar with no ill
> effects.
> i usually keep a couple of weeks of backups on disk. every once in a
while i
> go through those backup directories looking for old manual backups that
don't
> need to be there any more and manually delete them.
> 2. one dbmp for 60 db's will backup them up one at a time in alphabetical
> order. 3 seperate plans is much more of a headache to manage. the
biggest
> problem is that when you add a new db, if you forget to add it to one of
> those 3 dbmp's, it won't get backed up. if you delete a db without
changing
> the dbmp, you'll get errors when the jobs run trying to work on that db
> that's been deleted. if you have a dbmp for all user databases, then you
> don't have to modify a dbmp every time you add a new db or delete a db.
>

Need help with User Defined Function

I am accustomed to doing most of my function work in Access, but the boss would really like it if I could shedule some cubes to do the stuff that takes forever when you run it live.

To that end, I have an Access function that I call to get a field value for a query. I would like to be able to create an User Defined Function on the SQL server and call that function as a field value in a view. I have searched the forums and have not really found anything that wants to make sense to me as to how to do this.

The access function is as follows:

Public Function BuyerDeltas(IFSDate As Date, PODate As Date) As Long

If IFSDate < (Date + 14) Then
BuyerDeltas = IFSDate - 3 - PODate
ElseIf IFSDate < (Date + 29) Then
BuyerDeltas = IFSDate - 7 - PODate
ElseIf IFSDate > (Date + 28) Then
BuyerDeltas = IFSDate - 10 - PODate
Else
MsgBox "This should not be possible!", vbOKOnly, "Fix It!!!!!!!"
End If

End Function

The view that this is called from contains the IFSDate and PODate fields and I am able to call the function from the access query, but this is completely different than what I have seen in the help files on SQL.

I would love to be able to keep plugging away at doing this myself, but the boss also is pushing me to get it done and he doesn't want me taking forever to do it.

Any direction would be greatly appreciated!

I still cannot get this proceedure set up so it will work. I have been playing with it all day and have gotten to the following point:

CREATE FUNCTION BuyerDeltas
(@.IFSDate DATE, @.PODate DATE)
RETURNS decimal (5,0)
AS
BEGIN
DECLARE @.BaseDate DATE
DECLARE @.IFS decimal (5,0)
DECLARE @.PO decimal (5,0)
DECLARE @.ThisDay decimal (5,0)
SET @.BaseDate = CONVERT(DATETIME, '2000-12-31 00:00:00', 102)
-- Used so I can use DateDiff to get numbers for days to use in equations
-- It is just an arbitrary date that will be before any dates in the system
SET @.IFS = DateDiff(day, @.BaseDate, @.IFSDate)
SET @.PO = DateDiff(day, @.BaseDate, @.PODate)
SET @.ThisDay = DateDiff(day, @.BaseDate, GetDate())
Where @.IFS < @.ThisDay + 14
RETURN @.IFS - 3 - @.PO
Where (@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29)
RETURN @.IFS - 7 - @.PO
Where @.IFS < @.ThisDay + 28
RETURN @.IFS - 10 - @.PO
End

It gives me the following errors:

Server: Msg 443, Level 16, State 1, Procedure BuyerDeltas, Line 15
Invalid use of 'getdate' within a function.
Server: Msg 156, Level 15, State 1, Procedure BuyerDeltas, Line 18
Incorrect syntax near the keyword 'Where'.
Server: Msg 156, Level 15, State 1, Procedure BuyerDeltas, Line 20
Incorrect syntax near the keyword 'Where'.

I am really trying to understand this and get it to work right. If someone could just give me an example of a similarly structured function, I am sure I should be able to alter this one so that it works.

Calling the function from the view, actually, I guess I should just use the function in the INSERT code for the DTS package, might still drive me nuts when I try and use this, but I do really want to get this done right.

Thanks again!

|||

Hopefully this will help. We do not have a DATE datatype in SQL SERVER. We have a DATETIME datatype.

CREATE FUNCTION BuyerDeltas
(@.IFSDate DATETIME, @.PODate DATETIME)
RETURNS decimal (5,0)
AS
BEGIN
DECLARE @.BaseDate DATETIME
DECLARE @.IFS decimal (5,0)
DECLARE @.PO decimal (5,0)
DECLARE @.ThisDay decimal (5,0)
SET @.BaseDate = CONVERT(DATETIME, '2000-12-31 00:00:00', 102)

-- Used so I can use DateDiff to get numbers for days to use in equations
-- It is just an arbitrary date that will be before any dates in the system
SET @.IFS = DateDiff(day, @.BaseDate, @.IFSDate)
SET @.PO = DateDiff(day, @.BaseDate, @.PODate)
SET @.ThisDay = DateDiff(day, @.BaseDate, GetDate())

if (@.IFS < @.ThisDay + 14)
RETURN @.IFS - 3 - @.PO
else if ((@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29))
RETURN @.IFS - 7 - @.PO
else if (@.IFS < @.ThisDay + 28)
RETURN @.IFS - 10 - @.PO

RETURN -1 --trying to indicate a failure

--Where @.IFS < @.ThisDay + 14
-- RETURN @.IFS - 3 - @.PO
--Where (@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29)
--RETURN @.IFS - 7 - @.PO
--Where @.IFS < @.ThisDay + 28
-- RETURN @.IFS - 10 - @.PO
end
GO

|||

Thanks Asvin!

That is getting it very close, the only error I now get is:

Server: Msg 443, Level 16, State 1, Procedure BuyerDeltas, Line 16
Invalid use of 'getdate' within a function.

Not sure how else I can get today's date in this thing, but I keep trying. I am open to just getting the date as a variable, but when I tried that, I still go the error.

Thanks again!

|||

In 2000 you can't use the getdate function in a function (say that a few times fast) but you can in 2005.

The workaround is to add a parameter and pass in the date and time like:

create function functionName
(
@.parm1 datatype,
@.getdate datetime

Then add that to the call.

Another trick which I have never actually used is to create a view:

create view dateView
as
select getdate() as getdate

Then just add this to your queries that need the current date (using a cross join works nicely):

create view dateView
as
select getdate() as getdate
GO

select * from dateView
GO

Returns:

getdate
--
2006-04-07 23:58:30.217

select dateview.getdate, sysobjects.name
from sysobjects
cross join dateview

getdate name
--
2006-04-07 23:58:30.247 sysrowsetcolumns
2006-04-07 23:58:30.247 sysrowsets
2006-04-07 23:58:30.247 sysallocunits
.......

And so on.

|||

Thanks Louis,

I didn't actually use the ideas you put forth, but you did get me thinking. I realized that I could put GetDate() in the view that was used to make the table for the cube. I then do some alterations on the table based on what data is missing in some fields of the table. There are times that there are no dates in the fields I need to calculate the BuyerDeltas that this whole exercise is designed to calculate so I need to put dates in these fields before I can call the function anyway. After those updates, I can call the function and put the value I wanted calculated in the appropriate field of the table. There is probably a simpler and faster way to do it, but this works and the boss is breathing down my neck to get this finished.

Thanks again to all that helped, using a little bit from all of the responses has let me finally beat this thing.