Consolidation Query: writing

Showing posts with label writing. Show all posts

Tuesday, March 27, 2012

Convert

I am using the following snippet of code to help me convert the date and tim
e
in a query I am writing.
SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
dbo.Subjects.Subject, Convert
(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
The date converts fine to the format that I need. The time does not. It is
being displayed as military times and I want it to appear as a standard 12
format. i.e 9:00 AM
I tried all of the codes I found in BOL and none gave me what I wanted. Is
it possible to do what I want to do?
ThanksI think you can use the following:
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
HTH
Barry|||Thanks. But it still shows up as military time when I use 100.
"Barry" wrote:

> I think you can use the following:
> Convert
> (Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
> HTH
> Barry
>|||Umm not sure why - I have just checked the BOL and it confirms my
suggestion in the CAST and CONVERT section.
What are storing the Time as? Datetime?
Barry|||right(convert(varchar,dbo.TrainingSchedule.RequestedTime,100),7) as Time
Brennan wrote:

>I am using the following snippet of code to help me convert the date and ti
me
>in a query I am writing.
>SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
>dbo.Subjects.Subject, Convert
>(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
> Convert
>(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
>The date converts fine to the format that I need. The time does not. It i
s
>being displayed as military times and I want it to appear as a standard 12
>format. i.e 9:00 AM
>I tried all of the codes I found in BOL and none gave me what I wanted. Is
>it possible to do what I want to do?
>Thanks
>|||Usually, formatting is best left to the client since most client languages
have far better capabilities in this area. It isn't particularly clear what
datatypes you are using for the columns in question - the assumption is that
they are both datetime (or smalldatetime). If this assumption is not valid,
then you should clarify what the datatypes are and the expected formats of
the data (if applicable). One can question the wisdom of separating these
two intimately related bits of information into two separate columns -
especially given the dbms support.
If you must persist in this quest, you will most likely need to "generate"
the appropriate information in some convoluted and complex expression (and
possibly multiple queries). For the convert function, none of the available
formats has a space between the time and the AM/PM characters. If this can
be ignored, the 100 format is the closest - convert to this format and take
the last 7 characters (or all the characters from the last space to the end
of the string). You could also use the datepart functions to strip off and
convert the bits that are of interest. Experiment a bit - I think you will
understand the reason for the my first statement.|||Can you post a repro? Is the datatype really datetime? Also, I agree that fo
rmatting is best
performed in the client application.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:753F4CC7-DC7F-496E-8834-51667F2BFD0B@.microsoft.com...
> Thanks. But it still shows up as military time when I use 100.
> "Barry" wrote:
>|||Thanks I agree with you about the client. I am using smalldatetime.
My problem is that my client is a DNN portal. I am using an add in module
that let's me dynamically display the results of an SQL statement in a grid
on any selected page.
Unfortunately, it does not give me the opportunity to adjust any formatting
which was why I was trying to approach it from a Convert perspective. And I
know nothing about asp so I can't approach the problem from the client side.
I'll try some of the solutions mentions here, but I think I'm going to end u
p
writing an RS report to provide this information to our end users.
Thanks
"Scott Morris" wrote:

> Usually, formatting is best left to the client since most client languages
> have far better capabilities in this area. It isn't particularly clear wh
at
> datatypes you are using for the columns in question - the assumption is th
at
> they are both datetime (or smalldatetime). If this assumption is not vali
d,
> then you should clarify what the datatypes are and the expected formats of
> the data (if applicable). One can question the wisdom of separating these
> two intimately related bits of information into two separate columns -
> especially given the dbms support.
> If you must persist in this quest, you will most likely need to "generate"
> the appropriate information in some convoluted and complex expression (and
> possibly multiple queries). For the convert function, none of the availab
le
> formats has a space between the time and the AM/PM characters. If this ca
n
> be ignored, the 100 format is the closest - convert to this format and tak
e
> the last 7 characters (or all the characters from the last space to the en
d
> of the string). You could also use the datepart functions to strip off an
d
> convert the bits that are of interest. Experiment a bit - I think you wil
l
> understand the reason for the my first statement.
>
>

Tuesday, March 20, 2012

Conversion

Hi,
I am in the process writing scripts for CHAR/VARCHAR to
NCHAR/NVARCHAR conversion..Infact i have completed
it..When I run these scripts against DB is verly
slow..This is happening when i run convert scripts
eaxctly..Even select name from sysobjects doesn't return
values..There are heavy IO is going on the DB..what are
the areas i should concentrate the to improve performance..
Basically I have written cursor whic executes one by one
the alter scripts..
Sridhar...Can you tell us how you are performing the upgrade i.e.
Have a different database or are you performing an alter
column ?
Thanks
Peter
>--Original Message--
>Hi,
> I am in the process writing scripts for CHAR/VARCHAR
to
>NCHAR/NVARCHAR conversion..Infact i have completed
>it..When I run these scripts against DB is verly
>slow..This is happening when i run convert scripts
>eaxctly..Even select name from sysobjects doesn't return
>values..There are heavy IO is going on the DB..what are
>the areas i should concentrate the to improve
performance..
>
>Basically I have written cursor whic executes one by one
>the alter scripts..
>
>Sridhar...
>.
>|||By alter columns...
>--Original Message--
>Can you tell us how you are performing the upgrade i.e.
>Have a different database or are you performing an alter
>column ?
>Thanks
>Peter
>>--Original Message--
>>Hi,
>> I am in the process writing scripts for CHAR/VARCHAR
>to
>>NCHAR/NVARCHAR conversion..Infact i have completed
>>it..When I run these scripts against DB is verly
>>slow..This is happening when i run convert scripts
>>eaxctly..Even select name from sysobjects doesn't return
>>values..There are heavy IO is going on the DB..what are
>>the areas i should concentrate the to improve
>performance..
>>
>>Basically I have written cursor whic executes one by one
>>the alter scripts..
>>
>>Sridhar...
>>.
>.
>|||Changing a CHAR to VARCHAR will most likely involve each row being affected
and hence all the I/O. If you are altering more than one column per table
you might get better performance by creating a second table with the changed
columns and Inserting all the rows into it. Drop the original, rename the
new to the old and add the appropriate indexes, RI etc.. Just make sure you
have good backups first.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:297601c47e07$62bd4ae0$a501280a@.phx.gbl...
> Hi,
> I am in the process writing scripts for CHAR/VARCHAR to
> NCHAR/NVARCHAR conversion..Infact i have completed
> it..When I run these scripts against DB is verly
> slow..This is happening when i run convert scripts
> eaxctly..Even select name from sysobjects doesn't return
> values..There are heavy IO is going on the DB..what are
> the areas i should concentrate the to improve performance..
>
> Basically I have written cursor whic executes one by one
> the alter scripts..
>
> Sridhar...
>|||Consider what Andrew has said, but also regardless of
which way you are doing it take off all indexes before you
start.
How many rows of data are you converting ?
>--Original Message--
>By alter columns...
>>--Original Message--
>>Can you tell us how you are performing the upgrade i.e.
>>Have a different database or are you performing an alter
>>column ?
>>Thanks
>>Peter
>>--Original Message--
>>Hi,
>> I am in the process writing scripts for CHAR/VARCHAR
>>to
>>NCHAR/NVARCHAR conversion..Infact i have completed
>>it..When I run these scripts against DB is verly
>>slow..This is happening when i run convert scripts
>>eaxctly..Even select name from sysobjects doesn't
return
>>values..There are heavy IO is going on the DB..what are
>>the areas i should concentrate the to improve
>>performance..
>>
>>Basically I have written cursor whic executes one by
one
>>the alter scripts..
>>
>>Sridhar...
>>.
>>.
>.
>

Sunday, March 11, 2012

Controlling Data Modification at row level

Hi,
I'm writing an application that involves data that has a set of users
that are allowed to perform certain operations on it.
i.e. Only the row owner can modify a row, but there is a set of users
who can view it.

At the moment, I've started to implement this by calling a UDF at the
beginning of each stored procedure that validates that the user is
allowed to call the procedure on that particular row (trusting a higher
teir to verify the user), and throws an error if they are not.

I don't particularly like this solution, as I need a UDF for each
procedure, and will have to re-write the udf's if the access rules
change (which they might).

Can anyone suggest a method of implementing a more generic row
permissions system?

Cheers,
Ben"Bomza" <benelvin@.hotmail.com> wrote in message
news:1106584255.027100.151190@.c13g2000cwb.googlegr oups.com...
> Hi,
> I'm writing an application that involves data that has a set of users
> that are allowed to perform certain operations on it.
> i.e. Only the row owner can modify a row, but there is a set of users
> who can view it.
> At the moment, I've started to implement this by calling a UDF at the
> beginning of each stored procedure that validates that the user is
> allowed to call the procedure on that particular row (trusting a higher
> teir to verify the user), and throws an error if they are not.
> I don't particularly like this solution, as I need a UDF for each
> procedure, and will have to re-write the udf's if the access rules
> change (which they might).
> Can anyone suggest a method of implementing a more generic row
> permissions system?
> Cheers,
> Ben

Unfortunately there's no built-in or generic solution, so you need to
implement something yourself. Here's an alternative view-based approach,
which might give you some more ideas:

http://vyaskn.tripod.com/row_level_...r_databases.htm

Simon|||Views are of good use when situations like this.
In your table create a field called userID and assing the userID to
each row that has modifying permission. Create a view like this for
egsample....

Create view <name>
AS
Select col1, col2, col3
from <table>
where userid = sUser_sName().

Then update the table by updating this view so that only those records
that are visible to that user can update these records on the
destination table....!

Also create another view where userID<> sUser_sName() and grant only
view permission to the users... So they cant update the table thru this
view...!

This is just a guess. I have not tried it myself. So feel free to
comment on this..!!

Good luck..!|||They're both interesting ideas, and are probably a fair bit faster than
the way I came up with. I like the idea of being able to throw an
error when a client does something illegal rather than just doing
nothing and as I'm using ADO.NET, my "authorising" UDF method provides
a neat way of doing it. At worst the UDF does an EXISTS on a SELECT
statement, so its not a totally horrible method.

It must be a fairly common problem, so when I'm finished I might turn
it into a generic framework where you can grant and evoke various
permissions to any users on any rows, but at the moment I've just got
to get this working :) I think its all been addressed in SQL 2005
anyway...|||Bomza (benelvin@.hotmail.com) writes:
> It must be a fairly common problem, so when I'm finished I might turn
> it into a generic framework where you can grant and evoke various
> permissions to any users on any rows, but at the moment I've just got
> to get this working :) I think its all been addressed in SQL 2005
> anyway...

Not really. There is no particular support in SQL 2005 for row-level
security either.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Bomza" <benelvin@.hotmail.com> wrote in message
news:1106598807.774687.263530@.z14g2000cwz.googlegr oups.com...
<<>>
> It must be a fairly common problem, so when I'm finished I might turn
> it into a generic framework where you can grant and evoke various
> permissions to any users on any rows, but at the moment I've just got
> to get this working :) I think its all been addressed in SQL 2005
> anyway...

I usually address this in the front end design.
Something like.
All users see everything in one screen is read only.
There are links butons or whatever take them to another screen to do
updates.
The update screen is only available to supervisors or when you pick data is
maintained by your team.

This does of course rely on users not trying to break the system by
connecting up using odbc or something.
But.
I suspect others use the same sort of approach and that's why there aren't
loads of solutions available.

--
Regards,
Andy O'Neill

Control+Z when you design a package?

Hi everyone,

Is Microsoft thinking over the possibility to implement Control+Z in order to avoid drawbacks when you're writing a package? I mean, when you drop a container then you aren't able to retrieve again

That same behaviour happened with Sql2k-dts.

Thanks in advance and regards,

Thanks for your suggestion. We will consider this for a future release.

Bob

|||

enric vives wrote:

Hi everyone,

Is Microsoft thinking over the possibility to implement Control+Z in order to avoid drawbacks when you're writing a package? I mean, when you drop a container then you aren't able to retrieve again
.
That same behaviour happened with Sql2k-dts.

Thanks in advance and regards,

Log it at Connect. I will definately vote for it.

-Jamie

Thursday, March 8, 2012

Control parameter help......

I'm writing a page to do some reporting off of one of our databases, and I'm using 3 control parameters for my sql query that feeds my datagrid. 2 of the ControlParameters are from dropdownlists, and one is from a RadioButtonList. The ControlParameters that reference the dropdownlists are both working well, but the one for the RadioButtonList is not.

The error I am getting is:Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near'@.Booga'. (I changed my ControlParameter name from DateStr to Booga to try to avoid any conflicts with reserved words.)

Any ideas? Here is a snippet of my code:

formid="form1"runat="server"><tablewidth="100%"><tr><tdstyle="text-align: center"><strong>DataCenter</strong></td><tdstyle="text-align: center"><strong>Time Scope</strong></td><tdstyle="text-align: center"><strong>Call Status</strong></td></tr><tr><tdstyle="height: 47px; text-align: center"><asp:DropDownListID="DropDownList1"DataSourceID="SqlDataSource2"AutoPostBack="true"DataTextField="LocationName"runat="server"/></td><tdstyle="height: 47px; text-align: center"><asp:RadioButtonListID="RadioButtonList1"runat="server"AutoPostBack="true"Font-Size="Smaller"><asp:ListItemSelected="True"Value=" 1 = 1 ">All</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 1) >= (CAST(GETDATE() AS smalldatetime)))">Last Day</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 7) >= (CAST(GETDATE() AS smalldatetime)))">Last Week</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 30) >= (CAST(GETDATE() AS smalldatetime)))">Last 30 Days</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 120) >= (CAST(GETDATE() AS smalldatetime)))">Last 120 Days</asp:ListItem></asp:RadioButtonList></td><tdstyle="height: 47px; text-align: center"><asp:DropDownListID="DropDownList2"AutoPostBack="true"runat="server"><asp:ListItemSelected="True"Value="Open">Open</asp:ListItem><asp:ListItemValue="Closed">Closed</asp:ListItem></asp:DropDownList></td></tr></table><br/><pstyle="text-align: center"><strong>Displaying All Open Tickets</strong><br/></p><asp:SqlDataSourceID="SqlDataSource2"runat="server"SelectCommand="SELECT DISTINCT [locationname] FROM [profile]"ConnectionString="<%$ ConnectionStrings:Heat %>"/><tablewidth="100%"><trwidth="100%"><tdvalign="top"width="100%"><asp:GridViewID="GridView1"AllowSorting="True"runat="server"DataSourceID="SqlDataSource1"DataKeyNames="CallID"AutoGenerateColumns="False"Font-Size="Smaller"Width="100%"><Columns><asp:CommandField/><asp:BoundFieldDataField="CallID"HeaderText="Call ID"ReadOnly="True"SortExpression="CallID"/><asp:BoundFieldDataField="CustID"HeaderText="Customer ID"ReadOnly="True"SortExpression="CustID"/><asp:BoundFieldDataField="CallType"HeaderText="Call Type"ReadOnly="True"SortExpression="CallType"/><asp:BoundFieldDataField="Priority"HeaderText="Priority"ReadOnly="True"SortExpression="Priority"/><asp:BoundFieldDataField="Cause"HeaderText="Cause"ReadOnly="True"SortExpression="Cause"/><asp:BoundFieldDataField="CallDesc"HeaderText="Call Description"ReadOnly="True"SortExpression="CallDesc"><ItemStyleWidth=40%/></asp:BoundField><asp:BoundFieldDataField="RecvdBy"HeaderText="Received By"ReadOnly="True"SortExpression="RecvdBy"/><asp:BoundFieldDataField="RecvdDate"HeaderText="Call Date"ReadOnly="True"SortExpression="RecvdDate"><ItemStyleWrap="False"/></asp:BoundField><asp:BoundFieldDataField="RecvdTime"HeaderText="Call Time"ReadOnly="True"SortExpression="RecvdTime"><ItemStyleWrap="False"/></asp:BoundField></Columns></asp:GridView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"SelectCommand="SELECT * FROM CallLog INNER JOIN Profile ON CallLog.CustID = Profile.CustID WHERE (Profile.LocationName = @.LocationName) AND (CallLog.CallStatus = @.CallStatus) AND @.Booga "ConnectionString="<%$ ConnectionStrings:Heat %>"><SelectParameters><asp:ControlParameterControlID="DropDownList1"Name="LocationName"PropertyName="SelectedValue"Type="String"/><asp:ControlParameterControlID="DropDownList2"Name="CallStatus"PropertyName="SelectedValue"Type="String"/><asp:ControlParameterControlID="RadioButtonList1"Name="Booga"PropertyName="SelectedValue"Type="String"/></SelectParameters></asp:SqlDataSource></td></tr></table><br/><br/><br/>

<br />

</form>

Your SQL looks incomplete:

What is @.Boonga supposed to be related to?

|||

That's where the ControlParameter problem is:

<asp:ControlParameterControlID="RadioButtonList1"Name="Booga"PropertyName="SelectedValue"Type="String"/>

That should be making the SQL command end with one of the date comparisons that are controlled by the radiobuttonlist control.

Friday, February 24, 2012

contact details

I have a database which has contact column eg. Mr Peter Smith

I am writing a new database which is to have three seperate columns.. saluation, first name and surname. What would be the best way to split the column up?? I was thinking on concentrating on the spaces??

Note: some conacts may not have saluation inc in the contact column, and in this case the saluation column should be blank...

ThanksNote: some conacts may not have saluation inc in the contact column, and in this case the saluation column should be blank...

That bit should make it interesting.
Do you have a list somewhere of "valid" salutations?
What will you do with the extra space in:
Mr Peter Smith Jr.

Sunday, February 19, 2012

Consume Connection from Report Explorer Web Part

Hello!
I am writing a custom report viewer that I would like to work with the
Report Explorer web part in SP2.
I created my consumer web part and it is connected to the Report Explorer
but it does not seem to ever receive any data from the Report Explorer. Is
the Report Viewer web part the only web part that can consume the information
provided by the Report Explorer?
TIA!
BrianHi Brian,
Welcome to use MSDN Managed Newsgroup!
From your description, my understanding of this issue is: you want to write
the consumer web part to connect to the Report Explorer Web part. If I
misunderstood your concern, please feel free to point it out.
Based on my knowledge, the Report Explorer Web Part and the Report Viewer
Web Part use the technical "Connectable Web Part to handle the connections
between them. So if you want to customize your web part to connect to the
Report Explorer, you need to implement the ICellConsumer interface.
Here is a article talk about the technical "Connectable Web Part" and there
is the sample code for how to implement the interface.
Creating a Connectable Web Part
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spptsdk/htm
l/CreateConnectableWP_SV01003714.asp
Using Reporting Services SharePoint Web Parts in SQL Server 2000 Reporting
Services Service Pack 2
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/webrssp2.mspx
#EIAA
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael.
Thanks for the post.
Let me be a bit more specific. I already wrote a test web part implementing
the ICellConsumer interface.
When I connect it to the Report Explorer, the Cell that I get is an empty
string.
Actually, the CellReady method only seems to be called when the page loads.
Clicking on folders and reports in the Report Explorer does not seem to pass
any information to my web part. It passes something to the Report Viewer when
it is on the same page since instead of opening a new window to display a
report, the Report Viewer displays the report.
--
Take care,
Brian
"Michael Cheng [MSFT]" wrote:
> Hi Brian,
> Welcome to use MSDN Managed Newsgroup!
> From your description, my understanding of this issue is: you want to write
> the consumer web part to connect to the Report Explorer Web part. If I
> misunderstood your concern, please feel free to point it out.
> Based on my knowledge, the Report Explorer Web Part and the Report Viewer
> Web Part use the technical "Connectable Web Part to handle the connections
> between them. So if you want to customize your web part to connect to the
> Report Explorer, you need to implement the ICellConsumer interface.
> Here is a article talk about the technical "Connectable Web Part" and there
> is the sample code for how to implement the interface.
> Creating a Connectable Web Part
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spptsdk/htm
> l/CreateConnectableWP_SV01003714.asp
> Using Reporting Services SharePoint Web Parts in SQL Server 2000 Reporting
> Services Service Pack 2
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/webrssp2.mspx
> #EIAA
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Brain,
In the Report Explorer Web part, it pass the ReportUrl in the cell variable
in the cellReadyArgs passed in the CellReady EventHandler. And the Report
Viewer get it in the CellReady event. This event fire handled all by Web
Part architecture, and in you scenario, it seems the Report Explorer and
your own Web Part do not connected correctly, you may try to debug the SPS
session to see what these 2 Web Part really does.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, March 27, 2012

Convert

Tuesday, March 20, 2012

Conversion

Conversion

Conversion

Sunday, March 11, 2012

Controlling Data Modification at row level

Control+Z when you design a package?

Thursday, March 8, 2012

Control parameter help......

Friday, February 24, 2012

contact details

Sunday, February 19, 2012

Consume Connection from Report Explorer Web Part

Consolidation Query

Blog Archive

About Me