In my SQL Database I insert a Google Map link as this
http://maps.google.com/maps?q=42.2362,-71.7439+(ESN=0-10073)
How can I mask it so that it appears in my Web page layout as a hyperlink, for my reports.
Something like: Map This
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
Now i m working in a existing project. I have to do some updations. Inthat project database table contain a table(usergroups) field name as'Name'. When i am trying to insert a new record in (usergoups) tablelike as follows'insert into UserGroups(Name,modusr,moddt) values ('sam',131,'7/23/200610:02:13 PM')The response ll come as follows,Invalid column name 'FirstName'.Invalid column name 'LastName'.The usergroups table structure is as follows,UserGroupId int 4 (PK field)Name varchar 50modusr int 4moddt datetime 8Plz tell the correct insert query to add records for the abovetable....Thanks in advance
View 3 Replies View RelatedI'm want to store a time from a datetimepicker control(format = time) into a sql table. I'm using c# and windows forms with vs2008. Some example code would be nice. Thanks.
// dateTimePicker2
//
this.dateTimePicker2.Format = System.Windows.Forms.DateTimePickerFormat.Time;
this.dateTimePicker2.Location = new System.Drawing.Point(225, 21);
this.dateTimePicker2.Name = "dateTimePicker2";
this.dateTimePicker2.ShowUpDown = true;
this.dateTimePicker2.Size = new System.Drawing.Size(96, 20);
this.dateTimePicker2.TabIndex = 1;
Question?
1. What type should the time field be in the sql table? I want to be able to calculate with it at some time.
Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?
I am a student, and I am so confused by SQL code that calculates incomes of my contract in a year or each month of year.
I have 3 tables:
lodgings_Contract:
id_contract indentity primary,
id_person int,
id_room varchar(4),
day_begin datetime,
day_end datetime,
day_register datetime
money_per_month money
electric:
id_electric indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_electric money,
status bit
Water:
id_Water indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_water money,
status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year'
, Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes'
From lodgings_Contract a
, electric b
, Water c
Where a.id_room = b.id_room
And a.id_room = c.id_room
And b.status = 1
And c.status = 1
Group by Year(day_register)
I have a table that houses fully qualified table names, roughly 15. How can I iterate that table and create a view ? For example, something like this
Code:
Create Table tocreateviewfrom (dbname varchar(100))
Insert Into tocreateviewfrom Values (foxfire.dbo.abcd), (foxfire.dbo.abcde), (foxfire.dbo.abcdf), (foxfire.dbo.abcdg), (foxfire.dbo.abcde), (foxfire.dbo.abcdl)
--Then somehow iterate each value in the field dbname to create a view something like
Create View viewcreatedfromtable As
Select * from foxfire.dbo.abcd
Union All
Select * from foxfire.dbo.abcde
[Code] ....
I tried this, but it is only printing the last result returned not the entire result set
Code:
Declare @database varchar(max), @sql varchar(max)
Declare c1 Cursor For
Select Program
from tocreateviewfrom
Open c1
[Code] .....
I'm trying to get a SQL 7 and 6.5 DB to interact, but while there is no problem in SQL7, I cannot create a table called "Public" or a field called "Primary"!!
Does anyone know why this might be and if so where I might get a list of any other "invalid" names??
Thanks in advance,
Damon
Hello All.
You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar.
And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile.
My below script shows error. I have following records in my TempWeekFile
Week_No WeekCnt
10 1
11 2
12 3
13 4
How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you.
---------------------------------------------------------
if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month')
drop table WeeklySalesToThird_Month
Declare @WeekCount int
Declare @Cnt int
Declare @WeekNo varchar(2)
Select @WeekCount = count(*) from TempWeekFile
Select @WeekCount
Select @Cnt=1
CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month] (
[Grp] [varchar] (30) NULL ,
[SubGrp] [varchar] (30) NULL ,
while @Cnt<=@WeekCount
Begin
select @WeekNo = Week_No from TempWeekFile
where WeekCnt=@Cnt
[Week_@WeekNo] [Money] NULL
@Cnt=@Cnt+1
End
) ON [PRIMARY]
GO
--------------------------------------------------------
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View 2 Replies View RelatedHello Everyone:
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
How do I fix this?
TIA
Hi guys,
I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE
JurnalTransMoves.SUF
SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752
10
60
5752
20
5752
30
5753
400
3000
5753
500
5753
600
5753
700
5753
800
5754
7
15
5754
8
Is there any chance to do that?
Thanks in advance,
Aldo.
Code Snippet
SELECT
Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',
Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',
Accounts.FILTER AS 'ACCOUNTS.FILTER',
Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',
AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',
Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',
CreditTerms.DETAILS AS 'CreditTerms.DETAILS'
CreditTerms.CURRENF AS 'CreditTerms.CURRENF'
CreditTerms.MONTH AS 'CreditTerms.MONTH',
CreditTerms.DAYS AS 'CreditTerms.DAYS',
CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',
CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE
WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'
WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'
ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE
WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE
ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',
JurnalTrans.REF2 AS 'JurnalTrans.REF2',
JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',
JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF',
JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM
JURNALTRANSMOVES AS JurnalTransMoves_1
INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves
INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID
INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID
INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY
LEFT OUTER JOIN ITEMS AS Items
INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY
INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE
LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '3001' And '3020'
AND Accounts.ACCOUNTKEY IN ('123456')
ORDER BY Accounts.ACCOUNTKEY
Hi,
I have source table , fact table and four dim. tables , I have to update a field in fact table from source table.
How can I do it?
thanks...
HI everybody need help on this..
I have two tables below
table1
country countryid
africa ___
usa ___
italy ___
Spain ___
table2
countryid country name
1 africa
2 germany
3 italy
4 usa
I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..
THE RESULT WOULD BE
country countryid
africa 1
usa 4
italy 3
spain 0
thanks
Hi,
I have one table (PermProportionalCosting) that contains employeecode, costcentre and proportionpercentage. An employee may have two or more records in this table, indicating that their salary has been split across multiple costcentres.
Another table (Employee) contains a default costcentre for each employee.
Another table (TransActualHistoricalMaster) contains the hours paid to each employee for each pay period.
My query so far selects all employees and the total hours worked from the TransMaster table, and then selects the costcentre and proportionpercentage from the PermProportionalCosting table. Multiple lines are created for all employees in the PermProportionalCosting table, with each line indicating the proportionpercentage and associated costcentre.
This is my query so far:
SELECT TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode,PermanentProportionalCosting.PercentageSplit,
TransActualHistoricalMaster.OrdHours
FROM TransActualHistoricalMaster
INNER JOIN Employee ON TransActualHistoricalMaster.EmployeeCode = Employee.EmployeeCode
INNER JOIN CostCentre ON Employee.CostCentreCode = CostCentre.CostCentreCode
FULL OUTER JOIN PermanentProportionalCosting ON TransActualHistoricalMaster.EmployeeCode = PermanentProportionalCosting.EmployeeCode
GROUP BY TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode, PermanentProportionalCosting.PercentageSplit, TransActualHistoricalMaster.OrdHours
This next bit is what I am unsure of.
I would like the CostCentre field from the Employee table to go into the PermanentProportionalCosting.CostCentreCode column where the employee doesn't have a record in the PermanentProportionalCosting table. In the above query, there is a null value for each employee that doesn't have a record in the PermanentProportionalCosting table, so I would like my query to replace the null value with the costcentre from the Employee table.
Any assistance would really be appreciated.
Thanks.
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
View 12 Replies View RelatedHello all,
Please help....
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
Any help would be highly appreciated.
Thanks in advance.
How can I save the text of a rich text box in a sql express 2005 datatable ?
Of course I'd like to save the string and the format of the text (bold, color etc...).
Which column type I have to use for this RTF field ?
Thank you.
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies View RelatedHi,
I have a concern about adding a new field to a table with image field - which is huge.
Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field?
Previously this happened to me, and what was advised to put all the big fields at the end of the table.
Thanks.
does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?
was just curious.
-dk
Are Primary Key fields automatically indexed, or do you have to create a seperate index for a PK in order for it to be indexed? I'm using SQL Server 2005.
View 7 Replies View RelatedI have a table where the key is an autonumber. I also have a field which holds the reference of a room eg 0BM1. It is nvarchar. Is there a way I can set this field to duplicates = No, so that my user cannot enter the same room reference more than once. Or do I have to do this check in my asp.net code ?
TIA
I have the following fields in table A:
GL_ID|GL_Name_VC | Amount |Period_TI|Year_SI
===================================================
1000| Inventory| 8,000.00 | 01 | 2005
===================================================
1000| Inventory| -3,000.00 | 02 | 2005
===================================================
1000| Inventory| 5,000.00 | 02 | 2005
===================================================
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
GL_ID | GL_Name_VC |Op Bal|Period_Dr|Period_Cr|Period Bal|Closing Bal
======================================================================
1000 | Inventory |8,000 | 5,000 | -3,000 | 2,000 |10,000
The above report has an Op Bal column which is the sum of all amount in Period 01 in
Year 2005 carried forward as opening balance in Period 02, Period_Dr Column would contain
all positive amount in Period 02 & Period_Cr Column would contain all negative amount
in Period 02. Period Bal is the summation of both Period_Dr & Period_Cr and Closing Bal
column is the summation of Op Bal + Period Bal.
Guys, hope someone out there can help me with the sql command for the above report?
Hello,I would like to create more lines by concatenating values.When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' +'example'> the result is <This is an example> (on the same line).I woul like to get:<Thisisanexample> (each 'word' on a new line, but in 1 field)Whis SQL statement do i have to use?
View 2 Replies View Related
Hi
In Sqlserve we can create dynmic field on sql like:
Select '' as Name from Employee.
It creates a string field.
I want to how to create a Bit field in this way.
Regards
Deepak
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies View RelatedHi
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
Please help me.
Thanks.
Hi -
I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.
Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.
Is there a way to do this with a trigger (or an otherwise elegant way)?
Thanks in advance for ANY help or ideas.
Oliver
Hi there, my situation is
I have a table x with 3 filed
a nvarchar(100), b smalldatetime, c text(16)
. I want to create a view like this:
select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str%
So, I always get a message error said wrong datatype, how can i do, please help me.
I would like to create a field that increments automatically by 1 - to use it as an identifier for a row. This is so dammed simple but I can not find how to do it !
Thank you very much for any help.
By the way I configure SQLExpress with VS2005 and SQL Express Manager.
Thank you very much,
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !