Bigint autoincrement question

Hi All,
i wonder if i can get an bigint autoincrement field where the number begins with the current year + 1 autonumber
Does someone know if it is possible and if yes, how?
Already thanx.
Cheers Wimcreate table test_t
(col1 bigint identity (2004, 1), col2 char (5)
)
go

declare @.counter tinyint

set @.counter = 1

while @.counter <> 5
begin
insert into test_t
select 'test'
set @.counter = @.counter + 1
end
go

select * from test_t
go|||Originally posted by aldo_2003
create table test_t
(col1 bigint identity (2004, 1), col2 char (5)
)
go

declare @.counter tinyint

set @.counter = 1

while @.counter <> 5
begin
insert into test_t
select 'test'
set @.counter = @.counter + 1
end
go

select * from test_t
go

But it doen't change the year automatically|||How do you mean automatically ?

Can you clarify ?|||Originally posted by aldo_2003
How do you mean automatically ?

Can you clarify ?

The id should always start with the current year, so when it is 2005 the id should start with 2005 1 + increment|||Example please?|||Originally posted by aldo_2003
Example please?

It should be something like
create table test_t
(col1 bigint identity (datepart(yy, getdate())1, 1), col2 char (5)
)
go|||An example of what it would look like in a table.|||You should store your year value and your ID value separately, and then use a trigger to increment the ID value.

[YearColumn] defaults to year(getdate())

[IDColumn] is set by a trigger (Insert only!) to:

set [IDColumn] = isnull((select Max([IDCOLUMN]) from YourTable where [YearColumn] = Year(Getdate())), 0) + 1

Then you can combine the two values in your SQL code, or create a calculted column that combines the two.|||I like your solution, but the problem is that i cant set that column to primary key|||You can add a unique index to a computed column. You may be able to make it a primary key, thought I'm not certain about that. In any case, you could set the Year column and the ID column together as a composite primary key.

BI Data Presentation

Hi,

I'am very new to Datawarehousing concepts of SQL2005, my current doubt is how to present the data to the end user from the cube i generated in Visual studio2005?

i had worked in cognos before and there we had a viewer file which was having the extension as .ppr and can be called from application which is connected to the Cube, so here i would like to know what is the next step after cube so that i can present the data...

any help or links will be greatfull

Mat

Hi,

There are various posting in the forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1218497&SiteID=1

ProClarity

Office 2007

Performance Point

Panorama NovaView

Probably many more

Hope that helps

Matt

BI Accelerator: problem with current day setting

Hi, All!
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?Hi
I have already seen this problem a while ago. Checkout
http://www.dbforums.com/showthread.php?t=895520
I don't really understand what the guy that replied means by "Setting the al
l tu current".
What I did at the time (and of course that is not the solution) was to remov
e the Weel.Standard dimension from the cube.
Did you found a solution.?

quote:

---

Originally posted by Eugene Frolov
Hi, All!
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?

---

|||Hi!
Checkout this:
http://support.microsoft.com/defaul...b;en-us;834285.
Data in cubes become correct after delete ALL date dims exept only one with
custom rollups.
"rsada" <rsada.1fzsd0@.mail.webservertalk.com> /
: news:rsada.1fzsd0@.mail.webservertalk.com...
> Hi
> I have already seen this problem a while ago. Checkout
> http://www.webservertalk.com/showthread.php?t=895520
> I don't really understand what the guy that replied means by "Setting
> the all tu current".
> What I did at the time (and of course that is not the solution) was to
> remove the Weel.Standard dimension from the cube.
> Did you found a solution.?
>
>
> Eugene Frolov wrote:
>
> --
> rsada
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message447789.html
>

BI Accelerator: problem with current day setting

Hi, All!
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?
Hi
I have already seen this problem a while ago. Checkout
http://www.webservertalk.com/showthread.php?t=895520
I don't really understand what the guy that replied means by "Setting
the all tu current".
What I did at the time (and of course that is not the solution) was to
remove the Weel.Standard dimension from the cube.
Did you found a solution.?
Eugene Frolov wrote:
> *Hi, All!
> I'm experimenting with MSSABI and look this problem - after running
> SetCurentDay.dts package every measure values mutiplied by 6.
> Here is my steps:
> 1. Create Analytical, SM and staging databeses with Analytics
> builder
> utility for SMA template (unmodified).
> 2. Manualy run Master Import DTS package for loading sample data in
> Satging
> DB.
> 3. Manualy run Master Update DTS package for loading sample data in
> Subject
> Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1
> for
> automatic processing disable).
> 4. Manualy run processing of Sales cube in Ananlytical DB and browse
> data
> after its finishing.
> Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim
> -
> in rows), calculated members in Time.Standard dim - empty due to
> current
> day is not set.
> 5. Manualy run SetCurrentDay.dts package with gsCurrentDay =
> '10.05.2001'
> (October 5 2001)
> 6. Again manualy run processing of Sales cube in Ananlytical DB and
> browse
> data after its finishing.
> Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard
> dim -
> in rows), calculated members in
> Time.Standard dim have some values.
> This behaviour fist time was occured in my custom Analytical app.
> constructed using MSSABI, the standard template (Sales and
> marketing)
> behaviour is the same.
> Where is the problem - is MSSABI or I'm doing something wrong? *
rsada
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message447789.html
|||Hi!
Checkout this:
http://support.microsoft.com/default...;en-us;834285.
Data in cubes become correct after delete ALL date dims exept only one with
custom rollups.
"rsada" <rsada.1fzsd0@.mail.webservertalk.com> /
: news:rsada.1fzsd0@.mail.webservertalk.com...
> Hi
> I have already seen this problem a while ago. Checkout
> http://www.webservertalk.com/showthread.php?t=895520
> I don't really understand what the guy that replied means by "Setting
> the all tu current".
> What I did at the time (and of course that is not the solution) was to
> remove the Weel.Standard dimension from the cube.
> Did you found a solution.?
>
>
> Eugene Frolov wrote:
>
> --
> rsada
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message447789.html
>

BI Accelerator 1.1 and Bulk Insert

Dear Anyone,

I had been using BI Accelerator 1.1 for 2 years now. In our current project, I encountered the following error during the importing phase of BI.

I traced the problem to the bulk insert statement that BI Accelerator is executing which is ...

BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert] FROM 'C:\Program Files\Microsoft SQL Server Accelerator for BI\data\Project88\Import\S_Fact_BrdcastAlert.txt' WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL Server Accelerator for BI\data\Project88\DTS\Import\Format\S_Fact_Brdcast Alert.fmt' , DATAFILETYPE = 'widechar', BATCHSIZE = 100000 , MAXERRORS = 100 )

The error that it generates is as follows:

Bulk Insert: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

We export data to text files that BI Accelerator picks up. All other files imports properly imported without problems except for one fact table text file. We use the same format file that BI Accelerator uses to export to file the data to make sure that there would be no incompatabilities. File size is about 2.3GB++, which is a standard size with our other fact table files that doesnt have any errors.

We also checked for data error which we found none. We also checked the txt file we generate, notepad confirms that it is a unicode file.

Can anyone please shed a light in what is happening. Is this a bug? As much as possible I dont want to place a workaround on this one since our entire framework for loading data is all the same and has already been set up. Any help would be apreciated.

Thank YouAny reply would be appreciated.

Joseph Ollero
jollero@.wizardsgroup.com

BETWEEN keyword

I need to retrieve records where the date is in between the current date and 4 days previous.

I've tried: WHERE DateSubmitted BETWEEN GetDate() AND GetDate() - 4
it doesn't work...

Can someone help out?You are probably having problems because both the date and the time are used in such comparisons.

See if this post helps you:view post 483129

Terri|||Ok, thanks Terri.

BETWEEN DATES (TIME) HELP

Hi,

I need to display the employees from a current work shift. These are the following work shift:

4 AM – 12 PM

12 PM – 8 PM

8 PM – 4 AM

I’m having problems in third one “8 PM – 4 AM”. The next code is the one that I’m working on. If somebody knows a better way to do this let me know or if somebody can help me with this go ahead.

begin

declare @.from_time varchar(25);

set @.from_time = '8:00:00.000 PM';

declare @.to_time varchar(25);

set @.to_time = '4:00:00.000 AM';

declare @.current_date VARCHAR(25);

set @.current_date = getdate();

--select convert(smalldatetime, substring(@.current_date, 0, 13) + @.from_time),

--convert(smalldatetime, substring(@.current_date, 0, 13) + @.to_time),

--getdate();

if getdate() BETWEEN convert(smalldatetime, substring(@.current_date, 0, 13) + @.from_time) AND

convert(smalldatetime, substring(@.current_date, 0, 13) + @.to_time)

print 'OK';

else

print 'NO';

end

That's because of midnight crossover - the dates are changed also. Try this, it's far more simple and reliable:

Code Snippet

declare @.From int, @.To int, @.DT int

select @.From = 20, @.To = 4, @.DT = datepart(hh, getdate())

if @.DT >= @.From or @.DT < @.To
print 'OK'
else
print 'No'

|||Yeah. Thanks.

Best Way to lockdown SQL Server 2000

I am looking for the best way to lockdown a SQL Server 2000 box. There are
current SQL logins used by exsisting software that have SA credentials.
Without changing creds or using a third part vendor is there something equal
to DDL triggers in 2005? Could I create a trace that always is on?
Thanks for reading.
JasonWhat do you mean exactly by saying "lockdown"?
You do not want anybody to be able to login to the SQL Server?
--
Ekrem Ã?nsoy
"SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
news:6312BD12-BC3A-4A7D-A665-52E4D8B6A46D@.microsoft.com...
>I am looking for the best way to lockdown a SQL Server 2000 box. There are
> current SQL logins used by exsisting software that have SA credentials.
> Without changing creds or using a third part vendor is there something
> equal
> to DDL triggers in 2005? Could I create a trace that always is on?
> Thanks for reading.
> Jason|||I want to prevent people from dropping objects
"Ekrem Ã?nsoy" wrote:
> What do you mean exactly by saying "lockdown"?
> You do not want anybody to be able to login to the SQL Server?
> --
> Ekrem Ã?nsoy
>
> "SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
> news:6312BD12-BC3A-4A7D-A665-52E4D8B6A46D@.microsoft.com...
> >I am looking for the best way to lockdown a SQL Server 2000 box. There are
> > current SQL logins used by exsisting software that have SA credentials.
> > Without changing creds or using a third part vendor is there something
> > equal
> > to DDL triggers in 2005? Could I create a trace that always is on?
> >
> > Thanks for reading.
> >
> > Jason
>|||Hi
If it uses SA you can do NOTHING.
"SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
news:68380513-CF06-492B-9786-9489EE864EBA@.microsoft.com...
>I want to prevent people from dropping objects
> "Ekrem ?nsoy" wrote:
>> What do you mean exactly by saying "lockdown"?
>> You do not want anybody to be able to login to the SQL Server?
>> --
>> Ekrem ?nsoy
>>
>> "SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
>> news:6312BD12-BC3A-4A7D-A665-52E4D8B6A46D@.microsoft.com...
>> >I am looking for the best way to lockdown a SQL Server 2000 box. There
>> >are
>> > current SQL logins used by exsisting software that have SA credentials.
>> > Without changing creds or using a third part vendor is there something
>> > equal
>> > to DDL triggers in 2005? Could I create a trace that always is on?
>> >
>> > Thanks for reading.
>> >
>> > Jason
>>|||There has to be something you can do to track changes for auditing besides
enable C2.
"Uri Dimant" wrote:
> Hi
> If it uses SA you can do NOTHING.
> "SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
> news:68380513-CF06-492B-9786-9489EE864EBA@.microsoft.com...
> >I want to prevent people from dropping objects
> >
> > "Ekrem ?nsoy" wrote:
> >
> >> What do you mean exactly by saying "lockdown"?
> >>
> >> You do not want anybody to be able to login to the SQL Server?
> >>
> >> --
> >> Ekrem ?nsoy
> >>
> >>
> >>
> >> "SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
> >> news:6312BD12-BC3A-4A7D-A665-52E4D8B6A46D@.microsoft.com...
> >> >I am looking for the best way to lockdown a SQL Server 2000 box. There
> >> >are
> >> > current SQL logins used by exsisting software that have SA credentials.
> >> > Without changing creds or using a third part vendor is there something
> >> > equal
> >> > to DDL triggers in 2005? Could I create a trace that always is on?
> >> >
> >> > Thanks for reading.
> >> >
> >> > Jason
> >>
> >>
>
>|||SA's permissions can't be changed. You should create a new user for this job
and give it necessary permissions on your database.
--
Ekrem Ã?nsoy
"SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
news:3DC6C042-01B1-4A39-AE8A-4B7A94038A8C@.microsoft.com...
> There has to be something you can do to track changes for auditing besides
> enable C2.
> "Uri Dimant" wrote:
>> Hi
>> If it uses SA you can do NOTHING.
>> "SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
>> news:68380513-CF06-492B-9786-9489EE864EBA@.microsoft.com...
>> >I want to prevent people from dropping objects
>> >
>> > "Ekrem ?nsoy" wrote:
>> >
>> >> What do you mean exactly by saying "lockdown"?
>> >>
>> >> You do not want anybody to be able to login to the SQL Server?
>> >>
>> >> --
>> >> Ekrem ?nsoy
>> >>
>> >>
>> >>
>> >> "SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
>> >> news:6312BD12-BC3A-4A7D-A665-52E4D8B6A46D@.microsoft.com...
>> >> >I am looking for the best way to lockdown a SQL Server 2000 box.
>> >> >There
>> >> >are
>> >> > current SQL logins used by exsisting software that have SA
>> >> > credentials.
>> >> > Without changing creds or using a third part vendor is there
>> >> > something
>> >> > equal
>> >> > to DDL triggers in 2005? Could I create a trace that always is on?
>> >> >
>> >> > Thanks for reading.
>> >> >
>> >> > Jason
>> >>
>> >>
>>|||Thanks but I was looking for some techinical guidance.
"Ekrem Ã?nsoy" wrote:
> SA's permissions can't be changed. You should create a new user for this job
> and give it necessary permissions on your database.
> --
> Ekrem Ã?nsoy
>
> "SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
> news:3DC6C042-01B1-4A39-AE8A-4B7A94038A8C@.microsoft.com...
> > There has to be something you can do to track changes for auditing besides
> > enable C2.
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> If it uses SA you can do NOTHING.
> >>
> >> "SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
> >> news:68380513-CF06-492B-9786-9489EE864EBA@.microsoft.com...
> >> >I want to prevent people from dropping objects
> >> >
> >> > "Ekrem ?nsoy" wrote:
> >> >
> >> >> What do you mean exactly by saying "lockdown"?
> >> >>
> >> >> You do not want anybody to be able to login to the SQL Server?
> >> >>
> >> >> --
> >> >> Ekrem ?nsoy
> >> >>
> >> >>
> >> >>
> >> >> "SQLCOW" <SQLCOW@.discussions.microsoft.com> wrote in message
> >> >> news:6312BD12-BC3A-4A7D-A665-52E4D8B6A46D@.microsoft.com...
> >> >> >I am looking for the best way to lockdown a SQL Server 2000 box.
> >> >> >There
> >> >> >are
> >> >> > current SQL logins used by exsisting software that have SA
> >> >> > credentials.
> >> >> > Without changing creds or using a third part vendor is there
> >> >> > something
> >> >> > equal
> >> >> > to DDL triggers in 2005? Could I create a trace that always is on?
> >> >> >
> >> >> > Thanks for reading.
> >> >> >
> >> >> > Jason
> >> >>
> >> >>
> >>
> >>
> >>
>

Best way to do a dynamic bulk insert to a table

My current project is creating a social network for the university I work for. One of the features allows members of a group to send a message to all other group members. Currently, I run a foreach loop over each of the group members, and run a separate INSERT statement to insert a message into my messages table. Once the group has several hundreds members, everybody starts getting timeout errors. What is the best way to do this?

Here are two suggestions I've received: construct one sql statement that would contain multiple INSERT statements. It would be a large statement like:

INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user, @.to_user, @.subject, @.body); INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user2, @.to_user2, @.subject2, @.body2); INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user3, @.to_user3, @.subject3, @.body3);

etc...

Or, do the foreach loop in a stored procedure. I know the pros and cons of sprocs versus dynamic sql is a sticky subject, and, personally, I'd prefer to keep my logic in the C# code-behind file. What is the best way to do this is an efficient manner? I'd be happy to share some code, if that would help. Thanks for your input!

I think what you want is something like this. 1 single query.

INSERT into [messages] (from_user, to_user, subject, body)
SELECT @.from_user,userid,@.subject,@.body FROM groupmembers WHERE groupid=@.groupid AND userid<>@.from_user

Your input parameters:

@.from_user = the sending user
@.subject = the subject
@.body = the body
@.groupid=the recieving groupid

|||

If you going to insert data to a table that alreay has some data, I suggest you to use whatgunteman wrote.

If not, I suggest you to use:

1SELECT *2INTO MyNewTable-- it will be created automatically here3FROM MyTable1 t14INNERJOIN5 MyTable2 t26ON (t1.rid = t2.rid)7

Good luck.

|||

Thanks that worked like a charm. I can't believe I never thought of putting a subquery in an insert statement.