display SQL Server: February 2012

Friday, February 24, 2012

How do I enable 2005''s taskpad ?

I created a new SQL 2005 DB with reporting server installed. I can run reports for any DB on this server only. I have some 5 others servers(SQL 2000) connected via Microsoft SQL server management studio. How can I enable the reporting services to report on functions(similar to taskpad on 2000) on these servers too ? The report buttons are grayed out when I expand those servers trees under the mgt studio. I'm a SQL 2K user just learning SQL 2005.

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

If managing a 2005 server you can access reports by viewing the summary page (f7) and selecting a report from the drop down. You don't need reporting services installed to use this feature. The reports available is dependent on the node selected in the object explorer.

|||So are there any options or should we just run the MMC for SQL 2000 to get maintenance data from our SQL 2000 server? What are other people doing for things like disk usage and such?
|||An even better question... since there are no reports in SQL 2005 for a SQL DB running in compatability mode - what are people doing?
|||

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select p.plan_id, p.plan_name from sysdbmaintplans p, sysdbmaintplan_databases d where (d.database_name = 'All Databases' or d.database_name = 'All User Databases' or d.database_name = N'YOURDATABASENAME') and (p.plan_id = d.plan_id)

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

select sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName, sysindexes.rows,
case indid when 1 then 1 else 0 end as IsClusteredIndex,
sysindexes.indid, sysobjects.name, sysusers.name
from sysusers, sysobjects, sysindexes
where sysusers.uid = sysobjects.uid
and sysindexes.id = sysobjects.id
and sysobjects.name not like '#%'
and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by ObjectName, IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'), IndexName

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

How do I enable 2005's taskpad ?

Thanks all

I'm moving this thread to the Tools General forum. Hopefully then can help you out. If not, try the Reporting Services forum.

-Jeffrey

|||

Whilst managing 2000 servers from management studio there is no equivalent to Taskpad.

I guess I've been waiting on somebody else to do it... maybe it's been done and I haven't found it yet, but...

Most of the queries run by Taskpad work just fine against SQL 2000 or 2005, so creating a custom report that does the same things shouldn't be TOO hard.

among other things, Taskpad runs the following queries:

Code Snippet

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

select backup_finish_date from backupset where type = 'D' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'I' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

select backup_finish_date from backupset where type = 'L' and database_name = N'YOURDATABASENAME' order by backup_finish_date desc

For the Table Info tab, Enterprise Manager gets a list of tables/indexes using this query:

Code Snippet

and then runs sp_spaceused for each individual table (at least for those shown on each page).

Wish I had more free time to create something... anyone else wanna give it a shot?

|||

We have written a custom report for this for SQL2005

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx

Unfortunately it Management studio only allows custom reports against SQL2005 databases and in 90 compatibility. I have heard that might change in the future.

If you feel strongly about it vote for it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240476

|||Wow... I thought this was going to slip into the ether. Thanks cborden and SimonS! Major help.

how do i empty the transaction log

good day to everybody

I have here again encountered the problem that the transaction log came too big that it consume my 10 gig hardisk. How do I empty this in a simplest way.

I follow the error message to backup the transaction log to empty the table, but i think it's still the same. I did not see the difference, in short nothing happens. it doesn't empty the transaction log.

So what I did is make another database with different name (ex. tempdatabase), and import the tables including the record Sleep on the original database... then delete the original database which the transaction log got too big, and after that make a new database with the name of the original database and again import from the tempdatabase, and manually create the index for every table. It works OK, but is there any other way aside from my own way which is much easier and not time consuming.?

Thank you very much . . . and any help is very much appreciated.
MadixHi Madix,

Do you have any replications on the database whose log is too large? And if there is, is it transactional or merge replication? And what is the recovery mode of your database?

BACKUP LOG without NO TRUNCATE should help to make the logical log file shorter. But it may not reduce the size of a physical log file. You may consider to shrink the log (see BOL "Shrinking the transaction log").

Thanks,
-Peng|||actually sir, i use SQL Server database as my back-end database for my VISUAL FOXPRO front-end, and it has no other problem except that one. So I did not bother to study some of the feature of SQL capabilities. I do not know what the sql server is doing inside of my system.

I make a database, add a table, & select a key field in SQL Server then close it, set the back-up time, that's all i know in SQL. that is why when i encounter this problem, i've got a headache using my own way. it waste time. Maybe its time to know and ask from the experts what is the shortiest solution to solve this problem.

so far that is the only problem i encounter using it in Visual Foxpro.

Thanks for any help..

madix|||The simplest way to resolve this is to change the recovery mode to Simple.
This will remove the need for a transaction log, but may not meet your DR requirements.|||Hi,

U can also try this command below

DUMP TRAN <DATABASE NAME> WITH NO_LOG

Regards
Sachin

How do I embed and retrieve Crystal Reports from my app

Using VB .Net 2005 with Crystal Reports XI Release 2. I have a form that has a CrystalReportViewer control on it. When someone prints I run a sub that create a new instance of the form, creates a new reportdocument, loads the report to the new document, then passes the document to the report viewer on the form. This works great except I would like to either embed my crystal reports directly into my app or better yet embed them into thier own reports dll file. I want to do this so updates are easier and I can use the "Publish" function of VB 05 which doesn't seem to like publishing all the seperate files. The embedding should be the easy part, just set them as embedded resource. But I'm having trouble with the syntax to pull them out. For example you can use this to pull out a embdded icon:
Function GetEmbeddedIcon(ByVal strName As String) As Icon
Return New Icon(System.Reflection.Assembly.GetExecutingAssembly.GetManifestResourceStream(strName))
End Function

But I cannot get the syntax right to pull out and return a crystal report. Anyone have any ideas on how to pull the reports back out or a better way to do this. I dont have a lot of reports, about 15 right now, but that could grow.

-AllanWell...I couldn't find the answer anywhere and no one replied here. But I figured it out so I might as well help someone down the road.

Set your crystal reports to "embedded resource" and the copy to "do not copy". Then in your code you can clal them like any other object with a "New (reportname)". For example I have a crystal reports viewer control (apptly named CrystalReportViewer) on a form called ReportViewerForm. I made a small sub to print reports:

Friend Sub PrintReports(ByVal myReportFileName As ReportDocument, Optional ByVal mySQLFormula As String = "")
' Prints out reports pass to it by other procedures and forms.
Dim PrintPreview As New ReportViewerForm
Try
If mySQLFormula <> "" Then myReportFileName.RecordSelectionFormula = mySQLFormula
PrintPreview.CrystalReportViewer.ReportSource = myReportFileName
PrintPreview.ShowDialog()
Catch ex As CrystalDecisions.CrystalReports.Engine.EngineException
MsgBox("A error was generated by the Crystal Reports engine. Error details: " & ex.Message, MsgBoxStyle.Critical, "Engine Error")
Finally
myReportFileName = Nothing
PrintPreview = Nothing
End Try
End Sub

Then when I need to print a report, for example my Log report (called LogEntryReport.rpt) I call it like this:

PrintReports(New LogEntryReport, "{qLogEntryReport.LogIdNumber} = " & CurrentLogId)

passing my report as a new ReportDocument and my sql command (which is optional for those reports that don't need it). Now I can have one routine that will display reports and call it from anywhere passing the report name. Works dandy and the reports are embedded in the app...less clutter.

Hopefully this helps someone and its not a waste of bandwidth.

-Allan.

How Do I embed a RegEx in a Report Model

I am having trouble figuring out how to create a new expression-based field in a report model that relies upon the result of a regular expression. It looks like I cannot make calls to static methods in the .NET in a report model. Correct?

Here is my attempt at the expression I want:
=IF((System.Text.RegularExpressions.Regex.IsMatch(PreferedEmail)),True,False)

The error returned when I attempt to save the expression in Report Model Designer is "The following is character is not valid: ."

BTW, the message is copied verbatim. The poor grammer is not my fault.
I take it that no news is very bad news on this front. There is no way to reference a static .NET method/object in a Report Model expression. That's a real shame.
|||

Kevin,

You can use regular expression in reporting services for example:

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

Hammer

|||I'm going to go out on a limb here & guess that you've never tried that in a Report Model (SDML). You absolutely can do that in an expression embedded in a Report Definition (RDL). But the Report Model Designer will not allow you to save the expression.|||

You are correct -- referencing .NET methods from a report model expression is not supported. Depending on the report the user creates, report model expressions can potentially end up translated into SQL or MDX and embedded deep in some database query.

If you have VS and you're just using the expression as a surface expression in a particular report, you might save your report out as a file, load it up in Report Designer, and then add the expression there. I realize this doesn't give you anything in the model, however.

|||It's not the answer I wanted but now I understand why I can't have what I want. Thanks for the explanation.

How Do I embed a RegEx in a Report Model

Here is my attempt at the expression I want:
=IF((System.Text.RegularExpressions.Regex.IsMatch(PreferedEmail)),True,False)

The error returned when I attempt to save the expression in Report Model Designer is "The following is character is not valid: ."

BTW, the message is copied verbatim. The poor grammer is not my fault.I take it that no news is very bad news on this front. There is no way to reference a static .NET method/object in a Report Model expression. That's a real shame.|||

Kevin,

You can use regular expression in reporting services for example:

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

Hammer

|||It's not the answer I wanted but now I understand why I can't have what I want. Thanks for the explanation.

How do i Edit the Package Source file name Dynamically

Hi

I have created a package using DTS Designer to import the data from flat file to the SQL server.
i have sheduled the package to execute at every 1st day of the month and saved the the package to the local server.

i want to do the following steps dynamically from the Visual Basic

1. i am loading all the packages from the local server and also from the metadata services into a combo box. select one package from the combo box and i want to change the package shedule time from vb and i want to save the package back to the sql server.

2. when the package is executing at the schedule time at that time i want to change the source of the package that means flat filename(to different filename) and i need to execute the package and save the package.

how do i do this dynamically.

Thanks in advance

Regards
GandhiThe package schedule time is really a job schedule time - so you would use the sp_update_jobschedule stored procedure. For the 2nd part, when would the filename change and what would change it ?

How do I edit an Imported DTS package in SSIS?

I have used the wizard to import a DTS package from a SQL 2000 server to our new SQL 2005 server and need to make edits to reference the new server and database. I am able to see the package within the SQL Management studio under Integration Services, but I cannot find it in the Development studio?

What am I missing?

Thanks

The development studio is an offline tool that works on file packages only. Export the package from SQL Server / IS and add it to a project in BIDS.

How do I edit a query through code?

Hi!

Can someone tell me how or where I can find information on editind an SQL query through code?

I want to be able to run a user-defined lookup, where the user can choose what the query will look for.

Thanks in advance.

Guy

Your description is not very detailed. Do you want to create a dynamic query ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hi!

Thanks for the advice.

I was in a rush when I posted that, but I can provide some more detail now.

I want to make a query, where the user can change what the search criteria is.

I want the user to enter a string literal value from a text box or drop-down list, and then view the query with that criteria.

This needs to be done through code, and ideally work in Visual Web Developer as well as Visual Basic.

Thanks.

|||

check this

http://www.sommarskog.se/dynamic_sql.html

Madhu

|||

Hi.

Thanks for that link.

I will have a look when I have time.

Guy

How do I dynamically set the MultiValue property for 1 parameter based on another?

I am trying to set the property of 1 parameter based on the results of another parameter.

For example:

Parameters: PA and PB

If the user selects All or multiple values for PA

Then PB should not be MultiValue

Otherwise PB should be MultiValue

Does anyone have any suggestions?

Thanks

You could make PB MulitValue always, but have it's dataset filtered based on the PA parameter values. You just have to make sure PA comes first in your parameters list.

Hope that helps.

How do I dynamically change the ReportViewer's focus from one RDLC to another at runtime in cod

Howdy friends,

I have a question regarding SQL Reporting Services 2005. I have raised this question in several other places, only to watch it go unanswered. Hope to recieve the answer here.

I am using a SQL Reporting Services .RDLCs inside a Visual Basic 2005 windows client application. This is not webclient or web context; it is a Windows client. We will run the reports on the client side, not server side. The reports are embedded resources deployed in an MSI along with the rest of the application. I have one WinForm in the project which hosts the ReportViewer control. We have 5 unique RDLC report definitions. Any of our end user may want or need to preview at any of these 5 reports inside this single ReportViewer WinForm at run time.

So far I have found absolutely no way to dynamically change the RDLC which the ReportViewer displays at run time, and in code. How can this be done?

Whenever I ask this question, some joker always pops and says "That's easy, you just open your form in Visual Studio and use the smart tags to go through the binding setup." That is not particularly helpful because it doesn't answer the question. The question is not how to redesign the form inside Visual Studio with smart tags. The question is how to dynamically change the report that the ReportViewer displays dynamically, at runtime, and in code.

At the moment I think there is no solution to this problem. It seems that an RDLC is bound to the ReportViewer at design time and is immutably fixed at runtime. So far I have seen no working code which changes ReportVIewer from one RDLC to another. I am close to reaching the conclusion that I must make 5 copies of the same form, and bind 1 copy to 1 .RDLC in design mode. This is a pretty lame-arse solution if you ask me.

Anybody have a clue?

I am also struggling with the same problem.

I tried changing the ReportPath to a different rdlc at runtime. I also added a new "objectdatasource" to the Viewer window. However, I'm not sure how to specify which "objectdatasource" the ReportViewer should use.

Can Dave be right? "It seems that an RDLC is bound to the ReportViewer at design time and is immutably fixed at runtime. "

|||

When you bind the report viewer control to the form, there is a call to load the report on startup. You can remove this call, change the properties dynamically, then load the selected report in the viewer.

I bound Report1.rdlc to my viewer control.

I commented out the following line of code in the form load method.

//this.reportViewer1.RefreshReport();

And added:

this.reportViewer1.LocalReport.ReportEmbeddedResource = "WindowsApplication1.Report2.rdlc";

this.reportViewer1.RefreshReport();

The WindowsApplication1.Report2.rdlc could be a method that dynamically sets the value.

Check out http://www.gotreportviewer.com from more tips.

How do I dynamically change the ReportViewer's focus from one RDLC to another at runtime in

Howdy friends,

I have a question regarding SQL Reporting Services 2005. I have raised this question in several other places, only to watch it go unanswered. Hope to recieve the answer here.

So far I have found absolutely no way to dynamically change the RDLC which the ReportViewer displays at run time, and in code. How can this be done?

Anybody have a clue?

I am also struggling with the same problem.

Can Dave be right? "It seems that an RDLC is bound to the ReportViewer at design time and is immutably fixed at runtime. "

|||

I bound Report1.rdlc to my viewer control.

I commented out the following line of code in the form load method.

//this.reportViewer1.RefreshReport();

And added:

this.reportViewer1.LocalReport.ReportEmbeddedResource = "WindowsApplication1.Report2.rdlc";

this.reportViewer1.RefreshReport();

The WindowsApplication1.Report2.rdlc could be a method that dynamically sets the value.

Check out http://www.gotreportviewer.com from more tips.

How do I dynamically change the "TOP X" portion of a SELECT

I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error.

@.TopX int ( or varchar)

SELECT @.TopX CompanyName, Amount FROM Sales Where....

Why will this not work?

Only works in SQL Server 2005 or SQL Express:

SELECT TOP (@.Topx) ...

|||

Only works in SQL Server 2005 or SQL Express:

SELECT TOP (@.Topx) ...

|||

DECLARE @.stmtvarchar(500)DECLARE @.top varchar(5)SET @.top ='10'SET @.stmt ='SELECT TOP ' + @.top +' * FROM [Products]'EXEC(@.stmt)

You can dynamically create a sql statement:|||

Upgraded to 2005 and that worked perfectly...

Thanks!

How do I drop sql server mobile database when ...?

How do I drop sql server mobile database at specifical time in my application in PPC?

And, How do I drop it when user input the worng password third times in my application in PPC?

Thank you very much.

Simply delete (or open and fill with junk) the database file at particular time or after user entered incorrect password too many times.

|||

I mean How do I trigger the event?

if (sysdate>'01-may-2006' )

{

//Simply delete (or open and fill with junk) the database file

}

Is that right?

|||As far as I know SQL Mobile does not support database triggers. You have to perform this operation from your application instead.

How do I drop indexes?

Hi
How do I drop all the indexes in a database in one go?
Thanks in advance
HWrite a cursor that loops over sysindexes and for each index executes the DROP INDEX command. Make
sure that you exclude indid 0 and 255. Also, for indexes created with a PRIMARY KEY and UNIQUE
constraint, you need to execute ALTER TABLE ... DROP CONSTRAINT instead of DROP INDEX.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H|||Hi,
I have wrote a script to do this, please login to MSDN and have a look. This
script will generate
This stored procedure will be useful to generate scripts for below
index categories for DROP as well as CREATE . Probably u can use the DROP
part.
a. Clustered Index associated with a Primary key constraint
b. Non Clustered Index associated with a primary key constraint
c. Non Clustered Index associated with a Unique Key constraint
d. Normal Clustered Index
e. Normal unique Clustered Index
f. Normal Non Clustered Index
g. Normal unique Non Clustered Index
http://www.microsoft.com/india/msdn/articles/196.aspx?
Thanks
Hari
MCDBA
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H

How do I drop indexes?

Hi
How do I drop all the indexes in a database in one go?
Thanks in advance
HWrite a cursor that loops over sysindexes and for each index executes the DR
OP INDEX command. Make
sure that you exclude indid 0 and 255. Also, for indexes created with a PRIM
ARY KEY and UNIQUE
constraint, you need to execute ALTER TABLE ... DROP CONSTRAINT instead of D
ROP INDEX.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx
.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H|||Hi,
I have wrote a script to do this, please login to MSDN and have a look. This
script will generate
This stored procedure will be useful to generate scripts for below
index categories for DROP as well as CREATE . Probably u can use the DROP
part.
a. Clustered Index associated with a Primary key constraint
b. Non Clustered Index associated with a primary key constraint
c. Non Clustered Index associated with a Unique Key constraint
d. Normal Clustered Index
e. Normal unique Clustered Index
f. Normal Non Clustered Index
g. Normal unique Non Clustered Index
http://www.microsoft.com/india/msdn/articles/196.aspx?
Thanks
Hari
MCDBA
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx
.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H

How do I drop indexes?

Hi
How do I drop all the indexes in a database in one go?
Thanks in advance
H
Write a cursor that loops over sysindexes and for each index executes the DROP INDEX command. Make
sure that you exclude indid 0 and 255. Also, for indexes created with a PRIMARY KEY and UNIQUE
constraint, you need to execute ALTER TABLE ... DROP CONSTRAINT instead of DROP INDEX.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H
|||Hi,
I have wrote a script to do this, please login to MSDN and have a look. This
script will generate
This stored procedure will be useful to generate scripts for below
index categories for DROP as well as CREATE . Probably u can use the DROP
part.
a. Clustered Index associated with a Primary key constraint
b. Non Clustered Index associated with a primary key constraint
c. Non Clustered Index associated with a Unique Key constraint
d. Normal Clustered Index
e. Normal unique Clustered Index
f. Normal Non Clustered Index
g. Normal unique Non Clustered Index
http://www.microsoft.com/india/msdn/articles/196.aspx?
Thanks
Hari
MCDBA
"h" <anonymous@.discussions.microsoft.com> wrote in message
news:1d5cc01c45434$42f34bd0$a301280a@.phx.gbl...
> Hi
> How do I drop all the indexes in a database in one go?
> Thanks in advance
> H

How do I drop a restored database named "distribution?"

I restored a backup of a replication database named "distribution" to a non-replicated development instance (ss2000). Well, when I try to drop the database I am told I can not because it is used for replication. It isn't. But you now how Microsoft is . . . . So the question is: how do I drop the database without rebuilding the server, or jumping through firery hoops?

Thanks!

Scorched

First do this you bonehead!!

sp_configure 'allow updates', 1 GO RECONFIGURE WITH OVERRIDE GO

Then you can update the sysdatabases.category = 0

Drop away!

How do I drop a Foreigh Key

I have a column that includes th constraint References Table(Column) in its
definition. How do I get rid of this constraint?
Thanks for you assistance.
JohnLookup DROP CONSTRAINT in the BOL
"DevalilaJohn" <DevalilaJohn@.discussions.microsoft.com> wrote in message
news:20990D35-91EE-4A8E-805B-57C78D78240F@.microsoft.com...
>I have a column that includes th constraint References Table(Column) in
>its
> definition. How do I get rid of this constraint?
> Thanks for you assistance.
> John

How do I drop a Foreigh Key

I have a column that includes th constraint References Table(Column) in its
definition. How do I get rid of this constraint?
Thanks for you assistance.
John
Lookup DROP CONSTRAINT in the BOL
"DevalilaJohn" <DevalilaJohn@.discussions.microsoft.com> wrote in message
news:20990D35-91EE-4A8E-805B-57C78D78240F@.microsoft.com...
>I have a column that includes th constraint References Table(Column) in
>its
> definition. How do I get rid of this constraint?
> Thanks for you assistance.
> John

How do I downgrade SQL Server from SP4 to SP3?

Hi,

We have hit performance issues with our SQL server after upgraded it to SP3 from SP3a, is there any way that I can downgrade it back to Service Pack3a?

Thanks.

Hi,

refer and follow

http://support.microsoft.com/kb/889551 For removing SP3a

http://sqlserver2000.databases.aspfaq.com/why-am-i-having-problems-with-sql-server-2000-sp3/sp3a.html

http://www.sqlservercentral.com/accessdenied.aspx?SourceURL=/forums/shwmessage.aspx?forumid=5&messageid=123905

How do I do this? Get a report developed using beta.

Hi,

I have a report that was developed last year, but I want to migrate it to SQL 2005, how do I do that?

Thanks,

Mike

P.S. I am new to Reporting Services...

At least you should try to open this report in SQL 2005. Most probably that it will work just fine...|||

Hi,

Is their a tool I can use to pull the entire report into my new Server?

Thanks

Mike

how do I do this?

I want to change the order by criteria depending on a certain input paramater

So suppose

This is a simplified condensed version of what I need

declare @.sortID int
set @.sortID=1

So if the @.sortID=1 I wanna have this

select * from tblusersorder by name

else

select * from tblusers whereorder by age

so depending on the sort id the bolded text should change.

Only one way I know of ...

declare @.sortID int
set @.sortID=1
If (@.sortID = 1)
BEGIN
select * from tblusersorder by name
END
ELSE
BEGIN
select * from tblusers whereorder by age
END

I do not believe order by can be a parameter

|||

hello,

and other way to do, dynamic sql execution, some case is not good way to do but versstile

===============================

1declare @.idint2declare @.sqlnvarchar(max)34set @.id = 156set @.sql ='select top 10 productid, [name] from production.product '7+'order by ' +case @.id8when 1then'[name]'9when 2then'productid'10end1112execsp_executesql @.sql

===============================

|||busyweb is right. You could use dynamic SQL. I just never think of dynamic SQL because of the speed, security, maintainability problems. (smile)

How do I do this?

In my DB I have inadvertantly dropped a table. I do however have a backup. How can I get a copy of the (1) table from my backup DB to my current DB?Restore the backup and copy the table to your database, If there are no indexes and complex objects you can do SELECT INTO, SQL Server will create a simple table in your database. The other option create a new table with same name and copy the data from the restored database. Run a search for INTO in the BOL. Hope this helps.|||

Jim:

Create an empty database and restore your database to the newly created empty database. Then run the DDL to re-create the table in your old database and then copy the data from the restored database to the old database. Once you are satisfied that the old table is ok, drop the restored database.

[ Obvioulsy, I agree with Caddre. :-) ]

|||Thanks, here is the part I am missing. what is the syntax for going from one DB to another

i.e.

SELECT * INTO CurrentDB.NewTable FROM BackupDB.ExistingTable|||

Try this

SELECT * INTO CurrentDB.dbo.Newtable FROM BackupDB.dbo.ExistingTable

|||

SQL Server uses a 'four-part' naming convention. It follows this pattern:

Server.Database.schema.table

In 'normal' use, if you supply just the table name,

the 'schema' is assumed to be either the default user/owner or schema, the database is the current connected database, and the server is the current connected server.|||Thanks! That is exactly what I was looking for, issue resolved!

How do I do this with SQL Express?

Hi,

In Microsoft Access when I had a one-to-many relationship between tables, I could (in the master\parent) table enter a record and a plus symbol would appear enabling me to enter the many side details. How can this be done in SQL Express?

Thanks for your help

hi A,

what do you mean by that?

SQLExpress is "just" an engine where Access (in this case) is a client application... you have to programmatically deal this in your app as you require... say something similar to http://search.msdn.microsoft.com/search/default.aspx?siteId=0&tab=0&query=master+detail

regards

How do i do this with dts

Im using DTS to a basic import of a products csv into a corresponding table in sql server. I use the transformations tab to map up all the fields between the two. I have one field in the sql server table that I need to include a value everytime the dts is run. So for eg one of the fields in the sql table is ProductTypeCode and in this case it should always be 1. Now as a cheap and dirty workaround I could add another column in my csv and give all the fields values of 1 and map that column to my ProductTypeCode in sql server table but surely there a more correct way of doing this simple task.

Thanks in advanceYou can create a query for the source, using the Excel table, you can add a 1 to that query. So when you set up the source, use a query, then use the query builder to setup the initial design from the excel file. Then modify the resulting query to add the 1 parameter.

Brian|||Try this url for all your DTS questions most problems are covered here, it is run by Darren Green a SQL Server MVP.
http://www.sqldts.com Hope this helps.

Kind regards,
Gift Peddie

how do i do this using Crystal Reports IX?

Hi all,

Please help me with my CR IX problems...I am new to crystal reports.

I have a table that consist of:
[City, Program, Type of Payment, Total # of participants]
example:
[LA, Aerobics, Cash, 100
LA, Aerobics, Credit, 120
LA, Dance, Cash, 50
LA, Dance, Credit, 20]

I wanted to do a report so that it will show the folowing datas:
[City, $Aerobics, $Dance, CR Aerobics, CR Dance, Total]
example:
[LA, # of participants in Aerobics that pay by cash, # of participants in Dance that pay by cash, # of participants in Aerobics that pay by credit card, # of participants in Dance that pay by credit card, Total # of participants in LA]

Thank you very much in advance for your answer and time.
Have a wonderful day!Do a group by 'City' field. Create running total for #of participants and
select 'Use a formula' option. Use the following formula :

if Program="Aerobics" and Type of Payment="Cash" then
true
else
false;

This will give you the total # of participants with Cash payment for Aerobics. Similar running totals for Credit card payment etc.

Hope this helps!
Rashmi

How do I do this query

I have 3 tables
Users
Customerid - guid
userid - varchar
customertoworker
customerid - FK
workerid - FK
extract
customerid - guid
data layout
extract
EACFF367-8E73-4C4C-A9A7-036A0D7E57AB
customertoworker
EACFF367-8E73-4C4C-A9A7-036A0D7E57AB, 90ED4230-2711-4019-A2AD-071D52891BBF
EACFF367-8E73-4C4C-A9A7-036A0D7E57AB, 873762AB-34BD-455B-83B2-137645BE3331
users
EACFF367-8E73-4C4C-A9A7-036A0D7E57AB , RAINMAN
90ED4230-2711-4019-A2AD-071D52891BBF , BOB
873762AB-34BD-455B-83B2-137645BE3331 , JAMES
I want the data to appear in 1 row. I want to know the workers that all
users in the extract have in 1 row.
ex
RAINMAN, BOB, JAMES
select x.userid, z.userid,x.customerid
from cdtextractactivemlm x
inner join customertoworker y
on x.customerid = y.customerid
inner join useridentifier z
on z.customerid = y.workerid
order by y.customerid
The query above gives me 2 rows... can someone shed some light...
thanks for the help.Look at this example (and remember: this breaks normalization and should be
used for presentation purposes only):
http://milambda.blogspot.com/2005/0...s-as-array.html
ML
http://milambda.blogspot.com/|||niv
I understood from your narritave nothing, sorry
If you say that your query returns two rows , so try using TOP 1 clause to
get only one row as well as using ORDER BY clause to sort the output
"niv" <niv@.discussions.microsoft.com> wrote in message
news:D76674C3-3CB4-43F8-B0E0-CA8A85E3DC2F@.microsoft.com...
>I have 3 tables
> Users
> Customerid - guid
> userid - varchar
> customertoworker
> customerid - FK
> workerid - FK
>
> extract
> customerid - guid
> data layout
> extract
> EACFF367-8E73-4C4C-A9A7-036A0D7E57AB
> customertoworker
> EACFF367-8E73-4C4C-A9A7-036A0D7E57AB, 90ED4230-2711-4019-A2AD-071D52891BBF
> EACFF367-8E73-4C4C-A9A7-036A0D7E57AB, 873762AB-34BD-455B-83B2-137645BE3331
> users
> EACFF367-8E73-4C4C-A9A7-036A0D7E57AB , RAINMAN
> 90ED4230-2711-4019-A2AD-071D52891BBF , BOB
> 873762AB-34BD-455B-83B2-137645BE3331 , JAMES
> I want the data to appear in 1 row. I want to know the workers that all
> users in the extract have in 1 row.
> ex
> RAINMAN, BOB, JAMES
> select x.userid, z.userid,x.customerid
> from cdtextractactivemlm x
> inner join customertoworker y
> on x.customerid = y.customerid
> inner join useridentifier z
> on z.customerid = y.workerid
> order by y.customerid
> The query above gives me 2 rows... can someone shed some light...
> thanks for the help.|||I am using sql 2000.
This is a function written in sql?
Are there any others ways of getting the data into the format I want?
"ML" wrote:

> Look at this example (and remember: this breaks normalization and should b
e
> used for presentation purposes only):
> http://milambda.blogspot.com/2005/0...s-as-array.html
>
> ML
> --
> http://milambda.blogspot.com/|||Pure T-SQL user-defined function. Works in SQL 2000 and above.
The usual way would be to do it on the client (in the application tier).
ML
http://milambda.blogspot.com/|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
I will not even comment on that insane use of GUIDs and the single
character user_id, but what you are doing is a violation of 1NF.
Display is done in the front and not in the database. You are
basically not writing SQL at all.

How do I do this is SQL Server? (from an Oracle background)

SQL Server 2000 Ent, PowerBuilder 6.5 Ent, Window 2000 Pro

Hi,

My only experience is with Oracle,so please bear with me...

We have some new clients that require our pricing data be imported
into their Sql Server databases. The price lists are in Excel files,
which we recieve from numerous vendors on a quarterly basis.

In Oracle we take the following steps:

1. Import this data into tempory tables using bulk import utility
called sqlLoader.

2. Then we have procedures that parse in the data to our various price
tables.

3. Next we export the tables to a '.dmp' file. (contains table data
and table definitions I think).

4. Then the clients download the .dmp file

5. Our client application is then used to drop the price tables on
their local db. and import the .dmp file.

Can anyone point me in the right direction by telling me how to
approach this using SQL Server?

Thanks,
KarenSorry subject should be "How do I do this IN SQL Server? dang laptop
keyboard..|||One approach in SQL Server is to use DTS. You could create 2 DTS packages
as follows:

DTS Package 1:

1. Import data into temporary tables using DTS Transform Data tasks. DTS
can import data directly from Excel.

2. Execute procedures to load various price tables.

3. Export tables to individual files (containing data only).

DTS Package 2:

1. Recreate price tables using DDL embedded in DTS package (or simply
truncate existing destination tables)

2. Import data into price tables

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Karen" <nowhere@.home.com> wrote in message
news:c3ui70pbbemclvu2cqv00fhgnd41lrj3pa@.4ax.com...
> SQL Server 2000 Ent, PowerBuilder 6.5 Ent, Window 2000 Pro
> Hi,
> My only experience is with Oracle,so please bear with me...
> We have some new clients that require our pricing data be imported
> into their Sql Server databases. The price lists are in Excel files,
> which we recieve from numerous vendors on a quarterly basis.
> In Oracle we take the following steps:
> 1. Import this data into tempory tables using bulk import utility
> called sqlLoader.
> 2. Then we have procedures that parse in the data to our various price
> tables.
> 3. Next we export the tables to a '.dmp' file. (contains table data
> and table definitions I think).
> 4. Then the clients download the .dmp file
> 5. Our client application is then used to drop the price tables on
> their local db. and import the .dmp file.
> Can anyone point me in the right direction by telling me how to
> approach this using SQL Server?
> Thanks,
> Karen|||Thanks Dan.

On Mon, 12 Apr 2004 02:21:12 GMT, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:

>One approach in SQL Server is to use DTS. You could create 2 DTS packages
>as follows:
>DTS Package 1:
>1. Import data into temporary tables using DTS Transform Data tasks. DTS
>can import data directly from Excel.
>2. Execute procedures to load various price tables.
>3. Export tables to individual files (containing data only).
>
>DTS Package 2:
>1. Recreate price tables using DDL embedded in DTS package (or simply
>truncate existing destination tables)
>2. Import data into price tables

How do I do this in SS2000?

Hi all,

I have a simple query which returns all the names of products attached to a particular order.

select a.orderid, b.product_name
from orders a, order_items b
where a.orderid = b.orderid

Say it returns this data: (sorry about the formatting!)

orderid product_name
===== =========
001234 Sweater (Black)
001234 Trousers (Large)
001234 T-Shirt (Pink)

What I want to do is, instead of getting 3 rows back, I want to roll up all matching values (of product_name) from the order_items table into a simple string seperated by a comma. So, for the data above I would get a single row with the orderid and a string containing something like this: "Sweater (Black), Trousers (Large), T-Shirt (pink)".

I'm sure there's an easy way to do this in SQL Server 2000 but I've not been able to work out how to do this and I couldn't see anytihng in SQL Books Online..

TIA for any help...

MikeAre you prepared to use Analysis Services?|||Look at this. You may want to put it into a function, but this is the general idea


declare @.s varchar(8000)select @.s = b.product_name + ', ' + COALESCE(@.s, '')
 from orders a, order_items b
 where a.orderid = b.orderid
if @.s is not null
 set @.s = substring(@.s, 1, LEN(@.s) - 1)
else
 set @.s = ''
select @.s

|||Thanks for that... that works fine but I need to fine tune it a bit. At the moment it gives me all products for all orders whereas I need it to give me just the product names for each unique order. I tried using a "GROUP BY a.orderid" but it won't let me use the product_name column in this way.

I also want to be able to select the columns I need from the first table such as orderid, order_date etc.

I've experimented with both but can't seem to really get it to work... a little more help would be much appreciated!

Cheers,

Mike

PS: pkr - no I can't really use Analysis Services as this is part of a stored procedure for a web app that also has to run on Oracle so it needs to be fairly standard ANSI SQL.|||Assuming you don't know how many products you've got for a an order its difficult to write a single query. This is my suggestion.
1. Create a temp table with the OrderID and a "csv" text column, defaulted to ''
2. Insert the unique set of orderids into the temp table
3. Run a query that UPDATEs the csv column with itself plus the "," + product name|||Thanks pkr... in the end I wrote a function which is passed the order id and reads the values of the products into a cursor. It then builds the string of product names and returns it. It seems to work very well and I've learnt quite a lot about SQL functions that I didn't know before. I'm not at work so I can't post it but I will on Monday so possibly someone in the future can see how to do this.

Thanks for the help.

Mike|||I use that code within a function and it works fine. Forgot to suggest that. I would stay away from cursors unless you have to use them. There is a significant performance hit. I'd use the query above and avoid the cursor.|||Replacing cursors is nearly always a good idea. However, be careful with funcs, you can basically end up doing the same thing as a cursor. If you code it "incorrectly" the function will run for each row in the set, therefore the perf will be like a cursor anyway!|||Luckily the table I run the function on will not have that many rows in it at one time. I also looked at the stats for a few orders and it does only seem to be reading the ones it needs rather than processing the whole table which is good.

Will post the function when I get to work today... then people can tell me if could do it any better.

Mike.

How do I do this in crystal report?

Hi, I am currently using crystal report 8.0. I need to create a report that instead of print downwards i need it go sideway. For eg normal report

00512789
1

00512567
2

but i need to do this

00512789 00512567
1 2

I tried using the cross tab report but it does not suit my needs. And also the report is actually taking data from 3 queries but the input for all 3 queries is the same. How do i let users to enter the parameter once for all 3 queries. Any help is appreciated. Thank youHi,

As per my understanding u want to show ur data Horizontally insted of vertically.
If m i right then, u try with "Format Page With Multiple Columns" functionality

I sure u get the solution which u r looking for

-Yags

How do I do the concept of record checkouts?

Without using locks and with only using pure ANSI-92 SQL, I need to
pull off the concept of record checkouts. Imagine a table full of work
orders. I can only let someone use the web app to edit the work order
for 15 minutes. If someone has the work order checked out, and someone
else wants to edit it, it should prevent this unless 15 minutes have
passed. If 15 minutes have passed, then it should set this field to
null and let the other user edit the record. The way I have decided to
implement it is by storing 2 things -- the minute that the record was
checked out for editing, and the username of the user conducting the
task. I'm thinking in terms of an UPDATE statement with a special WHERE
clause that can clear any records where the checked out minute is older
than 15 minutes. This is not as simple as it first seems because you
have to look at what happens when the minute hand wraps around the
clock hour.
That is, unless you have an easier way to do the whole business.Instead of storing minutes, store the smalldatetime that the row was locked.
You can then use datediff(minute,LockedTime,getdate()) which will return the
number of minutes elapsed.
"Alfred" <99m@.myway.com> wrote in message
news:1139943161.167707.100660@.g43g2000cwa.googlegroups.com...
> Without using locks and with only using pure ANSI-92 SQL, I need to
> pull off the concept of record checkouts. Imagine a table full of work
> orders. I can only let someone use the web app to edit the work order
> for 15 minutes. If someone has the work order checked out, and someone
> else wants to edit it, it should prevent this unless 15 minutes have
> passed. If 15 minutes have passed, then it should set this field to
> null and let the other user edit the record. The way I have decided to
> implement it is by storing 2 things -- the minute that the record was
> checked out for editing, and the username of the user conducting the
> task. I'm thinking in terms of an UPDATE statement with a special WHERE
> clause that can clear any records where the checked out minute is older
> than 15 minutes. This is not as simple as it first seems because you
> have to look at what happens when the minute hand wraps around the
> clock hour.
> That is, unless you have an easier way to do the whole business.
>

How do I do restore only SOME DTS scripts from msdb database?

I understand that I can backup my DTS scripts by backing up the msdb
database, but how do I restore only CERTAIN DTS scripts from the database?
If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
related to other databases that I might NOT want to restore.
TIA,
Larry WoodsIf you want to move DTS from one server to another try with this.
Save DTS as Structured Storage File. Then copy it to the
destination server and in EM right click Data Transformation Services and
Open. Choose your file. Click Package then Save AS and chose server in which
you want to save DTS.
I hope this help
Ana
"Larry Woods" wrote:
> I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>
>|||I would perhaps do another install of sql server, restore ALL of the
Packages. Then save each of the packages you need as a flat file, then open
them on the correct server and save as SQl..
Good luck...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:%23ewfQc7KFHA.244@.TK2MSFTNGP12.phx.gbl...
>I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>

How do I do restore only SOME DTS scripts from msdb database?

I understand that I can backup my DTS scripts by backing up the msdb
database, but how do I restore only CERTAIN DTS scripts from the database?
If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
related to other databases that I might NOT want to restore.
TIA,
Larry WoodsIf you want to move DTS from one server to another try with this.
Save DTS as Structured Storage File. Then copy it to the
destination server and in EM right click Data Transformation Services and
Open. Choose your file. Click Package then Save AS and chose server in which
you want to save DTS.
I hope this help
Ana
"Larry Woods" wrote:

> I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>
>|||I would perhaps do another install of sql server, restore ALL of the
Packages. Then save each of the packages you need as a flat file, then open
them on the correct server and save as SQl..
Good luck...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:%23ewfQc7KFHA.244@.TK2MSFTNGP12.phx.gbl...
>I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>

How do I do restore only SOME DTS scripts from msdb database?

I understand that I can backup my DTS scripts by backing up the msdb
database, but how do I restore only CERTAIN DTS scripts from the database?
If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
related to other databases that I might NOT want to restore.
TIA,
Larry Woods
If you want to move DTS from one server to another try with this.
Save DTS as Structured Storage File. Then copy it to the
destination server and in EM right click Data Transformation Services and
Open. Choose your file. Click Package then Save AS and chose server in which
you want to save DTS.
I hope this help
Ana
"Larry Woods" wrote:

> I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>
>
|||I would perhaps do another install of sql server, restore ALL of the
Packages. Then save each of the packages you need as a flat file, then open
them on the correct server and save as SQl..
Good luck...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:%23ewfQc7KFHA.244@.TK2MSFTNGP12.phx.gbl...
>I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?
SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>

How do I do categories and subcategories and more subcategories?

Hi, I have a database that will have various categories:

take this:

Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas

(it's shoes)

I have to put that in a structure that makes sense. I don't think having several tables for categories, subcategories, subsubcategories... is the right way to do it. I'm not a design expert, so I was hoping someone might help me. How can I organize this? Any book, link, or advice is appreciated. Thank you.There are many ways to represent categories in relational tables. Probably the simplest to see and understand is the "parent" model, something like:CREATE TABLE categories (
catagoryID INT NOT NULL
CONSTRAINT XPKcategories
PRIMARY KEY (categoryID)
, ParentCategoryID INT NULL
CONSTRAINT XFK01categories
FOREIGN KEY ParentCategoryID
REFERENCES categories (categoryID)
, [name] VARCHAR(50) NOT NULL
)This allows you to create as many categories as you like, structures however you like. The primary and foreign key definitions prevent you from hurting yourself with bad data easily. The only real drawback is that it can be a little tricky to retrieve the heirarchy in a form that is easy to visualize... You can easily find the parent or child of any given category, but it can be a little tough to get the whole shebang when you want to see that.

-PatP|||There are many ways to represent categories in relational tables. Probably the simplest to see and understand is the "parent" model, something like:CREATE TABLE categories (
catagoryID INT NOT NULL
CONSTRAINT XPKcategories
PRIMARY KEY (categoryID)
, ParentCategoryID INT NULL
CONSTRAINT XFK01categories
FOREIGN KEY ParentCategoryID
REFERENCES categories (categoryID)
, [name] VARCHAR(50) NOT NULL
)This allows you to create as many categories as you like, structures however you like. The primary and foreign key definitions prevent you from hurting yourself with bad data easily. The only real drawback is that it can be a little tricky to retrieve the heirarchy in a form that is easy to visualize... You can easily find the parent or child of any given category, but it can be a little tough to get the whole shebang when you want to see that.

-PatP

Thanks. So how would that string I put in my OP fit into the table? What would it look like?|||If that is a single category, then it would go in as:INSERT INTO categories (categoryID, parentcategoryID, [name]) VALUES (1, NULL, 'Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas')If the colons signify breaks between categories, and all of them are purely hierarchical (each nests in all to the left), then I'd use:INSERT INTO categories (categoryID, parentcategoryID, [name])
SELECT 1, NULL, 'Kids' UNION
SELECT 2, 1, 'Boys Collection' UNION
SELECT 3, 2, 'Children Boys Collection' UNION
SELECT 4, 3, 'Children Boys Athletic' UNION
SELECT 5, 4, 'Athletic - Canvas'-PatP|||If that is a single category, then it would go in as:INSERT INTO categories (categoryID, parentcategoryID, [name]) VALUES (1, NULL, 'Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas')If the colons signify breaks between categories, and all of them are purely hierarchical (each nests in all to the left), then I'd use:INSERT INTO categories (categoryID, parentcategoryID, [name])
SELECT 1, NULL, 'Kids' UNION
SELECT 2, 1, 'Boys Collection' UNION
SELECT 3, 2, 'Children Boys Collection' UNION
SELECT 4, 3, 'Children Boys Athletic' UNION
SELECT 5, 4, 'Athletic - Canvas'-PatP

I know you have been more than gracious, but how would you select it back out? Also, i am thinking that in my shoe table that actually holds the shoes I would have a column like "shoe_category" that would hold the last level (here it is 5. Then I would

select * from categories where categoryid = 1 and parentcategoryid = categoryid

but that didn't yield any rows. but that didn't make sense to me anyway that it should (rambling out loud). How can I get that back out. I'm drawing, yet another, blank.

I also tried something like:

select * from categories c, categories c1
where c.categoryid = 1
and
c.categoryid = c1.parentcategoryid

but didn't get the right thing.|||This is where things get ugly fast. If you think about a category hierarchy, there isn't a "flat" way to look at it... The thing is inherantly "stacked" because of the hierarchy itself, so you can't pull it back as a single row (although you can retrieve a flat representation like your colon separated list, but that is NOT the hierarchy itself).

Think a bit about your hieararchy. How does it look in your mind? Do you see branches in the hierarchy? Do you want those branches included in a result set, and if so just how much of those branches do you want? The problem with hierarchies in general is that they are actually arbitrarily complex, and the way you handle them actually depends on how the user wants to think about them... For any N users, this is about N * Log(N) combinatations, only about half of which occur naturally to the coder!

-PatP|||This is where things get ugly fast. If you think about a category hierarchy, there isn't a "flat" way to look at it... The thing is inherantly "stacked" because of the hierarchy itself, so you can't pull it back as a single row (although you can retrieve a flat representation like your colon separated list, but that is NOT the hierarchy itself).

Think a bit about your hieararchy. How does it look in your mind? Do you see branches in the hierarchy? Do you want those branches included in a result set, and if so just how much of those branches do you want? The problem with hierarchies in general is that they are actually arbitrarily complex, and the way you handle them actually depends on how the user wants to think about them... For any N users, this is about N * Log(N) combinatations, only about half of which occur naturally to the coder!

-PatP

I was thinking I would want each level as requested by the user. They might click "kids" and it brings back one level down and next to that level there would be a dropdown containing all the other items at that level. For example, at that level, there may be kids -> boys -> canvas, or european, or athletic..., so yes, your initial email was right on target. It is difficult to imaging or retrieve the branches. I will think about some more.

Does this type of table have an "official" name, this structure? I could look in a book at various examples to help me or at least search for it by the correct type. Thanks for your help.|||Does this type of table have an "official" name, this structure? adjacency model, or adjacency list model

How do I do an UPDATE in this complex query?

Hi,
I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.
ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]
@.extractNum char(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmdtn_RECID
FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id
ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1
END
I have tried inserting Update between the 2 "WHERE" statements, but it returns an error
"Invalid column name 'rn'."
I have also tried opening the recordset in Access VB , but I am restricted to read-only. (acmdtn_RECID is the primary key)
I would prefer to have a stored procedure do this.
I can get it to work if I take out the parameter, but I need that part.
The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to run reports on the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get all of the the latest "id" records as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.
Any help would be greatly appreciated.

I would suggest changing this stored procedure to a function or perhaps making a version of this that is a function. Hang on and I'll try to show you. Maybe something like:

create function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( SELECT id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID
FROM ( SELECT dbo.acmdtn.*,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum
) Rank
WHERE rn = 1
)

|||

I mocked this up with this table and data:

create table dbo.acmdtn
( id integer,
efctv_from_dt datetime,
efctv_to_dt datetime,
modify_ts datetime,
extractno integer,
Active integer,
acmdtn_RECID integer
)
go

insert into acmdtn
select 1, '1/1/7', '2/1/7', '1/1/7', 1, 0, 1 union all
select 1, '2/1/7', '4/1/7', '1/15/7', 2, 0, 1 union all
select 1, '4/1/7', '1/1/8', getdate(), 3, 0, 1 union all
select 2, '1/1/7', '3/1/7', '1/1/7', 1, 0, 2 union all
select 2, '3/1/7', '7/1/7', '2/15/7', 2, 0, 2
select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 0 1
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 09:59:02.560 3 0 1
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 2
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 0 2
*/

I tested the UPDATE like this:

alter function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( SELECT id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID
FROM ( SELECT dbo.acmdtn.*,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum
) Rank
WHERE rn = 1
)

go

update selectCurrent_Acmdtn (1)
set Active = 1

select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 1 1
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 09:59:02.560 3 0 1
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 2
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 1 2
*/

Something to consider is the use of:

SELECT dbo.acmdtn.*,

This is dangerous because it is not intuitively obvious whether or not this statement will return all columns of the dbo.acmdtn table. This is because the meaning of this select statement is determined at function compile time and NOT at function execution time. Therefore, I strongly suggest that you alter this statement to explicitly list all columns returned by the select statement.

This problem comes into play whenever the structure of the table is altered because at that time the columns returned by this select are no longer the same as the columns contained in the table.

In addition, if the point of this function is only to perform the update then it would be better to eliminate from the select statement any columns that do not contribute to the update.

|||

Thanks alot for the reply. I'll give that a try.

(I think I screwed up a little because I posted this question multiple times. I kept getting a message that "the administrator may have deleted your post" and I couldn't find it on a search initially, so I kept on posting!)

The only thing that I didn't mention is that I have acmdtn_RECID as the primary key (It's an identy field that gets assigned when I do the import from the raw data, because the original raw data didn't have any primary keys).

Would that change your soloution any?

Thanks again.

|||

No, it will not really change this solution; however, you are correct in identifying that my test data would not be valid. It would be good to have an index based on extractno because this is what is used here for filtering the data.

Again, it would be good to eliminate the SELECT * syntax to pare down some of the data.

|||

Create a stored procedure to apply the UPDATE.

create PROCEDURE [dbo].[update_Current_acmdtn]

@.extractNum char(10)

SET NOCOUNT ON;

with cte

(

SELECT

dbo.acmdtn.*,

row_number() OVER (partition BY id ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM

dbo.acmdtn

WHERE

extractno > @.extractNum

)

update cte

set Active = 1

where rn = 1

return @.@.error

AMB

|||

Hunchback presents a good way of performing the update. I realized that I need to factor out a piece from my query so I amended my function similar to what Hunchback did. Also, I eliminated the SELECT * syntax:

alter function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( select id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum

)

go

update selectCurrent_Acmdtn (1)
set Active = 1
where rn = 1

select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 1 2
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 11:10:56.793 3 0 3
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 4
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 1 5
*/

Thanks, Hunchback. :-)

|||

Thanks alot guys,

I got it to work.

How do I do an Unattended install of "MSDE 1.0 sp4"?

I am trying to upgrade an MSDE 1.0 db with the Service Pack 4 patch

but I need to do either a Silent or Unattended install.

Does anyone know how to do this? Does anyone know the

command-line command to do this?

I tried using...

sql70sp4.exe -a -f1 "unattend.iss"

....but that didn't work. It popped up a window asking where I

should extract the contents of the .exe (that shouldn't be appearing

either) and then after it extracted it didn't do anything else.

Any help is greatly appreciated.

see if this helps

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q233312

How do I do a like '%<string>%' equivalent in FTS

How do the following equivalent query in FTS.

select brandName from Brand b where b.brandName like '%lf%'

returns "Alfa Brand"

select brandName from Brand b where CONTAINS (b.brandName, '"*lf*"')
returns Zero rows

pls help, I just can't FTS to return the row.

thx
jt

I can replicate this error. Your syntax is correct but it looks like the fulltext engine is ignoring the first wildcard (*)

Therefore your query turns into

select brandName from Brand b where CONTAINS (b.brandName, '"lf*"')

I'm not sure if this is standard behaviour across all installs of SQL. I'll see if i can find out.

|||I don't think FTS supports inter-word searching

|||There are Specific rules you will have to go with if you use FTS, depening on the wordbreaker you can use * at the beginning of a words. This depends on the wordbreaker which is used for the column / attribute.

e.g. if you search for *race in columns which contain TRACE and FASTRACE, you will find the second word as it was broken into FAST & RACE.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How do i do ?

CREATE PROCEDURE SP_Login
(
@.Email Varchar(60) = '',
@.Password Varchar(100) = ''
)
As
Declare @.UserEmail Int
Set Nocount On
If not exists( Select CdUser From User Where Email = @.Email And Password =
@.Password)
Begin
Raiserror(13001,18,1) --User not found
End
it should continues with a else to get the CdUser...I tryed:
Else
Begin
Set @.UserEmail = Select CdUser From User Where Email = @.Email
End
I'm trying to accomplish this, but doesn' work, What is the way ?You need a return statement to stop execution of the SP in the fiirst case..
.
and just setting the @.UserEmail variable does nopt return it to the client,
if that's what you're trying to do. Also, you can return different error
messages if the user exists and the password is wrong, if you want...
CREATE PROCEDURE SP_Login
@.Email Varchar(60) = '',
@.Password Varchar(100) = ''
As
Set Nocount On
Declare @.Err Integer
Declare @.Msg VarChar(500)
Set @.Msg = 'User: '+ @.Email + ' not found.'
If not Exists
(Select * From User
Where Email = @.Email)
Goto ErrHandler
-- ---
Set @.Msg = 'Incorrect password for User: '+ @.Email
If Not Exists
(Select * From User
Where Email = @.Email
And Password = @.Password)
Goto ErrHandler
-- --
-- Sp Only gets here if it passes
-- validation checks above
Select CdUser From User -- This returns CDUser to client
Where Email = @.Email
-- --
Return(0) -- This terminates SP processing
-- --
-- -- Rest only runs if error occored
ErrHandler:
Raiserror(@.Msg, 16,1)
Return(-1)|||Better not to raise an error at all - errors are for exceptional situations,
someone typing invalid login credentials is an expected situation. You could
return @.msg as an output param, look at the return value of the proc, or som
e
other method on the client rather than have to catch an exception.
KH
"CBretana" wrote:

> You need a return statement to stop execution of the SP in the fiirst case
..
> and just setting the @.UserEmail variable does nopt return it to the client
,
> if that's what you're trying to do. Also, you can return different error
> messages if the user exists and the password is wrong, if you want...
> CREATE PROCEDURE SP_Login
> @.Email Varchar(60) = '',
> @.Password Varchar(100) = ''
> As
> Set Nocount On
> Declare @.Err Integer
> Declare @.Msg VarChar(500)
>
> Set @.Msg = 'User: '+ @.Email + ' not found.'
> If not Exists
> (Select * From User
> Where Email = @.Email)
> Goto ErrHandler
> -- ---
> Set @.Msg = 'Incorrect password for User: '+ @.Email
> If Not Exists
> (Select * From User
> Where Email = @.Email
> And Password = @.Password)
> Goto ErrHandler
> -- --
> -- Sp Only gets here if it passes
> -- validation checks above
> Select CdUser From User -- This returns CDUser to client
> Where Email = @.Email
> -- --
> Return(0) -- This terminates SP processing
> -- --
> -- -- Rest only runs if error occored
> ErrHandler:
> Raiserror(@.Msg, 16,1)
> Return(-1)

How do I do "If statement" in SSIS?

I need to get value from one table and if it is (for example) string - TodayDate, I need to change it to "Today Date" with a space in it and use it later in my Data Flow.

So I would need something similar to

If value = "TodayDate" Then

value - "Today Date"

Else

.....

How do I do that?

Thanks.

Check on the Derived Column transform in SSIS dataflow task.

|||

To add to Wenyang's answer...you will need to use the Conditonal Operator within the Derived Column transform.

? : (Conditional) (SSIS)

(http://msdn2.microsoft.com/en-us/library/ms141680.aspx)

Wenyang, I don't thinkyour answer is complete. Do you really think it warrants being marked as an answer? Hope you don't mind me asking.

Regards

Jamie

|||

How do I create expresiion?

Column Name - MonitorType

MonitorType ? "SA" : @.MyVariable

This shows error.

I want to see if the MonitorType field returns "SA" and if yes - use the variable I have.

If it returns for example "BA" use another variable.

|||

Vita wrote:

How do I create expresiion?

Column Name - MonitorType

MonitorType ? "SA" : @.MyVariable

This shows error.

I want to see if the MonitorType field returns "SA" and if yes - use the variable I have.

If it returns for example "BA" use another variable.

MonitorType == "SA" ? @.[MyVariable] : @.[SomeOtherVariable]

-Jamie

|||

Thanks.

|||Please don't forget to mark posts as answered.|||

What if I have more than 2 variables.

If "SA" - var 1,

If BA" - var 2

If "DA" - var 3

If "DS" - var 4

Can I do Else If in the same expression?

|||

I believe you can continue to add additional tests in the Derived Column task.

Rob

|||

Vita wrote:

What if I have more than 2 variables.

If "SA" - var 1,

If BA" - var 2

If "DA" - var 3

If "DS" - var 4

Can I do Else If in the same expression?

Yes. You need to nest the Conditonal Operator.

MonitorType == "SA" ? @.var1 : (MonitorType == "BA" ? @.var2 : (MonitorType == "DA" ? @.var3 : (MonitorType == "DS" ? @.var4 : @.SomeDefaultVariable)))

-Jamie

please don't forget to mark as answered.

|||Thanks again. I appreciate it.

Sunday, February 19, 2012

How Do I distribute an existing DataBase - Please Help

I have a Systema that uses a DataBase an I Want to Install y other machines, I need to Install a Visual Studio Program that uses a Database, How do I Install The Visual Studio Program, the Sql Server Express, and the existing DataBase.

Thanks in Advance, Santiago

Visual Studio can be installed using the appropiate installer from the VS disks, SQL Server comes with the Visual Studio, so you either use the shipped version with Visual Studio and update it laterone with the SP2 Service Pack or leave the SQL Server Express edition out and install it seperately by using the most recent download from Microsoft. The database can be distributed by using SQL Backup and Restore (Backup the databases on the source server and restore them on the other clients)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Perhaps these resources will help:

SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648

How Do I distribute an existing DataBase - Please Help

Thanks in Advance, Santiago

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Perhaps these resources will help:

SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648

How do I display/upgrade SQL license Info

In SQL 2000 you went to Control Panel and SQL Licensing to view licensing mode and qty. I have searched everywhere for the equivelant in 2005 including books online, knowledge base but can't find anything.

Anybody know where it is hidden as I need to check this server is set up for per processor and check how many processors?

Thanks

Richard

This does not exist in SQL Server 2005, it works by the honour system

How do I display the day name instead of the day #

Hi,

I created a Time Dimension and ended up with the following (as shown in the attached image). The problem is that when I go to the Day level, I see numbers e.g. 1, 2, ..., 31. Instead I want to display the Weekday Name e.g. Monday, Tueday and so on (i.e. what we get with the datename(dw,<date>) function. Right?)

However I am not sure what to change and where in order to get the desired results. Can someone kindly guide me.

Thanks.use:
datename(dw,"dbo"."TimeDimension"."TheDate")

ie

select datename(dw,getdate())|||use:
datename(dw,"dbo"."TimeDimension"."TheDate")

Hi hirshal.

I tried entering the above in the Member Name Column and the Member Key column but did not get the desired effect :(

Can you kindly guide? Do I need to enter the above quoted formula in both fields (Member Name Column and Member Key column) or in only one of these?

Thanks.

How do I display SQL Server 1/1/1900 dates as blank in Access?

Is there an easy way to tell Access (2000 or 2003) to display the "1/1/1900"
that SQL Server 2005 considers a blank date as blank rather than as
"1/1/1900"? I know I can create a function like that shown below to do it.
Public Function BlankDate(datInputDate)
If datInputDate < #1/2/1900# Then
BlankDate = ""
Else
BlankDate = datInputDate
End If
End Function
I'm just hoping there's a simpler more universal way to do it, either on the
Access side or the SQL Server 2005 side.
Thanks!
Michael
I thought that blank date was null.
You could try in your query using:
nullif(FieldName, '1/1/1900') as FieldName
Note that this will not be updatable.
Russel Loski, MCSD.Net
"Michael Nagan" wrote:

> Is there an easy way to tell Access (2000 or 2003) to display the "1/1/1900"
> that SQL Server 2005 considers a blank date as blank rather than as
> "1/1/1900"? I know I can create a function like that shown below to do it.
> Public Function BlankDate(datInputDate)
> If datInputDate < #1/2/1900# Then
> BlankDate = ""
> Else
> BlankDate = datInputDate
> End If
> End Function
> I'm just hoping there's a simpler more universal way to do it, either on the
> Access side or the SQL Server 2005 side.
> Thanks!
> Michael
>
>

How do I display SQL Server 1/1/1900 dates as blank in Access?

Is there an easy way to tell Access (2000 or 2003) to display the "1/1/1900"
that SQL Server 2005 considers a blank date as blank rather than as
"1/1/1900"? I know I can create a function like that shown below to do it.
Public Function BlankDate(datInputDate)
If datInputDate < #1/2/1900# Then
BlankDate = ""
Else
BlankDate = datInputDate
End If
End Function
I'm just hoping there's a simpler more universal way to do it, either on the
Access side or the SQL Server 2005 side.
Thanks!
MichaelI thought that blank date was null.
You could try in your query using:
nullif(FieldName, '1/1/1900') as FieldName
Note that this will not be updatable.
--
Russel Loski, MCSD.Net
"Michael Nagan" wrote:
> Is there an easy way to tell Access (2000 or 2003) to display the "1/1/1900"
> that SQL Server 2005 considers a blank date as blank rather than as
> "1/1/1900"? I know I can create a function like that shown below to do it.
> Public Function BlankDate(datInputDate)
> If datInputDate < #1/2/1900# Then
> BlankDate = ""
> Else
> BlankDate = datInputDate
> End If
> End Function
> I'm just hoping there's a simpler more universal way to do it, either on the
> Access side or the SQL Server 2005 side.
> Thanks!
> Michael
>
>

Friday, February 24, 2012

Sunday, February 19, 2012

Blog Archive

About Me