sql reports: 2012

Thursday, March 29, 2012

Can we insert NULL in place of foreign key.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now I
tried out turning "Allow Null" on for the column "DepartmentID" in the
"Student" table. As soon as I did this, I was able to insert a row with
"DepartmentID" as <NULL>.
Now isn't this wrong? Or is it that the database overrides the checking of
the foreign key against the table "Department" when I check the "Allow Null"
option ON?
It would be really helpful if someone could elaborate on this.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriHi
Yes, referencing table may accept NULL's. Like people that don't have
children.
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:C5A2D39D-5C28-4661-B68B-B2CA6BA48CDF@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow
> Null"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Aayush Puri wrote:
> I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow Nul
l"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
SQL's foreign key constraints work like check constraints.
Specifically, values are permitted as long as the constraint isn't
violated. A null comparison returns an UNKNOWN result rather than a
FALSE one and UNKNOWN isn't regarded as a violation of the constraint.
So the behaviour you mentioned is "right" according to the SQL
standard. Whether it makes good logical sense is a different matter.
I'd suggest that you either disallow nulls or that you create some
default value in the Depatrtments table to represent the case that you
would otherwise have used a null for - a "Not Applicable" department
for example.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

can we increment the column --autimaticallyis there any possiblity with tiggers

hi,
i am a beginner to ms sql server2000
i have a table
create table ddd (a int, b int)
by table structure is a b
now when i enter a value in b column suppose '2' in column b
bext time when i insert a value in the column a i have to get the value
in b as 3 is thi spossible with triggers
insert into gdg values (1,2)
a b
1 2
insert into gdg (a) values(2)
a b
2 3--> i have to get this 3 automatically
is there any method to get this
pls help me
satishis this homework?sql

Can we include User Defined Functions in the Report Model

I have certain user defined functions in my database.

Can we include User Defined Functions in the Report Model.

Thank you,

Hi-

You can create custom expressions within a model using any combination of the supported semantic query functions in Model Designer. For example, you could create Employee.BirthWeek in a model by using the semantic query function Week(Employee.Birthday). The Employee.BirthWeek could then be consumed by reports through Report Builder.

However, you cannot create custom functions in code and reference them in a model. The underlying query translator would not be able to translate arbitrary code into T-SQL.

Hope that helps.

Thanks, Jon

Can we include a variable field value on the table header.

I am doing a page break on every date change and also set the property of he table header to repeat on each page. By doing this ,I am able to get the table header on each page even on page break. I want the changed date to be displayed on the every page break and in the table header.

Please help.

You should be able to do this without issue. Assuming you have a field which you are pulling from that shows the date, you could do something like this as an expression in the table header.

="Report Header - " & Fields!Date.Value

Hope this helps.

Can we implement Pivot Tables using MSRS?

Can we implement Pivot Tables using Reporting Services 2005? I need to
allow user to select the pivot column at runtime. I mean dynamically
changing the criteria for pivot tables after report is rendered.
Thanks,
YogeshHi,
You can implement pivoting using matrix.
Amarnath.
"Yogi" wrote:
> Can we implement Pivot Tables using Reporting Services 2005? I need to
> allow user to select the pivot column at runtime. I mean dynamically
> changing the criteria for pivot tables after report is rendered.
> Thanks,
> Yogesh
>

Can we hide some parameters

There will be a pararmeters panels when we browse reports.
The user can modify the parameters there.
But there are some parameters I do not want the usrs to modify.
How can I hide these parameters of restrict user to modify?In the report manager go to the report properties and then to the Parameters
section. Specify a default value for the parameter and then delete the prompt
string (DO NOT uncheck the "Prompt user" check box)
Of course, the parameter value will always be the same, no matter which user
runs the report or what he/she does.
I hope this helps.
--
Please mark the correct/helpful answers!

can we have temporary table in store procedure when using BCP ?

hi, good day,

when i run query in query analyzer

exec sp_test '2006-07-21'

it show result , however when i run it using BCP tools

exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '

it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure

how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,

thank you :eek:Hmph. I would have thought that would work. Post the code for sp_test.|||thanks for reply, the query was constructed in this way

CREATE PROCEDURE [SP_TEST]

--------CREATE TEMP TABLE----------
SELECT
*
INTO #TMP_TBL1
FROM
TABLE A, TABLE B

SELECT
*
INTO #TMP_TBL2
FROM
TABLE C, TABLE D

----------BEGINNING OF PROCESS------

SELECT *
FROM
(
SELECT * FROM
#TMP_TBL1
UNION
#TMP_TBL2

)|||You are posting skeleton code. Post the actual code you are using, and the actual error message you receive.|||hi, blindman, sorry , i can't expose my company query and it would be few page query, but i use this simple store procedure to test on , it doesn't work as well

CREATE PROCEDURE [SP_TEST]

AS

SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER

----------------------
SELECT DISTINCT
COUNT(CUST_CD)

FROM
#TMP_CUSTOMER
GO

when i run in query analyzer , using "exec SP_TEST" , it work

but when run
Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:\TEST.TXT -c -Slocalhost -Usa -Ppassword'

it give error "SQLState = S0002, NativeError = 208"
and show
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.

do u have any idea on it ?|||I tried you code and was able to duplicate your error. I tried a few work-arounds, but none of them were succesful. BCP seems to be failing to recognize any table created within the procedure, temporary or permanent.

I have not run into this before, but I have not used BCP very frequently. I am surprised, none the less.

Wait until Monday, and lets see if any other forum members recognize this error.|||Hi

Did a quick RTFM:

Copying Data To or From a Temporary Table

When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:
bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -PpasswordHowever, do not specify the database name when using global temporary tables because temporary tables exist only in tempdb. It is possible to use a local temporary table (for example, #temp_authors) only when bulk copying data using the BULK INSERT statement.The key point appears to be the last line. A rather interesting turn of phrase that says (in a roundabout way) - "you can't".

HTH

EDIT - in fact Nakata that is so poorly written (and assuming English isn't your first language - believe it or not it is my first language and I had to read it a couple of times) it basically says that local temp tables work with BULK INSERT but not (by implication) bcp.|||thanks pootle flump and blindman,
this is bad for me with the answer, i may need to re-write the whole query, my god , that really killing me ...any other alternative i can have if possible no need to re-write all query that involve temporary table :(|||Not a big user of bcp... I wonder if it has the same objection to Table variables...|||Hi,

This is a shortcoming of the way that bcp works, particularly when a stored procedure is used as the command for a "queryout" operation.

bcp needs to understand the format of the results it is about to receive, before it issues the query. If you use the "out" operation, giving a table name, it issues the following command:

SET FMTONLY ON
SELECT * FROM <table name>
SET FMTONLY OFF

If you use the "queryout" operator with an exec statement, it issues the following command:

SET FMTONLY ON
exec ...
SET FMTONLY OFF

Unfortunately, this technique only works with the simplest of stored procedures, where the format of the ouput can be determined WITHOUT ACTUALLY RUNNING THE CODE IN THE PROCEDURE. Your case does not work because #TMP_CUSTOMER does not exist unless you actually run the code.

FYI, a solution with a table variable WILL work, e.g.

<code>
create proc dbo.bills_test
as
begin

declare @.output table (col1... , col2 ... , ...)

insert @.output
select ...

select col1, col2 ...
from @.output

end
<\code>

you can play with this in query analyser, to get a stored proc that will work with bcp...

HTH,

Bill|||thanks everyone for contribution , i really learn a lot from this issue , i finally found that if i replace #tmp_tbl to tempdb..tmp_tbl , it work

will tempdb object will causing any performance issue , i really not sure ..

:beer:|||That may cause problems if more than one user executes your process simultaneously. You should consider using thompbil's suggestion and implementing a table variable.|||but I have not used BCP very frequently.

Oh, for the love of god....

In any case just put the results of the sproc into a table and bcp that out|||Oh, for the love of god....You got a problem wit dat, joisey-boy?|||tell me you use DTS...come on, you can tell us|||USE Northwind
GO

CREATE PROC mySproc99
AS
SET NOCOUNT ON
DECLARE @.myTabVar99 table (Col1 int)
INSERT INTO @.myTabVar99(Col1)
SELECT OrderId FROM Orders

SELECT * FROM @.myTabVar99
GO

EXEC mySproc99

exec master..xp_cmdshell 'bcp "exec Northwind.dbo.mySproc99" queryout c:\test.txt -c -T -S<servername>'

DROP PROC mySproc99
GO|||I do use DTS. To transfer data to staging tables. It's fine for migrating data. I don't do any processing within DTS.
DTS may not have the cache' of command-line utilities, then again DTS would have handled this poster's problem easily.sql

can we have SQL Server 2000 as a back end with RS2005? If yes, then there is no data migra

Any Answer would be appriciated...
TIAYes. You have to have a SQL 2005 license on whatever box RS 2005 is on but
the db used for metadata/object storage can be 2000 (I did this). There is
no lose in functionality and it is totally supported.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<ajmera.puneet@.gmail.com> wrote in message
news:1153982939.763548.198810@.m73g2000cwd.googlegroups.com...
> Any Answer would be appriciated...
> TIA
>|||Bruce L-C [MVP] wrote:
> Yes. You have to have a SQL 2005 license on whatever box RS 2005 is on but
> the db used for metadata/object storage can be 2000 (I did this). There is
> no lose in functionality and it is totally supported.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <ajmera.puneet@.gmail.com> wrote in message
> news:1153982939.763548.198810@.m73g2000cwd.googlegroups.com...
> > Any Answer would be appriciated...
> > TIA
> >
Thanks Bruce.

Can we have a Subreport inside another subreport

I need to have a subreport inside another subreport. Is it possible directly, if not through any other way. Also could anyone give/send me some tutorials on Crystal Reports, even website will be helpful.
Thanks GuysNever tried it, can't see why not. Easiest way to find out would be to give it a go...?|||I think it is possible|||Madhi,

Could you please let me know how? I am also facing the same issue.

Thanks in advace.

MR|||Madhi,

Could you please let me know how? I am also facing the same issue.

Thanks in advace.

MR

Consider you have three reports say Report1, Report2 and Report3
Suppose you want Report3 as subreport of report2 and report2 is subreport of report1

First Design Report1 and Insert report2 as Subreport, then Goto preview of Report2 and now insert report3 as subreport of report2. Save Report1

Now Report1 has the subreport report2 which has other subreport report3|||i would like explain the working as Madhi told.

first create all the three reports individually.
like Report1, Report2, Report3
Report1 (say) Your Main report.
Report2 (Say) Sub Report of Main Report.
Report3 (Say) Sub Report of Report2.

once u have created all the three reports individually you can make them sub of others.

Right Click in the PageFooter of Report1 (Main Report).
Insert new section.
now Right Click on the newly created section.
goto Insert --> SubReport.

if u have parameters the must be same linked togather.

to link sub report with main report RightClick on the subreport section in the main report.
goto Change SubReport Link...
Add the Field u want to link with (Parameter of Main Report).

You Have Gone through process of Adding SubReport.

Similarly u can add sub report of sub report. :cool: :thumb:

Silly Star

Consider you have three reports say Report1, Report2 and Report3
Suppose you want Report3 as subreport of report2 and report2 is subreport of report1

First Design Report1 and Insert report2 as Subreport, then Goto preview of Report2 and now insert report3 as subreport of report2. Save Report1

Now Report1 has the subreport report2 which has other subreport report3|||In theory, your answer sounds correct... but I can't make it work. I'm working in Crystal Reports 10 with DB2 databases. I have 3 reports that I need to join together, which I'll call the primary(A), subreport(B) and mini-subreport(C).

Before realizing I needed 3 reports (thought 2 would do), I had the subreport(B) and mini-subreport(C) joined together just fine. The fields in subreport(B) and the mini-subreport(C) are in the group footer section.

I created a new "outer shell" as the primary(A) report and inserted subreport(B) [with the embedded mini-subreport(C)] in the report header. The mini-subreport(C) disappeared. I am not able to re-insert the mini-subreport(C) into subreport(B) because all insert->subreport options are grayed out.

Can anyone tell me how to have 2 (or more) nested subreports? Do they have to be in a certain section? Is there an order in which subreports need to be added? Any advice would be greatly appreciated.

Thank you!
Gloria|||No you can't have a subreport with in a sub report. A main report may have n number of Sub reports|||No you can't have a subreport with in a sub report. A main report may have n number of Sub reports

It is possible. Did you try the method I suggested?|||It is possible? I tried what you suggested, but options are grayed out when I try to insert a subreport within a subreport. What am I missing?|||I am facing the same problem!! I am using CR-11 with ASP.NET
I have a main report (A) that has one subreport (B); the subreport has two subreports (C and D);
I cannot break this hirerchy because the first subreport (B) can appear independently from some other place.

I tried both ways i.e. If you use the edit subreport from the main report then the Insert subreport option is grayed out;
If you work on subreport then then insert it into main report (A) then also the third subreport is not being displayed.

Any solution for this!!|||I have been struggling with this problem for a few days..

Crystal's support site (the Recommended Reading by support staff (http://support.businessobjects.com/fix/tsreccr.asp) makes it very clear:

LIMITATIONS:
You cannot insert a subreport within a subreport.
You cannot link a subreport to another subreport.

HTH... If someone has a way around this I would love to know - we are reverting to HTML text strings of tables ... argh|||Would you mind sharing your HTML work-around? That sounds like it might work for us.

Thanks!

Can we have a folder as input path for SSIS package, and process all files within it?

My client wish to run a batch process where in a folder he will put number of files, which needs to be processed by ssis package. Is that possible directly in ssis?

Alternate solution that I have thought of is...
1. user will copy all files in c:\folder1
2. a .NET component will
2.1 pick up files from folder1 one by one (process all files in the folder)
2.2 will copy that file in c:\INPUT folder by the name in.xls
2.3 will call SSIS package
2.4 SSIS package will process the file in.xls
2.5 end loop..go back to step 2.1

Do we have any othre way of achieving this task? Any suggestions are welcome. Thanks in advanc.

Regards,
MS Guy

MS Guy wrote:
Can we have a folder as input path for SSIS package, and process all files within it?
My client wish to run a batch process where in a folder he will put number of files, which needs to be processed by ssis package. Is that possible directly in ssis?
Alternate solution that I have thought of is...
1. user will copy all files in c:\folder1
2. a .NET component will
2.1 pick up files from folder1 one by one (process all files in the folder)
2.2 will copy that file in c:\INPUT folder by the name in.xls
2.3 will call SSIS package
2.4 SSIS package will process the file in.xls
2.5 end loop..go back to step 2.1
Do we have any othre way of achieving this task? Any suggestions are welcome. Thanks in advanc.
Regards,
MS Guy

There's a few things that make the answer "It depends" but there's a few things you should look at:
1) MULTIFLATFILE connection manager. This connection manager points to a colelction of files (usually all files in a folder) and you can then apply a single operation (such as loading the contents of all those files into a table) in one step. Usually the metadata of the files needs to be the same - it depends on what operation you're applying.

2) Use a Foreach loop container to loop over the files and process them individually. There's a bit more guidance on this here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx plus Darren and Allan have some good stuff on this at www.sqlis.com.

-Jamie

Can we handle ALL errors within a stored proceudre?

Hello, friends,
We call stored procedures from our app (asp.net), and use Try...Catch to
handle any possible DB error. But, can we handle all errors in a stored
proceudre? In another word, all DB errors should be caught within a stored
procedure, and return back to callers gracefully, as if nothing wrong.
We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
Violation were still caught by our app, not sp.
Any ideas, reference papers, sample source code?
Thanks a lot.http://www.sommarskog.se/error-handling-II.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:59F7E8E6-1477-4A42-9B42-BB34D0136102@.microsoft.com...
> Hello, friends,
> We call stored procedures from our app (asp.net), and use Try...Catch to
> handle any possible DB error. But, can we handle all errors in a stored
> proceudre? In another word, all DB errors should be caught within a stored
> procedure, and return back to callers gracefully, as if nothing wrong.
> We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
> Violation were still caught by our app, not sp.
> Any ideas, reference papers, sample source code?
> Thanks a lot.

Can we handle all errors within a stored proceudre.

Hello, friends,
We are calling stored procedures (sp) from our web app (asp.net). Since
using Try...Catch... in asp.net with C# or VB.net is very expensive, we are
considering to handle all database error in sp so that web app won't see any
DB error.
Do we have a way to catch all erros in sp and return back to callers
gracefully, as if nothing wrong in DB?
We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
Unique contraint violation, etc.
Any ideas, reference papers, sample source code?
Thanks a lotexamnotes <Andrew@.discussions.microsoft.com> wrote in
news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:

> We are calling stored procedures (sp) from our web app (asp.net).
> Since using Try...Catch... in asp.net with C# or VB.net is very
> expensive, we are considering to handle all database error in sp so
> that web app won't see any DB error.
> Do we have a way to catch all erros in sp and return back to callers
> gracefully, as if nothing wrong in DB?
> We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
> Unique contraint violation, etc.
> Any ideas, reference papers, sample source code?
In SQL Server 2005 you can, I've used the following code sample to
demonstrate it in my classes (Using the pubs database):
set XACT_ABORT on
begin try
begin transaction
declare @.id int;
insert into jobs values ('Testjobb',10,10)
set @.id = @.@.identity;
insert into employee values ('123456789','Gates',null,'Bill',@.id+
1,10,'0877',GetDate());
commit transaction
end try
begin catch
select error_message();
rollback transaction
end catch
I'll leave it up to you to implement this in a stored procedure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||What about SQL Server 2000? That is the one we are using.
"Ole Kristian Bang?s" wrote:

> examnotes <Andrew@.discussions.microsoft.com> wrote in
> news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:
>
> In SQL Server 2005 you can, I've used the following code sample to
> demonstrate it in my classes (Using the pubs database):
> set XACT_ABORT on
> begin try
> begin transaction
> declare @.id int;
> insert into jobs values ('Testjobb',10,10)
> set @.id = @.@.identity;
> insert into employee values ('123456789','Gates',null,'Bill',@.id+
> 1,10,'0877',GetDate());
> commit transaction
> end try
> begin catch
> select error_message();
> rollback transaction
> end catch
> I'll leave it up to you to implement this in a stored procedure.
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||examnotes <Andrew@.discussions.microsoft.com> wrote in
news:C25DD758-622D-4CB5-A870-F112B1C337C6@.microsoft.com:

> What about SQL Server 2000? That is the one we are using.
Sorry. This feature was introduced in SQL Server 2005. In SQL Server 2000
you have to check for errors after each and every command that may cause an
error (which may happen to be almost everyone)
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql

Can we get the Next Row Value ?

In SSRS 2005 we have First,Last and Previous functions which get the related values based on the use. I want to compare with the NEXT row value and print it according to my requirement.

My question is How do I get the Next Row Value. Please Help.

Could be tricky, however try using a protected variable in the Code view... something like this (untested):

protected myprevValue as string

public function setString(myString as string) as string

myprevValue = myString
return ""

end function

public function getString() as string
return myprevValue

end function

Then you use a hidden text box with the expression =setString(myString)

If you have multiple values or want to lookup by row number you can use a dictionary object to store and retrieve them.

No guarantees on this and placement of the text boxes are key. I have not tried this with getting the Next value however it did work for doing things like lookups against data outside of the current dataset.

cheers,

Andrew

|||

Hello Andrew,

I tried the way you explained but I was able to get the previous row value only. Is there any other way wherein we can get the Next row's field value.

Please help,

|||

Looking into the future is tough.

You have to iterate through the set once to store the values in a lookup dictionary object, and then iterate again to retrieve. This becomes unmanageable with many fields... hopefully you're only looking up a couple of values here. I have not tried this rownumber method - I was using it to look up descriptive names for keys.

1st table - will contain the set function in a textbox expression passing in the row number and value. This table can be hidden.

=setValue(RowNumber(Nothing),myfield.value)

2nd table - will contain the get function passing in the row number.

=getValue(RowNumber(Nothing)

Code Below

public dictLookup as new System.Collections.Generic.Dictionary(Of String, String)

Function setValue(value as object, value2 as object) as object

dictLookup.Add(value, value2)

return value

End Function

Function getValue(value as object) as object

If dictLookup.ContainsKey(value)

return dictLookup(value)

else

return value

end if

End Function

can we get the deleted data back in SQL 2000

Hi im current using SQL 2000, data in three tables has got deleted on friday,
in the mean time some data is also get added into that tables. after two days
only i recognize the some data are deleted. No i need the deleted data and
also the newly inserted data. what should i do?
--
Sakthivel
SQL Server Developer
INDIAHi
Only if you have BACKUP LOG file issued during this period
"Sakthi" <Sakthi@.discussions.microsoft.com> wrote in message
news:ABD49489-6EC5-475B-ABB9-6F77B5A86903@.microsoft.com...
> Hi im current using SQL 2000, data in three tables has got deleted on
> friday,
> in the mean time some data is also get added into that tables. after two
> days
> only i recognize the some data are deleted. No i need the deleted data and
> also the newly inserted data. what should i do?
> --
> Sakthivel
> SQL Server Developer
> INDIA|||http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sakthi" <Sakthi@.discussions.microsoft.com> wrote in message
news:ABD49489-6EC5-475B-ABB9-6F77B5A86903@.microsoft.com...
> Hi im current using SQL 2000, data in three tables has got deleted on friday,
> in the mean time some data is also get added into that tables. after two days
> only i recognize the some data are deleted. No i need the deleted data and
> also the newly inserted data. what should i do?
> --
> Sakthivel
> SQL Server Developer
> INDIA|||Yes, I have the backlog file. Everyday midnight the fullbackup will be taken
and stored.
Sakthivel
SQL Server Developer
INDIA
"Uri Dimant" wrote:
> Hi
> Only if you have BACKUP LOG file issued during this period
>
> "Sakthi" <Sakthi@.discussions.microsoft.com> wrote in message
> news:ABD49489-6EC5-475B-ABB9-6F77B5A86903@.microsoft.com...
> > Hi im current using SQL 2000, data in three tables has got deleted on
> > friday,
> > in the mean time some data is also get added into that tables. after two
> > days
> > only i recognize the some data are deleted. No i need the deleted data and
> > also the newly inserted data. what should i do?
> > --
> > Sakthivel
> > SQL Server Developer
> > INDIA
>
>|||Is the database in simple or full recovery model?
Are you doing regular transaction log backups?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sakthi" <Sakthi@.discussions.microsoft.com> wrote in message
news:9037CCBC-8515-44C0-9B51-64DA9B27F27C@.microsoft.com...
> Yes, I have the backlog file. Everyday midnight the fullbackup will be taken
> and stored.
>
> --
> Sakthivel
> SQL Server Developer
> INDIA
>
> "Uri Dimant" wrote:
>> Hi
>> Only if you have BACKUP LOG file issued during this period
>>
>> "Sakthi" <Sakthi@.discussions.microsoft.com> wrote in message
>> news:ABD49489-6EC5-475B-ABB9-6F77B5A86903@.microsoft.com...
>> > Hi im current using SQL 2000, data in three tables has got deleted on
>> > friday,
>> > in the mean time some data is also get added into that tables. after two
>> > days
>> > only i recognize the some data are deleted. No i need the deleted data and
>> > also the newly inserted data. what should i do?
>> > --
>> > Sakthivel
>> > SQL Server Developer
>> > INDIA
>>|||Thanks Tibor Karaszi and Uri
--
Sakthivel
SQL Server Developer
INDIA
"Tibor Karaszi" wrote:
> http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sakthi" <Sakthi@.discussions.microsoft.com> wrote in message
> news:ABD49489-6EC5-475B-ABB9-6F77B5A86903@.microsoft.com...
> > Hi im current using SQL 2000, data in three tables has got deleted on friday,
> > in the mean time some data is also get added into that tables. after two days
> > only i recognize the some data are deleted. No i need the deleted data and
> > also the newly inserted data. what should i do?
> > --
> > Sakthivel
> > SQL Server Developer
> > INDIA
>
>

Can we find the list of filenames associated with a DB using VDI ?

Hi all,
I want to find the list of filenames associated with the SQL server instance programmatically. As VDI is concerned with taking backup of SQL server. Can we find the list of filenames associated with a DB using VDI ? or Is there any other way to know filenames?

Thanks in Advance,
Ram

You can always execute sp_helpfile in a database to find the files for that database.

Or select * from sys.database_files

-Sue

Can we find the list of filenames associated with a DB using VDI ?

Thanks in Advance,
Ram

You can always execute sp_helpfile in a database to find the files for that database.

Or select * from sys.database_files

-Sue

Can we extract adjectives using SQL server 2005 text mining?

Do you mean, filter out everything in a text that is not an adjective? Then no, this

is not supported by the Term Extraction transform.

A possible solution is to run Term Extraction with a maximum phrase length of 1, then manually remove the non-adjectives from the list of terms.

Term Lookup will only use the terms available in the table

sql

can we export olap cubes from sql server 2005 analysis service to sql server 2000 analysis servi

Dear all,

Thank you very much for your kind attention.

Is it possible to export olap cubes from sql server 2005 analysis service to sql server 2000 analysis service? And if it is possible, then how can it be done? Really need help for this and thank you very much in advance for your help.

I am looking forward to hearing from you.

With best regards,

Yours sincerely,

Helen

No, it isn't possible to export cubes from AS2005 to AS2K. You'll have to rebuild them from scratch, I'm afraid.

Regards,

Chris

|||

Hi, Chris,

Thank you for your advice.

With best regards,

Yours sincerely,

Helen

Can we encrypt the whole DataBase in Sql Server 2005.

Hi,
I am new to Sql Server. I wanted to know can I encrypt the whole
DataBase in Sql Server 2005. If yes, can somebody guide me step by step
to this. I searched a lot on net. But of no use, as the only help
available is to encrypt columns in the table.
Thanks and Regards
Nitin Verma.SQL 2005 supports column level encryption. So you could encrypt ever
column, but I would not advise it. Check out Laurentiu Cristofor's
blog for some GREAT info on 2005 encryption
(http://blogs.msdn.com/lcris/default.aspx)
Jim Youmans
DBA
St Louis, Missouri
nitinv wrote:
> Hi,
> I am new to Sql Server. I wanted to know can I encrypt the whole
> DataBase in Sql Server 2005. If yes, can somebody guide me step by step
> to this. I searched a lot on net. But of no use, as the only help
> available is to encrypt columns in the table.
> Thanks and Regards
> Nitin Verma.|||In a situation with a need for extreme security, EFS could be used (unless
using a clustered server.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1154354782.895003.215230@.i42g2000cwa.googlegroups.com...
> SQL 2005 supports column level encryption. So you could encrypt ever
> column, but I would not advise it. Check out Laurentiu Cristofor's
> blog for some GREAT info on 2005 encryption
> (http://blogs.msdn.com/lcris/default.aspx)
> Jim Youmans
> DBA
> St Louis, Missouri
>
> nitinv wrote:
>|||Thanks Jim, for your advice. Atleast I am now sure that SQL Server 2005
does not support DataBase Level Encrytion. I will look for other
options now.
Jim Youmans wrote:
[vbcol=seagreen]
> SQL 2005 supports column level encryption. So you could encrypt ever
> column, but I would not advise it. Check out Laurentiu Cristofor's
> blog for some GREAT info on 2005 encryption
> (http://blogs.msdn.com/lcris/default.aspx)
> Jim Youmans
> DBA
> St Louis, Missouri
>
> nitinv wrote:|||Arnie, I appreciate your advice, but if I use EFS, will my Sql Server
be able to read the files in the folder, which has been encrypted.
I guess that it loads the Database files, but shows the Database as
suspected.
May be you could throw some more light on it.|||I have used EFS for database storage and backup file locations. The key is
that the SQL Server Service Account MUST own the EFS folder(s).
When setting this up, I log in as the service account, create the EFS
folder(s), COPY any existing databases to the location (Keep copies until I
am sure that the process went smoothly. Then attach the database.
Usually all goes well.
But if clustering is used, when the cluster fails over, there is problem
with key transfer. So I do not recommend EFS with Clustering.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Nitin Verma" <nitin.verma.in@.gmail.com> wrote in message
news:1154410210.573642.61620@.i42g2000cwa.googlegroups.com...
> Arnie, I appreciate your advice, but if I use EFS, will my Sql Server
> be able to read the files in the folder, which has been encrypted.
> I guess that it loads the Database files, but shows the Database as
> suspected.
> May be you could throw some more light on it.
>|||Thanks Arnie,
I hope it works.
Arnie Rowland wrote:
[vbcol=seagreen]
> I have used EFS for database storage and backup file locations. The key is
> that the SQL Server Service Account MUST own the EFS folder(s).
> When setting this up, I log in as the service account, create the EFS
> folder(s), COPY any existing databases to the location (Keep copies until
I
> am sure that the process went smoothly. Then attach the database.
> Usually all goes well.
> But if clustering is used, when the cluster fails over, there is problem
> with key transfer. So I do not recommend EFS with Clustering.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Nitin Verma" <nitin.verma.in@.gmail.com> wrote in message
> news:1154410210.573642.61620@.i42g2000cwa.googlegroups.com...

Tuesday, March 27, 2012

Can we encrypt a report in reporting services?

thanks in advance

Hi Chandra,

At what point do you want to encrypt the report? The actual renderered report? The report definition? The data that makes up the report?

Reporting Services does not have special encryption for a renderered report. Instead, the technologies that RS uses have built in encryption. SQL Server, IIS with SSL, File shares where a report may be stored, encrypted email, etc...

|||

That is fine brad and thanks for the update.

But what is that msdn2 talks at Initializing a Report Server (http://msdn2.microsoft.com/en-us/library/ms157133.aspx) is it report server database encryption? How does that helps some one looking for reports encryption?

could you explain that?

thanks

Can we edit the relationship in Database diagram

I am using SQL 2005.
I have define a relationship between two tables in Database Diagrams.
I want to change setting of the relationship, but I can't find out how to
modify it.
Can we edit the relationship in Database diagram?
You can , right click on any of the tables in the database diagram,
click on the relationship
Chirag Shah
ad wrote:
> I am using SQL 2005.
> I have define a relationship between two tables in Database Diagrams.
> I want to change setting of the relationship, but I can't find out how to
> modify it.
> Can we edit the relationship in Database diagram?

Can we edit the relationship in Database diagram

I am using SQL 2005.
I have define a relationship between two tables in Database Diagrams.
I want to change setting of the relationship, but I can't find out how to
modify it.
Can we edit the relationship in Database diagram?You can , right click on any of the tables in the database diagram,
click on the relationship
Chirag Shah
ad wrote:
> I am using SQL 2005.
> I have define a relationship between two tables in Database Diagrams.
> I want to change setting of the relationship, but I can't find out how to
> modify it.
> Can we edit the relationship in Database diagram?sql

Can we edit the relationship in Database diagram

I am using SQL 2005.
I have define a relationship between two tables in Database Diagrams.
I want to change setting of the relationship, but I can't find out how to
modify it.
Can we edit the relationship in Database diagram?You can , right click on any of the tables in the database diagram,
click on the relationship
Chirag Shah
ad wrote:
> I am using SQL 2005.
> I have define a relationship between two tables in Database Diagrams.
> I want to change setting of the relationship, but I can't find out how to
> modify it.
> Can we edit the relationship in Database diagram?

Can we drill through to details for calculated members?

Hi, experts,

Can we drill down to details for calculated members of a cube? Or we can only drill through to details for measures residing in a specific measure group in a cube?

Hope it is clear for your help.

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kind regards,

Yours sincerely,

Drilldown (navigating in a hirarchy tree) is possible through all members - even calculated once

I guess what you ask is drillthrough (http://technet.microsoft.com/en-us/library/ms145964.aspx) and this works only on base measures as far as I know. (SQL Server 2005)

HANNES

|||

Hi, Hannes,

Thanks a lot for your help.

Sorry for the mistyping, yes, I was asking for 'drill through' capability for calculated members.

Thanks again.

With kindest regards,

Yours sincerely,.

|||

Hello you two,

Today I searched a solution for this problem too.

And I came to the same result as you are.

Then my result is right … I hope so =)

Have a nice day

Alex

|||

Hello experts,

I’ve got an idea but I don’t know how to continue ….

I thought if you can’t make a drillthrough on a calculated member I can program one.

On the website Microsoft technet I found the code snippet:

DRILLTHROUGH [MAXROWS Unsigned_Integer] <MDX SELECT statement> [RETURN Set_of_Attributes_and_Measures [,Set_of_Attributes_and_Measures ...] ]

But where is the point to take it in?

Best regards

Alex

|||

Hi,

if your question is- with which programm could you execute an drillthrough statement?

SQL Server Management Studio - its a regular MDX Statement.

Best regards, HANNES

|||

Hello Hannes,

Thanks for the response. I know that, but I searched for a possibility to upgrade the cube through programming some parts.

Best regards, Alex

Can we drill down to details for calculated members?

Hi, experts,

Can we drill down to details for calculated members of a cube? Or we can only drill through to details for measures residing in a specific measure group in a cube?

Hope it is clear for your help.

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kind regards,

Yours sincerely,

Drilldown (navigating in a hirarchy tree) is possible through all members - even calculated once

I guess what you ask is drillthrough (http://technet.microsoft.com/en-us/library/ms145964.aspx) and this works only on base measures as far as I know. (SQL Server 2005)

HANNES

|||

Hi, Hannes,

Thanks a lot for your help.

Sorry for the mistyping, yes, I was asking for 'drill through' capability for calculated members.

Thanks again.

With kindest regards,

Yours sincerely,.

|||

Hello you two,

Today I searched a solution for this problem too.

And I came to the same result as you are.

Then my result is right … I hope so =)

Have a nice day

Alex

|||

Hello experts,

I’ve got an idea but I don’t know how to continue ….

I thought if you can’t make a drillthrough on a calculated member I can program one.

On the website Microsoft technet I found the code snippet:

DRILLTHROUGH [MAXROWS Unsigned_Integer] <MDX SELECT statement> [RETURN Set_of_Attributes_and_Measures [,Set_of_Attributes_and_Measures ...] ]

But where is the point to take it in?

Best regards

Alex

|||

Hi,

if your question is- with which programm could you execute an drillthrough statement?

SQL Server Management Studio - its a regular MDX Statement.

Best regards, HANNES

|||

Hello Hannes,

Thanks for the response. I know that, but I searched for a possibility to upgrade the cube through programming some parts.

Best regards, Alex

Can we drill down to details for calculated members?

Hi, experts,

Can we drill down to details for calculated members of a cube? Or we can only drill through to details for measures residing in a specific measure group in a cube?

Hope it is clear for your help.

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kind regards,

Yours sincerely,

Drilldown (navigating in a hirarchy tree) is possible through all members - even calculated once

I guess what you ask is drillthrough (http://technet.microsoft.com/en-us/library/ms145964.aspx) and this works only on base measures as far as I know. (SQL Server 2005)

HANNES

|||

Hi, Hannes,

Thanks a lot for your help.

Sorry for the mistyping, yes, I was asking for 'drill through' capability for calculated members.

Thanks again.

With kindest regards,

Yours sincerely,.

|||

Hello you two,

Today I searched a solution for this problem too.

And I came to the same result as you are.

Then my result is right … I hope so =)

Have a nice day

Alex

|||

Hello experts,

I’ve got an idea but I don’t know how to continue ….

I thought if you can’t make a drillthrough on a calculated member I can program one.

On the website Microsoft technet I found the code snippet:

DRILLTHROUGH [MAXROWS Unsigned_Integer] <MDX SELECT statement> [RETURN Set_of_Attributes_and_Measures [,Set_of_Attributes_and_Measures ...] ]

But where is the point to take it in?

Best regards

Alex

|||

Hi,

if your question is- with which programm could you execute an drillthrough statement?

SQL Server Management Studio - its a regular MDX Statement.

Best regards, HANNES

|||

Hello Hannes,

Thanks for the response. I know that, but I searched for a possibility to upgrade the cube through programming some parts.

Best regards, Alex

Can we drill down to details for calculated members?

Hi, experts,

Can we drill down to details for calculated members of a cube? Or we can only drill through to details for measures residing in a specific measure group in a cube?

Hope it is clear for your help.

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kind regards,

Yours sincerely,

Drilldown (navigating in a hirarchy tree) is possible through all members - even calculated once

I guess what you ask is drillthrough (http://technet.microsoft.com/en-us/library/ms145964.aspx) and this works only on base measures as far as I know. (SQL Server 2005)

HANNES

|||

Hi, Hannes,

Thanks a lot for your help.

Sorry for the mistyping, yes, I was asking for 'drill through' capability for calculated members.

Thanks again.

With kindest regards,

Yours sincerely,.

|||

Hello you two,

Today I searched a solution for this problem too.

And I came to the same result as you are.

Then my result is right … I hope so =)

Have a nice day

Alex

|||

Hello experts,

I’ve got an idea but I don’t know how to continue ….

I thought if you can’t make a drillthrough on a calculated member I can program one.

On the website Microsoft technet I found the code snippet:

DRILLTHROUGH [MAXROWS Unsigned_Integer] <MDX SELECT statement> [RETURN Set_of_Attributes_and_Measures [,Set_of_Attributes_and_Measures ...] ]

But where is the point to take it in?

Best regards

Alex

|||

Hi,

if your question is- with which programm could you execute an drillthrough statement?

SQL Server Management Studio - its a regular MDX Statement.

Best regards, HANNES

|||

Hello Hannes,

Thanks for the response. I know that, but I searched for a possibility to upgrade the cube through programming some parts.

Best regards, Alex

sql

Can we drill down to details for calculated members?

Hi, experts,

Can we drill down to details for calculated members of a cube? Or we can only drill through to details for measures residing in a specific measure group in a cube?

Hope it is clear for your help.

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kind regards,

Yours sincerely,

Drilldown (navigating in a hirarchy tree) is possible through all members - even calculated once

I guess what you ask is drillthrough (http://technet.microsoft.com/en-us/library/ms145964.aspx) and this works only on base measures as far as I know. (SQL Server 2005)

HANNES

|||

Hi, Hannes,

Thanks a lot for your help.

Sorry for the mistyping, yes, I was asking for 'drill through' capability for calculated members.

Thanks again.

With kindest regards,

Yours sincerely,.

|||

Hello you two,

Today I searched a solution for this problem too.

And I came to the same result as you are.

Then my result is right … I hope so =)

Have a nice day

Alex

|||

Hello experts,

I’ve got an idea but I don’t know how to continue ….

I thought if you can’t make a drillthrough on a calculated member I can program one.

On the website Microsoft technet I found the code snippet:

DRILLTHROUGH [MAXROWS Unsigned_Integer] <MDX SELECT statement> [RETURN Set_of_Attributes_and_Measures [,Set_of_Attributes_and_Measures ...] ]

But where is the point to take it in?

Best regards

Alex

|||

Hi,

if your question is- with which programm could you execute an drillthrough statement?

SQL Server Management Studio - its a regular MDX Statement.

Best regards, HANNES

|||

Hello Hannes,

Thanks for the response. I know that, but I searched for a possibility to upgrade the cube through programming some parts.

Best regards, Alex

Can we drill down reports in a another different pages with different layouts?

Hi, all experts here,

Thank you for your kind attention.

I am wondering if we can drill down reports in a different page with different layouts and columns? e.g. I have too many columns desired to see in a report, so I want to direct the users to another page with different layouts and columns, so the first view in the report wont display so many columns in a page. Is it possible to make it in SQL Server 2005 Reporting Services? And if it is possible then how? Hope my question is clear for your help.

Thanks a lot in advance and I am looking forward to hearing from you.

With best regards,

Yours sincerely,

Yes, you can use the Action property of your column textbox. When you look at the options, you can either jump to a new report or to a bookmark or to an URL. In your case, you may like to use Jump to Bookmark. Use BookmarkID for the tables or lists which are displayed in subsequent pages and use that ID to jump to, from your first page columns.

Shyam

|||

Hi, Shyam,

Thank you for your kind help and advice.

Yes, in the navigation property (action property), I can set the actions accordingly. But the pages I am going to will still get the same columns as in the first page. What I want is drill down to a page with different columns displayed? Is that possible? Thank you very much. And I am looking forward to hearing from you further.

With best regards,

Yours sincerely,

|||

In that case, you may have to navigate to a subreport which will have different layout. Create a subreport and jump to this subreport providing the necessary parameters.

Shyam

|||

Hi, Shyam,

Thanks a lot for your kind help.

With best regards,

Yours sincerely,

Can we download the DTS Designer?

I am using SQL Server Express 2005 CPT April, and I would like to use the DTS Designer with it. I have been looking for a download but have not found it yet. Does anyone know where I can download it from or if DTS Designer is included in the tools that come with SQL Server Express? I haven't gotten those tools working yet.

ThanksIf you mean the Integration Services (we renamed for SQL Server 2005) then no it can not be downloaded for use with Express(this is the same story as MSDE in 2000). You should download the Developer, Std or Ent SKUs from June as the IS designer is part of each of these.

-Euan

Can we do Transaction log backup and log shipping on same server.

Hi,
I know that Log Shipping and Transaction log backup cannot be
done on same primary server side by side because i tried it, but i want
possible reason for that.
Next i want to know is that can we Detach and re Atach Log
Shipping Database on secondary server in a Standby mode (as you know
this database is itself in standby mode before detaching).
thanks and regards,
Sajid.What's version are you using?
<csajid@.gmail.com> wrote in message
news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> Hi,
> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
> thanks and regards,
> Sajid.
>|||> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
know, log backups has to be performed in sequence. So if the first log backup is shipped to the
other server, and the second is done by you and not shipped to the server, then the third log backup
done my log shipping won't restore as the second haven't been restored. It is possible that the
built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
thing to do.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
No.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> Hi,
> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
> thanks and regards,
> Sajid.
>|||Hi All,
Thanks for this reply.
It clears my all doubt.
thanks again.
Regards,
Sajid.
Tibor Karaszi wrote:
> > I know that Log Shipping and Transaction log backup cannot be
> > done on same primary server side by side because i tried it, but i want
> > possible reason for that.
> You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
> know, log backups has to be performed in sequence. So if the first log backup is shipped to the
> other server, and the second is done by you and not shipped to the server, then the third log backup
> done my log shipping won't restore as the second haven't been restored. It is possible that the
> built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
> thing to do.
>
> > Next i want to know is that can we Detach and re Atach Log
> > Shipping Database on secondary server in a Standby mode (as you know
> > this database is itself in standby mode before detaching).
> No.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> > Hi,
> >
> > I know that Log Shipping and Transaction log backup cannot be
> > done on same primary server side by side because i tried it, but i want
> > possible reason for that.
> >
> > Next i want to know is that can we Detach and re Atach Log
> > Shipping Database on secondary server in a Standby mode (as you know
> > this database is itself in standby mode before detaching).
> >
> > thanks and regards,
> > Sajid.
> >|||Hi All,
I want to schedule a job in ms sql server 2000 which will
copy the backup file from one server whose drive is mapped to
destination server where the backup file should get copied.
After copying the file i want to restore it on a same
destination server.
I can restore it with the help of Restore Database with move
option command, but the problem is the backup file is in format like
'sample_db_200605122100.bak' where sample is database name and numbers
indicate the date and time when backup was taken.
Now, i want to make a batch file which will delete the
previous same backup file, copy the backup file and rename it to
standard filename say 'sample_db_backup.bak'.
This batch file i can shedule to run through ms sql server
jobs by using 'xp_cmdshell'.
Please revert me on this ASAP.
Thanks and Regards,
Sajid.
csajid@.gmail.com wrote:
> Hi All,
> Thanks for this reply.
> It clears my all doubt.
> thanks again.
> Regards,
> Sajid.
> Tibor Karaszi wrote:
> > > I know that Log Shipping and Transaction log backup cannot be
> > > done on same primary server side by side because i tried it, but i want
> > > possible reason for that.
> >
> > You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
> > know, log backups has to be performed in sequence. So if the first log backup is shipped to the
> > other server, and the second is done by you and not shipped to the server, then the third log backup
> > done my log shipping won't restore as the second haven't been restored. It is possible that the
> > built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
> > thing to do.
> >
> >
> > > Next i want to know is that can we Detach and re Atach Log
> > > Shipping Database on secondary server in a Standby mode (as you know
> > > this database is itself in standby mode before detaching).
> >
> > No.
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> > > Hi,
> > >
> > > I know that Log Shipping and Transaction log backup cannot be
> > > done on same primary server side by side because i tried it, but i want
> > > possible reason for that.
> > >
> > > Next i want to know is that can we Detach and re Atach Log
> > > Shipping Database on secondary server in a Standby mode (as you know
> > > this database is itself in standby mode before detaching).
> > >
> > > thanks and regards,
> > > Sajid.
> > >sql

Can we do the Validations in bcp format file

Hi
I need few clarifications. I am using bcp 9.0 tool to load the data into the
table in SQL Server 2005.
Can we do validations in bcp format file.
The validations like
1. if the datafile field data is "ABC" I want to insert the data into table
as "DEF" or if the datafile field data is "123" I want to insert the data
into table as "456" .
2. Format the date filed and insert the formated date data into table column
.
3. Assign default values or Constant values for the columns of the table,
the data for those fields are not exist in the date file
Thanks in Advance
Regards
VeeruVeeru (Veeru@.discussions.microsoft.com) writes:
> I need few clarifications. I am using bcp 9.0 tool to load the data into
> the table in SQL Server 2005.
> Can we do validations in bcp format file.
> The validations like
> 1. if the datafile field data is "ABC" I want to insert the data into
> table as "DEF" or if the datafile field data is "123" I want to insert
> the data into table as "456" .
> 2. Format the date filed and insert the formated date data into table
> column.
> 3. Assign default values or Constant values for the columns of the table,
> the data for those fields are not exist in the date file
I've answered this question in .tools. Please don't post the same question
independently to multiple newsgroups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

can we do string manipulation on a parameter?

I populated a parameter list with dataset.
The parameter @.type_year has items look like
"plan_2000" or "Actual_2000".
Now my question is, if we can use some string function targeting @.type_year in the query.
something like "select ..... from ... where
year=getyear(@.type_year) and type=gettype(@.type_year)" ?
Please let me know if you know the feasibility. Thanks.
Sincerelyyou can. regarding your SQL database its easier to do.
for example in SQL Serbver, you can create 2 functions to extract the year
part or the type part of your parameter. (using the SQL substring function)
but I recommand to use 2 parameters
* 1 for the year
* 1 for the type
"Frank RS" <FrankRS@.discussions.microsoft.com> a écrit dans le message de
news:2B0E0EA7-2955-4B9A-BEFA-053ECA609CB9@.microsoft.com...
> I populated a parameter list with dataset.
> The parameter @.type_year has items look like
> "plan_2000" or "Actual_2000".
> Now my question is, if we can use some string function targeting
@.type_year in the query.
> something like "select ..... from ... where
> year=getyear(@.type_year) and type=gettype(@.type_year)" ?
> Please let me know if you know the feasibility. Thanks.
>
> Sincerely

Can we do Even Programming in MS SQL 2005 Reporting Services ?

Hi,

Can anyone help me in this ?

Can we do event drive Programming things only in SQL 2005 Reporting Services without using VB.net ?

I want that on mouse move event or on focus on some particular cell of table or matrix a hidden picture or graph is visible.

I am using DUNDA's Plugins in my report.

Can anyone tell me how i can do this ?

RS_DBA

Do you have Visual Studio 2005? You can do event driven programming with that and use the report viewer to show your SQL reports. It allows you to easily create a front end for you reports.|||

Hi,

This is my limitation i have to use only SQL 2005 reporting service.

Can we do a double Cursor?

Can we do a double Cursor where we select the data form one table using a
cursor and then use the key data such as ORDER number to select data from
another table using a cursor?
regards,
RonYou can. You can also try to take your bike on the freeway at rush hour and
challenge a street racer...
Any reason you can't do this with a simple join? What exactly are you
trying to accomplish?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
<Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
> Can we do a double Cursor where we select the data form one table using a
> cursor and then use the key data such as ORDER number to select data from
> another table using a cursor?
> --
> regards,
> Ron|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I have absolutely no idea what an "SQR program" is. Do you mean square
root?
Anyway, a double cursor would look like this:
CREATE TABLE #pk
(
id INT PRIMARY KEY
)
CREATE TABLE #fk
(
id int FOREIGN KEY REFERENCES #pk(id),
foo VARCHAR(2)
)
SET NOCOUNT ON
INSERT #pk
SELECT 1
UNION SELECT 2
INSERT #fk
SELECT 1, 'a'
UNION SELECT 1, 'b'
UNION SELECT 2, 'c'
DECLARE @.pkID INT, @.foo VARCHAR(2)
DECLARE c1 CURSOR FOR
SELECT id FROM #pk
OPEN c1
FETCH NEXT FROM c1 INTO @.pkID
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE c2 CURSOR FOR
SELECT foo FROM #fk WHERE id = @.pkID
OPEN c2
FETCH NEXT FROM c2 INTO @.foo
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Outer loop: '+RTRIM(@.pkID)
PRINT 'Inner loop: '+@.foo
FETCH NEXT FROM c2 INTO @.foo
END
CLOSE c2
DEALLOCATE c2
FETCH NEXT FROM c1 INTO @.pkID
END
CLOSE c1
DEALLOCATE c1
DROP TABLE #fk, #pk
Please don't do this on a production system. If you do, don't tell them I
told you how to do it. I will deny it and tell them it was Celko, spoofing
my IP and all.
On 3/22/05 8:03 PM, in article
9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
<Ronhayim@.discussions.microsoft.com> wrote:

> Im trying to simulate SQR program. Do you have an example where you use
> double cursor.|||On Tue, 22 Mar 2005 15:41:02 -0800, Ron wrote:

>Can we do a double Cursor where we select the data form one table using a
>cursor and then use the key data such as ORDER number to select data from
>another table using a cursor?
Hi Ron,
Even if you really do need a cursor (which I doubt - and the statistics
are on my side), there is really no need to use two of the beasts.
Why not create one query that joins the two tables the way you want them
to be joined, filters rows you don't need and returns only the columns
you need? Then, if you really must, you can always use that query for
your cursor...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The script was a lot of help. SQR is a program that used by Peoplesoft.
This was just a special need. All of you right, just joining the tables
should do the it.
"Aaron [SQL Server MVP]" wrote:

> I have absolutely no idea what an "SQR program" is. Do you mean square
> root?
> Anyway, a double cursor would look like this:
>
> CREATE TABLE #pk
> (
> id INT PRIMARY KEY
> )
> CREATE TABLE #fk
> (
> id int FOREIGN KEY REFERENCES #pk(id),
> foo VARCHAR(2)
> )
> SET NOCOUNT ON
> INSERT #pk
> SELECT 1
> UNION SELECT 2
> INSERT #fk
> SELECT 1, 'a'
> UNION SELECT 1, 'b'
> UNION SELECT 2, 'c'
> DECLARE @.pkID INT, @.foo VARCHAR(2)
> DECLARE c1 CURSOR FOR
> SELECT id FROM #pk
> OPEN c1
> FETCH NEXT FROM c1 INTO @.pkID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE c2 CURSOR FOR
> SELECT foo FROM #fk WHERE id = @.pkID
> OPEN c2
> FETCH NEXT FROM c2 INTO @.foo
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Outer loop: '+RTRIM(@.pkID)
> PRINT 'Inner loop: '+@.foo
> FETCH NEXT FROM c2 INTO @.foo
> END
> CLOSE c2
> DEALLOCATE c2
> FETCH NEXT FROM c1 INTO @.pkID
> END
> CLOSE c1
> DEALLOCATE c1
> DROP TABLE #fk, #pk
>
> Please don't do this on a production system. If you do, don't tell them I
> told you how to do it. I will deny it and tell them it was Celko, spoofin
g
> my IP and all.
>
> On 3/22/05 8:03 PM, in article
> 9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
> <Ronhayim@.discussions.microsoft.com> wrote:
>
>

Can we distribute MSDE2000A.exe?

The MSDE redistribution agreement is unclear (to me). It talks about
redistributing specific parts of MSDE. I just want to put the whole
MSDE2000A.exe setup program on a CD with my app's setup program. Is that
allowed?
Thanks!
J.
hi,
Process Patrol wrote:
> The MSDE redistribution agreement is unclear (to me). It talks about
> redistributing specific parts of MSDE. I just want to put the whole
> MSDE2000A.exe setup program on a CD with my app's setup program. Is
> that allowed?
yes... you only have to (freely) register for distributions rights at
http://www.microsoft.com/sql/msde/ho...stregister.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Can we disable SA Account in SQL Server.

Hi,
I am running SQL Server 2000 SP3. For Security purpose i want to
disable SA Account.
But i am not sure that, we can disable SA account.
Please give me reply.
Thanks & Regards,
Sajid C.Hi
You cannot disable SA account. You can remove BUILTIN/Administrators login
to ensure that users of Windows admin group do not get autamically access
to SQL Server
<csajid@.gmail.com> wrote in message
news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> Hi,
> I am running SQL Server 2000 SP3. For Security purpose i want to
> disable SA Account.
> But i am not sure that, we can disable SA account.
> Please give me reply.
> Thanks & Regards,
> Sajid C.
>|||Uri Dimant wrote:
[vbcol=seagreen]
> Hi
> You cannot disable SA account. You can remove BUILTIN/Administrators login
> to ensure that users of Windows admin group do not get autamically access
> to SQL Server
>
> <csajid@.gmail.com> wrote in message
> news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
If you are usin SQL Server 2005 you can disable it by
alter login sa disable
even you can rename it
alter login sa with name = <new name for sa>
Regards
Amish Shah
http://shahamishm.tripod.com|||amish
> disable SA Account.
Yep, but he is using SQL Server 2000
"amish" <shahamishm@.gmail.com> wrote in message
news:1155030451.500469.238540@.m73g2000cwd.googlegroups.com...
> Uri Dimant wrote:
>
> If you are usin SQL Server 2005 you can disable it by
> alter login sa disable
> even you can rename it
> alter login sa with name = <new name for sa>
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>|||With SQL 2000, Give the sa account a complex and strong password, lock the
password in a safe, and don't allow anyone to use the sa account.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<csajid@.gmail.com> wrote in message
news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> Hi,
> I am running SQL Server 2000 SP3. For Security purpose i want to
> disable SA Account.
> But i am not sure that, we can disable SA account.
> Please give me reply.
> Thanks & Regards,
> Sajid C.
>|||Hi all,
Thanks for your Reply.
Thanks & Regards,
Sajid C.
Arnie Rowland wrote:[vbcol=seagreen]
> With SQL 2000, Give the sa account a complex and strong password, lock the
> password in a safe, and don't allow anyone to use the sa account.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <csajid@.gmail.com> wrote in message
> news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...

Can we disable SA Account in SQL Server.

Hi,
I am running SQL Server 2000 SP3. For Security purpose i want to
disable SA Account.
But i am not sure that, we can disable SA account.
Please give me reply.
Thanks & Regards,
Sajid C.Hi
You cannot disable SA account. You can remove BUILTIN/Administrators login
to ensure that users of Windows admin group do not get autamically access
to SQL Server
<csajid@.gmail.com> wrote in message
news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> Hi,
> I am running SQL Server 2000 SP3. For Security purpose i want to
> disable SA Account.
> But i am not sure that, we can disable SA account.
> Please give me reply.
> Thanks & Regards,
> Sajid C.
>|||Uri Dimant wrote:
> Hi
> You cannot disable SA account. You can remove BUILTIN/Administrators login
> to ensure that users of Windows admin group do not get autamically access
> to SQL Server
>
> <csajid@.gmail.com> wrote in message
> news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > I am running SQL Server 2000 SP3. For Security purpose i want to
> > disable SA Account.
> >
> > But i am not sure that, we can disable SA account.
> >
> > Please give me reply.
> >
> > Thanks & Regards,
> > Sajid C.
> >
> >
If you are usin SQL Server 2005 you can disable it by
alter login sa disable
even you can rename it
alter login sa with name = <new name for sa>
Regards
Amish Shah
http://shahamishm.tripod.com|||amish
>> I am running SQL Server 2000 SP3. For Security purpose i want to
> disable SA Account.
Yep, but he is using SQL Server 2000
"amish" <shahamishm@.gmail.com> wrote in message
news:1155030451.500469.238540@.m73g2000cwd.googlegroups.com...
> Uri Dimant wrote:
>> Hi
>> You cannot disable SA account. You can remove BUILTIN/Administrators
>> login
>> to ensure that users of Windows admin group do not get autamically
>> access
>> to SQL Server
>>
>> <csajid@.gmail.com> wrote in message
>> news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
>> > Hi,
>> >
>> > I am running SQL Server 2000 SP3. For Security purpose i want to
>> > disable SA Account.
>> >
>> > But i am not sure that, we can disable SA account.
>> >
>> > Please give me reply.
>> >
>> > Thanks & Regards,
>> > Sajid C.
>> >
>> >
> If you are usin SQL Server 2005 you can disable it by
> alter login sa disable
> even you can rename it
> alter login sa with name = <new name for sa>
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>|||With SQL 2000, Give the sa account a complex and strong password, lock the
password in a safe, and don't allow anyone to use the sa account.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<csajid@.gmail.com> wrote in message
news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> Hi,
> I am running SQL Server 2000 SP3. For Security purpose i want to
> disable SA Account.
> But i am not sure that, we can disable SA account.
> Please give me reply.
> Thanks & Regards,
> Sajid C.
>|||Hi all,
Thanks for your Reply.
Thanks & Regards,
Sajid C.
Arnie Rowland wrote:
> With SQL 2000, Give the sa account a complex and strong password, lock the
> password in a safe, and don't allow anyone to use the sa account.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <csajid@.gmail.com> wrote in message
> news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > I am running SQL Server 2000 SP3. For Security purpose i want to
> > disable SA Account.
> >
> > But i am not sure that, we can disable SA account.
> >
> > Please give me reply.
> >
> > Thanks & Regards,
> > Sajid C.
> >

Can we directly execute a text file that contains all sql commands

Hi, friends,
We have our stored procedures (sp) saved in a .sql file, one for each. When
we want to create an sp, we open the corresponding .sql file using notepad o
r
sql query analyzer, and then execute it.
Do we have a way that we just need to pass those .sql files' name, including
full path, and run all those sp creation sql code in a batch, without
openning each .sql file one by one?
Thanks a lot for your help.Yes, use osql.
exec master..xp_cmdshell 'osql ...'
You can look up the syntax for osql in Books Online...
A
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.|||You can use the command line tools for that. Look up 'osql' in BOL.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.

Thursday, March 29, 2012

Tuesday, March 27, 2012

Blog Archive

About Me