How do I manage this mess? Thanks!

Say for example I have the following 2 database tables, the first one contains the old employee data, and has the fields shown below:

oldEmployeeID

FirstName

LastName

DateOfBirth

HiringDate

TerminationDate

and another one containing the new employee data with similar fields but instead of oldEmployeeID, it is showing the newEmployeeID.

During the conversion process, something were messed up and instead of putting in the original hiring date of the workers into the new employee database, the conversion date was put in, which, depending on the mood of HR ladies, could be any date, and at the same time, of course, new employee join the company, and we assume their hiring dates were entered correctly. On top of that, there are some employee who were terminated before the conversion took place but we still need to keep a record of that.

And I created a third table, say, emplyeeAll with similar fields to the employee data tables.

So here is what I need to do: if the firstName, lastName and DateOfBirth in the old employee data table and the new employee data table matches, I would assume they are the same employee, hence I would put the information for the employee obtained from the new employee data table to the employeeAll table, with the Hiring Date changed to the Hiring Date of the old employee data table (and do not copy the record from the old employee table to prevent duplicates), otherwise, I would simply copy and paste the data in new and old employee table to my employeeAll table.

I know this is really confusing, but...well...hope you know what I am saying...

Is it possible to have a SQL statement for all these? If so, how should the statement looks like?

Thanks a lot!

Regards,

Anyi

Hi,

If I understand this correctly, (I think I do....perhaps) ... if you have data in two tables (and I know you have three, the last would be the destinationtable employeeAll) and you wish to collect the difference between table 1 and 2 (employeeOld, employeeNew) then the left outer join is your answer...

--If you wish to collect differential rows from old table

select employeeOld.*

from employeeOld

left outer join employeeNew on employeeOld.FirstName = employeeNew.FirstName and employeeOld.DateOfBirth = employeeNew.DateOfBirth

where employeeNew.DateOfBirth is null

Similarly you can mix 'n match the above to collect differetial data from the other two tables...note the 'select' is table where the additional data lies and the where clause (is null) is the comparison table.

Hope it helps

|||

I thinks that something like this is what you want:

Code Snippet

-- This collects the data
INSERT INTO EmployeeAll
SELECT
n.EmployeeID,
n.LastName,
n.FirstName,
n.DateOfBirth,
coalesce( o.HiringDate, n.HiringDate ),
coalesce( o.TerminationDate, n.TerminationDate ),
n.{RemainingColumns}
FROM NewEmployees n
LEFT JOIN OldEmployees o
ON ( n.LastName = o.LastName
AND n.FirstName = o.FirstName
AND n.DateOfBirth = o.DateOfBirth
)

|||

But would this actually copy the rest of the record into EmployeeAll, i.e., the records that only showed up in the old employee table (the employees terminated before the conversion took place) or the records that only showed up on the new employee table (i.e., the employees hired after the conversion was completed)?

Thanks!

Regards,

Anyi

Arnie Rowland wrote:

I thinks that something like this is what you want:

Code Snippet

-- This collects the data
INSERT INTO EmployeeAll
SELECT
n.EmployeeID,
n.LastName,
n.FirstName,
n.DateOfBirth,
coalesce( o.HiringDate, n.HiringDate ),
coalesce( o.TerminationDate, n.TerminationDate ),
n.{RemainingColumns}
FROM NewEmployees n
LEFT JOIN OldEmployees o
ON ( n.LastName = o.LastName
AND n.FirstName = o.FirstName
AND n.DateOfBirth = o.DateOfBirth
)

|||

If you want both the records in the old table that don't exists in the new table, the records in the new table that don't exists in the old table, and the records that are in both (with the corrected HiringDate), then change the JOIN from a LEFT JOIN to a FULL JOIN. (This works in SQL 2005 -NOT SQL 2000.)

If you are using SQL 2000, you will need three queries to accomplish the same task.

How do I make the name of a variable dynamic?

Hi guys, I have the following store procedure:

PROCEDURE dbo.AddSearchColumn (@.A1, @.A2. @.A3, @.A4, @.A5) AS
Declare @.cElements cursor,@.DocNum varchar(100)
BEGIN
Set @.cElements = cursor for select FirstNaname rom dbo.DocTable1
open @.cElements
fetch NEXT from @.cElements into @.DocNum

while (@.@.FETCH_STATUS = 0)
begin
if (@.DocNum==@.A1 //Here is what I need to do: I want use @.A1 at the first loop step, @.A2 at the second, and so on
...... // @.A1, @.A2 are the parameters in input
end
END

close @.cElements
DEALLOCATE @.cElements

I hope my explanation is clear.

Please, give some hints.
Thanks for your time,

Christian Malatesti

Can you explain in more detail what it is that you are trying to do? From what I understand you want to compare the values in the parameters against "FirstNaname" from table docTable1?

|||

What I am trying to do is this:

I have several parameters in input: @.A1, @.A2, @.A3, @.A4,...and so on (they are more than four) that I need to use

while looping through the elements of the cursor.

I know the order that the input parameters need to be used.

At the first step of the loop I have to compare the first element retrieved by the cursor with @.A1.

At the second step of the loop I have to compare the second element retrieved by the cursor with @.A2.

At the third step of the loop I have to compare the third element retrieved by the cursor with @.A3.

and so on...

What I would like to accomplish is creating a variable (@.dynamicVariable) that at the first contains the value of @.A1, atthe second step, @.dynamicVariable contains the value of @.A2, andso on...

Below is a basic example.

Declare@.dynamicVariable , @.counter int

set @.counter=1;

while (@.@.FETCH_STATUS = 0)
begin

set @.dynamicVariable = "@.A" + ToString(@.counter)
if (@.DocNum==@.dynamicVariable....

//something like this:

set @.counter=@.counter + 1;
end
END

Thanks for responding me.

Christian Malatesti

|||Is the number of parameters fixed?|||YES, i have 17 parameters in input|||And you will always have 17 values returned from your SQL statement?|||

I do not have any parameters in output.

The input parameters are used to perfrom various INSERT, that I did not include in the post.

Christian

|||

Perhaps you can get the results into 2 tables (table variables) each with identity columns and compare by rowid?

How do I insert text into empty xml element in sql server 2005?

Hello

I have the following xml data stored in an xml datatype colmun in sql
server 2005:

<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>

I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.

...
<pagetext>
Some content goes here.
</pagetext>
...

I've tried to achieve this using the query below:

update tbl_tree SET theTree.modify(' replace value of (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')

but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'

I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.

Is there a way to do this?

Any help very much appreciated.

For those interested, this has now been answered via a newsgroup here:
http://groups.google.com/group/microsoft.public.sqlserver.xml/browse_thread/thread/424e62d11c396b3e/bab10449576dccd8#bab10449576dccd8

How do I insert text into empty xml element in sql server 2005?

Hello
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
PeterYou would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegroups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>|||Thanks Michael - I appreciate your help.sql

How do I insert text into empty xml element in sql server 2005?

Hello
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
Peter
You would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegro ups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>
|||Thanks Michael - I appreciate your help.

How Do I Increase File Upload Size?

Does anyone know what the max upload size is? I want to save some documents
with my reports, but I get the following errors:
"Maximum request length exceeded."
or
"There was an exception running the extensions specified in the config file.
--> Maximum request length exceeded."
Thanks in advance,
-RGJIs the report server running on Windows 2003 with IIS 6? IIS 6 has a
security restriction of a default 4 MB file upload/download limit. This is
due to a buffering restriction implemented in IIS 6.0
(AspMaxRequestEntityAllowed setting in MetaBase.xml).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RGJ" <RGJ@.discussions.microsoft.com> wrote in message
news:81C3E356-EE8C-490E-BC38-8F3A83DAF9E8@.microsoft.com...
> Does anyone know what the max upload size is? I want to save some
documents
> with my reports, but I get the following errors:
> "Maximum request length exceeded."
> or
> "There was an exception running the extensions specified in the config
file.
> --> Maximum request length exceeded."
> Thanks in advance,
> -RGJ|||You got my hopes up :-)
Yes, It is running on Windows 2003 with IIS 6. I have tried changing that
value, but no luck.
Here is the documentation I found on that setting:
he AspMaxRequestEntityAllowed property specifies the maximum number of bytes
allowed in the entity body of an ASP request. If a Content-Length header is
present and specifies an amount of data greater than the value of
AspMaxRequestEntityAllowed, IIS returns a 403 error response. This property
is related in function to MaxRequestEntityAllowed, but is specific to ASP
request. Whereas you might set the MaxRequestEntityAllowed property to 1 MB
at the general World Wide Web Publishing Service (WWW Service) level, you may
choose to set AspMaxRequestEntityAllowed to a lower value, if you know that
your specific ASP applications handle a smaller amount of data.
I also tried increasing the AspBufferingLimit which has a 4Mb limit to 40Mb
- no good
The AspBufferingLimit property sets the maximum size of the ASP buffer. If
response buffering is turned on, this property controls the maximum number of
bytes that an ASP page can write to the response buffer before a flush occurs.
Do you know if there is a setting in web.config that might override this?
Thank you for your assistance,
-RGJ
"Robert Bruckner [MSFT]" wrote:
> Is the report server running on Windows 2003 with IIS 6? IIS 6 has a
> security restriction of a default 4 MB file upload/download limit. This is
> due to a buffering restriction implemented in IIS 6.0
> (AspMaxRequestEntityAllowed setting in MetaBase.xml).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "RGJ" <RGJ@.discussions.microsoft.com> wrote in message
> news:81C3E356-EE8C-490E-BC38-8F3A83DAF9E8@.microsoft.com...
> > Does anyone know what the max upload size is? I want to save some
> documents
> > with my reports, but I get the following errors:
> >
> > "Maximum request length exceeded."
> > or
> > "There was an exception running the extensions specified in the config
> file.
> > --> Maximum request length exceeded."
> >
> > Thanks in advance,
> >
> > -RGJ
>
>|||Please verify that in IIS Manager, you have turned on "Enable direct
metabase edit / all­ow changes to MetaBase configuration while IIS is
running" before changing metabase.xml. Otherwise the changes will be
ignored! Also did you try to run iisreset?
You should also be able to do this from the command-line:
1. cscript adsutil.vbs get w3svc/aspmaxrequestentityallowed
2. replace 'get' to 'set' to configure the new value
3. then get again
Is it new or the old value?
-Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RGJ" <RGJ@.discussions.microsoft.com> wrote in message
news:5947747C-0259-4424-BA3D-272FECD7D33C@.microsoft.com...
> You got my hopes up :-)
> Yes, It is running on Windows 2003 with IIS 6. I have tried changing that
> value, but no luck.
> Here is the documentation I found on that setting:
> he AspMaxRequestEntityAllowed property specifies the maximum number of
bytes
> allowed in the entity body of an ASP request. If a Content-Length header
is
> present and specifies an amount of data greater than the value of
> AspMaxRequestEntityAllowed, IIS returns a 403 error response. This
property
> is related in function to MaxRequestEntityAllowed, but is specific to ASP
> request. Whereas you might set the MaxRequestEntityAllowed property to 1
MB
> at the general World Wide Web Publishing Service (WWW Service) level, you
may
> choose to set AspMaxRequestEntityAllowed to a lower value, if you know
that
> your specific ASP applications handle a smaller amount of data.
> I also tried increasing the AspBufferingLimit which has a 4Mb limit to
40Mb
> - no good
> The AspBufferingLimit property sets the maximum size of the ASP buffer. If
> response buffering is turned on, this property controls the maximum number
of
> bytes that an ASP page can write to the response buffer before a flush
occurs.
> Do you know if there is a setting in web.config that might override this?
> Thank you for your assistance,
> -RGJ
> "Robert Bruckner [MSFT]" wrote:
> > Is the report server running on Windows 2003 with IIS 6? IIS 6 has a
> > security restriction of a default 4 MB file upload/download limit. This
is
> > due to a buffering restriction implemented in IIS 6.0
> > (AspMaxRequestEntityAllowed setting in MetaBase.xml).
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "RGJ" <RGJ@.discussions.microsoft.com> wrote in message
> > news:81C3E356-EE8C-490E-BC38-8F3A83DAF9E8@.microsoft.com...
> > > Does anyone know what the max upload size is? I want to save some
> > documents
> > > with my reports, but I get the following errors:
> > >
> > > "Maximum request length exceeded."
> > > or
> > > "There was an exception running the extensions specified in the config
> > file.
> > > --> Maximum request length exceeded."
> > >
> > > Thanks in advance,
> > >
> > > -RGJ
> >
> >
> >|||Robert,
You were close. Thanks for putting me on the right track.
I was able to increase the File Upload by following this article ..
====================================================
Upload Larger Files
By default, ASP.NET only permits files that are 4,096 kilobytes (KB) (or 4
MB) or less to be uploaded to the Web server. To upload larger files, you
must change the maxRequestLength parameter of the <httpRuntime> section in
the Web.config file.
Note When the maxRequestLength attribute is set in the Machine.config file
and then a request is posted (for example, a file upload) that exceeds the
value of maxRequestLength, a custom error page cannot be displayed. Instead,
Microsoft Internet Explorer will display a "Cannot find server or DNS" error
message.
If you want to change this setting for all of the computer and not just this
ASP.NET application, you must modify the Machine.config file.
By default, the <httpRuntime> element is set to the following parameters in
the Machine.config file:
<httpRuntime executionTimeout="90" maxRequestLength="4096"
useFullyQualifiedRedirectUrl="false" minFreeThreads="8"
minLocalRequestFreeThreads="4" appRequestQueueLimit="100" />
The Machine.config file is located in the \System
Root\Microsoft.NET\Framework\Version Number\CONFIG directory.
====================================================
I modifiedthe machine.config file and reboot - and it worked !!!
Thanks for your help,
-RGJ|||Thanks for getting back and sharing how you resolved it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RGJ" <RGJ@.discussions.microsoft.com> wrote in message
news:609C3712-46CF-451A-AC98-B09864097228@.microsoft.com...
> Robert,
> You were close. Thanks for putting me on the right track.
> I was able to increase the File Upload by following this article ..
> ====================================================> Upload Larger Files
> By default, ASP.NET only permits files that are 4,096 kilobytes (KB) (or 4
> MB) or less to be uploaded to the Web server. To upload larger files, you
> must change the maxRequestLength parameter of the <httpRuntime> section in
> the Web.config file.
> Note When the maxRequestLength attribute is set in the Machine.config file
> and then a request is posted (for example, a file upload) that exceeds the
> value of maxRequestLength, a custom error page cannot be displayed.
Instead,
> Microsoft Internet Explorer will display a "Cannot find server or DNS"
error
> message.
> If you want to change this setting for all of the computer and not just
this
> ASP.NET application, you must modify the Machine.config file.
> By default, the <httpRuntime> element is set to the following parameters
in
> the Machine.config file:
> <httpRuntime executionTimeout="90" maxRequestLength="4096"
> useFullyQualifiedRedirectUrl="false" minFreeThreads="8"
> minLocalRequestFreeThreads="4" appRequestQueueLimit="100" />
> The Machine.config file is located in the \System
> Root\Microsoft.NET\Framework\Version Number\CONFIG directory.
> ====================================================> I modifiedthe machine.config file and reboot - and it worked !!!
> Thanks for your help,
> -RGJ
>

How do I group this query here

I have the following sql statement below;

DECLARE @.val int
SET @.val = 1
WHILE (@.val <= 7 )
BEGIN
SELECT TOP(1) id, queue
FROM itn_articles WHERE asection = @.val
ORDER BY queue DESC
SET @.val = @.val + 1
END

Essential it just loops through a select statement 7 times, now the problem is how would I do this and group my results together so I could ORDER them; instead them coming out in a different query output and, which makes the order unrankable

Why do you need to do it in a loop? You can get the same results as

SELECT Id,Queue FROM itn_articles WHERE asection <= 7

|||

OMG, I feel like such a douche, lol..Thanks for the help guys I really appreciate it

How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT â' the following columns â'DatabaseName,
ObjectId and ObjectNameâ' columns were empty.
Why is this?
Please let me know what Iâ'm missing out on?
(Note: A similar question had been posted earlier on â' but there was no
satisfactory answer to it.)
Cheers!
SQLCatzThis is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running traces on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & SQL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular tables using the TextData
column. this is so far the only reliable way I found to audit access for a set of tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT â' the following columns â'DatabaseName,
> ObjectId and ObjectNameâ' columns were empty.
> Why is this?
> Please let me know what Iâ'm missing out on?
> (Note: A similar question had been posted earlier on â' but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be able
to get all the tables that were accessed.
Cheers!
SQLCatz

How do i get the report in PDF format?

Hi all,

I need to get a report in PDF format.How to get that.I did the coding but am not getting.The following code i tried am not getting.what is the wrong in this coding.

WebMedRS.ReportingService rs = new WebMedRS.ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
parametersIn[0] = new ParameterValue();
parametersIn[0].Name = "claimid";
parametersIn[0].Value = Request.QueryString[0].ToString();
ParameterValue[] parametersOut = null;
byte[] result = null;
string reportPath = "/CMS/CmsFrom1";
string format = "Image";
string historyID = null;
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;
result = rs.Render(reportPath, format, null, null, parametersIn, null, null, out encoding, out mimeType, out parametersOut, out warnings, out streamIDs);
Response.ClearContent();
Response.AppendHeader("content-length", result.Length.ToString());
Response.ContentType = "application/pdf";
Response.BinaryWrite(result);
Response.Flush();
Response.Close();

Thanks In Advance,

Yours,

Senthil

You will have to change the format:

string format = "PDF";

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How do I get the CLSID for SQL Server 2005 OLEDB Provider?

How do I get the CLSID for SQL Server 2005 OLEDB Provider? The following code yields the CLSID for 2000 Provider

m_hr = CLSIDFromProgID(L"SQLOLEDB",&clsid); // SQL Server

Thanks

JEK

Do you have in mind m_hr = CLSIDFromProgID(L"SQLNCLI",&clsid); ?

|||

That's it...

Thanks

JEK

How do I get SSIS to do this...?

I feel like I'm losing my mind. I can write code in 7 different languages, but I can't figure out how to create an SSIS package to do the following:

Table A, 6 columns: EmpID, Code1, Code2, Code3, LocationCode, ScriptPath

Table B, 5 columns: Code1, Code2, Code3, LocationCode, ScriptPath

Using the values in the Code columns for lookup, I need to pull the most specific value from Table B and store it in Table A. So the task is to fill in the LocationCode and ScriptPath columns in Table A from Table B using the following logic:

If

Table B has a row where Code1,

Code2 and Code3 all match the

appropriate values in the Table A

row then copy the LocationCode

from Table B to that row in Table A.

Else If

Table B has a row where Code1

and Code2 both match the appropriate

values in the Table A row then copy

the LocationCode from Table B to

that row in Table A.

Else If

Table B has a row where Code1

matches the appropriate value

Table A row then copy the

LocationCode from Table B to

that row in Table A.

Else

Place a default value in that row

in Table A.

Same logic for the ScriptPath.

The logic is pretty straight forward, but I can't wrap my brain around how to turn this into an SSIS package.

Can someone give me a quick sketch of what components to use to create something like this?

Thanks.

J

Roughly,

Lookup on 1, 2, 3

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1, 2

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1

->Successful lookup -> Union All

->Failed lookup -> Derived Column (for default) -> Union All

It's the same Union All for all branches.

Honestly, I think I'd do this in a Exec SQL, and not a data flow. If I am understanding your question properly, you will be updating Table A, so you'd either have to use an OLE DB Command in the data flow to issue the update, or write it to a temp table and issue an Exec SQL after the data flow.

|||

I'm writing it as a stored procedure now, but I just wanted to get my hands dirty with the SSIS stuff - so I gave it a shot.

I can't even figure out how to get the lookups to work properly in SSIS. When I play with it some more, I'll post the error that I'm getting. Maybe you can tell me what I'm doing wrong at that point...

Thanks for the response.

J

How do I get rid of this problem

Hi, I did a web page for the intranet where I work using ASP and a database in SQL Server, the connection line is the following:
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=sqloledb;Server=zapopan02;database=data;U ser Id=sa"
The problem comes the first time I try to establish the connection after 5 min. (aprox) of inactivity.
I get the following error:
Microsoft OLE DB Provider for SQL Server error '80004005'
Timeout expired
Then, I refresh the page and it works fine (and keeps working until I stop using the page for a while)
Could someone pleaseee tell me how to get rid of this problem?
Thank in advance
-RichardGenerally some sort of problem finding the DSN. Could be lost in the global.asa file, or perhaps this is not a SYSTEM DSN.

May refer to this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q169/4/70.asp&NoWebContent=1) about connection pooling FAQ.|||For those who might have the same problem that I did, here is the answer to my own question:
There is a parameter named Connect Timeout wich is 15 seconds by default, that means that if after 15 sec the connection has not been established, oledb returns an error.
So, I changed the connection line to the following:
oConn.Open " Provider=sqloledb;Server=zapopan02;database=data;U
ser Id=sa;Connection Timeout=30"
And it worked, but anyway, I dont feel so satisfied because the problem is not 100% solved, I dont get the error anymore and the page opens but its still slow the first time.
Thanks anyway
- Richard

how do i get date into dd-mm-yy format?

i have the following in an insert stored proc

CONVERT(CHAR(8),GETDATE(),10)

but this inserts 03-06-06.

how do i get it to 06-03-06?

Cheers,
Craig

Probably by setting the machine you're running on to run in a time zone where this format is standard ?|||

my sp is as follows:-

IF (@.blnConsent = 1 AND @.dteActualConsentDate <> NULL) --This is PC insert for YES consent where a ConsentDate is supplied
BEGIN
INSERT INTO tblStudyServices
(SurveyID, PatientID, FourRegularDrugs, FourRegularDrugsNo, HRD,
ReadmissSixMonths, Consent, ConsentDate, CreationDate)
VALUES
(@.intSurveyID, @.intPatientID, @.blnFourRegularDrugs, @.intFourRegularDrugsNo, @.blnHRD,
@.blnReadmissSixMonths, @.blnConsent, @.dteActualConsentDate, CONVERT(CHAR(8),GETDATE(),10))
END

@.dteActualConsentDate is passed in as 14-03-06

if i then look in the table it has

CosentDate = 14-03-06
CreationDate = 03-06-06

i have tried SET_DATEFORMAT dmy

but still no luck

|||Datetime values are not stored in any special format. There are special allowed format and rules for inserting datetime. For output, it is the client application that formats the value to something that is human-readable. See http://www.karaszi.com/SQLServer/info_datetime.asp for more information. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:3207581d-2ba8-4191-b788-7db50a967db0@.discussions.microsoft.com... >i have the following in an insert stored proc >
> CONVERT(CHAR(8),GETDATE(),10) >
> but this inserts 03-06-06. >
> how do i get it to 06-03-06? >
> Cheers,
> Craig >
>|||Did you see the reply I posted? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:acc61a6e-96e2-4412-9e4f-5ffdd7ab7b62@.discussions.microsoft.com...
> my sp is as follows:- >
> IF (@.blnConsent = 1 AND @.dteActualConsentDate <> NULL) --This is PC
> insert for YES consent where a ConsentDate is supplied
> BEGIN
> INSERT INTO tblStudyServices
> (SurveyID, PatientID, FourRegularDrugs, FourRegularDrugsNo, HRD,
> ReadmissSixMonths, Consent, ConsentDate, CreationDate)
> VALUES
> (@.intSurveyID, @.intPatientID, @.blnFourRegularDrugs,
> @.intFourRegularDrugsNo, @.blnHRD,
> @.blnReadmissSixMonths, @.blnConsent, @.dteActualConsentDate,
> CONVERT(CHAR(8),GETDATE(),10))
> END >
> @.dteActualConsentDate is passed in as 14-03-06 >
> if i then look in the table it has >
> CosentDate = 14-03-06
> CreationDate = 03-06-06 >
> i have tried SET_DATEFORMAT dmy >
> but still no luck >
>

How do I escape ampersands in stored procedure?

I am using the following stored procedure to gather information from one of my tables:
CREATE PROCEDURE usr_GetCustomerNumber
@.custName varchar(20)
AS
SELECT CUNO FROM CIPNAME0
WHERE CUNM LIKE @.custName
GO
The problem I am facing is that some of the customers have ampersands (&) in their names, i.e. A & M Auto Supply. When I feed in anything with an ampersand to @.custName, procedure doesn't return any information. I am using the following code to call the stored procedure:
Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
Dim custSelected As String
Dim custNum As String
Dim objDataSet As New DataSet
Dim objCmd As New SqlCommand("usr_GetCustomerNumber", objConn)
Dim objReader As SqlDataReader
Try
lblStatus.Text = ""
custSelected = lstResults.SelectedItem.Text
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@.custName", custSelected)
objConn.Open()
objReader = objCmd.ExecuteReader
While objReader.Read
custNum = objReader("CUNO")
End While
objReader.Close()
objConn.Close()
Catch ex As SqlException
lblStatus.ForeColor = Color.Red
lblStatus.Text = ex.Message
End Try
lblStatus.Text = custNum
End Sub
I am trying to determine exactly how I would escape the & and still get the desired results. Should I search the custSelected string for the & and try to escape it before it gets to the stored procedure? I have no clue.
Thanks!
Ariston Collander
ariston@.coxcomputer.comAmpersand character does not have any special meaning in LIKE pattern. Only characters %, _, [, ], ^ has special meaning. Your C# code looks find to me. The ampersand character might be getting encoded before reaching your click routine. You can verify it by either setting a break point in the C# code and looking at the value or running a SQL Profiler trace to see the executed SP call with the parameter values.

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 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 determine if SQL Sever Login is disabled using T-Sql?

How do I determine if SQL Sever Login is disabled using T-Sql?
I can disable it and enabled it using the following T-Sql function:
ALTER LOGIN login [ENABLE | DISABLE]
I just want to know if it is enable or disabled?It's stored in the system table sys.server_principals, in the column called
is_disabled
However, in SQL Server 2005 system tables are not visible by default; you
must use the Dedicated Administrator Connection (DAC) which you can read
about in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Hi,
You can also query the system view and look into column is_disabled.1
denotes the login is disabled
select * from sys.sql_logins
Tahnks
Hari
SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Thanks Hari. I totally blew that answer. I was doing some testing, and was
in a connection that was not a sysadmin, just a regular user. So when I
couldn't see server_principals, I assumed it was because it was a system
table.
Then of course logging in using the DAC, I was sysadmin, so I could see that
view.
So sys.server_principals is the answer.
If you check the BOL for sys.sql_logins, you'll see that it is a view based
on sys.server_principals, containing all the columns from that view, plus a
view others.
--
HTH
Kalen Delaney, SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> Hi,
> You can also query the system view and look into column is_disabled.1
> denotes the login is disabled
> select * from sys.sql_logins
>
> Tahnks
> Hari
> SQL Server MVP
> <rodrigo.gloria@.gmail.com> wrote in message
> news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
>> How do I determine if SQL Sever Login is disabled using T-Sql?
>> I can disable it and enabled it using the following T-Sql function:
>> ALTER LOGIN login [ENABLE | DISABLE]
>> I just want to know if it is enable or disabled?
>|||Kalen Delaney wrote:
> Thanks Hari. I totally blew that answer. I was doing some testing, and was
> in a connection that was not a sysadmin, just a regular user. So when I
> couldn't see server_principals, I assumed it was because it was a system
> table.
> Then of course logging in using the DAC, I was sysadmin, so I could see that
> view.
> So sys.server_principals is the answer.
> If you check the BOL for sys.sql_logins, you'll see that it is a view based
> on sys.server_principals, containing all the columns from that view, plus a
> view others.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> > Hi,
> >
> > You can also query the system view and look into column is_disabled.1
> > denotes the login is disabled
> >
> > select * from sys.sql_logins
> >
> >
> >
> > Tahnks
> > Hari
> > SQL Server MVP
> >
> > <rodrigo.gloria@.gmail.com> wrote in message
> > news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> >> How do I determine if SQL Sever Login is disabled using T-Sql?
> >>
> >> I can disable it and enabled it using the following T-Sql function:
> >>
> >> ALTER LOGIN login [ENABLE | DISABLE]
> >>
> >> I just want to know if it is enable or disabled?
> >>
> >
> >
Thank you very much.

How do I determine if SQL Sever Login is disabled using T-Sql?

How do I determine if SQL Sever Login is disabled using T-Sql?
I can disable it and enabled it using the following T-Sql function:
ALTER LOGIN login [ENABLE | DISABLE]
I just want to know if it is enable or disabled?It's stored in the system table sys.server_principals, in the column called
is_disabled
However, in SQL Server 2005 system tables are not visible by default; you
must use the Dedicated Administrator Connection (DAC) which you can read
about in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Hi,
You can also query the system view and look into column is_disabled.1
denotes the login is disabled
select * from sys.sql_logins
Tahnks
Hari
SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Thanks Hari. I totally blew that answer. I was doing some testing, and was
in a connection that was not a sysadmin, just a regular user. So when I
couldn't see server_principals, I assumed it was because it was a system
table.
Then of course logging in using the DAC, I was sysadmin, so I could see that
view.
So sys.server_principals is the answer.
If you check the BOL for sys.sql_logins, you'll see that it is a view based
on sys.server_principals, containing all the columns from that view, plus a
view others.
--
HTH
Kalen Delaney, SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> Hi,
> You can also query the system view and look into column is_disabled.1
> denotes the login is disabled
> select * from sys.sql_logins
>
> Tahnks
> Hari
> SQL Server MVP
> <rodrigo.gloria@.gmail.com> wrote in message
> news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
>