set affinity for SqlExpress: February 2012

Friday, February 24, 2012

how to set Default instant of sql server 2005

Dear All,
I install sql 2005 that instant name give Harshad but after installation
come as harshad\harshad
my pc name harshad. Now I want to set as harshad or (local) instant without
reinstalation.
thanks,
harshadharshad,
The bad news is that you cannot rename an instance. You will have to
reinstall to get a (local) instance. In the installation process do not
give an instance name but choose the default instance.
RLF
"harshad" <harshad7_jp@.hotmail.com> wrote in message
news:274295E6-1EE8-445E-B3A8-73655BD467AD@.microsoft.com...
> Dear All,
> I install sql 2005 that instant name give Harshad but after installation
> come as harshad\harshad
> my pc name harshad. Now I want to set as harshad or (local) instant
> without reinstalation.
> thanks,
> harshad
>

How to set default date in SQL to

somthing other than 1/1/1900? I use Visual Studio.NET and Aspx.
Even if I pass a system.dbnull.value to the table, it keeps showing
a default date of the above. I want to end-up with a <NULL> for the
date entry from my code when a user deletes a date from my
aspx page.

Thanks,You can mix something from:

use a DEFAULT constraint to your date column
use NULLIF() function with an out-of-range date
use ISNULL() function with an out-of-range date
and check that your table supports NULLs in your date column

we also have problems passing nulls via VB6 (must be variants).

Cesar.|||Thanks Cesar,

I saw a KB on Microsoft saying that functionality is a bug but don't see
they fixed it. I will give the ole variant a try. Thanks again.

BobbyJ

How to set default date

Hi Guys,

I have a time standard dimension used as a filter to my report.

How can I set the parameter to get the current month and year to be used by the time standard filter.

Select the report, and go to Report->Parameters, select the parameter, in the default values sectoin, set the value there. You can use function too, like get current date, use =Today, and DatePart() to get year, month, date, whatever.

how to set default data & log directory

I tried to add volume to default data & log directory of "new data default location" of database setting at SQL server properties, but it never be saved, when I get in properties again, the setting is disappear.
Does somebody could advise me how to save it?
Thank you
TingNever mind, I found out why the directory couldn't be able saved is because I went through it via terminal sevices. :p

How to set datetime?>

I've mined through the support files but without finding an answer to my
question.. In Access, I used to use the paramter Get() to have a datetime
field to default to the server's date/time each time a new record was
inserted. Anyone know how I can do this in SQL Server?Default the column to GETDATE()
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>|||CREATE TABLE blat
(
id INT,
dt SMALLDATETIME DEFAULT GETDATE()
)
GO
INSERT blat(id) SELECT 1
GO
SELECT * FROM blat
GO
DROP TABLE blat
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>|||I'm assuming you mean on the Formula line.. but doing that changes ALL
entries in the table in the DATETIME field to the same entry! (the
latest)... each entry should be unique..|||How do you expect to back-fill rows that already exist?
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:%23BYqwausEHA.1400@.TK2MSFTNGP11.phx.gbl...
> I'm assuming you mean on the Formula line.. but doing that changes ALL
> entries in the table in the DATETIME field to the same entry! (the
> latest)... each entry should be unique..
>|||This is a new table, so all data entered so far is strictly garbage data
that will be removed before anything goes live..|||The DEFAULT constraint will be evaluated upon insertion; so if you have your
DEFAULT set to GETDATE(), the current date at the time the row is inserted
is used.
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..
>|||Then why do you care what is being applied to the existing data, if it's
just going to be thrown away?
Anyway, don't use the formula line, and STOP "designing" tables in
Enterprise Manager.
To quote myself:
...
There are subtle differences in what happens to rows that existed prior to
the column addition, depending on whether you define the new column as NULL
or NOT NULL.
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME NOT NULL DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 1
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 2
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
Note the slight difference in the output.
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..

How to set datetime?>

I've mined through the support files but without finding an answer to my
question.. In Access, I used to use the paramter Get() to have a datetime
field to default to the server's date/time each time a new record was
inserted. Anyone know how I can do this in SQL Server?Default the column to GETDATE()
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>|||CREATE TABLE blat
(
id INT,
dt SMALLDATETIME DEFAULT GETDATE()
)
GO
INSERT blat(id) SELECT 1
GO
SELECT * FROM blat
GO
DROP TABLE blat
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>|||I'm assuming you mean on the Formula line.. but doing that changes ALL
entries in the table in the DATETIME field to the same entry! (the
latest)... each entry should be unique..|||How do you expect to back-fill rows that already exist?
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:%23BYqwausEHA.1400@.TK2MSFTNGP11.phx.gbl...
> I'm assuming you mean on the Formula line.. but doing that changes ALL
> entries in the table in the DATETIME field to the same entry! (the
> latest)... each entry should be unique..
>|||This is a new table, so all data entered so far is strictly garbage data
that will be removed before anything goes live..|||The DEFAULT constraint will be evaluated upon insertion; so if you have your
DEFAULT set to GETDATE(), the current date at the time the row is inserted
is used.
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..
>|||Then why do you care what is being applied to the existing data, if it's
just going to be thrown away?
Anyway, don't use the formula line, and STOP "designing" tables in
Enterprise Manager.
To quote myself:
...
There are subtle differences in what happens to rows that existed prior to
the column addition, depending on whether you define the new column as NULL
or NOT NULL.
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME NOT NULL DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 1
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 2
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
Note the slight difference in the output.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..

How to set datetime?>

I've mined through the support files but without finding an answer to my
question.. In Access, I used to use the paramter Get() to have a datetime
field to default to the server's date/time each time a new record was
inserted. Anyone know how I can do this in SQL Server?
Default the column to GETDATE()
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>
|||CREATE TABLE blat
(
id INT,
dt SMALLDATETIME DEFAULT GETDATE()
)
GO
INSERT blat(id) SELECT 1
GO
SELECT * FROM blat
GO
DROP TABLE blat
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:emj1XGjsEHA.532@.TK2MSFTNGP10.phx.gbl...
> I've mined through the support files but without finding an answer to my
> question.. In Access, I used to use the paramter Get() to have a datetime
> field to default to the server's date/time each time a new record was
> inserted. Anyone know how I can do this in SQL Server?
>
|||I'm assuming you mean on the Formula line.. but doing that changes ALL
entries in the table in the DATETIME field to the same entry! (the
latest)... each entry should be unique..
|||How do you expect to back-fill rows that already exist?
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:%23BYqwausEHA.1400@.TK2MSFTNGP11.phx.gbl...
> I'm assuming you mean on the Formula line.. but doing that changes ALL
> entries in the table in the DATETIME field to the same entry! (the
> latest)... each entry should be unique..
>
|||This is a new table, so all data entered so far is strictly garbage data
that will be removed before anything goes live..
|||The DEFAULT constraint will be evaluated upon insertion; so if you have your
DEFAULT set to GETDATE(), the current date at the time the row is inserted
is used.
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..
>
|||Then why do you care what is being applied to the existing data, if it's
just going to be thrown away?
Anyway, don't use the formula line, and STOP "designing" tables in
Enterprise Manager.
To quote myself:
...
There are subtle differences in what happens to rows that existed prior to
the column addition, depending on whether you define the new column as NULL
or NOT NULL.
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME NOT NULL DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 1
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
CREATE TABLE Adam
(id INT)
GO
INSERT Adam(id) SELECT 1
GO
ALTER TABLE Adam
ADD InsertedDate DATETIME DEFAULT (GETDATE())
GO
INSERT Adam(id) SELECT 2
GO
SELECT * FROM Adam
GO
DROP TABLE Adam
GO
Note the slight difference in the output.
http://www.aspfaq.com/
(Reverse address to reply.)
"Adrian Leontovich" <adrian@. lionsmaneproductions.com> wrote in message
news:elKZo1usEHA.2956@.TK2MSFTNGP12.phx.gbl...
> This is a new table, so all data entered so far is strictly garbage data
> that will be removed before anything goes live..

How to set date format as mm/dd/yyyyThh:mm:ss.ttttZ+00:00?

Hi,
I use "For XML" statement to export xml, all are my expected. But the
datetime field value is not my expected format(e.g.
ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
You would have to use CONVERT(nvarchar(40), datetimeval, 127) + N'Z' in the
select clause since SQL Server currently does not know what timezone you
could possibly want.
Best regards
Michael
"ABC" <abc@.abc.com> wrote in message
news:urvzmAilHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use "For XML" statement to export xml, all are my expected. But the
> datetime field value is not my expected format(e.g.
> ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
> date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
>
>

How to set date format as mm/dd/yyyyThh:mm:ss.ttttZ+00:00?

Hi,
I use "For XML" statement to export xml, all are my expected. But the
datetime field value is not my expected format(e.g.
ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?You would have to use CONVERT(nvarchar(40), datetimeval, 127) + N'Z' in the
select clause since SQL Server currently does not know what timezone you
could possibly want.
Best regards
Michael
"ABC" <abc@.abc.com> wrote in message
news:urvzmAilHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use "For XML" statement to export xml, all are my expected. But the
> datetime field value is not my expected format(e.g.
> ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
> date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
>
>

How to set date as default

Hi, I probably do something stupid, but I cannot figure out how to set the date as deault when

inserting a record in a table. I have created a Default in the database, giving it a name and the Value = GetDate() and bind it to the desired column.

When I insert a record without filling in a value in this datefield, the table shows me "01/01/1900" in stead of Today.

What am I doing wrong ?

Help is appreciated, Ger.

How are you doing the insert?|||

Here is part of the code :

Dim ConnStr As String = "workstation id=ONTWIKKEL;packet size=4096;integrated security=SSPI;data source='ONTWIKKEL\WEBAPPS';persist security info=False;initial catalog=UPOdyssee"
Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConnStr)
Dim mSQL As String
Dim a(99)
a(1) = Request.Form("HtxtVestiging")
a(2) = Request.Form("HtxtInschrijfdatum")
a(3) = Request.Form("HtxtNaamVoorletters")
a(4) = Request.Form("HtxtRoepnaam")
a(5) = Request.Form("HtxtLoginnaam")
a(6) = Request.Form("HtxtPaswoord")
a(7) = Request.Form("HtxtAdres")
a(8) = Request.Form("HtxtPostcode")
a(9) = Request.Form("HtxtWoonplaats")
a(10) = Request.Form("HtxtTelnr")
a(11) = Request.Form("HtxtEmail")
a(12) = Request.Form("HtxtMobiel")
a(13) = Request.Form("HtxtBereikbaarVia")
a(14) = Request.Form("HtxtGeboortedatum")
a(15) = DDL_Geslacht.SelectedValue
a(16) = Request.Form("HtaWatVoorWerk")
a(17) = DDL_VastTijdelijk.SelectedValue
a(18) = Request.Form("HtxtVanafWelkePeriode")
a(19) = Request.Form("HtxtTotWelkePeriode")
a(20) = DDL_Beschikbaarheid.SelectedValue
a(21) = Request.Form("HtxtVanUren")
a(22) = Request.Form("HtxtTotUren")
a(23) = DDL_Vervoer.SelectedValue
a(24) = DDL_Reisbereidheid.SelectedValue
a(25) = Request.Form("HtxtMaxReistijd")
a(26) = Request.Form("HtxtMaxKM")
a(27) = Request.Form("HtxtInkomenBruto")
a(28) = Request.Form("HtxtInkomenNetto")
a(29) = DDL_Uitkering.SelectedValue
a(30) = Request.Form("HtxtUitkering")
a(31) = Request.Form("HtxtFiscaalnr")
a(32) = DDL_Identificatiebewijs.SelectedValue
a(33) = Request.Form("HtxtSoortLegitimatiebewijs")
a(34) = Request.Form("HtxtLegitimatiebewijsnr")
a(35) = Request.Form("HtxtGeldigTot")
a(36) = DDL_Rijbewijs.SelectedValue
a(37) = Request.Form("HtxtSoortRijbewijs")
a(38) = Request.Form("HtxtRijbewijsnr")
a(39) = DDL_MilitaireDienst.SelectedValue
a(40) = Request.Form("HtxtFunctieMD")
a(41) = Request.Form("HtxtJaarMD")
a(42) = Request.Form("HtxtPaspoortnr")
a(43) = Request.Form("HtxtEinddatumPaspoort")
a(44) = Request.Form("HtxtVergunningnr")
a(45) = Request.Form("HtxtEinddatumVergunning")
a(46) = Request.Form("HtxtBankGiroNr")
a(47) = DDL_Ziektekosten.SelectedValue
a(48) = Request.Form("HtxtPolisnr")
a(49) = DDL_HoeAanOnsGekomen.SelectedValue
a(50) = Request.Form("HtxtOpl1")
a(51) = Request.Form("HtxtOpl2")
a(52) = Request.Form("HtxtOpl3")
a(53) = Request.Form("HtxtOpl4")
a(54) = Request.Form("HtxtOpl5")
a(55) = Request.Form("HtxtOpl6")
a(56) = Request.Form("HtxtOpl7")
a(57) = Request.Form("HtxtRichting1")
a(58) = Request.Form("HtxtRichting2")
a(59) = Request.Form("HtxtRichting3")
a(60) = Request.Form("HtxtRichting4")
a(61) = Request.Form("HtxtRichting5")
a(62) = Request.Form("HtxtRichting6")
a(63) = Request.Form("HtxtRichting7")
a(64) = Request.Form("HtxtDipl1")
a(65) = Request.Form("HtxtDipl2")
a(66) = Request.Form("HtxtDipl3")
a(67) = Request.Form("HtxtDipl4")
a(68) = Request.Form("HtxtDipl5")
a(69) = Request.Form("HtxtDipl6")
a(70) = Request.Form("HtxtDipl7")
a(71) = Request.Form("HtxtJaar1")
a(72) = Request.Form("HtxtJaar2")
a(73) = Request.Form("HtxtJaar3")
a(74) = Request.Form("HtxtJaar4")
a(75) = Request.Form("HtxtJaar5")
a(76) = Request.Form("HtxtJaar6")
a(77) = Request.Form("HtxtJaar7")
a(78) = DDL_NogStudie.SelectedValue
a(79) = Request.Form("HtxtWelkeStudie")
a(80) = Request.Form("HtxtWGVanTot1")
a(81) = Request.Form("HtxtWGVanTot2")
a(82) = Request.Form("HtxtWGVanTot3")
a(83) = Request.Form("HtxtWGVanTot4")
a(84) = Request.Form("HtxtWGVanTot5")
a(85) = Request.Form("HtxtWGVanTot6")
a(86) = Request.Form("HtxtFunctie1")
a(87) = Request.Form("HtxtFunctie2")
a(88) = Request.Form("HtxtFunctie3")
a(89) = Request.Form("HtxtFunctie4")
a(90) = Request.Form("HtxtFunctie5")
a(91) = Request.Form("HtxtFunctie6")
a(92) = Request.Form("HtxtVertrek1")
a(93) = Request.Form("HtxtVertrek2")
a(94) = Request.Form("HtxtVertrek3")
a(95) = Request.Form("HtxtVertrek4")
a(96) = Request.Form("HtxtVertrek5")
a(97) = Request.Form("HtxtVertrek6")
a(98) = Request.Form("HtaBijzOpm")
a(99) = DDL_Functie.SelectedIndex + 1 ' i.v.m. de zerobased van de ddl en de 1-based van de tabel
mSQL = "INSERT into Medewerkers (Vestiging,Inschrijfdatum, Naam, Roepnaam, Loginnaam, Paswoord, "
mSQL = mSQL & "Adres, Postcode, Woonplaats, Telefoonnummer, Email, Mobiel, BereikTel, Geboortedatum, Geslacht, WatVoorWerk, VastTijdelijk, VanafPeriode, TotPeriode, Beschikbaarheid, "
mSQL = mSQL & "VanUren, TotUren, Vervoer, Reisbereidheid, MaxReistijd, MaxKM, InkomenBruto, InkomenNetto, Uitkering, SoortUitkering, Fiscaalnr, "
mSQL = mSQL & "Identificatiebewijs, SoortLegibewijs, Legitimatiebewijs, GeldigTot, Rijbewijs, "
mSQL = mSQL & "Soortrijbewijs, Rijbewijsnr, MilitaireDienst, FunctieMD, JaarMD, Paspoortnr, EinddatumPaspoort, Vergunningnr, EinddatumVergunning, BankGironr, "
mSQL = mSQL & "Ziektekosten, Polisnr, HoeAanOnsGekomen, Opl1, Opl2, Opl3, Opl4, Opl5, Opl6, "
mSQL = mSQL & "Opl7, Richting1, Richting2, Richting3, Richting4, Richting5, Richting6, Richting7, Dipl1, Dipl2, Dipl3, Dipl4, Dipl5, Dipl6, Dipl7, "
mSQL = mSQL & "Jaar1, Jaar2, Jaar3, Jaar4, Jaar5, Jaar6, Jaar7, NogStudie, WelkeStudie, WGVanTot1, WGVanTot2, WGVanTot3, WGVanTot4, WGVanTot5, "
mSQL = mSQL & "WGVanTot6, Functie1, Functie2, Functie3, Functie4, Functie5, Functie6, Vertrek1, Vertrek2, Vertrek3, Vertrek4, "
mSQL = mSQL & "Vertrek5, Vertrek6, BijzOpm, FunctieID) "
mSQL = mSQL & "VALUES ('" & a(1) & "','" &a(2) & "','" & a(3) & "','" & a(4) & "','" & a(5) & "','" & a(6) & "',"
mSQL = mSQL & "'" & a(7) & "','" & a(8) & "','" & a(9) & "','" & a(10) & "','" & a(11) & "','" & a(12) & "',"
mSQL = mSQL & "'" & a(13) & "','" & a(14) & "','" & a(15) & "','" & a(16) & "','" & a(17) & "','" & a(18) & "',"
mSQL = mSQL & "'" & a(19) & "','" & a(20) & "','" & a(21) & "','" & a(22) & "','" & a(23) & "','" & a(24) & "',"
mSQL = mSQL & "'" & a(25) & "','" & a(26) & "','" & a(27) & "','" & a(28) & "','" & a(29) & "','" & a(30) & "',"
mSQL = mSQL & "'" & a(31) & "','" & a(32) & "','" & a(33) & "','" & a(34) & "','" & a(35) & "','" & a(36) & "',"
mSQL = mSQL & "'" & a(37) & "','" & a(38) & "','" & a(39) & "','" & a(40) & "','" & a(41) & "','" & a(42) & "',"
mSQL = mSQL & "'" & a(43) & "','" & a(44) & "','" & a(45) & "','" & a(46) & "','" & a(47) & "','" & a(48) & "',"
mSQL = mSQL & "'" & a(49) & "','" & a(50) & "','" & a(51) & "','" & a(52) & "','" & a(53) & "','" & a(54) & "',"
mSQL = mSQL & "'" & a(55) & "','" & a(56) & "','" & a(57) & "','" & a(58) & "','" & a(59) & "','" & a(60) & "',"
mSQL = mSQL & "'" & a(61) & "','" & a(62) & "','" & a(63) & "','" & a(64) & "','" & a(65) & "','" & a(66) & "',"
mSQL = mSQL & "'" & a(67) & "','" & a(68) & "','" & a(69) & "','" & a(70) & "','" & a(71) & "','" & a(72) & "',"
mSQL = mSQL & "'" & a(73) & "','" & a(74) & "','" & a(75) & "','" & a(76) & "','" & a(77) & "','" & a(78) & "',"
mSQL = mSQL & "'" & a(79) & "','" & a(80) & "','" & a(81) & "','" & a(82) & "','" & a(83) & "','" & a(84) & "',"
mSQL = mSQL & "'" & a(85) & "','" & a(86) & "','" & a(87) & "','" & a(88) & "','" & a(89) & "','" & a(90) & "',"
mSQL = mSQL & "'" & a(91) & "','" & a(92) & "','" & a(93) & "','" & a(94) & "','" & a(95) & "','" & a(96) & "',"
mSQL = mSQL & "'" & a(97) & "','" & a(98) & "','" & a(99) & "')"
Dim Command As SqlClient.SqlCommand = New SqlClient.SqlCommand(mSQL)
Dim x As Integer
Conn.Open()
Command.Connection = Conn
x = Command.ExecuteNonQuery()
Conn.Close()
Command = Nothing

Inschrijdatum is empty, so I expect that the field in the table will be TODAY and not 1/1/1900.

Ger.

|||

Two problems:

First, Inschrijdatum isn't empty, it's a zero length string.

Secondly, the only way you are going to get SQL Server to insert the default is one of three ways.

a) Don't mention the column in the insert at all.

b) Tell it to use the DEFAULT like INSERT ... VALUES (...,DEFAULT,...), note there is no quotes around DEFAULT, it is not a string.

c) Explicitly tell it the default value like INSERT ... VALUES (...,GetDate(),...)

That said, replace:

"','" &a(2) & "','"

with:

"'," & IIF(a(2)<>"","'" & a(2) & "'","DEFAULT") & ",'"

|||

Oh, I guess I should mention this code is susceptible to SQL injection attacks.

For example, type this in your field labeled "HtaBijzOpm": "','') TRUNCATE Medewerkers --" and submit your form, and you've just deleted all the records from your Medewerkers table.

|||

Motley wrote:
Oh, I guess I should mention this code is susceptible to SQL injection attacks.
For example, type this in your field labeled "HtaBijzOpm": "','') TRUNCATE Medewerkers --" and submit your form, and you've just deleted all the records from your Medewerkers table.

Thanks Motley for replying I will reconstruct the inserts according your advice.

How can I avoid this SQL injection attack ?

Ger.

How to set CSSClass in crystal report

Hi

I'm using crystal report version9,i need to set Cssclass in report. I have a cssclass named "cssreport" with the properties color:maroon;

i gave right click in the designer section,selected set cssclass and i selected object scope and gave the cssclass name also, after saving i executed but still am not getting the result
what should i do,any one help me

RinuSee if you find solution here
http://support.businessobjects.com/

How to set connection, Send Parameter and Filter data

Dear all .Netter,

I'm newbie in Reporting Services. I'm able to creating raw Reporting Services reports.

I have these questions to you all :

1. How to change connection to server and database when running in ASP.Net form. Currently in development PC, the server name is ServerA and the Database name is DatabaseA. After i copy to production server, the server name is ServerB and the database name is DatabaseB.

2. How to send parameter to Reporting Services report. I want to show name of user that print the report.

3. How to filter data that will be showed in the Reporting Services. I want to print Invoice with no : INV-2007-0001 and next INV-2007-0002

Sorry, if I'm lazy, but i'm running with time to replace Crystal Reports with Reporting Services.

Thanks and Regards,

Kusno.

These are some great links that will give you any answer you want

http://www.codeproject.com/sqlrs/AHCreatRepsAspNet.asp
http://msdn2.microsoft.com/en-us/library/ms170246.aspx
http://www.codeproject.com/sqlrs/ReportViewer2005.asp

Have Fun Smile

how to set connection string value by using SET switch of dtexec sql server 2005 command

I need to load a text file into sql server table using SSIS package.

the idea is to load this file with the help of dtexec command by giving the file path and name in the SET switch of dtexec command.

anyone having an idea; would be of great help.

Regards,

Salman Shehbaz.

Try using the package configuration wizard to get the property path:

Jamie talks about it here:

http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx

|||

o.kays

i finally got hold of the command;

here it is

execute master.dbo.xp_cmdshell 'dtexec /Ser "ServerName" /SQ "SSISPackagehName" /SET "\Package.Connections[ConnectionManagerName].Properties[ConnectionString]";"TextFilePath/Name"'

Regards,

Salman Shehbaz.

How to set connection at runtime

Hi,
Our application connects to one database in the testing environment and to a
different database in the production environment. Currently, with Crystal
Reports, we maintain a single set of reports, and at runtime, when the
reports are loaded, the application sets the report's connection acordingly:
if the application is comnnected to the production server, the report's
connection is set to the production server, otherwise to the test server.
Using MS Reporting Services, how can the report's connection be set at
runtime from the application before the report connects to the database?
Thank you in advance,
RichardHi Richard,
One way you can set the connection at runtime is through a user input
parameter. You would just have to reference Parameter!param.Value in
the connection string. There may be other ways to do it dynamically
without the user having to input data, but I am not sure if there is a
way for MS Reporting Services to tell which environment it is in. Good
luck!
Lance M.
Richard wrote:
> Hi,
> Our application connects to one database in the testing environment and to a
> different database in the production environment. Currently, with Crystal
> Reports, we maintain a single set of reports, and at runtime, when the
> reports are loaded, the application sets the report's connection acordingly:
> if the application is comnnected to the production server, the report's
> connection is set to the production server, otherwise to the test server.
> Using MS Reporting Services, how can the report's connection be set at
> runtime from the application before the report connects to the database?
> Thank you in advance,
> Richard

How to set Concurrent execution of 2 insert statements

Hi,
I ahve the followign trigger. In this I want to set the 2 Insert statements
at the bottom, to execute concurrently. How can I do that?
CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
FOR INSERT
AS
DECLARE @.CompOrderNo VarChar(30)
DECLARE @.Sno int
DECLARE @.EndUser varchar(25)
DECLARE @.ESN varchar(20)
DECLARE @.MinNo varchar(12)
SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
inserted --where CompanyORderNo is null
UPDATE WyethDataCard
SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
INSERT INTO OrderStatus ( Company_OrderNo,Status_ID,Created_Date,C
ompanySno
)
VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
INSERT INTO EndUserChangeHistory
(MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
VALUES ( @.CompOrderNo,
@.ESN,
@.MinNo,
@.EndUser,
'Database Import / Manual Insert',
GetDate()
)
Is there some statement like CONCURRENT EXEC to achieve this?
Thanks
pmudWhy do you want to make them concurrent? Even if the machine has several pro
cessors, there's no
guarantee that this will happen at the same time. If you want both or nothin
g, you should have some
error handling in the trigger (see the articles on error handling at www.sommarsko
g.se).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
> Hi,
> I ahve the followign trigger. In this I want to set the 2 Insert statement
s
> at the bottom, to execute concurrently. How can I do that?
> CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
> FOR INSERT
> AS
> DECLARE @.CompOrderNo VarChar(30)
> DECLARE @.Sno int
> DECLARE @.EndUser varchar(25)
> DECLARE @.ESN varchar(20)
> DECLARE @.MinNo varchar(12)
> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
> UPDATE WyethDataCard
> SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
> SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
> INSERT INTO OrderStatus ( Company_OrderNo,Status_ID,Created_Date,C
ompanyS
no)
> VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
> INSERT INTO EndUserChangeHistory
> (MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
> VALUES ( @.CompOrderNo,
> @.ESN,
> @.MinNo,
> @.EndUser,
> 'Database Import / Manual Insert',
> GetDate()
> )
> Is there some statement like CONCURRENT EXEC to achieve this?
> Thanks
> --
> pmud|||Hi Tibor,
Actually, by concurrent execution, I meant parallel execution of the 2
statements. Is tehre any way I can set parallel execution fo teh 2 statement
s
rather than having one after the other?
Thanks
--
pmud
"Tibor Karaszi" wrote:

> Why do you want to make them concurrent? Even if the machine has several p
rocessors, there's no
> guarantee that this will happen at the same time. If you want both or noth
ing, you should have some
> error handling in the trigger (see the articles on error handling at www.sommars
kog.se).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
>|||Do you mean that the inserts should be atomic or within a single
transaction?
If so, then refer to this document, specifically the section titled
"Database Transactions".
http://msdn.microsoft.com/library/d...ent.
asp
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
> Hi,
> I ahve the followign trigger. In this I want to set the 2 Insert
> statements
> at the bottom, to execute concurrently. How can I do that?
> CREATE TRIGGER [Identity_Trigger] ON dbo.WyethDataCard
> FOR INSERT
> AS
> DECLARE @.CompOrderNo VarChar(30)
> DECLARE @.Sno int
> DECLARE @.EndUser varchar(25)
> DECLARE @.ESN varchar(20)
> DECLARE @.MinNo varchar(12)
> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
> UPDATE WyethDataCard
> SET CompanyORderNo= CONVERT(VARCHAR(15), 'TK-WT-VZ-'+RTRIM(SNo) )
> SELECT @.CompOrderNo= CompanyORderNo from WyethDataCard
> INSERT INTO OrderStatus (
> Company_OrderNo,Status_ID,Created_Date,C
ompanySno)
> VALUES( @.CompOrderNo ,'25', GetDate(),@.Sno )
> INSERT INTO EndUserChangeHistory
> (MasterOrderNo,ESN,MinNo,EndUser,Modifie
dBy,ModifiedOn)
> VALUES ( @.CompOrderNo,
> @.ESN,
> @.MinNo,
> @.EndUser,
> 'Database Import / Manual Insert',
> GetDate()
> )
> Is there some statement like CONCURRENT EXEC to achieve this?
> Thanks
> --
> pmud|||no
why?
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:B0D56D29-D70C-4684-9F7F-0957CBFF6443@.microsoft.com...
> Hi Tibor,
> Actually, by concurrent execution, I meant parallel execution of the 2
> statements. Is tehre any way I can set parallel execution fo teh 2
> statements
> rather than having one after the other?
> Thanks
> --
> pmud
>
> "Tibor Karaszi" wrote:
>|||Is there any tutorial or book you rae aware which I can read to quickly come
up to speed with transctions and how they are implemeted?
Actually, I am aware of the basic concept of trnsctions but have never
implemeted them.
Do you have any suggestions for me to get up to speed Quickly?
Thanks
--
pmud
"JT" wrote:

> Do you mean that the inserts should be atomic or within a single
> transaction?
> If so, then refer to this document, specifically the section titled
> "Database Transactions".
> http://msdn.microsoft.com/library/d...en
t.asp
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:8338600F-74B5-4742-85E6-62789BD55AFD@.microsoft.com...
>
>|||You will not need a book to understand the concept of basic transactions.
Database transaction:
http://en.wikipedia.org/wiki/Database_transaction
BEGIN TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms188929(SQL.90).aspx
COMMIT TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms190295(SQL.90).aspx
ROLLBACK TRANSACTION
http://msdn2.microsoft.com/en-us/library/ms181299(SQL.90).aspx
The simplified sample below attempts to transfer a balance of $100 between 2
accounts. If all goes well, the transaction is committed (saved) and the
value of 1 is returned. If there is an error, then the transaction is rolled
back (cancelled), and a value of -1 or -2 is returned. By executing both
updates within a transactions, we insure that the work is either all saved
or all cancelled and the accounts are balanced.
begin transaction
update Accounts set balance = balance - 100 where acct_number = 4625
if @.@.error <> 0
begin
rollback transaction
return -1
end
update Accounts set balance = balance + 100 where acct_number = 4650
if @.@.error <> 0
begin
rollback transaction
return -2
end
commit transaction
return 1
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:33501F2A-06A5-4CA4-A15F-3D8987DE9C80@.microsoft.com...
> Is there any tutorial or book you rae aware which I can read to quickly
> come
> up to speed with transctions and how they are implemeted?
> Actually, I am aware of the basic concept of trnsctions but have never
> implemeted them.
> Do you have any suggestions for me to get up to speed Quickly?
> Thanks
> --
> pmud
>
> "JT" wrote:
>|||Thanks JT. That is definitely helpful. :)
--
pmud
"JT" wrote:

> You will not need a book to understand the concept of basic transactions.
> Database transaction:
> http://en.wikipedia.org/wiki/Database_transaction
> BEGIN TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms188929(SQL.90).aspx
> COMMIT TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms190295(SQL.90).aspx
> ROLLBACK TRANSACTION
> http://msdn2.microsoft.com/en-us/library/ms181299(SQL.90).aspx
> The simplified sample below attempts to transfer a balance of $100 between
2
> accounts. If all goes well, the transaction is committed (saved) and the
> value of 1 is returned. If there is an error, then the transaction is roll
ed
> back (cancelled), and a value of -1 or -2 is returned. By executing both
> updates within a transactions, we insure that the work is either all saved
> or all cancelled and the accounts are balanced.
> begin transaction
> update Accounts set balance = balance - 100 where acct_number = 4625
> if @.@.error <> 0
> begin
> rollback transaction
> return -1
> end
> update Accounts set balance = balance + 100 where acct_number = 4650
> if @.@.error <> 0
> begin
> rollback transaction
> return -2
> end
> commit transaction
> return 1
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:33501F2A-06A5-4CA4-A15F-3D8987DE9C80@.microsoft.com...
>
>|||pmud (pmud@.discussions.microsoft.com) writes:
> I ahve the followign trigger. In this I want to set the 2 Insert
> statements at the bottom, to execute concurrently. How can I do that?
You can't, and there would be no point with it.
On the other hand:

> SELECT @.Sno=SNo, @.EndUser=[End User], @.ESN=ESN , @.MinNo=MobileNo from
> inserted --where CompanyORderNo is null
This is not going to work out well. Triggers in SQL Server fires
once *per statement*, so the inserted table can hold many rows, which
your trigger fails to handle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to SET CONCAT_NULL_YIELDS_NULL OFF?

I'm writing a select query that's, in effect, like this: Select FirstName + ' ' + LastName FROM Users.

How do I get just the first name to display in my gridview if last name is null?

I'm familiar with aspx and aspx.cs files, but stored procedures are beyond me right now.

You need to use ISNULL or COALESCE.

Select FirstName + ' ' + ISNULL(LastName,'') FROM Users

how to set column name dynamically?

HI chaps

my scenario is that, i have a table in which i have column such as col01,col02,col03....col31

i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col?') and i have to do integer increment in last two character (?01...?31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?

waiting for your reply

regards

Something like the following will work for you:

create table DynCols
(col01 int,
col02 int,
col11 int,
col12 int) -- and so on, fo all 30 columns

declare @.query varchar(255)
set @.query = 'select '

declare @.counter int
set @.counter = 1

while @.counter <= 31
begin
if @.counter < 10
set @.query = @.query + 'col' + '0' + convert(char(1), @.counter) + ','
else
set @.query = @.query + 'col' + convert(char(2), @.counter) + ','
set @.counter = @.counter + 1
end

-- remove the last comma and append the rest:
set @.query = substring(@.query, 0, len(@.query) - 1) + ' from DynCols'

print @.query

The result is:

select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col3 from DynCols

|||

fafnir wrote:

Something like the following will work for you:
create table DynCols
(col01 int,
col02 int,
col11 int,
col12 int) -- and so on, fo all 30 columns
declare @.query varchar(255)
set @.query = 'select '
declare @.counter int
set @.counter = 1
while @.counter <= 31
begin
if @.counter < 10
set @.query = @.query + 'col' + '0' + convert(char(1), @.counter) + ','
else
set @.query = @.query + 'col' + convert(char(2), @.counter) + ','
set @.counter = @.counter + 1
end
-- remove the last comma and append the rest:
set @.query = substring(@.query, 0, len(@.query) - 1) + ' from DynCols'
print @.query
The result is:
select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col3 from DynCols

thanx for your reply....

yes i can do it that way but still it will be string (varchar) can you tell me how to execute that statement? i can print the statement but i dont know how will I able to execute a query which is stored in the varaible

|||

sp_executesql @.query

|||

i have written the follwoing script

DECLARE @.CURRENTDATE DATETIME

DECLARE @.STARTDATE DATETIME

DECLARE @.LOOPDATE DATETIME

DECLARE @.NOOFMONTHS INT

DECLARE @.NOOFDAYS INT

DECLARE @.DAYCOUNTER TINYINT

DECLARE @.I INT

DECLARE @.J TINYINT

DECLARE @.DAYNUMBER VARCHAR(6)

/*INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR(BPCODE)

SELECT DISTINCT LTRIM(RTRIM(CZMMCU)) FROM STAGING.DBO.F0007*/

SET @.CURRENTDATE = (SELECT CONVERT(DATETIME,CAST(DATE AS VARCHAR(10))) FROM PRESENTATIONEUROPE.DBO.CALENDAR

WHERE CURRENTDAY = 0 )

--Check for first time execution and set start date accordingly

IF (SELECT MAX(DATE) FROM PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR) IS NOT NULL

SET @.STARTDATE = CONVERT(DATETIME,(SELECT CONVERT(VARCHAR(8),MAX(DATE)) FROM

PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR))

ELSE

SET @.STARTDATE = (SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4),(CAST(MIN(CZYR)AS INT) + 2000))+'-'+

CONVERT(VARCHAR(2),MIN(CZMT))+'-01') FROM STAGING.DBO.F0007 WHERE CZYR = (SELECT MIN(CZYR) FROM STAGING.DBO.F0007))

--GET THE NO OF MONTH DIFFERENCE

SET @.NOOFMONTHS = DATEDIFF(MM,@.STARTDATE,@.CURRENTDATE)

SET @.I = 1

SET @.LOOPDATE = @.STARTDATE

DECLARE @.LOOPDATE2 INT

DECLARE @.QUERY VARCHAR(500)

WHILE (@.I<=@.NOOFMONTHS)

BEGIN

SET @.LOOPDATE2 = (CONVERT(VARCHAR(8),CONVERT(DATETIME, CONVERT(VARCHAR(4),YEAR(@.LOOPDATE))

+'-'+ CONVERT(VARCHAR(2),MONTH(@.LOOPDATE))+'-01'),112))

SET @.J = 1

WHILE (@.J<=31)

BEGIN

IF (@.J<10)

BEGIN

SET @.QUERY = 'INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR (BPCODE, DATE, DATEE1, WORKINGDAY ) SELECT LTRIM(RTRIM(CZMMCU)), @.LOOPDATE2, (SELECT DATEE1 FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE DATE ='+CONVERT(VARCHAR(8),@.LOOPDATE,112)+'),

CZTD0'+CONVERT(CHAR(1),@.J)+' FROM STAGING.DBO.F0007 T1 LEFT OUTER JOIN PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR T2

ON LTRIM (RTRIM(T1.CZMMCU)) = T2.BPCODE AND T2.DATE =' +CONVERT(VARCHAR(8),@.LOOPDATE2)+

'WHERE T2.BPCODE IS NULL AND CZYR='+ SUBSTRING(CONVERT(VARCHAR(4),YEAR(@.LOOPDATE)),3,4)+

'AND CZMT ='+CONVERT(CHAR(2),MONTH(@.LOOPDATE))

END

ELSE

BEGIN

CZTD'+CONVERT(CHAR(1),@.J)+' FROM STAGING.DBO.F0007 T1 LEFT OUTER JOIN PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR T2

ON LTRIM (RTRIM(T1.CZMMCU)) = T2.BPCODE AND T2.DATE =' +CONVERT(VARCHAR(8),@.LOOPDATE2)+

'WHERE T2.BPCODE IS NULL AND CZYR='+ SUBSTRING(CONVERT(VARCHAR(4),YEAR(@.LOOPDATE)),3,4)+

'AND CZMT ='+CONVERT(CHAR(2),MONTH(@.LOOPDATE))

END

SP_EXECUTESQL @.QUERY

SET @.J = @.J+1

END

PRINT @.LOOPDATE

SET @.I = @.I + 1

END

and getting follwing error

Msg 102, Level 15, State 1, Line 62

Incorrect syntax near 'SP_EXECUTESQL'.

|||

use:

exec sp_executesql @.query

How To Set Column Default Value to include Text AND GetDate() ?

GETDATE() works alone as default in a DateTime column, but:

1) How do you include current Date concatenated with text like:

- Column default Value = 'Submitted 2007-07-30 13:15:54.953'

2) is it possible to truncate that DateTime value (deleting the seconds 54.953)

- Final Column default Value = 'Submitted 2007-07-30 13:15'

Thinking Cast or convert but unsuccessful to date:

- 'Submitted ' + Cast(CHAR(16),GETDATE()) ?

I think the short answer here is, 'Yes'; it is just a matter of getting the format that you want. Here is an example:

Code Snippet

declare @.what table (aVc varchar(30) default('Submitted ' + left(convert(varchar, getdate(),120), 16)) )
insert into @.what default values

select * from @.what

/*
aVc
Submitted 2007-07-30 13:15
*/

Giving it another look you might want to try making your default

default('Submitted ' + convert(varchar(16), getdate(),120))

Might simplify the expression a bit.

|||Thanks Kent!

how to set collation to SQL_Latin1_General_CP1_CI_AS at install ?

help
All my USER DB have been created using the following collationL
SQL_Latin1_General_CP1_CI_AS
My "SQL instance 1" uses :
Latin1_General_CI_AS
Im in the process of setting up "SQL instance 2" and cannot find an option
to set the collation to SQL_Latin1_General_CP1_CI_AS
At install the "COLLATION DESIGNATOR and sort order" field shows
LATIN1_GENERAL.
The "SQL collations...." option below shows various options.
I cannot see an options to setup as "SQL_Latin1_General_CP1_CI_AS" or
"Latin1_General_CI_AS".
How can i use the setup wizard to select "SQL_Latin1_General_CP1_CI_AS"
Thanks for any help
Scott
(SQL 2005 standard - english)CP1 specifies code page 1252, for all other code pages the complete code
page number is specified.
CI specifies case-insensitive
AS specifies accent-sensitive.
Sort order ID SQL collation name
52 SQL_Latin1_General_Cp1_CI_AS
For more information about this topic, you can refer to the following link:
http://msdn2.microsoft.com/en-us/library/ms180175.aspx
Ekrem Önsoy
"Scott" <s@.yahoo.co.uk> wrote in message
news:O2pRP4%23CIHA.1212@.TK2MSFTNGP05.phx.gbl...
> help
> All my USER DB have been created using the following collationL
> SQL_Latin1_General_CP1_CI_AS
> My "SQL instance 1" uses :
> Latin1_General_CI_AS
> Im in the process of setting up "SQL instance 2" and cannot find an option
> to set the collation to SQL_Latin1_General_CP1_CI_AS
> At install the "COLLATION DESIGNATOR and sort order" field shows
> LATIN1_GENERAL.
> The "SQL collations...." option below shows various options.
> I cannot see an options to setup as "SQL_Latin1_General_CP1_CI_AS" or
> "Latin1_General_CI_AS".
> How can i use the setup wizard to select "SQL_Latin1_General_CP1_CI_AS"
> Thanks for any help
> Scott
> (SQL 2005 standard - english)
>
>|||thats helpful, many thanks
scott

How to set Client Machine name when using ADO to connect to SQL Server 2000?

When you connect to SQL Server using SQLConnection, how to set client machine name(or IP address) so that you can monitor the process on Server side using Enterprise Manager?

Whatever IP address you provided in the connection string can be used to monitor along with the user id of sql in case mixed mode authentication is there.

Please post more details..

Satya

|||

In your connection string, you can add a name/value pair like

workstation id=MYCOMPUTERNAME

and now when you runsp_who you will see MYCOMPUTERNAME for the value in thehostname column.

You can also set a value for you application like

Application Name=Your Application

within your connection string.

How to set change color for visited links ?

Hello All,
I have a parameterized report (Report1), which has a field that has 'Jump to
Report' property setup. When the user selects their parameters, Report1
displays, say 10 records displayed, and when the user clicks on the 'Jump to
Report' field of the 2nd record from the displayed 10 records, it displays
the second report (Report2). Now this is what I am looking for....When if
the user wants to go back to Report1, is there anyway to show him - by
changing color - the ones he has already viewed and those ones he didn't?
Please help! Thanks in advance.You would have to pass this state back to the original report via a link
('return to report') and parameter. This would not work if you used the back
button on the browser.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"amma" <amma@.discussions.microsoft.com> wrote in message
news:F4EA3D59-9617-4244-B10E-F98136F88770@.microsoft.com...
> Hello All,
> I have a parameterized report (Report1), which has a field that has 'Jump
> to
> Report' property setup. When the user selects their parameters, Report1
> displays, say 10 records displayed, and when the user clicks on the 'Jump
> to
> Report' field of the 2nd record from the displayed 10 records, it displays
> the second report (Report2). Now this is what I am looking for....When
> if
> the user wants to go back to Report1, is there anyway to show him - by
> changing color - the ones he has already viewed and those ones he didn't?
> Please help! Thanks in advance.
>
>|||Brian, Thanks for the reply. I guess my question was not clear..let me try
to explain this better - I have a link in 'Report2' to 'return to report'
(which is Report1 in my case) and that is working ok. I am actually trying
to change the COLOR of the field that I have already clicked to view the
extended report, so that next time when I go back to that report (report1) I
know which one I haven't viewed and which one I have viewed. sorry if I
confuse you...
Thanks in advance.
"Brian Welcker [MSFT]" wrote:
> You would have to pass this state back to the original report via a link
> ('return to report') and parameter. This would not work if you used the back
> button on the browser.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "amma" <amma@.discussions.microsoft.com> wrote in message
> news:F4EA3D59-9617-4244-B10E-F98136F88770@.microsoft.com...
> > Hello All,
> > I have a parameterized report (Report1), which has a field that has 'Jump
> > to
> > Report' property setup. When the user selects their parameters, Report1
> > displays, say 10 records displayed, and when the user clicks on the 'Jump
> > to
> > Report' field of the 2nd record from the displayed 10 records, it displays
> > the second report (Report2). Now this is what I am looking for....When
> > if
> > the user wants to go back to Report1, is there anyway to show him - by
> > changing color - the ones he has already viewed and those ones he didn't?
> > Please help! Thanks in advance.
> >
> >
> >
> >
>
>

How to set celldata and Permissions to Role

Hi All,

I know how to create a role and adding Members(users) to that role by using AMO class library,but i need some sample code like how we can set the database permissions to the role and how we can set the celldata to that particular role.If any one knows about this please send reply immediately.

it is very urgent.

Thanks in advance.

Hi,

I believe the Adventure Works sample AMO application has code for creating permissions (search for CubePermission or DimensionPermission in the code):

http://msdn2.microsoft.com/en-us/library/ms160736.aspx

In general, you can use this 'reverse engineering' trick to get AMO code:

- create the objects you want with the user interface (in BI Development Studio with wizard/editors or in SQL Management Studio); in this case, use the security dialog to setup the database/dimensions/cubes permissions

- then look at the XML: in BI Development Studio, right click on the cube item -> View Code and search for the CubePermission XML fragment to see the CubePermission, similar for DimensionPermission; in SQL Management you can right click on the cube, script Alter or Create, and then find the CubePermission XML fragment

- to be easier to read, copy the CubePermission in a separate file

- there is a 1-to-1 relationship between the AMO classes and properties and the XML, by reading the XML you will know what classes and properties to set; for example, the <CubePermission> element tells you to create in AMO a CubePermission object; the <Name> sub-element corresponds to the Name property

Adrian Dumitrascu

How to set bound connection?

Hi,
I understand that if you want to allow many
concurrent users, one is set the connection to bound
connection and another suggestion is server isolation
level.
May i check with you, how to set the bound
connection?
Is it set it at the startup of the program or every
connection?
Set the isolation level beter or bound connection
better?
Thank you.
regards,
florence
Florence,
AFAIK, bound connections are used only with Extended Stored Procedures. Are
you using them?
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:330001c4a9ef$18edebe0$a401280a@.phx.gbl...
> Hi,
> I understand that if you want to allow many
> concurrent users, one is set the connection to bound
> connection and another suggestion is server isolation
> level.
> May i check with you, how to set the bound
> connection?
> Is it set it at the startup of the program or every
> connection?
> Set the isolation level beter or bound connection
> better?
> Thank you.
> regards,
> florence
>

How to set bound connection?

Hi,
I understand that if you want to allow many
concurrent users, one is set the connection to bound
connection and another suggestion is server isolation
level.
May i check with you, how to set the bound
connection?
Is it set it at the startup of the program or every
connection?
Set the isolation level beter or bound connection
better?
Thank you.
regards,
florenceFlorence,
AFAIK, bound connections are used only with Extended Stored Procedures. Are
you using them?
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:330001c4a9ef$18edebe0$a401280a@.phx.gbl...
> Hi,
> I understand that if you want to allow many
> concurrent users, one is set the connection to bound
> connection and another suggestion is server isolation
> level.
> May i check with you, how to set the bound
> connection?
> Is it set it at the startup of the program or every
> connection?
> Set the isolation level beter or bound connection
> better?
> Thank you.
> regards,
> florence
>

HOW to set Authentication Using OSQL

I would like to change the authentication method for an MSDE instance. How
do I do it via OSQL ?
This I need when there is no EM installed. I would like to turn the default
authentication from Windows to Mixed mode.
I searched BOL but it talked about using EM.
Thanks in advance for your input.
MacYou can switch MSDE to mixed mode during installation and after that.
Here it is
http://support.microsoft.com/default.aspx?scid=kb;en-us;319930&Product=sql#5
Regards
---
All information provided above AS IS.
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:bnu8sh$20ag$1@.si05.rsvl.unisys.com...
> I would like to change the authentication method for an MSDE instance.
How
> do I do it via OSQL ?
> This I need when there is no EM installed. I would like to turn the
default
> authentication from Windows to Mixed mode.
> I searched BOL but it talked about using EM.
> Thanks in advance for your input.
> Mac
>|||Thanks for the quick response!! That worked.
"SkyWalker" <tcp_43@.hotmail.com_TAKETHISOFF> wrote in message
news:%23o1rkv9nDHA.372@.TK2MSFTNGP11.phx.gbl...
> You can switch MSDE to mixed mode during installation and after that.
> Here it is
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;319930&Product=sql#5
>
> Regards
> ---
> All information provided above AS IS.
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:bnu8sh$20ag$1@.si05.rsvl.unisys.com...
> > I would like to change the authentication method for an MSDE instance.
> How
> > do I do it via OSQL ?
> > This I need when there is no EM installed. I would like to turn the
> default
> > authentication from Windows to Mixed mode.
> > I searched BOL but it talked about using EM.
> >
> > Thanks in advance for your input.
> > Mac
> >
> >
>

How to set ANSI_NULLS on on a existing table?

Hi,
I was trying to create an index on the indexed view, when I got this
error
Msg 1935 : Cannot create index.Object 'tablename' was created with the
following SET option off: ANSI_NULLS.
Is there anyway inside the database to set ANSI_NULLS on for the
existing table?
Thanks a lot for your help.
AJ> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
You'll need to recreate the table with SET ANSI_NULL ON.
Hope this helps.
Dan Guzman
SQL Server MVP
<aj70000@.hotmail.com> wrote in message
news:1144964972.095350.55480@.t31g2000cwb.googlegroups.com...
> Hi,
> I was trying to create an index on the indexed view, when I got this
> error
> Msg 1935 : Cannot create index.Object 'tablename' was created with the
> following SET option off: ANSI_NULLS.
> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
> Thanks a lot for your help.
> AJ
>|||Hi Dan,
Thanks for the reply. that is a bummer since I have atleast 12 tables
that i need to use for indexed view. and these tables contain financial
information.
AJ|||aj70...@.hotmail.com wrote:
> Hi Dan,
> Thanks for the reply. that is a bummer since I have atleast 12 tables
> that i need to use for indexed view. and these tables contain financial
> information.
> AJ
Were your tables created with ANSI_NULLS OFF by design or due to
ignorance? Unfortunately, ignorance is the usual reason. Take care
always to have ANSI_NULLS set to ON when creating tables or other
objects.
Note that Enterprise Manager defaults to ANSI_NULLS OFF when it's
installed. Those people who create tables with Enterprise Manager often
forget to change that setting first. Your procs and other objects may
suffer the same problem if you create them in EM. The smartest option
is to avoid EM altogether. Don't use EM to create objects.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to set ANSI_NULLS on on a existing table?

Hi,
I was trying to create an index on the indexed view, when I got this
error
Msg 1935 : Cannot create index.Object 'tablename' was created with the
following SET option off: ANSI_NULLS.
Is there anyway inside the database to set ANSI_NULLS on for the
existing table?
Thanks a lot for your help.
AJ> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
You'll need to recreate the table with SET ANSI_NULL ON.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<aj70000@.hotmail.com> wrote in message
news:1144964972.095350.55480@.t31g2000cwb.googlegroups.com...
> Hi,
> I was trying to create an index on the indexed view, when I got this
> error
> Msg 1935 : Cannot create index.Object 'tablename' was created with the
> following SET option off: ANSI_NULLS.
> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
> Thanks a lot for your help.
> AJ
>|||Hi Dan,
Thanks for the reply. that is a bummer since I have atleast 12 tables
that i need to use for indexed view. and these tables contain financial
information.
AJ|||aj70...@.hotmail.com wrote:
> Hi Dan,
> Thanks for the reply. that is a bummer since I have atleast 12 tables
> that i need to use for indexed view. and these tables contain financial
> information.
> AJ
Were your tables created with ANSI_NULLS OFF by design or due to
ignorance? Unfortunately, ignorance is the usual reason. Take care
always to have ANSI_NULLS set to ON when creating tables or other
objects.
Note that Enterprise Manager defaults to ANSI_NULLS OFF when it's
installed. Those people who create tables with Enterprise Manager often
forget to change that setting first. Your procs and other objects may
suffer the same problem if you create them in EM. The smartest option
is to avoid EM altogether. Don't use EM to create objects.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to set and use SQL Server Express 2005 after installation?

I have never used SQL Server, except that I did use MS Access, so after I downloading and installing the SQL Server Express 2005 ... I really do not know how to make it works!

1. Let say I would like to create a table at my local computer (1st) as a Server, how I do it (configuration)?

2. Then I have a second PC, as a client how I link to that table?

Might be they are 2 silly questions, but I would like to try out the new SQL Server Express and compare what are the better features with MS Access!

Thanks to any help

hi,

Anh Truong wrote:

I have never used SQL Server, except that I did use MS Access, so after I downloading and installing the SQL Server Express 2005 ... I really do not know how to make it works!
1. Let say I would like to create a table at my local computer (1st) as a Server, how I do it (configuration)?

SQLExpress, as SQL Server as well, is "just" a database engine (actually lot more than just that), but is not "an application" as well as you consider Access... actually, in Access world, the situation is the same as JET is the database engine and Access is the application intended to manage and interact with JET database(s)...

so, in Access, you just open "Access" and create your own database(s) and database's objects, where SQL Server/SQLExpress does not provide an ambient like that.. they feature other management tools... command line tools like SqlCMD.exe, or visual tools like SQL Server Management Studio (SQL Server Management Studio Express, for SQLExpress edition)... with these UI tools, you can visually interact with your instance, manage database(s) and database's object(s)... or, like in SqlCMD.exe, you can execute straigth Transact-SQL commands to perform both DDL and DML commands...

2. Then I have a second PC, as a client how I link to that table?

you are still "thingking" in term of JET/Access databases, a file based engine, where SQL Server/SQLExpress is a traditional client/server based architecture.. you do not create objects on a client... you always create them on the file system of the computer running the SQL Server instance, as you do not directly interact with the related files, but with the "server" only... it's up to the server to manage and interact with the underlying physical files..

as regard "remote" connections, you do not "link that tables"... you connect to the remote server, interacting with the desired database, querying relative objects like tables, procedures, views, .....

just remember SQLExpress installs by default disabling network protocols and remote connections... use SQL Server Configuration Manager to enable the desired network protocol, and Surface Area Configuration to enable remote connections over TCP/IP..
actually even managing the eventual FireWall comes to play, where you have to enable connections over the database engine used port (or binaries) and SQLBrowser port as well (UDP1434)..

SQLBrowser is an additional service used to resolve connections to Named Instances.. it listen on UDP1434 for incoming connections initiated by remote clients, it the queryies the Named Instance for it's used TCP/IP port and redirect the incoming connection to that port the instance is listening on..

regards

How to set an alert to monitr the data file

Hi Guys,
I want to setup a alert that monitor my database data file. When data file
reaches 2/3 full, then produce an alert that inform me by sending me an email
or paging. How to do that in sql server 2005.
Thanks,Iter
Do you mean to examine a datafile growth? There are also events in the
Profiler as weell as writing your own script to compare sizes.
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:8623EC2E-706B-4EB7-8392-5A032043EF06@.microsoft.com...
> Hi Guys,
> I want to setup a alert that monitor my database data file. When data file
> reaches 2/3 full, then produce an alert that inform me by sending me an
> email
> or paging. How to do that in sql server 2005.
> Thanks,
>

How to set an alert to monitr the data file

Hi Guys,
I want to setup a alert that monitor my database data file. When data file
reaches 2/3 full, then produce an alert that inform me by sending me an email
or paging. How to do that in sql server 2005.
Thanks,
Iter
Do you mean to examine a datafile growth? There are also events in the
Profiler as weell as writing your own script to compare sizes.
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:8623EC2E-706B-4EB7-8392-5A032043EF06@.microsoft.com...
> Hi Guys,
> I want to setup a alert that monitor my database data file. When data file
> reaches 2/3 full, then produce an alert that inform me by sending me an
> email
> or paging. How to do that in sql server 2005.
> Thanks,
>

How to set an action?

Hi,
I want to link to a website (like www.google.com) when user click any cell in cube browser.

Then I set an action, the setting detail as following:
Target Type: Cells
Target Object: All Cells
Action Content Type: URL
Action expression: www.google.com

But it's not work fine. When I click any cell in cube browse there is an error message.

Is any problem in my setting detail? especially Action expression?

thanks,You should put double quotes around literal srings in the action expression, so:

"www.google.com"|||Thanks. I add double quotes to it. There is no error message.

But I click the cell, there is no response.
I thought the action would be open a browser and link to google.
Why the action is no any response?|||Try the full URL: "http://www.google.com", because certain safety options can block browsing if the URL doesn't start with "http://" or "https://"

How to set affinity for SqlExpress ?

I am using "sp_configure" to set the affinity for SqlExpress. I am using 8-cpu machine. When i check configuration "affinity mask" is set 0 (meaning 1st cpu by default) and "affinity I/O mask" is set to 64(7th cpu). If i try to set "affinity mask" to any value other than 64(7th cpu), it thorws error and doesn't allow me to set affinity value.

Here is what i am doing.

"affinity I/O mask" is already set to 64(7th cpu).

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure "affinity mask" , 128 setting affinity 8th cpu.

RECONFIGURE

If i run above statements it gives error like SqlExpress can't run on more than one cpu.

if i set the "affinity mask" to 64, it doesn't give any error but it doesn't work as expected. Am i missing something or doing something wrong ?

SQL Server 2005 Express use only one CPU.

http://blogs.msdn.com/czhower/archive/2006/01/06/510192.aspx

|||

Agreed - SQL Express only uses one CPU. But how does one set up express to always use lets say processor 6 ?

Regards,

Avinash

|||

Are you restarting SQL Server after making the change?

Apparently SQLExpress requires that.

http://msdn2.microsoft.com/en-us/library/ms187104.aspx

|||

Yes - but I guess my troubles are different. Let me try and clarify.

By default the I/O affinity mask is set to 64 and affinity mask to 0.

Lets say I want express to use processor 7 and so I will need to update affinity mask to 64. But then there seems to be a rule that disallows both the i/o affinity and processor affinity to be set to the same processor. So the run time value may never be set to 64 even with the Configure with override option. But you cant also change the affnity on the I/O mask because sql express only supports one processor.

Seems like a catch 22 situation to me. Ofcourse I may be missing something here.

Thanks,

Avinash

|||
Does appear to be a conundrum.

Both affinity I/O mask and affinity mask should be 0 by default.
Not sure why you're getting 64 on the affinity I/O.

Are you getting any messages in the SQL Error Log?|||

Lemme check. Dont remember seeing any though.

Thanks,

Avinash

How to set affinity for SqlExpress ?

Here is what i am doing.

"affinity I/O mask" is already set to 64(7th cpu).

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure "affinity mask" , 128 setting affinity 8th cpu.

RECONFIGURE

If i run above statements it gives error like SqlExpress can't run on more than one cpu.

if i set the "affinity mask" to 64, it doesn't give any error but it doesn't work as expected. Am i missing something or doing something wrong ?

SQL Server 2005 Express use only one CPU.

http://blogs.msdn.com/czhower/archive/2006/01/06/510192.aspx

|||

Agreed - SQL Express only uses one CPU. But how does one set up express to always use lets say processor 6 ?

Regards,

Avinash

|||

Are you restarting SQL Server after making the change?

Apparently SQLExpress requires that.

http://msdn2.microsoft.com/en-us/library/ms187104.aspx

|||

Yes - but I guess my troubles are different. Let me try and clarify.

By default the I/O affinity mask is set to 64 and affinity mask to 0.

Seems like a catch 22 situation to me. Ofcourse I may be missing something here.

Thanks,

Avinash

|||
Does appear to be a conundrum.

Both affinity I/O mask and affinity mask should be 0 by default.
Not sure why you're getting 64 on the affinity I/O.

Are you getting any messages in the SQL Error Log?|||

Lemme check. Dont remember seeing any though.

Thanks,

Avinash

How to set Access Workgroup

My package is to copy data from an Access 97 db into a SQL2k5 table.

Problem is that the 'mdb' is subscribed to a 'mdw' workgroup and I have been unable to workout / read how to get the SSIS manager to join the workgroup. Connection fails because "...you don't have necessary permission..."

I was able to do this previously for SQL 2k by messing with the registry, but I'm now on unfamiliar teritory.

Any answers? Cheers.

Open the connection manager UI, and select the All button. Within there you get the Jet OLEDB:System Database propery, which you point at your MDW. You could also just change the connection string directly from the properties grid, or use an expression or configuration if the location can be derived or just change.

|||

Connection works. Although I found that I had to specify the password in both the 1st dialogue and security:password item in the 'All' page.

I'm now getting a unicode conversion failure - I've seen other pastings about this so I'll follow those.

Many Thanks.

How To Set a Variable During an Insert Into Select From

Hello.
I'm inserting rows into a table that I retrieve from another table.
There's a lot of data manipulation going on during this process.
For 10 columns in the Select From portion I'm using a CASE statement that
starts with CASE
WHEN Left(Discount_Specification, 2)= @.PF THEN etc.
END,
Instead of doing the "Left" 10 times (10 * 8 million rows in the "From"
table!) I though of setting a variable: Set @.MyVar =
Left(Discount_Specification, 2) and then
saying WHEN @.MyVar = @.PF etc.
I just don't know where in the logic to place this Set @.MyVar so it works
for each row that's inserted.
TIA,
RitaHard to say without DDL, but perhaps something like:
INSERT INTO ...
SELECT ds, ds + 'a', col2
FROM
(
SELECT LEFT(Discout_Specification, 2) AS ds, col2 FROM tbl
) AS t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:A07835AD-AAC2-4225-BA8C-FDA70C1C0631@.microsoft.com...
> Hello.
> I'm inserting rows into a table that I retrieve from another table.
> There's a lot of data manipulation going on during this process.
> For 10 columns in the Select From portion I'm using a CASE statement that
> starts with CASE
> WHEN Left(Discount_Specification, 2)= @.PF THEN etc.
> END,
> Instead of doing the "Left" 10 times (10 * 8 million rows in the "From"
> table!) I though of setting a variable: Set @.MyVar =
> Left(Discount_Specification, 2) and then
> saying WHEN @.MyVar = @.PF etc.
> I just don't know where in the logic to place this Set @.MyVar so it works
> for each row that's inserted.
> TIA,
> Rita
>|||Hi Tibor,
Thanks for your response.
I'm trying to figure out how to use it along with a CASE statement.
Here's my code:
INSERT INTO MyTable(
Col1,
Col2,
etc.)
SELECT
CASE
WHEN Left(SM.Discount_Specification, 2) IN (@.P, @.L) THEN
Something
ELSE 1
END,
CASE
WHEN Left(SM.Discount_Specification, 2) = @.K THEN
SomethingElse
ELSE 1
END,
Etc.
From MyTable
Thanks,
Rita
"Tibor Karaszi" wrote:

> Hard to say without DDL, but perhaps something like:
> INSERT INTO ...
> SELECT ds, ds + 'a', col2
> FROM
> (
> SELECT LEFT(Discout_Specification, 2) AS ds, col2 FROM tbl
> ) AS t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RitaG" <RitaG@.discussions.microsoft.com> wrote in message
> news:A07835AD-AAC2-4225-BA8C-FDA70C1C0631@.microsoft.com...
>|||Is this a "lazy programmer doesn't want to type all those keystrokes" issue
or something else? It is possible that "Left(SM.Discount_Specification, 2)"
indicates a schema issue. If so, you should consider a change to the schema
to unbind the two attributes currently stored in the Discount_Specification
column. This can be done permanently via the addition of another column
(and the movement of the associated information), via a view, or via a
computed column, via a udf, etc. You can also do this via a derived table
within this particular query.
insert ...
select case when derived_discount in (@.P, @.L) then x else y end,
...
from
(select Left(SM.Discount_Specification, 2) as derived_discount,
...
from MyTable ) as t1
where ...|||Hi Scott,
No, it's not a "lazy programmer"! :-)
I just thought there may be a more efficient way since I'm dealing with a
large volume of rows (up to 10 million).
Thanks for your reponse. That was what I was looking for.
Rita
"Scott Morris" wrote:

> Is this a "lazy programmer doesn't want to type all those keystrokes" issu
e
> or something else? It is possible that "Left(SM.Discount_Specification, 2
)"
> indicates a schema issue. If so, you should consider a change to the sche
ma
> to unbind the two attributes currently stored in the Discount_Specificatio
n
> column. This can be done permanently via the addition of another column
> (and the movement of the associated information), via a view, or via a
> computed column, via a udf, etc. You can also do this via a derived table
> within this particular query.
> insert ...
> select case when derived_discount in (@.P, @.L) then x else y end,
> ...
> from
> (select Left(SM.Discount_Specification, 2) as derived_discount,
> ...
> from MyTable ) as t1
> where ...
>
>

How to set a value to TEXT column

Hi, everybody,
I tried, to paste a clipboard content into an empty text column,
and then to show the content of the text column
by using a select command, but unfortunatelly the original text was
truncated.
does anyone know, how to correctly set a value into a text type column
using standard SQL Server tools such as Enterprise manager or Query
analyzer (but w i t h o u t writting an insert/update command)?
thanks
Libor
"Libor Forejtnik" <lforejtn@.seznam.cz> wrote in message
news:clqta15kb0graa8rt7ongplh6e5cekn0ok@.4ax.com...
> does anyone know, how to correctly set a value into a text type column
> using standard SQL Server tools such as Enterprise manager or Query
> analyzer (but w i t h o u t writting an insert/update command)?
Sorry, it's not possible. Those tools are not meant to be used for data
entry. You'll have to write INSERTs/UPDATEs.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

How to set a SP run at mid-night ?

(1)I need to insert the data from vfp into sql every mid-night .
I think I can use SP to process the inset statment .
However, How can I set it run at mid-night automically 'Take a look at sql job:
http://msdn.microsoft.com/library/e...tomate_7awj.asp
-oj
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23evjgqtSFHA.3312@.TK2MSFTNGP12.phx.gbl...
> (1)I need to insert the data from vfp into sql every mid-night .
> I think I can use SP to process the inset statment .
> However, How can I set it run at mid-night automically '
>
>|||SQLAgent ?
Gopi
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23evjgqtSFHA.3312@.TK2MSFTNGP12.phx.gbl...
> (1)I need to insert the data from vfp into sql every mid-night .
> I think I can use SP to process the inset statment .
> However, How can I set it run at mid-night automically '
>
>

how to set a schedule to run codes like

backup log <dbname> with truncate_only
thanks
benExpand SQL Server Agent, create a new job, add a job step with that code. However, the question is
why you want to execute that command regularly? If you don't do log backup, set the database to
simple recovery model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<libin918@.gmail.com> wrote in message news:1149613568.316745.80160@.g10g2000cwb.googlegroups.com...
> backup log <dbname> with truncate_only
> thanks
> ben
>|||Thanks Tibor.
Yes. we can setup to simple model. But the thing is we want a full
model and meanwhile we dont want them to grow up too big. it doesnt
make a sense? :)
Thanks again
Tibor Karaszi wrote:
> Expand SQL Server Agent, create a new job, add a job step with that code. However, the question is
> why you want to execute that command regularly? If you don't do log backup, set the database to
> simple recovery model.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <libin918@.gmail.com> wrote in message news:1149613568.316745.80160@.g10g2000cwb.googlegroups.com...
> > backup log <dbname> with truncate_only
> >
> > thanks
> >
> > ben
> >|||If you are in full model, then the log is emptied every time you backup the transaction log. So
adding backup log with truncate_only will not add anything to that backup strategy, it will only
break the log backup sequence. Are you doing regular log backups? How often?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<libin918@.gmail.com> wrote in message news:1149686820.193242.3960@.y43g2000cwc.googlegroups.com...
> Thanks Tibor.
> Yes. we can setup to simple model. But the thing is we want a full
> model and meanwhile we dont want them to grow up too big. it doesnt
> make a sense? :)
> Thanks again
> Tibor Karaszi wrote:
>> Expand SQL Server Agent, create a new job, add a job step with that code. However, the question
>> is
>> why you want to execute that command regularly? If you don't do log backup, set the database to
>> simple recovery model.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <libin918@.gmail.com> wrote in message
>> news:1149613568.316745.80160@.g10g2000cwb.googlegroups.com...
>> > backup log <dbname> with truncate_only
>> >
>> > thanks
>> >
>> > ben
>> >
>

how to set a schedule to run codes like

How to set a ReportParameter to the value NULL?

Hi,

how can I set a ReportParameter in C# to NULL? My code is as follows

ReportParameter[] param = new ReportParameter[1];
param[0] = new ReportParameter("LanguageID", "1");
param[1] = new ReportParameter("TopCount", "30");
this.ReportViewer1.ServerReport.SetParameters(param);

Sometimes I want to pass the value NULL for the parameter TopCount so my stored proc can handle a special case. I tried

param[1] = new ReportParameter("TopCount", null);

but it doesn't work...

I know, that there is a possibility for using urls to pass parms. There you have to put the parm name, followed by ":isnull=true".

Thanks,
Dirk

You didn't say exactly how it's not working, but I'm guessing that you're getting a compilation error because the second argument to the ReportParameter constructor is ambiguos.

Try this:

string val = null;
param[1] = new ReportParameter("TopCount", val);

|||Thanks, Chris, thats it!

How to set a relationship here?

How to create a relation between gf_game and gf_gamegenre here? gf_gamegenre is responsible for the relation between a game and it's genre(s). The relationship between gf_genre and gf_gamegenre worked. (http://img361.imageshack.us/my.php?image=relationzl9.jpg)

When I try to set a relationshop between gamegenre and game I'm getting this error:

'gf_game' table saved successfully
'gf_gamegenre' table
- Unable to create relationship 'FK_gf_gamegenre_gf_game'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_gf_gamegenre_gf_game". The conflict occurred in database "gamefactor", table "dbo.gf_game", column 'gameID'.

Thanks for any help!

Shouldn't gf_gamegenre have a composite primary key of genreID and gameID? Then you would link gf.genreID with gf_gamegenre.genreID and gf_game.gameID with gf_gamegenre.genreID

How to set a primary key constraint in a View table using SQL Server 2005

Hi All,

I have created a table using VIEWS in SQL server 2005, now i want to be ablle to edit it in a datagrid but i cannot do so as i there is no primary key!

now does anybody know how to set a primary key constraint so i can set one of the fields as a primary key to identify the row?

many thanks

You can't apply PRIMARY KEY constriant on views.|||

Hi,

Is there any way in which i can state one of the columns to be a unique identifier? as i want to be able to edit my datagrid which is populating a VIEW table from SQL '05 but i cannot do so as i do not have a unique number to identify the row, even though one of the columns in the table is a PK in its original table.

any ideas?

|||

If you just want a unique column to identify a row,newid() function is good for you Smile , which can be used in your CREATE VIEW statements:

use northwind
go
create view v_test as
select o.OrderID, C.ContactName,newid() as ColID
from Orders o join Customers c
on o.CustomerID=C.CustomerID

How to set a photo path when creating table

Hi all,
I am doing a project which need to use Microsoft SQL Server 2000 as my
database. Now, I am creating a table, that I need to put in a photo.
So, I am facing a problem of setting the photo path. Can u all give me
some help on it. Thks.
Besides that, I am using Visual Basic 6.0 to do my GUI, Graphic User
Interface. So, I need to do the database linking between my database in
SQL Server to VB. Can I have the sample code which can do the linking
part. Thks.
Whats photo path?
If you are trying to save an image, use image datatype. or keep it in a
particular folder in server and update the location in database.
Use connection string,
"Driver={SQL Server};Server=ServerName;Database=pubs;Uid=sa;Pwd =asdasd;"
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"jyetying" wrote:

> Hi all,
> I am doing a project which need to use Microsoft SQL Server 2000 as my
> database. Now, I am creating a table, that I need to put in a photo.
> So, I am facing a problem of setting the photo path. Can u all give me
> some help on it. Thks.
> Besides that, I am using Visual Basic 6.0 to do my GUI, Graphic User
> Interface. So, I need to do the database linking between my database in
> SQL Server to VB. Can I have the sample code which can do the linking
> part. Thks.
>
|||Have a look at this.
http://support.microsoft.com/kb/194975/en-us
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jyetying" <yam_jyet_ying@.hotmail.com> wrote in message
news:1158310229.209741.157910@.p79g2000cwp.googlegr oups.com...
> Hi all,
> I am doing a project which need to use Microsoft SQL Server 2000 as my
> database. Now, I am creating a table, that I need to put in a photo.
> So, I am facing a problem of setting the photo path. Can u all give me
> some help on it. Thks.
> Besides that, I am using Visual Basic 6.0 to do my GUI, Graphic User
> Interface. So, I need to do the database linking between my database in
> SQL Server to VB. Can I have the sample code which can do the linking
> part. Thks.
>

How to set a photo path when creating table

Hi all,
I am doing a project which need to use Microsoft SQL Server 2000 as my
database. Now, I am creating a table, that I need to put in a photo.
So, I am facing a problem of setting the photo path. Can u all give me
some help on it. Thks.
Besides that, I am using Visual Basic 6.0 to do my GUI, Graphic User
Interface. So, I need to do the database linking between my database in
SQL Server to VB. Can I have the sample code which can do the linking
part. Thks.Whats photo path?
If you are trying to save an image, use image datatype. or keep it in a
particular folder in server and update the location in database.
Use connection string,
"Driver={SQL Server};Server=ServerName;Database=pubs;
Uid=sa;Pwd=asdasd;
"
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"jyetying" wrote:

> Hi all,
> I am doing a project which need to use Microsoft SQL Server 2000 as my
> database. Now, I am creating a table, that I need to put in a photo.
> So, I am facing a problem of setting the photo path. Can u all give me
> some help on it. Thks.
> Besides that, I am using Visual Basic 6.0 to do my GUI, Graphic User
> Interface. So, I need to do the database linking between my database in
> SQL Server to VB. Can I have the sample code which can do the linking
> part. Thks.
>|||Have a look at this.
http://support.microsoft.com/kb/194975/en-us
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jyetying" <yam_jyet_ying@.hotmail.com> wrote in message
news:1158310229.209741.157910@.p79g2000cwp.googlegroups.com...
> Hi all,
> I am doing a project which need to use Microsoft SQL Server 2000 as my
> database. Now, I am creating a table, that I need to put in a photo.
> So, I am facing a problem of setting the photo path. Can u all give me
> some help on it. Thks.
> Besides that, I am using Visual Basic 6.0 to do my GUI, Graphic User
> Interface. So, I need to do the database linking between my database in
> SQL Server to VB. Can I have the sample code which can do the linking
> part. Thks.
>

Friday, February 24, 2012

Blog Archive

About Me