I have datalayer.pdb file is uploaded on the server, I want to make some changes to the table in database and want to upload it again. can anyone tell me how do I make changes to the programming in that datalayer file?
View 1 RepliesI have a table(tblUser) on SQL Server. I like to create a text file somewhere on the SQL Server after a new record is inserted into the table(tblUser). The content of the text file should be the new record. Is there any way to do that?
Thanks!
Regards,
Kevin Jin
I want to execute a vb.net
file
When I make some changes in a table.
How can I handle this situation?
I am using sql server
2005 express edition sujith
I have a bulk insert script. I have a stored procedure. I saved the stored procedure as a .sql file in another folder too. I have another program that can "run external program" and the only files it will run is a .bat or .exe. I want my other program to be able to trigger the stored procedure to run. I think this means I need a .bat file.
Here is my stored procedure:
USE [EricaTraining]
GO
/****** Object: StoredProcedure [dbo].[LoadDailyAdjReport] Script Date: 03/29/2013 10:56:42 ******/
SET ANSI_NULLS ON
GO
[code]....
We have a database called Itemphotos. In this table is a field called 'PID' picture ID. We have a photos directory with files like 2181.jpg, 2182.jpg, 2184.jpg. The number is the ID number for the picture of the part. If a record exist in the database based on the PID "Primary Key" , check the directory to make sure the jpg file exist. Here is the code I have so far.
use [ItemPhotos]
select pid from dbo.itemdata
declare @file_path nvarchar(500)
declare @file_exists
int set @file_path = 'av-sql2c$inetpubphotosmacolaphotos' + [itemdata].[pid]
[code]....
i am trying to run an exe using t-sql command and even through job, but it is giving me issues. But if i double click on the exe it runs fine.
For example i created a bat file just to make a directory - mkdir d:example
double clicking it, a folder is created BUt through
EXEC xp_cmdshell "D:ew.bat"
it gives me error -
'D:ew.bat' is not recognized as an internal or external command,
and when i do with the job with command - @command=N'idhimahajanD$ew.bat',
it gives an error on execution - Executed as user: NT ServiceSQLAgent$PAXAS. The process could not be created for step 1 of job 0x2FCDDE36F160CD4688D2ACE1D748A99E (reason: Access is denied). The step failed.
HiI have a backup device file ... ".bck" whick has grown pretty large.Is there any way I can reduce its size ?ThanksDavid Greenberg
View 4 Replies View RelatedHi friends,
I am working on a application in which,at once i have to make a huge amount of data to insert and delete in my data base.At this time of some transaction my data base log file,shows data base error like the log file of this data base has been full.plese see the sys.databsesd view...like..
Please help me.If you need the exception then i will send you later.
Cheers..
Thanking you
Sadik Ali
I’ve been trying for a while now (won’t say how long), to get BIDS to read a very simple Excel file.
I’m talking SIMPLE!! No matter what I don, SSIS keeps throwing an error, and of course it doesn’t say what the error is, so I can’t really debug it.
I’ve tried this at least 20x with flat files, and it works perfect each time. I’ve done Data Conversions, Sorting, Union All, and several other Transformations; all work perfect.
Trying to used Excel as a data source, is proving to be a mind numbing experience. Of over 20 different attempts, it hasn’t worked even once. I can make it as simple as you can possibly imagine, and SSIS, refuses to even perform the first step (I’m not even trying to create a table in SQL Server anymore).
I have the Excel file path (very simple)
I have the Excel version (very simple)
I have the Connection Manager (very simple)
I have the Sheet name (very simple)
All I can do is see a preview of the Excel sheet before the process runs. As soon as I het F5, I get an instant error, for no reason whatsoever, and no debugging options,whatsoever.
I have following job script. I need to make file connection and db connection passing in job as parameter.
where in following script is should pass it.
if i am passing here but when i change path it still getting old path in package.what i needs to change in package.also how to make connection dynamic so in each env when we deploy it will automatically change. We have sql job script for each env.
I have the following code in an SSIS package and I get the following error:
Error: 0x0 at Unzip downloaded file, Boolean ReadGzipHeader(): Unable to decompress C:ETLPOSDataIngramWeeklyINVEN.zip; The magic number in GZip header is not correct. Make sure you are passing in a GZip stream.
Error: 0x4 at Unzip downloaded file: The Script returned a failure result.
Task failed: Unzip downloaded file
The zip file unzips perfectly using winzip utility, so that file is definitely not a problem here.
The code I am using in a script task is as follows:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text
Imports System.IO.Compression
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = Dts.Results.Success
Dim success As Boolean = True
Dim workFilePath As String
workFilePath = Dts.Variables("zipFilePath").Value.ToString()
If File.Exists(workFilePath) Then
If Not workFilePath.EndsWith(".zip") Then
Dts.Events.FireInformation(0, "", workFilePath + " is not compressed; skipping decompression", Nothing, -1, True)
Return
End If
Dim uncompressedFileName As String
Dim bytes(Int16.MaxValue) As Byte
Dim n As Integer = 1
Try
uncompressedFileName = Dts.Variables("unCompressedFileName").Value.ToString()
Dts.Events.FireInformation(0, "", "decompressing " + workFilePath + " to " + uncompressedFileName, Nothing, -1, True)
Using writer As New FileStream(uncompressedFileName, FileMode.Create)
Using compressedStream As Stream = File.Open(workFilePath, FileMode.Open, FileAccess.Read, FileShare.None)
Using unzipper As New GZipStream(compressedStream, CompressionMode.Decompress)
Do Until n = 0
n = unzipper.Read(bytes, 0, bytes.Length)
writer.Write(bytes, 0, n)
Loop
unzipper.Close()
End Using
compressedStream.Close()
End Using
writer.Close()
End Using
Catch ex As Exception
Dts.Events.FireError(0, ex.TargetSite().ToString(), "Unable to decompress " + workFilePath + "; " + ex.Message, Nothing, -1)
success = False
Finally
If success = False And File.Exists(uncompressedFileName) Then
Dts.TaskResult = Dts.Results.Failure
File.Delete(uncompressedFileName)
End If
End Try
Else
Dts.Events.FireError(0, "", workFilePath + " does not exist", Nothing, -1)
Dts.TaskResult = Dts.Results.Failure
Return
End If
End Sub
End Class
Instead of the GZipStream do I have to use some other stream class for a zip file. Please let me know.
Thanks,
Monisha
Can somebody help me convert this SQL2000 Function TO C# Function pleaseCREATE Function dbo.CalculateNextRentDate ( @Rent_Payment_Date datetime, @Frequency varchar(50) , @Number int)RETURNS DATETIMEASbeginDECLARE @NextPaymentDate DatetimeSET @Number = @Number - 1.IF @Frequency = 'month' IF @rent_payment_date = dateadd(month, datediff(month, 0+@Number, @rent_payment_date) + 1, -1) BEGIN SET @NextPaymentDate = dateadd(month, datediff(month, 0, @rent_payment_date) + 2, -1) END ELSE BEGIN SET @NextPaymentDate = dateadd(month, 1+@Number, @rent_payment_date) END return @NextPaymentDateend
View 1 Replies View RelatedOur company has its Departments And Services.
Now We are making it online.
Both have separate email list, phone numbers, and more.
Will I make one table and adds the field Type (Values: D or S).
Or make them separate.
Remember one thing If we merge them then Email And PhoneNumber Table will also me merge
other wise they will also separate.
What is better.
How to make our own DTS package I have to split the data on my own.. Data is very biig almost 30 to 40 million. I need to splitt them into 10k chunks in database and with my desired table name.
Waiting for a +tive reply.
Regards
Shani ;)
hi i have a tabel in my database i tray to generat report for this tabel
this tabel have all this fields:
company_id
emp_no
seq_no
interval_date
in_time
in_type
out_time
out_type
wage_code
status
i want all his colums can be search
but with company_id
like if he enter company_id and emp_no okay give him result if he enter Company_id and interval_date okay give him result
i write this
Code Snippet
SELECT company_id, emp_no, seq_no, interval_date, in_time, in_type, out_time, out_type, wage_code, status
FROM interval
WHERE (company_id LIKE @CompanyID) AND (emp_no LIKE @EmployeeID) OR
(company_id = @CompanyID) AND (interval_date = @IntervalDate) OR
(company_id = @CompanyID) AND (in_time = @InTime) OR
(company_id = @CompanyID) AND (in_type = @InType) OR
(company_id = @CompanyID) AND (out_time = @OutTime) OR
(company_id = @CompanyID) AND (out_type = @OutType) OR
(company_id = @CompanyID) AND (wage_code = @WageCode) OR
(company_id = @CompanyID) AND (status = @Status)
but in report preview it tell me i must enter intrevalDate ?
Hi have have this problem, I have a table called PABX that has all the callings registry and what I need to do is for each client(PABX.cod_client) I have 2 types of calls (VC1, VC2) , and for these types I need to select all the registries chaging the dialed number(PABX.NRTELEFONE) for the new one (TROCAR.NRTELEFONE) and for those client that doesn't need to change select the PABX.NRTELEFONE
is it possible through SQL Server 2000(via stored procedure) or I'll need to do it by my application using a vector ?
Thanks
hiiiiiiiiii I am creating a web application using vb.net in which i m using the concept of classes. now i am done all the code for inserting the values in the database using the class but it is difficult to fetch the values from the database using select command and sending them to a WebForm . i want to know how i send send the values coming from the select command to a datagrid or another web controlif possible provide me a sample code thanks for your help
View 2 Replies View Relatedhello all i have a 2 questions hope that u can help me my first question is: in ms access there was a data type named yes/no and it was a checkbox is there a checkbox data type in sql server 2005?
my second question is i need the connection code between asp.net 2005 and sql server 2005 i searched in here for that code but i got more confuse i found two codes and both are not working so hope u can give me the right connecting code. that's all thanks
hi all,
Now i want to log some information (e.g.time,count...)during SP execution,how can i do it in Sql?
Thanks
Can anyone tell me how I would go about making my SQL server accessable from the Internet, or know of any good tutorials to get me started, I haven't had much luck looking on google.
I need to access an SQL database from one server on another server for a web application.
Thanks
Hi guys, I have a car_race table which has these fields
car_id int
race_id int
b_car_won varchar // can have 'y' or 'n'
I need to know if the car lost the 1st race but won the next race
And example of that table for car_id 1:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 2
b_car_won 'YES'
Now this is the tricky part, the database has some data integrity issues, so this can occur:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
car_id 1
race_id 4
b_car_won 'YES'
If I query I'd get 2 rows where race_id > 1. And I only need the first one, because 3 is the next race.
I need to fetch in a single row if possible, the result of the 1st race and the 2nd race. How can I do this?
Hello!
I have two tables
users and pictures.
table users have a clustered (PK) index on userid
table pictures have a clustered (PK) index on userid
when I do this query:
"select userid from pictures where userid=123"
then It will do a clustered index seek
But If I do any of those:
"select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
or
"select (select userid from pictures where usedid = t1.userid) from users t1"
It will do a clustered index scan.
How can I force it to seek my index instead of scan?
Thanks!
i want to make a database for pharmacy using sql server and VB.NET
so any body can give me illuminations
thanks in advance
george albert
I have a T-SQL query that is used to pull up some data for once-a-day export, just out of curiosity more then anything, is there a way to make this shorter?SELECT DISTINCT u.userId,u.lastName,u.firstName,u.address1,u.address2,u.city,u.state,u.zip,CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)= 0 THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)<> 0 THEN 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) <> 0THEN 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0))WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) <> 0THEN 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0))END CPGkeyFROM [...] JOIN [...]I'm hoping there is something in a way of....If userClass Between 1 and 3 ThenIF COALESCE(u.RetailerNumber_fk,0)= 0 Then 'Corporate'ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))End IF as CPGKey,Thanks in advace.
View 1 Replies View RelatedI need to restore a copy of database to a new db name
i'm trying by creating a new db and restoring by doing
RESTORE DATABASE [userr] FROM DISK = N'D:sqlbackupsuser.bak' WITH FILE = 4, NOUNLOAD, STATS = 10
GO
i'm getting an error
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'userr' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What's the best way to do this?
I thought this would be somewhat easy but I'm having trouble with this one. I have a statement that if 'ACTLABCOST' or 'ACTMATCOST' has a value of 0.00 then I need to make it .00.
Here's the statement:
Select
CONVERT(VARCHAR(10), xn_approveddate, 101) + ' ' + convert(VARCHAR(8), xn_approveddate, 108)as "Approved Date",
WORKTYPE,
[Code]....
If following code, it is ok if I execute part1 and q1 together.
but if I try to execute Q2, I got error
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@str'.
I guess I have to execute part1 and Q2 at same time.
Is there a way to avoid that. I mean after I execute part1 @str will be kept in memory, and I can execut q2 without a problem? (like in SAS)
Thank
/* Part1*/
/* how to make @str global*/
declare @str nvarchar(20);
set @str='%subway%';
/*Q1*/
select *, case regionname when 'telesales' then 't' else 'o' end as rn
from dbo.RPT_ContractDetails
where businessname like @str
/* Q2*/
select contracttypename,regionname, count(*)as cou, sum(fundingamount)as Dollar
from dbo.RPT_ContractDetails
where businessname like @str
group by contracttypename,regionname
Hello!
I would like to backup a database through t-sql into a file.
I do this :
EXEC sp_addumpdevice 'disk', 'mydb','c:mydb.bak'
it tells "Command(s) completed successfully."
but I can not find the file c:mydb.bak ...
but if I do it again I get :
"Msg 15026, Level 16, State 1, Procedure sp_addumpdevice, Line 74
Logical device 'mealmanager' already exists."
:-(
Any idea? I simply want to get a .bak file of the db, that's all.
Thanks a lot for any feedback!
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
hello !
Could someone tell me which collation I have to use for a text field with a unique index, but which should make the diffrence between œ and oe. (Latin1_General_CS_AS does not make a diffrence between them)
Thanks a lot for any feedback !
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
select contractnbr,affcontractnbr,tdd,affrtramount,affturn,
pt=datediff(d,afffirstfundingdate,affRTRBalanceZeroDate),ratio=datediff(d,afffirstfundingdate,affRTRBalanceZeroDate)/afftrun
from #targetfinishedaff
datediff(d,afffirstfundingdate,affRTRBalanceZeroDate) appears two times in this code, Is there a way to make it appear only once?
Thx
I have this code:
USE BDPrincipal
GO
IF OBJECT_ID(N'aquery') is not null
DROP FUNCTION aquery
GO
CREATE FUNCTION aquery()
Returns nvarchar(500)
as
Begin
Declare @var nvarchar(500);
Set @var = 'Select Distinct Description from dbo.tblScanners';
Return @var
end
GO
exec sp_executesql aquery;
I created that code to prove if it works.
But the result doesn´t appear, and the message is:
Command(s) completed successfully.
I need it to work.
Because I need to create a very dinamic query.
Please help me!
I want to make query for average sales price, the records are like this
item, Sales_qty, UnitPrice
A, 10, 1000
A, 20, 1100
A, 5, 1500
B, 1, 1000
B, 2, 1200
and display like this
A, 35, 1128 (35 FROM total qty, 1128 from average sales price)
B, 3, 1133
Thanks in advance
i was wondering if anyone could help me write all this into one or two quries...
alter table testdata add areacode varchar(3),
countystate varchar(35),
citystate varchar(30),
county_name varchar(30),
ltv float
go
UPDATE t1
SET t1.county_name=t2.county
FROM testdata t1
INNER JOIN master.dbo.zip t2
ON t1.statecode+t1.countycode=t2.fips
go
update t1 set
maritalstatus = case maritalstatus
when '1' then 'M'
when '2' then 'S' end,
homeowner = case homeowner
when '1' then 'R'
when '2' then 'T'
when '3' then '9'
when '4' then 'H' end,
areacode = substring(phone,1,3),
citystate = rtrim(city)+' '+state_abbrv,
countystate = rtrim(county_name)+' '+state_abbrv,
ltv = case when estcurhomeval >0 then cast(oo_mtg_amnt as float)/cast(estcurhomeval as float) end
from testdata t1
go
basically, i have to run this against some fairly large tables, so i want to try and get it down to one pass,
instead of three.
Thanks in advance!