query database table

Friday, March 30, 2012

How to remove unused columns and eliminate warnings

I have hundreds of these I think mainly due to the way the union all task works. Any ideas on ways to eliminate these?Select the connections in the editor and hit the delete button.
K|||

I guess I wasn't clear. Those columns are used earlier on in the data flow so I can't delete them from the connection. However I get incessant warnings about them hanging around later in the data flow.

|||Chris,

could you give us more details? How did those columns get generated?

That might give us a better idea if you can get rid of them.

Thanks,|||Just an idea... As far as I understand SSIS columns are "recreated" behind an asynchronous transform...

I.e. when you have a source with 2 fields (A and B), a derieved column task which calulates C by A+B, a sort by C passing A and B through it and a destination where you write A and C in a file you should get a warning that B is not used and can be removed... That's because B is passed through an asynchronous transform so it's a new column. And in fact, it's not used behind the sort...

At least that's how I understand SSIS...sql

how to remove trace flag in startup option

Hi,
I am having a problem and please help. I tried to add a trace flag -g512 to
our sql 2005 sp2 clustered instance. I added to the startup parameters line
of the SQL configuration manager, something like this:
-dT:\Microsoft SQL Server\msql\MSSQL.1\MSSQL\DATA\master.mdf;-eT:\Microsoft
SQL Server\msql\MSSQL.1\MSSQL\LOG\ERRORLOG;-lT:\Microsoft SQL
Server\msql\MSSQL.1\MSSQL\DATA\mastlog.ldf -g512
after this I cannot get SQL started up. And I tried to manually remove this
flag from registry and sql does not start. When I looked at the registry, it
is back again. Please help.
Thanks very much.
--
BrianThe options need to be separated by semi-colon. Look at that last option. SQL Server will try to use
for the master database log file a file named: "mastlog.ldf -g512". Set a semi-colon after the file
name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
news:B5B10076-C531-43FC-A627-1355455320F2@.microsoft.com...
> Hi,
> I am having a problem and please help. I tried to add a trace flag -g512 to
> our sql 2005 sp2 clustered instance. I added to the startup parameters line
> of the SQL configuration manager, something like this:
> -dT:\Microsoft SQL Server\msql\MSSQL.1\MSSQL\DATA\master.mdf;-eT:\Microsoft
> SQL Server\msql\MSSQL.1\MSSQL\LOG\ERRORLOG;-lT:\Microsoft SQL
> Server\msql\MSSQL.1\MSSQL\DATA\mastlog.ldf -g512
> after this I cannot get SQL started up. And I tried to manually remove this
> flag from registry and sql does not start. When I looked at the registry, it
> is back again. Please help.
> Thanks very much.
> --
> Brian|||Thanks Tibor,
I added a semi colon, and saved OK. when I tried to bring SQL online, it
failed. And I checked again in configuration manager, it is back to where it
was (without semi colon). Can you please help again? Maybe help me to get
this flag removed first?
Thanks.
Brian
--
Brian
"Tibor Karaszi" wrote:
> The options need to be separated by semi-colon. Look at that last option. SQL Server will try to use
> for the master database log file a file named: "mastlog.ldf -g512". Set a semi-colon after the file
> name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
> news:B5B10076-C531-43FC-A627-1355455320F2@.microsoft.com...
> > Hi,
> >
> > I am having a problem and please help. I tried to add a trace flag -g512 to
> > our sql 2005 sp2 clustered instance. I added to the startup parameters line
> > of the SQL configuration manager, something like this:
> >
> > -dT:\Microsoft SQL Server\msql\MSSQL.1\MSSQL\DATA\master.mdf;-eT:\Microsoft
> > SQL Server\msql\MSSQL.1\MSSQL\LOG\ERRORLOG;-lT:\Microsoft SQL
> > Server\msql\MSSQL.1\MSSQL\DATA\mastlog.ldf -g512
> >
> > after this I cannot get SQL started up. And I tried to manually remove this
> > flag from registry and sql does not start. When I looked at the registry, it
> > is back again. Please help.
> >
> > Thanks very much.
> >
> > --
> > Brian
>
>|||Here is the error:
2007-11-30 09:54:43.45 spid5s Unable to open the physical file
"T:\Microsoft SQL Server\msql\MSSQL.1\MSSQL\DATA\mastlog.ldf -g512".
Operating system error 2: "2(The system cannot find the file specified.)".
Thanks very much.
--
Brian
"Tibor Karaszi" wrote:
> The options need to be separated by semi-colon. Look at that last option. SQL Server will try to use
> for the master database log file a file named: "mastlog.ldf -g512". Set a semi-colon after the file
> name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
> news:B5B10076-C531-43FC-A627-1355455320F2@.microsoft.com...
> > Hi,
> >
> > I am having a problem and please help. I tried to add a trace flag -g512 to
> > our sql 2005 sp2 clustered instance. I added to the startup parameters line
> > of the SQL configuration manager, something like this:
> >
> > -dT:\Microsoft SQL Server\msql\MSSQL.1\MSSQL\DATA\master.mdf;-eT:\Microsoft
> > SQL Server\msql\MSSQL.1\MSSQL\LOG\ERRORLOG;-lT:\Microsoft SQL
> > Server\msql\MSSQL.1\MSSQL\DATA\mastlog.ldf -g512
> >
> > after this I cannot get SQL started up. And I tried to manually remove this
> > flag from registry and sql does not start. When I looked at the registry, it
> > is back again. Please help.
> >
> > Thanks very much.
> >
> > --
> > Brian
>
>|||The semi-colon is probably removed by the cluster handling. Does it work if you use the SQL Server
Configuration Manager application (which I assume is cluster-aware)?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
news:4167E684-0A13-4358-BAE1-58B30E6288AD@.microsoft.com...
> Thanks Tibor,
> I added a semi colon, and saved OK. when I tried to bring SQL online, it
> failed. And I checked again in configuration manager, it is back to where it
> was (without semi colon). Can you please help again? Maybe help me to get
> this flag removed first?
> Thanks.
> Brian
> --
> Brian
>
> "Tibor Karaszi" wrote:
>> The options need to be separated by semi-colon. Look at that last option. SQL Server will try to
>> use
>> for the master database log file a file named: "mastlog.ldf -g512". Set a semi-colon after the
>> file
>> name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Brian Brian" <BrianBrian@.discussions.microsoft.com> wrote in message
>> news:B5B10076-C531-43FC-A627-1355455320F2@.microsoft.com...
>> > Hi,
>> >
>> > I am having a problem and please help. I tried to add a trace flag -g512 to
>> > our sql 2005 sp2 clustered instance. I added to the startup parameters line
>> > of the SQL configuration manager, something like this:
>> >
>> > -dT:\Microsoft SQL Server\msql\MSSQL.1\MSSQL\DATA\master.mdf;-eT:\Microsoft
>> > SQL Server\msql\MSSQL.1\MSSQL\LOG\ERRORLOG;-lT:\Microsoft SQL
>> > Server\msql\MSSQL.1\MSSQL\DATA\mastlog.ldf -g512
>> >
>> > after this I cannot get SQL started up. And I tried to manually remove this
>> > flag from registry and sql does not start. When I looked at the registry, it
>> > is back again. Please help.
>> >
>> > Thanks very much.
>> >
>> > --
>> > Brian
>>

how to remove trace flag in startup option

How to remove time from date?

Hello,
I have a table (T1) that has a field that holds a date with a time. In
another table (T2) I have records related to T1 on an ID field (one T1 to
many T2). T2 has a date field also, but this field does not have time (ie
the time part is midnight). I want to join the two tables on the ID and
Date but the time portions of the dates are not equal so they do not match.
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND T1.Date = T2.Date
Does anyone know how I can set the time portion of T1.Date to midnight in
the query (not in the table) so the join will work.
I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
want the time part dropped or midnight.
Thanks
EdmundHi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry|||I don't know why I did not think of this. It was staring right at me.
Thanks Barry. I've gone for CONVERT(CHAR(10),T1.Date, 120)) as it will
implicitly convert to DATETIME with date as midnight.
Edmund.
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1149517628.941905.154220@.h76g2000cwa.googlegroups.com...
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry
>|||try this:
CONVERT(VARCHAR(10), T1.date, 126)
Barry wrote:
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry|||Edmund,
You might want to chk this article :: http://www.aspfaq.com/show.asp?id=2460
Extract from that article:
--
For example, to get today's date in YYYYMMDD format, you currently need to
call the following:
SELECT CONVERT(CHAR(8), GETDATE(), 112)
What does the 112 mean? Nothing. It's just an arbitrary number representing
this specific format.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Edmund" wrote:

> Hello,
> I have a table (T1) that has a field that holds a date with a time. In
> another table (T2) I have records related to T1 on an ID field (one T1 to
> many T2). T2 has a date field also, but this field does not have time (ie
> the time part is midnight). I want to join the two tables on the ID and
> Date but the time portions of the dates are not equal so they do not match
.
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND T1.Date = T2.Date
> Does anyone know how I can set the time portion of T1.Date to midnight in
> the query (not in the table) so the join will work.
> I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
> want the time part dropped or midnight.
> Thanks
> Edmund
>
>|||Edmund,
To truly remove time from datetime and not just "hide" it:
SELECT CAST(DATEDIFF(DAY,0,getDate()) AS DATETIME)
Returns:
2006-06-06 00:00:00.000
Mark|||A slightly better way to do it than using the CONVERT() function calls
would be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d,
DATEDIFF(d, 0, T2.Date), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web
article about datetime data including a short section on stripping time
info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
*mike hodgson*
http://sqlnerd.blogspot.com
Barry wrote:

>Hi,
>Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
>(Varchar(13), T2.Date, 112)
>
>HTH
>Barry
>
>|||Mike, this is very good.
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:u4N4C8PiGHA.1612@.
TK2MSFTNGP04.phx.gbl...
A slightly better way to do it than using the CONVERT() function calls would
be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d, DATEDIFF(d, 0, T2.Da
te), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web article a
bout datetime data including a short section on stripping time info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
mike hodgson
http://sqlnerd.blogspot.com
Barry wrote:
Hi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry

How to remove the sqlcachedependancy ?

I had sqlcachedependancy installed. But now I cannot uninstall it?

1. I unregistered:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe -E -S localhost
-d DNN1 -dd

2. I remove the Dependencry.Start() from my application_start

' System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)

3. I removed the cache section from my web.config

4. I removed the outputcache statements from my aspx pages.

However, I still get the next error:

FileError When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.

What to do?

Any help or advice is appreciated.

Jelle

Have you done a search through all of your application's files, looking for a rogue SqlDependency instance?|||

Thank you for your reply. I looked mainly in the default.aspx and forgot that for Dotnetnuke I also modified the install.aspx file. So in the end I copy-past all the next files over my files:

default.aspx + codebehind

install.aspx + codebehind

global.ascx

assembly + global file in app_code directory

That solved the solution.

Main reason I wanted to uninstall is that in case the webserver does stop a connection, the database server starts logging errors that the connectio n with the remote webserver is lost...and then the database server hangs. Would be nicer of MS SQL2005 to stop the logging in case the webserver does not request any info anymore.

how to remove the sql server registry mess?

after uninstalling the sql server and related stuff, I found tons of sql
server registry entries.
How do remove them all properly? any script or tools. I tried the setup
form disk to remove but still no joyHi
You don't give the version you are trying to remove, but the following may
help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful if
it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
John
"gs" wrote:

> after uninstalling the sql server and related stuff, I found tons of sql
> server registry entries.
> How do remove them all properly? any script or tools. I tried the setup
> form disk to remove but still no joy
>
>|||thank you. I need the info the 2005
what about the express version also
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...[vbcol=seagreen]
> Hi
> You don't give the version you are trying to remove, but the following may
> help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful
> if
> it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
> John
> "gs" wrote:
>|||Hi
I would expect SQLExpress to be the same or very similar.
John
"gs" wrote:

> thank you. I need the info the 2005
>
> what about the express version also
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...
>
>sql

how to remove the sql server registry mess?

after uninstalling the sql server and related stuff, I found tons of sql
server registry entries.
How do remove them all properly? any script or tools. I tried the setup
form disk to remove but still no joy
Hi
You don't give the version you are trying to remove, but the following may
help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful if
it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
John
"gs" wrote:

> after uninstalling the sql server and related stuff, I found tons of sql
> server registry entries.
> How do remove them all properly? any script or tools. I tried the setup
> form disk to remove but still no joy
>
>
|||thank you. I need the info the 2005
what about the express version also
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...[vbcol=seagreen]
> Hi
> You don't give the version you are trying to remove, but the following may
> help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful
> if
> it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
> John
> "gs" wrote:
|||Hi
I would expect SQLExpress to be the same or very similar.
John
"gs" wrote:

> thank you. I need the info the 2005
>
> what about the express version also
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...
>
>

Friday, March 30, 2012

How to remove unused columns and eliminate warnings

how to remove trace flag in startup option

how to remove trace flag in startup option

How to remove time from date?

How to remove the sqlcachedependancy ?

how to remove the sql server registry mess?

how to remove the sql server registry mess?

query database table

Blog Archive

About Me